[BAM-18689] Cannot upgrade to Bamboo 6.1.1 on Microsoft SQL Server due to "not a constraint" error

Type: Bug
Resolution: Fixed
Priority: High
Fix Version/s: 6.2.0, 6.1.4
Affects Version/s: 6.0.4, 6.1.1
Component/s: Database (MS SQL), Upgrading
Labels:
- bamboo-bugfix-kanban-candidate

Symptom Severity:
Severity 2 - Major
Bug Fix Policy:
View Atlassian Server bug fix policy

Summary

Cannot upgrade to Bamboo 6.1.1 (and presumably 6.0.4) on Microsoft SQL Server due to "not a constraint" error.

Environment

Microsoft SQL Server with case sensitive collation

Steps to Reproduce

Install Bamboo 5.13.2 on a Microsoft SQL database with CS_AS collation.
Upgrade the instance to Bamboo 6.1.1

Expected Results

Upgrade succeeds.

Actual Results

The below exception is thrown in the <bamboo-home>/atlassian-bamboo.log file:

2017-09-18 10:51:03,765 TRACE [localhost-startStop-1] [JdbcUtils] Executing: [alter table EXTERNAL_MEMBERS drop constraint FK_O35Y9WAFG8GXRHFEDX6P840A9] took 9.784 ms
2017-09-18 10:51:03,771 ERROR [localhost-startStop-1] [AbstractUpgradeManager] java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: 'FK_O35Y9WAFG8GXRHFEDX6P840A9' is not a constraint.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: 'FK_O35Y9WAFG8GXRHFEDX6P840A9' is not a constraint.
        at com.google.common.base.Throwables.propagate(Throwables.java:160)
        at com.atlassian.bamboo.upgrade.tasks.v5_16.UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.lambda$dropConstraint$4(UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.java:59)
        at java.util.Optional.ifPresent(Optional.java:159)
        at com.atlassian.bamboo.upgrade.tasks.v5_16.UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.dropForeignKeyConstraintIfExists(UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.java:47)
        at com.atlassian.bamboo.upgrade.tasks.v5_16.UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.lambda$null$1(UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.java:38)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
        at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:175)
        at java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:948)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
        at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
        at com.atlassian.bamboo.upgrade.tasks.v5_16.UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.lambda$doUpgrade$2(UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.java:38)
        at com.atlassian.bamboo.upgrade.AbstractBootstrapUpgradeTask.withDatabaseConnection(AbstractBootstrapUpgradeTask.java:69)
        at com.atlassian.bamboo.upgrade.tasks.v5_16.UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.doUpgrade(UpgradeTask51605RemoveOldForeignKeysFromRenamedTables.java:34)
        at com.atlassian.bamboo.upgrade.AbstractUpgradeManager.runUpgradeTask(AbstractUpgradeManager.java:175)
        at com.atlassian.bamboo.upgrade.BootstrapUpgradeManagerImpl.doUpgrade(BootstrapUpgradeManagerImpl.java:57)
        at com.atlassian.bamboo.setup.DefaultBootstrapManager.performPersistenceUpgrade(DefaultBootstrapManager.java:285)
        at com.atlassian.config.bootstrap.DefaultAtlassianBootstrapManager.init(DefaultAtlassianBootstrapManager.java:77)
        at com.atlassian.bamboo.setup.BootstrapLoaderListener.contextInitialized(BootstrapLoaderListener.java:117)
        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:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Notes

The foreign keys dropped by this upgrade task exist as lower case in MS SQL:

FK_i5xelapgkulq7lqovowe4irbc	VARIABLESTOAUTOINCREMENT
FK_hbr9i9d2dct3a205rhh82y555	EXTERNAL_MEMBERS
FK_o35y9wafg8gxrhfedx6p840a9	EXTERNAL_MEMBERS
FK_stjxt95av33tmiyd4xtf68nhc	LOCAL_MEMBERS
FK_kbapw8j5ejxolemecf1p86p83	LOCAL_MEMBERS

Workaround

Shutdown Bamboo

Run the below SQL manually on your Bamboo database to drop the constraints:

ALTER TABLE EXTERNAL_MEMBERS DROP CONSTRAINT FK_hbr9i9d2dct3a205rhh82y555
GO
ALTER TABLE EXTERNAL_MEMBERS DROP CONSTRAINT FK_o35y9wafg8gxrhfedx6p840a9
GO
ALTER TABLE LOCAL_MEMBERS DROP CONSTRAINT FK_stjxt95av33tmiyd4xtf68nhc
GO
ALTER TABLE LOCAL_MEMBERS DROP CONSTRAINT FK_kbapw8j5ejxolemecf1p86p83
GO
ALTER TABLE VARIABLESTOAUTOINCREMENT DROP CONSTRAINT FK_i5xelapgkulq7lqovowe4irbc
GO

Start Bamboo to proceed with upgrade

derived from

BAM-18471 Oracle cannot update schema during upgrade

Closed

Monique Khairuliana (Inactive) made changes - 19/Aug/2019 2:03 AM

Workflow	Original: Bamboo Workflow 2016 v1 - Restricted [ 2439080 ]	New: JAC Bug Workflow v3 [ 3383978 ]
Status	Original: Resolved [ 5 ]	New: Closed [ 6 ]

Owen made changes - 24/Oct/2018 5:07 AM

Symptom Severity

Original: Major [ 14431 ]

New: Severity 2 - Major [ 15831 ]

Krystian Brazulewicz made changes - 18/Dec/2017 1:07 PM

Fix Version/s		New: 6.1.4 [ 75133 ]
Fix Version/s	Original: 6.1.2 [ 73637 ]

Alexey Chystoprudov made changes - 20/Sep/2017 8:35 PM

Resolution		New: Fixed [ 1 ]
Status	Original: In Progress [ 3 ]	New: Resolved [ 5 ]

Alexey Chystoprudov made changes - 20/Sep/2017 9:23 AM

Fix Version/s

New: 6.2.0 [ 73008 ]

Alexey Chystoprudov made changes - 20/Sep/2017 9:22 AM

Remote Link

New: This issue links to "+core+ Bamboo › C Licence › ~~BAM-18689~~_fix_constraint_drop_for_ms_sql (tardigrade-bamboo)" [ 321609 ]

Alexey Chystoprudov made changes - 20/Sep/2017 9:19 AM

Remote Link

New: This issue links to "+core+ New and Tasty › A Core Builds › ~~BAM-18689~~_fix_constraint_drop_for_ms_sql (tardigrade-bamboo)" [ 321558 ]

Alexey Chystoprudov made changes - 20/Sep/2017 9:01 AM

Fix Version/s		New: 6.1.2 [ 73637 ]
Assignee		New: Alexey Chystoprudov [ achystoprudov ]
Status	Original: Open [ 1 ]	New: In Progress [ 3 ]

Alexey Chystoprudov made changes - 20/Sep/2017 8:37 AM

Component/s		New: Database (MS SQL) [ 12660 ]
Component/s		New: Upgrading [ 11560 ]
Status	Original: Needs Triage [ 10030 ]	New: Open [ 1 ]

Jeremy Owen made changes - 20/Sep/2017 7:34 AM

Regular Expression

New: com\.microsoft\.sqlserver\.jdbc\.SQLServerException\: \'FK_O35Y9WAFG8GXRHFEDX6P840A9\' is not a constraint

Assignee:: Alexey Chystoprudov

Reporter:: Jeremy Owen

Affected customers:: 2 This affects my team

Watchers:: 2 Start watching this issue

Created:: 20/Sep/2017 6:52 AM

Updated:: 19/Aug/2019 2:03 AM

Resolved:: 20/Sep/2017 8:35 PM

Details

Description

Summary

Environment

Steps to Reproduce

Expected Results

Actual Results

Notes

Workaround

Attachments

Issue Links

Forms

Activity

People

Dates