Bamboo UpgradeTask60101AddUniqueIndex fails with ORA-00933: SQL command not properly ended

XMLWordPrintable

    • Severity 3 - Minor

      Summary

      Bamboo Upgrade task 60101 fails with ORA-00933: SQL command not properly ended

      Environment

      • Oracle 12c

      Steps to Reproduce

      1. Have a duplicated agent assignment in table AGENT_ASSIGNMENT
      2. Upgrade Bamboo to 6.1 on Oracle 12c

      Expected Results

      Upgrade succeeds.

      Actual Results

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

      2017-09-25 08:10:56,671 ERROR [16-UpgradeTaskBackgroundThread:pool-31-thread-1] [AbstractUpgradeManager] java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

	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:1017)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:766)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:420)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:327)
	at com.atlassian.bamboo.upgrade.tasks.v6_1.UpgradeTask60101AddUniqueIndex.removeDuplicates(UpgradeTask60101AddUniqueIndex.java:61)
	at com.atlassian.bamboo.upgrade.tasks.v6_1.UpgradeTask60101AddUniqueIndex.lambda$doUpgrade$0(UpgradeTask60101AddUniqueIndex.java:44)
	at com.atlassian.bamboo.upgrade.AbstractBootstrapUpgradeTask.withDatabaseConnection(AbstractBootstrapUpgradeTask.java:69)
	at com.atlassian.bamboo.upgrade.tasks.v6_1.UpgradeTask60101AddUniqueIndex.doUpgrade(UpgradeTask60101AddUniqueIndex.java:41)
	at com.atlassian.bamboo.upgrade.AbstractUpgradeManager.runUpgradeTask(AbstractUpgradeManager.java:175)
	at com.atlassian.bamboo.upgrade.UpgradeManagerImpl.doUpgrade(UpgradeManagerImpl.java:99)
	at com.atlassian.bamboo.upgrade.UpgradeLauncher.lambda$upgradeAndStartBamboo$0(UpgradeLauncher.java:104)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	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 com.atlassian.bamboo.utils.BambooRunnables$1.run(BambooRunnables.java:47)
	at com.atlassian.bamboo.security.ImpersonationHelper.runWith(ImpersonationHelper.java:26)
	at com.atlassian.bamboo.security.ImpersonationHelper.runWithSystemAuthority(ImpersonationHelper.java:17)
	at com.atlassian.bamboo.security.ImpersonationHelper$1.run(ImpersonationHelper.java:41)
	at java.lang.Thread.run(Thread.java:745)
2017-09-25 08:10:56,720 INFO [16-UpgradeTaskBackgroundThread:pool-31-thread-1] [AbstractUpgradeManager] Completed task 60101 successfully.
2017-09-25 08:10:56,722 FATAL [localhost-startStop-1] [UpgradeLauncher] Upgrade task error: Task for build 60101 failed with exception: ORA-00933: SQL command not properly ended

      Workaround

      1. Stop Bamboo
      2. Run the below select statement to identify any duplicates in the AGENT_ASSIGNMENT table: 
        select AA.ASSIGNMENT_ID, AA.EXECUTABLE_ID, AA.EXECUTABLE_TYPE, AA.EXECUTOR_ID, AA.EXECUTOR_TYPE
	from AGENT_ASSIGNMENT AA
	join (select EXECUTABLE_ID, EXECUTABLE_TYPE, EXECUTOR_ID, EXECUTOR_TYPE
       from AGENT_ASSIGNMENT
        group by EXECUTABLE_ID, EXECUTABLE_TYPE, EXECUTOR_ID, EXECUTOR_TYPE
        having count(*) > 1) DUP
     on DUP.EXECUTABLE_ID = AA.EXECUTABLE_ID
        and DUP.EXECUTABLE_TYPE = AA.EXECUTABLE_TYPE
    	and DUP.EXECUTOR_ID = AA.EXECUTOR_ID
    	and DUP.EXECUTOR_TYPE = AA.EXECUTOR_TYPE;
      3. Always backup your database before performing any modifications.
      4. The query will return any rows that are duplicated. You will need to ensure to leave one of the returned rows present. Manually remove each duplicate from the AGENT_ASSIGNMENT table so that only one unique row exists for the assignment.
      5. Start Bamboo (the constraint that the upgrade task was trying to create will be created automatically)

            Assignee:
            Alexey Chystoprudov
            Reporter:
            Jeremy Owen
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: