Details

Type: Bug
Resolution: Unresolved
Priority: Low
Fix Version/s: None
Affects Version/s: 5.9.1, 5.9.2, 5.8.18
Component/s: Server - Upgrade Manager
Labels:
None

Support reference count:
6
Symptom Severity:
Severity 2 - Major
UIS:
2
Bug Fix Policy:
View Atlassian Server bug fix policy

Description

Some Oracle-backed Confluence instances might encounter a fatal error when being upgraded from lower versions to 5.9 or later. Error messages shown in the logs:

2017-06-15 16:18:54,597 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table USER_RELATION add constraint FK_U2URELATION_LASTMODIFIER foreign key (LASTMODIFIER) references user_mapping
2017-06-15 16:18:54,598 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-02267: column type incompatible with referenced column type

2017-06-15 16:18:54,598 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:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1707)
	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1670)
	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:310)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:410)
	at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:167)
	at bucket.core.persistence.hibernate.schema.SchemaHelper.updateSchemaIfNeeded(SchemaHelper.java:173)
	at bucket.core.persistence.hibernate.schema.SchemaHelper.updateSchemaIfNeeded(SchemaHelper.java:154)
	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:139)
	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:4853)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5314)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:753)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:729)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
	at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
	at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)

Root cause

This is caused by a breaking change introduced back in 5.5 in which we updated Hibernate default dialect for Oracle DBMS from net.sf.hibernate.dialect.Oracle9Dialect to net.sf.hibernate.dialect.OracleIntlDialect. The former maps String to Oracle VARCHAR2 while the latter maps it to NVARCHAR2. This mismatch will cause troubles when we try to create any foreign keys from new NVARCHAR2 columns to old VARCHAR2 columns or vice versa. Some of our upgrade tasks in 5.9 do exactly this.

Not all Oracle-backed instances are affected by this bug because the dialect change just applied to new installations. Only when you updated the dialect manually to OracleIntlDialect in confluence.cfg.xml.

How to check if your instances are affected by this bug?

You will be affected by this bug if all below conditions are met

Your instance has been using Oracle ever since.
Your instance was setup using a version smaller than 5.5 e.g. 5.4, 2.5, etc...
You've updated Hibernate dialect to use net.sf.hibernate.dialect.OracleIntlDialect in <CONFLUENCE_HOME_DIR>/confluence.cfg.xml
You're trying to upgrade Confluence either

From version	To version
Any versions smaller than 5.9.1	5.9.1 or later
5.9.1 or 5.9.2	5.9.3 or later

Workaround

There are 2 options (please restore your database to pre-upgrade state before applying any of these):

Just revert the Hibernate dialect to use net.sf.hibernate.dialect.Oracle9Dialect in <CONFLUENCE_HOME_DIR>/confluence.cfg.xml then run the upgrade as normal. Highly recommended, but only do this if you have no plan to upgrade Confluence to version 6.1 or higher before this bug is properly fixed.
Run a set of queries to convert existing text columns from VARCHAR2 to NVARCHAR2. See https://confluence.atlassian.com/kb/confluence-upgrade-fails-because-oracle-could-not-complete-schema-update-838275652.html. There will be some failed queries because of existing foreign key constraints, you need to disable them first, convert the columns then enable them again.

Attachments

Issue Links

causes

CONFSERVER-51935 Implement Oracle database schema to accept VARCHAR2 datatype for Confluence database column.

Gathering Interest

mentioned in: Page Loading...; Page Loading...

Confluence instances using Oracle DBMS may not be able to upgrade to version 5.9 and later