Upgrading Bamboo Data center to version 9.5 or higher fails with SQL Exception linked to Deployment_version_artifact and Deployment_project_artifact tables
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
While upgrading Bamboo to version 9.5 or higher the upgrade fails while running the upgrade tasks 90502 or 90503 with the below SQL Exception.
Task for build 90502 failed with exception: error executing work; SQL [n/a]; constraint [DEPLOYMENT_PROJECT_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing work
Task for build 90503 failed with exception: error executing work; SQL [n/a]; constraint [DEPLOYMENT_VERSION_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing workEnvironment
Current Version : 9.4.x , Upgrade version : 9.5.x
Diagnosis
Bamboo 9.5 and higher introduced two new upgrade task which is 90502 and 90503. 90502 Upgrade task migrates data from DEPLOYMENT_PROJECT_ITEM and DEPLOYMENT_PROJECT_ITEM_BA table to DEPLOYMENT_PROJECT_ARTIFACT table. 90503 Upgrade task migrates data from DEPLOYMENT_VERSION_ITEM and DEPLOYMENT_VERSION_ITEM_BA table to DEPLOYMENT_VERSION_ARTIFACT table.
Bamboo will run the below SQL Insert queries as part of the upgrade process.
The problem is that both the upgrade tasks fails with the below exception in atlassian-bamboo.log file
2024-11-02 02:16:07,040 ERROR [20-UpgradeTaskBackgroundThread:pool-21-thread-1] [SqlExceptionHelper] Duplicate entry '1441797' for key 'DEPLOYMENT_PROJECT_ARTIFACT.PRIMARY'
2024-11-02 02:16:07,044 ERROR [20-UpgradeTaskBackgroundThread:pool-21-thread-1] [AbstractUpgradeManager] org.springframework.dao.DataIntegrityViolationException: error executing work; SQL [n/a]; constraint [DEPLOYMENT_PROJECT_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing work
org.springframework.dao.DataIntegrityViolationException: error executing work; SQL [n/a]; constraint [DEPLOYMENT_PROJECT_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing work2024-11-08 23:42:51,627 ERROR [20-UpgradeTaskBackgroundThread:pool-21-thread-1] [SqlExceptionHelper] Duplicate entry '9142310' for key 'DEPLOYMENT_VERSION_ARTIFACT.PRIMARY'
2024-11-08 23:42:51,631 ERROR [20-UpgradeTaskBackgroundThread:pool-21-thread-1] [AbstractUpgradeManager] org.springframework.dao.DataIntegrityViolationException: error executing work; SQL [n/a]; constraint [DEPLOYMENT_VERSION_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing work
org.springframework.dao.DataIntegrityViolationException: error executing work; SQL [n/a]; constraint [DEPLOYMENT_VERSION_ARTIFACT.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing workCause
Cause 1
The fist cause is presence of duplicate data in the existing tables from which Bamboo is trying to migrate the data into the new tables.
Cause 2
The second cause is that the new tables ( DEPLOYMENT_PROJECT_ARTIFACT and DEPLOYMENT_VERSION_ARTIFACT ) already exist in the older Bamboo version before 9.5 probably because of some earlier failed upgrade attempt and while trying to rollback these tables were not removed and exist with some data.
Solution
Solution 1
Please run the below 2 SQL queries on the original Bamboo Instance from which you are trying to upgrade and validate if there are no duplicates. if you find some duplicates you'll need to identify where are these duplicates coming from and remove, If you are unsure please raise a support ticket.
If you are able to remove the duplicates please perform the upgrade again and check.
SELECT
DEPLOYMENT_PROJECT_ITEM_ID,
DEPLOYMENT_PROJECT_ID,
ARTIFACT_DEFINITION_ID
FROM
DEPLOYMENT_PROJECT_ITEM
INNER JOIN
DEPLOYMENT_PROJECT_ITEM_BA
ON
DEPLOYMENT_PROJECT_ITEM_ID = BAM_ARTIFACT_ITEM_IDSELECT
DEPLOYMENT_VERSION_ITEM_ID,
DEPLOYMENT_VERSION_ID,
PLAN_KEY,
BUILD_NUMBER,
ARTIFACT_ID
FROM
DEPLOYMENT_VERSION_ITEM
INNER JOIN
DEPLOYMENT_VERSION_ITEM_BA
ON
DEPLOYMENT_VERSION_ITEM_ID = VERSION_BAM_ARTIFACT_ITEM_IDSolution 2
If there are no duplicates as per solution 1, probably the new tables DEPLOYMENT_PROJECT_ARTIFACT and DEPLOYMENT_VERSION_ARTIFACT already exist in the original Bamboo version ( < 9.5 ) from which you are trying to upgrade. You can follow the below steps
1) Run the below SQL query and check if it returns any results.
SELECT * FROM DEPLOYMENT_PROJECT_ARTIFACT
SELECT * FROM DEPLOYMENT_VERSION_ARTIFACT2) If the above SQL query returns any results, please run the below delete queries
DELETE FROM DEPLOYMENT_PROJECT_ARTIFACT
DELETE FROM DEPLOYMENT_VERSION_ARTIFACT Please make sure to backup the above 2 tables before running any delete queries, this might be required if you wish to rollback.
3) Once the above is done, please rerun the upgrade and check if the issue is resolved.