Confluence upgrade fails due to "java.sql.SQLException: ORA-12899: value too large for column"
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