Confluence Upgrade Fails because Oracle could not Complete Schema Update

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

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 schema

Diagnosis

Environment

  • Database: Oracle Database

Diagnostic Steps

  1. Ensure that the Oracle Database dialect used is correct. You may check this from the <Confluence-Home>/confluence.cfg.xml file.
    The dialect should be as per follow:

    <property name="hibernate.dialect">net.sf.hibernate.dialect.OracleIntlDialect</property>
  2. 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

  1. Back up the following:
    • <Confluence-Installation> directory.
    • <Confluence-Home> directory.
    • Full Confluence Database.
  2. Disable the constraints in your Confluence Oracle DB.
  3. Run the following SQL script to generate an ALTER TABLE statement 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');
  4. 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);
  5. Execute the SQL query from the Diagnosis section again to double check if the problematic columns' data types have been corrected or not.
  6. Re-enable back the DB constraints.
  7. Follow the steps provided in our Upgrading Confluence documentation to upgrade your Confluence instance to the latest Confluence version.


Last modified on Jun 20, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.