Bamboo doesn't delete data from "deployment_env_vcs_location" table when deleting marked for deleted data from table "vcs_location"

XMLWordPrintable

    • Severity 3 - Minor

      Issue Description:

      Bamboo doesn't delete data from "deployment_env_vcs_location" table when deleting marked for deleted data from table "vcs_location". This results in integrity constraint violation because you cannot delete data from parent table leaving the child data orphaned.

      Issue reproduced locally here at Atlassian:
      Oracle 11g with Bamboo Version 5.15.3
      Recreated the same issue and hit the same error:
      2018-02-28 18:41:33,209 INFO [http-nio-8085-exec-16] [AccessLogFilter] robhit POST http://localhost:8085/admin/deleteLinkedRepository.action 590909kb
2018-02-28 18:41:41,877 INFO [http-nio-8085-exec-16] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements
2018-02-28 18:41:41,878 WARN [http-nio-8085-exec-16] [SqlExceptionHelper] SQL Error: 2292, SQLState: 23000
2018-02-28 18:41:41,878 ERROR [http-nio-8085-exec-16] [SqlExceptionHelper] ORA-02292: integrity constraint (BAMUSER.FK_IQW5YM58JU0JVE08BRNPJKEO2) violated - child record found

2018-02-28 18:41:41,878 WARN [http-nio-8085-exec-16] [SqlExceptionHelper] SQL Error: 2292, SQLState: 23000
2018-02-28 18:41:41,878 ERROR [http-nio-8085-exec-16] [SqlExceptionHelper] ORA-02292: integrity constraint (BAMUSER.FK_IQW5YM58JU0JVE08BRNPJKEO2) violated - child record found

2018-02-28 18:41:41,887 ERROR [http-nio-8085-exec-16] [BatchingBatch] HHH000315: Exception executing batch [could not execute batch]
      MySQL DB with Bamboo Version 6.2.3:
      018-02-28 10:51:19,019 INFO [http-nio-8125-exec-18] [AccessLogFilter] robhit POST http://localhost:8125/623/admin/deleteLinkedRepository.action 440146kb
2018-02-28 10:51:19,513 INFO [http-nio-8125-exec-18] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements
2018-02-28 10:51:19,515 ERROR [http-nio-8125-exec-18] [BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`bam6.2.3`.`deployment_env_vcs_location`, CONSTRAINT `FK_iqw5ym58ju0jve08brnpjkeo2` FOREIGN KEY (`VCS_LOCATION_ID`) REFERENCES `VCS_LOCATION` (`VCS_LOCATION_ID`))], SQL: delete from VCS_LOCATION where VCS_LOCATION_ID=?
2018-02-28 10:51:19,519 WARN [http-nio-8125-exec-18] [SqlExceptionHelper] SQL Error: 1451, SQLState: 23000
2018-02-28 10:51:19,520 ERROR [http-nio-8125-exec-18] [SqlExceptionHelper] Cannot delete or update a parent row: a foreign key constraint fails (`bam6.2.3`.`deployment_env_vcs_location`, CONSTRAINT `FK_iqw5ym58ju0jve08brnpjkeo2` FOREIGN KEY (`VCS_LOCATION_ID`) REFERENCES `VCS_LOCATION` (`VCS_LOCATION_ID`))
2018-02-28 10:51:19,523 ERROR [http-nio-8125-exec-18] [ExceptionMapperStandardImpl] HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute batch]
      MySQL DB with Bamboo Version 6.4.0
      2018-02-28 19:11:26,473 INFO [http-nio-8335-exec-14] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements
2018-02-28 19:11:26,474 ERROR [http-nio-8335-exec-14] [BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`bamb640schema`.`deployment_env_vcs_location`, CONSTRAINT `FK_iqw5ym58ju0jve08brnpjkeo2` FOREIGN KEY (`VCS_LOCATION_ID`) REFERENCES `VCS_LOCATION` (`VCS_LOCATION_ID`))], SQL: delete from VCS_LOCATION where VCS_LOCATION_ID=?
2018-02-28 19:11:26,476 WARN [http-nio-8335-exec-14] [SqlExceptionHelper] SQL Error: 1451, SQLState: 23000
2018-02-28 19:11:26,476 ERROR [http-nio-8335-exec-14] [SqlExceptionHelper] Cannot delete or update a parent row: a foreign key constraint fails (`bamb640schema`.`deployment_env_vcs_location`, CONSTRAINT `FK_iqw5ym58ju0jve08brnpjkeo2` FOREIGN KEY (`VCS_LOCATION_ID`) REFERENCES `VCS_LOCATION` (`VCS_LOCATION_ID`))
2018-02-28 19:11:26,480 ERROR [http-nio-8335-exec-14] [ExceptionMapperStandardImpl] HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute batch]
      Steps to reproduce the issue:
      1. Configure a linked repository.
      2. Add it to a source code checkout task of the deployment plan
      3. Try to delete the repository from the linked repository and you will hit this error.

      The reason that we have noticed here:

      • The data from the table "deployment_env_vcs_location" is not deleted by Bamboo upon deleting the repository from the linked repository page.
      • This leads to the state where you can't delete the data from the parent table leaving the child tables data orphan. The table "deployment_env_vcs_location" has the column "VCS_LOCATION_ID" linked to the same column of the table "VCS_LOCATION"

       

      Workaround:
      • Take a Backup Of the Bamboo Current State first.
      • Delete the data from the backend using the following query:
        Oracle DB Compatible Query
        delete from deployment_env_vcs_location
where VCS_LOCATION_ID in (select VCS_LOCATION_ID from VCS_LOCATION where MARKED_FOR_DELETION=1);
      • Wait for the bamboo to run the deletion service back again automatically. Note that the deletion service is executed by bamboo every 24h.

         

            Assignee:
            Marcin Gardias
            Reporter:
            Robhit Saxena (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: