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 Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.

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 Step

  1. First, please 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. Please 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. First, please backup the following:
    • <Confluence-Installation> directory.
    • <Confluence-Home> directory.
    • Full Confluence Database.
  2. Next, you may then disable the constraints in your Confluence Oracle DB.
  3. Once you've done this, 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. Once you've generated the SQL queries, please then execute each one of them 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. Then, please execute the SQL query from the Diagnosis section again to double check if the problematic columns' data types have been corrected or not.
  6. After this, please re-enable back the DB constraints.
  7. Lastly, please follow the steps provided in our Upgrading Confluence documentation to upgrade your Confluence instance to the latest Confluence version.


Last modified on Aug 12, 2016

Was this helpful?

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