Issue Summary
Rerunning a build after deleting a stage can break result removal and expiry.
Steps to Reproduce
- Create a 3 stage plan
- Run a build
- Delete the 2nd stage
- Rerun the build from Step 2 (don't perform a new build, if it was build #1, rerun build result #1)
- Attempt to manually delete the result or expire it
Expected Results
The result can be removed or expired.
Actual Results
Result cannot be deleted or expired.
The below exception is thrown in the $BAMBOO_HOME/logs/atlassian-bamboo.log (or UI if deleting a result) file:
2020-08-19 13:09:59,399 ERROR [2-BuildExpiryBean:pool-5-thread-1] [SqlExceptionHelper] ERROR: update or delete on table "buildresultsummary" violates foreign key constraint "fk_4p9t5luuw2awjxfgensarfhpn" on table "chain_stage_result" Detail: Key (buildresultsummary_id)=(284065795) is still referenced from table "chain_stage_result". 2020-08-19 13:09:59,400 ERROR [2-BuildExpiryBean:pool-5-thread-1] [BuildExpiryBeanImpl] A problem has occurred during expiry of BAM-BOO org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY_ID=?]; constraint [fk_4p9t5luuw2awjxfgensarfhpn]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:247) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:391) at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:350) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate$1.doInTransaction(BambooTransactionHibernateTemplate.java:36) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:28) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:33) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.expireBuildPlan(BuildExpiryBeanImpl.java:270) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl$1.call(BuildExpiryBeanImpl.java:137) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl$1.call(BuildExpiryBeanImpl.java:101) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57) at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at com.atlassian.bamboo.utils.BambooRunnables$1.run(BambooRunnables.java:48) 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:748) Caused by: org.hibernate.exception.ConstraintViolationException: could not execute batch at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:128) at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:104) at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:147) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:212) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:625) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:470) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1440) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.lambda$expireBuildPlan$0(BuildExpiryBeanImpl.java:279) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:384) ... 18 more Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY_ID=284065795 was aborted: ERROR: update or delete on table "buildresultsummary" violates foreign key constraint "fk_4p9t5luuw2awjxfgensarfhpn" on table "chain_stage_result" Detail: Key (buildresultsummary_id)=(284065795) is still referenced from table "chain_stage_result". Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2212) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:509) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:853) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1546) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544) at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:118) ... 29 more Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "buildresultsummary" violates foreign key constraint "fk_4p9t5luuw2awjxfgensarfhpn" on table "chain_stage_result" Detail: Key (buildresultsummary_id)=(284065795) is still referenced from table "chain_stage_result". at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211) ... 34 more
You may also see the following:
2020-08-13 14:58:15,795 WARN [2-BuildExpiryBean:pool-5-thread-1] [BuildExpiryBeanImpl] Unable to expire BAM-BRAN-36 org.springframework.dao.InvalidDataAccessApiUsageException: deleted object would be re-saved by cascade (remove deleted object from associations): [com.atlassian.bamboo.resultsummary.vcs.RepositoryChangesetImpl#281837570]; nested exception is org.hibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations): [com.atlassian.bamboo.resultsummary.vcs.RepositoryChangesetImpl#281837570] at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:277) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:387) at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:336) at com.atlassian.bamboo.plan.PlanHibernateDao.getPlanByKey(PlanHibernateDao.java:80) at com.sun.proxy.$Proxy134.getPlanByKey(Unknown Source) at com.atlassian.bamboo.plan.PlanManagerImpl.getPlanByKey(PlanManagerImpl.java:104) at com.atlassian.bamboo.plan.PlanManagerImpl.getPlanByKey(PlanManagerImpl.java:154) at com.atlassian.bamboo.security.acegi.intercept.aopalliance.AuthorityOverrideMethodSecurityInterceptor.invoke(AuthorityOverrideMethodSecurityInterceptor.java:22) at com.sun.proxy.$Proxy138.getPlanByKey(Unknown Source) at com.atlassian.bamboo.resultsummary.BuildResultsSummaryManagerImpl.removeChainResult(BuildResultsSummaryManagerImpl.java:801) at com.atlassian.bamboo.resultsummary.BuildResultsSummaryManagerImpl.removeResultSummary(BuildResultsSummaryManagerImpl.java:778) at com.atlassian.bamboo.resultsummary.BuildResultsSummaryManagerImpl.removeResultSummary(BuildResultsSummaryManagerImpl.java:764) at com.atlassian.bamboo.security.acegi.intercept.aopalliance.AuthorityOverrideMethodSecurityInterceptor.invoke(AuthorityOverrideMethodSecurityInterceptor.java:22) at com.sun.proxy.$Proxy191.removeResultSummary(Unknown Source) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.expireResultSummaries(BuildExpiryBeanImpl.java:314) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.lambda$expireBuildPlan$0(BuildExpiryBeanImpl.java:278) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:384) at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:350) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate$1.doInTransaction(BambooTransactionHibernateTemplate.java:36) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:28) at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:33) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.expireBuildPlan(BuildExpiryBeanImpl.java:270) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl$1.call(BuildExpiryBeanImpl.java:137) at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl$1.call(BuildExpiryBeanImpl.java:101) Caused by: org.hibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations): [com.atlassian.bamboo.resultsummary.vcs.RepositoryChangesetImpl#281837570] at org.hibernate.internal.SessionImpl.forceFlush(SessionImpl.java:1483) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:170) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:97) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73) at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:678) at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:670) at org.hibernate.engine.spi.CascadingActions$5.cascade(CascadingActions.java:219) at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:471) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:396) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:197) at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:504) at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:436) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:399) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:197) at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:130) at org.hibernate.event.internal.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:159) at org.hibernate.event.internal.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:150) at org.hibernate.event.internal.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:83) at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:44) at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1415) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1952) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370) at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:392) at com.atlassian.bamboo.plan.PlanHibernateDao$1.doInHibernate(PlanHibernateDao.java:91) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:384) ... 66 more
- This stacktrace has been heavily truncated
Workaround
Make DB backup and test suggested steps at staging environment before proceeding.
Search for orphaned stage results by SQL
SELECT * FROM CHAIN_STAGE_RESULT CSR WHERE CSR.STAGERESULT_ID IN ( SELECT MIN(STAGERESULT_ID) as STAGERESULT_ID FROM CHAIN_STAGE_RESULT GROUP BY CHAINRESULT_ID, LIST_POSITION HAVING COUNT(LIST_POSITION)>=2 ) AND CSR.STAGERESULT_ID NOT IN ( SELECT STAGERESULT_ID FROM BUILDRESULTSUMMARY where STAGERESULT_ID is not null );
Delete found chain stage results
delete from CHAIN_STAGE_RESULT where STAGERESULT_ID = -- id from previous query