Confluence upgrade fails due to "java.sql.SQLException: ORA-12899: value too large for column"
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When upgrading Confluence, the following error appears in atlassian-confluence.log file:
Caused by: java.sql.SQLException: ORA-12899: value too large for column "CONFPROD5"."AO_9412A1_AONOTIFICATION"."GLOBAL_ID" (actual: 419, maximum: 255)Cause
There is an entry(s) in your Oracle database that is too long.
It may be because of the encoding that is being used in your previous database, which does not match up to the encoding you have set for your new Oracle.
Resolution 1
Truncate all data with column length larger than 255:
UPDATE <table_name> SET <column_name>=SUBSTR(<column_name>,1,255) WHERE LENGHT(<column_name>) > 255;
For example, if the actual error message is "value too large for column "CONFPROD5"."AO_9412A1_AONOTIFICATION"."GLOBAL_ID" (actual: 419, maximum: 255)", the UPDATE statement will look like this:
UPDATE AO_9412A1_AONOTIFICATION SET GLOBAL_ID=SUBSTR(GLOBAL_ID,1,255) WHERE LENGHT(GLOBAL_ID) > 255;
You might need to check if your database collation is supported
Resolution 2
Another alternative is to increase the column size to more than 255 characters:
ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE varchar(600);For example, if you want to set OS_PROPERTYENTRY to 600.
ALTER TABLE OS_PROPERTYENTRY ALTER COLUMN STRING_VAL TYPE varchar(600); When you perform confluence upgrade, the column size might reset and value will be truncated