Oracle cannot update schema during upgrade

Summary

Upgrading Bamboo 5.13.2 to Bamboo 6.0.3 connected to Oracle 12 will have error "Cannot update schema" when adding foreign key

Environment

Oracle 12.1.0.2

Steps to Reproduce

1. Connect Bamboo 5.13.2 to Oracle server
2. Upgrade Bamboo 5.13.2 to Bamboo 6.0.3

Expected Results

The upgrade will proceed without problem

Actual Results

The below exception is thrown in the atlassian-bamboo.log file:

2017-06-19 17:02:19,389 FATAL [localhost-startStop-1] [UpgradeLauncher] Failed to initialise Bamboo container
com.atlassian.config.ConfigurationException: Cannot update schema
	at com.atlassian.bamboo.hibernate.BambooSchemaHelper.updateSchemaIfNeeded(BambooSchemaHelper.java:35)
	at com.atlassian.bamboo.upgrade.UpgradeLauncher.upgradeAndStartBamboo(UpgradeLauncher.java:84)
	at com.atlassian.bamboo.upgrade.UpgradeLauncher.contextInitialized(UpgradeLauncher.java:42)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4842)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5303)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1407)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1397)
	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:745)
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table EXTERNAL_MEMBERS add constraint FK_8l06alwmlyit7hw9h9yeiy67n foreign key (GROUPID) references GROUPS]
	at org.hibernate.tool.schema.internal.TargetDatabaseImpl.accept(TargetDatabaseImpl.java:59)
	at org.hibernate.tool.schema.internal.SchemaMigratorImpl.applySqlString(SchemaMigratorImpl.java:431)
	at org.hibernate.tool.schema.internal.SchemaMigratorImpl.applySqlStrings(SchemaMigratorImpl.java:420)
	at org.hibernate.tool.schema.internal.SchemaMigratorImpl.applyForeignKeys(SchemaMigratorImpl.java:386)
	at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigrationToTargets(SchemaMigratorImpl.java:214)
	at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:60)
	at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:134)
	at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:101)
	at com.atlassian.bamboo.hibernate.BambooSchemaHelper.updateSchemaIfNeeded(BambooSchemaHelper.java:32)
	... 11 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-02275: such a referential constraint already exists in the table

Notes

• Running the SQL in the following lines in the log will return results that the previous foreign keys still exist:

  2017-06-19 16:57:11,614 INFO [localhost-startStop-1] [AbstractUpgradeManager] 51509 : Drop all FK constraints created by an older Hibernate version (bootstrap)
  2017-06-19 16:57:11,614 INFO [localhost-startStop-1] [AbstractUpgradeManager] ---------------------------------------------------------------------------------
  2017-06-19 16:57:11,621 INFO [localhost-startStop-1] [AbstractDbmsBean] Detected schema: KLFOONG
  ...
  2017-06-19 16:58:34,764 INFO [localhost-startStop-1] [JdbcUtils] Executing: [select TC.CONSTRAINT_NAME, CCU.COLUMN_NAME, TC.CONSTRAINT_TYPE   from ALL_CONSTRAINTS TC   join ALL_CONS_COLUMNS CCU        on TC.TABLE_NAME = CCU.TABLE_NAME        and TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME where upper(TC.TABLE_NAME) = 'EXTERNAL_MEMBERS' and TC.OWNER = 'KLFOONG' ]
  

  CONSTRAINT_NAME	COLUMN_NAME	CONSTRAINT_TYPE
  FK_HBR9I9D2DCT3A205RHH82Y555	EXTENTITYID	R
  FK_O35Y9WAFG8GXRHFEDX6P840A9	GROUPID	R
  SYS_C005380	EXTENTITYID	C
  SYS_C005381	GROUPID	C
  SYS_C005382	EXTENTITYID	P
  SYS_C005382	GROUPID	P

• The same error happens to the following foreign keys:
  • EXTERNAL_MEMBERS - FK_O35Y9WAFG8GXRHFEDX6P840A9
  • EXTERNAL_MEMBERS - FK_HBR9I9D2DCT3A205RHH82Y555
  • LOCAL_MEMBERS - FK_STJXT95AV33TMIYD4XTF68NHC
  • LOCAL_MEMBERS - FK_KBAPW8J5EJXOLEMECF1P86P83
  • VARIABLESTOAUTOINCREMENT - FK_I5XELAPGKULQ7LQOVOWE4IRBC

Workaround

1. Run the following SQL manually to delete the foreign keys:

   ALTER TABLE EXTERNAL_MEMBERS DROP CONSTRAINT FK_O35Y9WAFG8GXRHFEDX6P840A9;
   ALTER TABLE EXTERNAL_MEMBERS DROP CONSTRAINT FK_HBR9I9D2DCT3A205RHH82Y555;
   ALTER TABLE LOCAL_MEMBERS DROP CONSTRAINT FK_STJXT95AV33TMIYD4XTF68NHC;
   ALTER TABLE LOCAL_MEMBERS DROP CONSTRAINT FK_KBAPW8J5EJXOLEMECF1P86P83;
   ALTER TABLE VARIABLESTOAUTOINCREMENT DROP CONSTRAINT FK_I5XELAPGKULQ7LQOVOWE4IRBC;
   

2. Restart Bamboo to continue with the upgrade