Uploaded image for project: 'Bamboo Data Center'
  1. Bamboo Data Center
  2. BAM-18806

The primary key constraint on COMMIT_FILES has NOT been dropped upgrade from 5.6.2 -> 6.2.2 with MYSQL database

XMLWordPrintable

      Summary

      While upgrading from Bamboo 5.6.2 to version 6.2.2 Bamboo is unable to drop the primary key constraint and the column "LIST_POSITION" on the table "COMMIT_FILES"

      Environment

      • Database: MySQL

      Steps to Reproduce

      1. Install Bamboo 5.6.2
      2. Have data in the "COMMIT_FILES" table and "USER_COMMIT" table
      3. Perform the upgrade to version 6.2.2

      Expected Results

      Primary key constraint should have dropped on the table "COMMIT_FILES"
      Column "LIST_POSITION" is dropped.

      Actual Results

      Constraint is not dropped which results into multiple constraints(primary + foreign) on the same column "COMMIT_ID"

      2017-11-02 21:54:52,120 INFO [16-UpgradeTaskBackgroundThread:pool-31-thread-1] [UpgradeTask51205DropObsoleteCommitFilesColumn] Dropping primary key on COMMIT_FILES
2017-11-02 21:54:52,142 INFO [16-UpgradeTaskBackgroundThread:pool-31-thread-1] [UpgradeTask51205DropObsoleteCommitFilesColumn] The primary key constraint on COMMIT_FILES has NOT been dropped.

      I am suspecting it is because of the fact that Bamboo is adding foreign key first and later it tries to drop the primary key constraint which might be bumping into:

      Error Code: 1025. Error on rename of './upgrade_test/#sql-74_5de' to './upgrade_test/commit_files' (errno: 150 - Foreign key constraint is incorrectly formed)

      The above error is thrown when I tried to remove the primary key manually using SQL.
      But the below error is thrown in the customer's log:

      2017-11-02 00:46:45,762 WARN [16-UpgradeTaskBackgroundThread:pool-32-thread-1] [UpgradeTask51205DropObsoleteCommitFilesColumn] The primary key constraint on COMMIT_FILES has NOT been dropped.
java.sql.SQLException: Error on rename of './bamboodb/#sql-1_be' to './bamboodb/COMMIT_FILES' (errno: 150 - Foreign key constraint is incorrectly formed)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)

      LIST_POSITION column is not dropped, resulting in the below errors when attempting to build:

      org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [insert into COMMIT_FILES (COMMIT_ID, COMMIT_FILE_NAME, COMMIT_FILE_REIVISION) values (?, ?, ?)]; SQL state [HY000]; error code [1364]; could not execute batch; nested exception is org.hibernate.exception.GenericJDBCException: could not execute batch
at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:248)
at org.springframework.orm.hibernate5.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:755)
at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:594)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
at sun.reflect.GeneratedMethodAccessor168.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy77.commit(Unknown Source)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:504)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy100.updateResultSummaryWithBuildChanges(Unknown Source)
at com.atlassian.bamboo.chains.ChainExecutionManagerImpl$1.getChainState(ChainExecutionManagerImpl.java:249)
at com.atlassian.bamboo.chains.ChainExecutionManagerImpl.tryStartChainState(ChainExecutionManagerImpl.java:315)
at com.atlassian.bamboo.chains.ChainExecutionManagerImpl.delayedStart(ChainExecutionManagerImpl.java:232)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy194.delayedStart(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy194.delayedStart(Unknown Source)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl$1$1$1.lambda$call$0(PlanExecutionManagerImpl.java:374)
at com.atlassian.bamboo.util.CacheAwareness$3.call(CacheAwareness.java:136)
at com.atlassian.bamboo.util.CacheAwareness$3.call(CacheAwareness.java:133)
at com.atlassian.bamboo.util.CacheAwareness.withValuesOlderThanTimestampReloaded(CacheAwareness.java:162)
at com.atlassian.bamboo.util.CacheAwareness.withValuesOlderThanTimestampReloaded(CacheAwareness.java:133)
at com.atlassian.bamboo.util.CacheAwareness.withValuesOlderThanTimestampReloaded(CacheAwareness.java:187)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl$1$1$1.call(PlanExecutionManagerImpl.java:373)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl$1$1$1.call(PlanExecutionManagerImpl.java:366)
at io.atlassian.util.concurrent.ManagedLocks$ManagedLockImpl.withLock(ManagedLocks.java:293)
at com.atlassian.bamboo.plan.PlanExecutionLockServiceImpl.lock(PlanExecutionLockServiceImpl.java:75)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl.doWithProcessLock(PlanExecutionManagerImpl.java:655)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl.access$400(PlanExecutionManagerImpl.java:130)
at com.atlassian.bamboo.plan.PlanExecutionManagerImpl$1$1.run(PlanExecutionManagerImpl.java:362)
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)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute batch
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:121)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:97)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:147)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.getBatch(JdbcCoordinatorImpl.java:195)
at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1296)
at org.hibernate.persister.collection.OneToManyPersister.recreate(OneToManyPersister.java:186)
at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:50)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:582)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:456)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1435)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:491)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3201)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2411)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:467)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:146)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:220)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68)
at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:582)
... 53 more
Caused by: java.sql.BatchUpdateException: Field 'LIST_POSITION' doesn't have a default value
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:111)
... 73 more
Caused by: java.sql.SQLException: Field 'LIST_POSITION' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
... 77 more

      Workaround

      1. Always backup your database before performing any modifications
      2. After the upgrade, Drop the foreign key constraint first: 
        alter table COMMIT_FILES drop foreign key FK_qu6jkca23aqtgitxwt91l7gmv;
      3. Drop the primary key constraint: 
        alter table COMMIT_FILES drop primary key;
      4. Add foreign key constraint again: 
        alter table COMMIT_FILES add CONSTRAINT FK_qu6jkca23aqtgitxwt91l7gmv FOREIGN KEY (COMMIT_ID) REFERENCES `USER_COMMIT` (`COMMIT_ID`)
      5. Drop the "LIST_POSITION" column: 
           alter table COMMIT_FILES drop column LIST_POSITION;

            [BAM-18806] The primary key constraint on COMMIT_FILES has NOT been dropped upgrade from 5.6.2 -> 6.2.2 with MYSQL database

            pavan konduru added a comment -

            Have there been any updates on this issue?

            pavan konduru added a comment - Have there been any updates on this issue?

            pavan konduru added a comment -

            When I run the :

            alter table COMMIT_FILES drop primary key;

            I get this error:

            mysql> alter table COMMIT_FILES drop primary key;
            ERROR 1025 (HY000): Error on rename of './bamboo/#sql-6672_8' to './bamboo/commit_files' (errno: 150 - Foreign key constraint is incorrectly formed)

            I have upgraded bamboo from 5.3 to 6.2 and mysql from 5.1 to 5.7

            pavan konduru added a comment - When I run the : alter table COMMIT_FILES drop primary key; I get this error: mysql> alter table COMMIT_FILES drop primary key; ERROR 1025 (HY000): Error on rename of './bamboo/#sql-6672_8' to './bamboo/commit_files' (errno: 150 - Foreign key constraint is incorrectly formed) I have upgraded bamboo from 5.3 to 6.2 and mysql from 5.1 to 5.7

            Telstra Automation Engineering added a comment -

            this drops the last column, and also works around the many duplicated COMMIT_ID, which do actually have different COMMIT_FILES, but this leads us to throwing away data.

            Telstra Automation Engineering added a comment - this drops the last column, and also works around the many duplicated COMMIT_ID, which do actually have different COMMIT_FILES, but this leads us to throwing away data.

            Telstra Automation Engineering added a comment -

            The work-around that's working for us at the moment is to run this sql after restoring the db, but before pointing bamboo at it (we're currently building up a new dev instance from the db dump of the old instance):

             

             

            SET sql_mode = ''; CREATE TABLE ae_tmp as SELECT COMMIT_ID, COMMIT_FILE_NAME, COMMIT_FILE_REIVISION FROM COMMIT_FILES GROUP BY COMMIT_ID;
            drop table COMMIT_FILES;

            alter table ae_tmp rename as COMMIT_FILES;
            alter table COMMIT_FILES add CONSTRAINT FK_qu6jkca23aqtgitxwt91l7gmv FOREIGN KEY (COMMIT_ID) REFERENCES `USER_COMMIT` (`COMMIT_ID`);

            Telstra Automation Engineering added a comment - The work-around that's working for us at the moment is to run this sql after restoring the db, but before pointing bamboo at it (we're currently building up a new dev instance from the db dump of the old instance):     SET sql_mode = ''; CREATE TABLE ae_tmp as SELECT COMMIT_ID, COMMIT_FILE_NAME, COMMIT_FILE_REIVISION FROM COMMIT_FILES GROUP BY COMMIT_ID; drop table COMMIT_FILES; alter table ae_tmp rename as COMMIT_FILES; alter table COMMIT_FILES add CONSTRAINT FK_qu6jkca23aqtgitxwt91l7gmv FOREIGN KEY (COMMIT_ID) REFERENCES `USER_COMMIT` (`COMMIT_ID`);

              achystoprudov Alexey Chystoprudov
              rsaxena@atlassian.com Robhit Saxena (Inactive)
              Affected customers:
              0 This affects my team
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: