Build expiry fails with foreign key (FK_p3gerdfga5l9fu5qijd0j36sj) constraint violation (fixing the deletion procedure)

XMLWordPrintable

    • Type: Bug
    • Resolution: Fixed
    • Priority: Low
    • 7.2.4
    • Affects Version/s: 7.2.3
    • Component/s: Expiry
    • None
    • 1
    • Severity 3 - Minor

      Issue Summary

      Build expiry fails with foreign key (FK_p3gerdfga5l9fu5qijd0j36sj) constraint violation errors.

      Environment

      • Bamboo connected to MySQL.

      Steps to Reproduce

      1. Create a new plan in Bamboo (e.g. PROJ-PLAN) – no need to link any repositories.
      2. Create two stages inside this plan (e.g. Default Stage and Stage 2).
      3. Create one job with a Script task using the following content inside the first stage i.e. Default Stage: 
        exit 0
      1. Create one job with a Script task using the following content inside the second stage i.e. Stage 2): 
        echo hello > world.txt
exit 1
      1. Create an artifact inside the second job with the following details:
        • Name: Test
        • Copy pattern: */.txt
        • Shared
      2. Enable your plan (e.g. PROJ-PLAN).
      3. Run a build – your build will fail.
      4. Disable your second job.
      5. Rerun failed/incomplete jobs only – your build will succeed.
      6. Run a new build – at this point you should have two successful builds.
      7. Go to the Bamboo admin > Plans > Expiry page.
      8. Edit your expiry configuration and:
        • Select "Complete build & deployment results, build & release artifacts and all logs".
        • Set "Minimum builds to keep" to 1.
      9. Save.
      10. Click "Run now".

      Expected Results

      The first build of your plan should be removed.

      Actual Results

      No builds from your plan are removed and the following error message is thrown in the logs:

      atlassian-bamboo.log
      2021-04-08 08:48:49,030 INFO [2-BuildExpiryBean:pool-5-thread-1] [BuildExpiryBeanImpl] Found 1 build(s) to expire for PROJ-PLAN
2021-04-08 08:48:49,169 INFO [2-BuildExpiryBean:pool-5-thread-1] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements
2021-04-08 08:48:49,170 ERROR [2-BuildExpiryBean:pool-5-thread-1] [BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`bamboo723`.`buildresultsummary`, CONSTRAINT `FK_p3gerdfga5l9fu5qijd0j36sj` FOREIGN KEY (`CHAIN_RESULT`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`))], SQL: delete from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY_ID=?
2021-04-08 08:48:49,171 WARN [2-BuildExpiryBean:pool-5-thread-1] [SqlExceptionHelper] SQL Error: 1451, SQLState: 23000
2021-04-08 08:48:49,171 ERROR [2-BuildExpiryBean:pool-5-thread-1] [SqlExceptionHelper] Cannot delete or update a parent row: a foreign key constraint fails (`bamboo723`.`buildresultsummary`, CONSTRAINT `FK_p3gerdfga5l9fu5qijd0j36sj` FOREIGN KEY (`CHAIN_RESULT`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`))
2021-04-08 08:48:49,175 ERROR [2-BuildExpiryBean:pool-5-thread-1] [BuildExpiryBeanImpl] A problem has occurred during expiry of PROJ-PLAN
org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY_ID=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
	at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:245)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:392)
	at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:351)
	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:109)
	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:131)
	at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:106)
	at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:148)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:198)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:633)
	at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
	at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1349)
	at com.atlassian.bamboo.build.expiry.BuildExpiryBeanImpl.lambda$expireBuildPlan$0(BuildExpiryBeanImpl.java:279)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:385)
	... 18 more
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`bamboo723`.`buildresultsummary`, CONSTRAINT `FK_p3gerdfga5l9fu5qijd0j36sj` FOREIGN KEY (`CHAIN_RESULT`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`))
	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:403)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1154)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1835)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1319)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:954)
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:121)
	... 32 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`bamboo723`.`buildresultsummary`, CONSTRAINT `FK_p3gerdfga5l9fu5qijd0j36sj` FOREIGN KEY (`CHAIN_RESULT`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`))
	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:403)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1915)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2136)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1813)
	... 37 more

      This is happening because STAGERESULT_ID becomes NULL after rerunning failed/incomplete jobs on step 9. The original STAGERESULT_ID inside the CHAIN_STAGE_RESULT table does not get removed but the STAGERESULT_ID for your job inside the BUILDRESULTSUMMARY table becomes NULL.

      You can identify rows that are potentially causing this issue by using the following select query inside your database:

      select buildresultsummary_id,
       build_type,
       build_key,
       build_number,
       stageresult_id,
       chain_result
  from buildresultsummary
 where build_type = 'BUILD'
   and stageresult_id is null
 order by build_key, build_number;

      Workaround

      Currently there is no known workaround for this behavior. A workaround will be added here when available

      Notes

      • This does not seem to affect PostgreSQL. The same steps were carried out in Bamboo 7.2.3 connected to PostgreSQL but STAGERESULT_ID does not become NULL and Bamboo is capable of expiring the results.
      • This has only been tested against MySQL and PostgreSQL and may affect other databases as well.

            Assignee:
            Marcin Gardias
            Reporter:
            Bruno Rosa
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: