Details
-
Bug
-
Resolution: Fixed
-
High
-
5.12.0.2
-
Severity 2 - Major
-
1
-
Description
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
- Install Bamboo 5.6.2
- Have data in the "COMMIT_FILES" table and "USER_COMMIT" table
- 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
- Always backup your database before performing any modifications
- After the upgrade, Drop the foreign key constraint first:
alter table COMMIT_FILES drop foreign key FK_qu6jkca23aqtgitxwt91l7gmv;
- Drop the primary key constraint:
alter table COMMIT_FILES drop primary key;
- Add foreign key constraint again:
alter table COMMIT_FILES add CONSTRAINT FK_qu6jkca23aqtgitxwt91l7gmv FOREIGN KEY (COMMIT_ID) REFERENCES `USER_COMMIT` (`COMMIT_ID`)
- Drop the "LIST_POSITION" column:
alter table COMMIT_FILES drop column LIST_POSITION;