Confluence Upgrade Fails due to ORA-01440
Symptoms
Upgrade to Confluence 3.5.x or higher fails. In this case, Oracle was not the original Confluence database. It was migrated from other database, e.g. from MySQL to Oracle using Oracle SQL Developer.
The following appears in the atlassian-confluence.log:
2011-10-13 18:39:20,739 INFO [main] [confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask] doUpgrade Beginning task to add not null and unique constraints on Content Permissions and Content Permission Set columns
2011-10-13 18:39:20,927 ERROR [main] [confluence.upgrade.ddl.AlterTableExecutor] alterTable Failed to run alter table commands
2011-10-13 18:39:20,989 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@f36a2e failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID number(19,0) not null]; SQL state [72000]; error code [1440]; ORA-01440: column to be modified must be empty to decrease precision or scale
; nested exception is java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale
Diagnosis
Check the table definition of the CONTENT_PERM table and compare it with a fresh Confluence set up on Oracle, specifically on CPS_ID column data type. Here is the default DDL of Confluence 3.5.13's CONTENT_PERM:
CREATE TABLE "CONTENT_PERM"
(
"ID" NUMBER(19,0) NOT NULL ENABLE,
"CP_TYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"USERNAME" VARCHAR2(255 BYTE),
"GROUPNAME" VARCHAR2(255 BYTE),
"CPS_ID" NUMBER(19,0) NOT NULL ENABLE,
"CREATOR" VARCHAR2(255 BYTE),
"CREATIONDATE" DATE,
"LASTMODIFIER" VARCHAR2(255 BYTE),
"LASTMODDATE" DATE,
)It is a problem if the CPS_ID is different from the default. For example:
"CPS_ID" NUMBER(24,0),
Cause
Some database migration tools cannot translate table/column definition correctly, therefore they are different than the default ones. During the upgrade, Confluence tries to alter the column's data type to its default. However, since the column contains numeric data, this is not possible in Oracle and ORA-01440 is thrown.
ORA-01440 | column to be modified must be empty to decrease precision or scale |
Cause | An ALTER TABLE MODIFY statement attempted to decrease the scale or precision of a numeric column containing data. In order to decrease either of these values, the column must contain only NULL values. An attempt to increase the scale without also increasing the precision will also cause this message. |
Action | Set all values in the column to NULL before decreasing the numeric precision or scale. If attempting to increase the scale, increase the precision in accordance with the scale or set all values in the column to NULL first. |
Workaround
Make sure you have a sufficient Production Backup Strategy before performing any of the following workaround.
Workaround 1
Dump the database to an SQL script. Modify the column data type manually to its default, then reimport the modified database dump.
Workaround 2
(Assuming your pre-upgrade state is Confluence 3.4.8)
- Setup a fresh Confluence 3.4.8 using Oracle database
- Export the DDL only using Oracle SQL Developer's Export Tools by unticking the Export Data
- Export the data of your Confluence 3.4.8 production database by unticking the Export DDL
- Import
PROD-NoData.sqlto the new database - Import
PROD-NoDDL.sqlto the new database - Your new database should now have the correct schema structures (e.g. column data types) with the same production data.