Bamboo specs states cleanup process fails with foreign key constraint violation errors

XMLWordPrintable

    • Type: Bug
    • Resolution: Fixed
    • Priority: Low
    • 8.0.0, 7.2.5
    • Affects Version/s: 7.0.3, 7.1.1, 7.2.1
    • Component/s: Bamboo Specs
    • None
    • 3
    • Severity 3 - Minor

      Problem

      The bamboo specs states cleanup process fails with foreign key constraint violation errors on the DEPLOYMENT_PROJECT table when attempting to remove a record inside the VCS_SPECS_STATE table.

      Caused by: org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=?]; constraint [BAMBOO.FK_M9NNB8AMB4KAI65ULI04DDT4M]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch

      Environment

      • Bamboo 7.1.1

      Steps to Reproduce

      1. Create a linked repository configuration in Bamboo pointing to a Bitbucket Server repository hosting your specs code.
      2. Create a new empty project (SPECS) and build plan (STASH) in Bamboo.
      3. Use the following YAML specs file to create a deployment project: 
        ---
version: 2
deployment:
  name: deployment
  source-plan: SPECS-STASH
 
release-naming: release-1
 
environments:
  - Production
 
Production:
  triggers:
    - build-success
  tasks:
    - clean
    - artifact-download
    - script:
        - echo hello
      4. Scan the repository for specs code inside the linked repository configuration a few times (each time you scan new entries will be added to the VCS_SPECS_SOURCE and VCS_SPECS_STATE tables).
      5. At this point a you should have a new deployment project.
      6. Since you clicked the scan button a few times we now have orphaned specs states in the database and need to clean them up.
      7. Trigger the specs states cleanup process.
        • You can add the following system attribute to modify the cadence of the specs states cleanup process. It's running every 2am by default. Let's make it run every 5 minutes. 
          -Dbamboo.orphaned.specs.states.cleanup.cron="0 0/5 * ? * *"
      8. Restart Bamboo.
      9. Confirm the schedule has changed by looking for the following information in the Bamboo logs: 
        2020-10-27 10:28:08,335 INFO [localhost-startStop-1] [RemoveOrphanedSpecsStateScheduler] Used '0 0/5 * ? * *' to schedule RemoveOrphanedSpecsStateJob. Next trigger time Tue Oct 27 10:30:00 GMT 2020
      10. After starting Bamboo wait 5 minutes for the process to kick in.

      Expected Results

      The specs states cleanup process should remove old/orphaned entries and leave the last entry that's associated to the deployment project in the database.

      2020-10-27 10:35:00,020 INFO [scheduler_Worker-8] [BambooSpecsManagerImpl] Starting specs states cleanup
2020-10-27 10:35:00,034 INFO [scheduler_Worker-8] [BambooSpecsManagerImpl] Removing orphaned specs states for repository 6029313 and branch develop
2020-10-27 10:35:00,058 INFO [scheduler_Worker-8] [BambooSpecsManagerImpl] Removing orphaned specs states for repository 6029313 and branch master
2020-10-27 10:35:00,158 INFO [scheduler_Worker-8] [BambooSpecsManagerImpl] Specs states cleanup finished. Removed 17 records
2020-10-27 10:35:00,159 INFO [scheduler_Worker-8] [RemoveOrphanedSpecsStateJob] Completed removal of old Bamboo Specs states in 157.0 ms. Removed 17 record(s).

      Actual Results

      Bamboo tries to remove the entry associated to the deployment project and fails to do so because there's an existing link there. It seems we're only checking build plans for existing links and not deployment projects:

      2020-10-27 11:00:00,006 INFO [scheduler_Worker-3] [BambooSpecsManagerImpl] Starting specs states cleanup
2020-10-27 11:00:00,013 INFO [scheduler_Worker-3] [BambooSpecsManagerImpl] Removing orphaned specs states for repository 6029313 and branch master
2020-10-27 11:00:00,021 INFO [scheduler_Worker-3] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements
2020-10-27 11:00:00,022 ERROR [scheduler_Worker-3] [BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Batch entry 0 delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=7110663 was aborted: ERROR: update or delete on table "vcs_specs_source" violates foreign key constraint "fk_m9nnb8amb4kai65uli04ddt4m" on table "deployment_project"
  Detail: Key (vcs_specs_source_id)=(7143431) is still referenced from table "deployment_project".  Call getNextException to see other errors in the batch.], SQL: delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=?
2020-10-27 11:00:00,023 WARN [scheduler_Worker-3] [SqlExceptionHelper] SQL Error: 0, SQLState: 23503
2020-10-27 11:00:00,023 ERROR [scheduler_Worker-3] [SqlExceptionHelper] Batch entry 0 delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=7110663 was aborted: ERROR: update or delete on table "vcs_specs_source" violates foreign key constraint "fk_m9nnb8amb4kai65uli04ddt4m" on table "deployment_project"
  Detail: Key (vcs_specs_source_id)=(7143431) is still referenced from table "deployment_project".  Call getNextException to see other errors in the batch.
2020-10-27 11:00:00,024 ERROR [scheduler_Worker-3] [SqlExceptionHelper] ERROR: update or delete on table "vcs_specs_source" violates foreign key constraint "fk_m9nnb8amb4kai65uli04ddt4m" on table "deployment_project"
  Detail: Key (vcs_specs_source_id)=(7143431) is still referenced from table "deployment_project".
2020-10-27 11:00:00,025 ERROR [scheduler_Worker-3] [ExceptionMapperStandardImpl] HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute batch]
2020-10-27 11:00:00,027 ERROR [scheduler_Worker-3] [JobRunShell] Job removeOrphanedSpecsStateGroup.removeOrphanedSpecsStateJob threw an unhandled Exception:
org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=?]; constraint [fk_m9nnb8amb4kai65uli04ddt4m]; 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.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:801)
	at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:637)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
	at sun.reflect.GeneratedMethodAccessor220.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:343)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
	at com.sun.proxy.$Proxy109.commit(Unknown Source)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152)
	at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:28)
	at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:33)
	at com.atlassian.bamboo.specs.BambooSpecsManagerImpl.removeOrphanedSpecStatesNoTx(BambooSpecsManagerImpl.java:129)
	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:343)
        ...
Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from VCS_SPECS_STATE where VCS_SPECS_STATE_ID=7110663 was aborted: ERROR: update or delete on table "vcs_specs_source" violates foreign key constraint "fk_m9nnb8amb4kai65uli04ddt4m" on table "deployment_project"
  Detail: Key (vcs_specs_source_id)=(7143431) is still referenced from table "deployment_project".  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 org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
	at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:118)
	... 48 more
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "vcs_specs_source" violates foreign key constraint "fk_m9nnb8amb4kai65uli04ddt4m" on table "deployment_project"
  Detail: Key (vcs_specs_source_id)=(7143431) is still referenced from table "deployment_project".
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
	... 54 more

      Workaround

      Currently there are no known workarounds for this behaviour. A workaround will be added here when available.

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

                Created:
                Updated:
                Resolved: