Confluence Upgrade Fails because Oracle could not Complete Schema Update
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Problem
Upgrading Confluence instance to version 5.9.x and above failed. The following errors appear in atlassian-confluence.log:
2016-08-05 11:29:18,350 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-02267: column type incompatible with referenced column type
2016-08-05 11:29:18,350 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLSyntaxErrorException: ORA-02267: column type incompatible with referenced column type
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
...
2016-08-05 11:29:18,365 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: com.atlassian.config.ConfigurationException: Cannot update schema
com.atlassian.confluence.upgrade.UpgradeException: com.atlassian.config.ConfigurationException: Cannot update schema
at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:155)
at com.atlassian.confluence.plugin.PluginFrameworkContextListener.launchUpgrades(PluginFrameworkContextListener.java:118)
at com.atlassian.confluence.plugin.PluginFrameworkContextListener.contextInitialized(PluginFrameworkContextListener.java:77)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4842)
...
Caused by: com.atlassian.config.ConfigurationException: Cannot update schema
at bucket.core.persistence.hibernate.schema.SchemaHelper.updateSchemaIfNeeded(SchemaHelper.java:175)
at bucket.core.persistence.hibernate.schema.SchemaHelper.updateSchemaIfNeeded(SchemaHelper.java:154)
at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:139)
... 11 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-02267: column type incompatible with referenced column type
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
...
2016-08-05 11:29:18,381 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
2016-08-05 11:29:18,381 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: Cannot update schemaDiagnosis
Environment
- Database: Oracle Database
Diagnostic Steps
Ensure that the Oracle Database dialect used is correct. You may check this from the
<Confluence-Home>/confluence.cfg.xmlfile.
The dialect should be as per follow:<property name="hibernate.dialect">net.sf.hibernate.dialect.OracleIntlDialect</property>Identify columns with the incorrect data type by executing the following SQL query:
SELECT * FROM all_tab_columns WHERE owner='<Oracle-DB-Username>' AND data_type IN ('VARCHAR2');
Cause
The pre-upgrade Oracle DB dialect uses column data types that do not support Unicode. In this case, it is VARCHAR.
Resolution
Modify each data type manually by following the steps below:
Please perform these changes in a staging/test environment first to minimize the impact to your Confluence users
- Back up the following:
<Confluence-Installation>directory.<Confluence-Home>directory.- Full Confluence Database.
- Disable the constraints in your Confluence Oracle DB.
Run the following SQL script to generate an
ALTER TABLEstatement for each column with incorrect data types.SELECT 'ALTER TABLE '||table_name||' MODIFY '||column_name||' NVARCHAR2('||data_length||');' FROM user_tab_columns WHERE data_type IN ('VARCHAR2');Execute each of the generated queries to fix the problematic columns' data types.
Some of the affected columns' name may be one of the Oracle Reserved Keywords, e.g.
USER.In this case, you may use double quotes to exclude it from being taken as one of Oracle Reserved Keywords as per below:
ALTER TABLE AO_9412A1_AOTASK MODIFY "USER" NVARCHAR2(255); ALTER TABLE AO_9412A1_AONOTIFICATION MODIFY "USER" NVARCHAR2(255);- Execute the SQL query from the Diagnosis section again to double check if the problematic columns' data types have been corrected or not.
- Re-enable back the DB constraints.
- Follow the steps provided in our Upgrading Confluence documentation to upgrade your Confluence instance to the latest Confluence version.