Upgrading Bamboo to 9.5 or higher fails with SQL Exception linked to Deployment_version_artifact and Deployment_project_artifact tables
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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 or90503 with the below SQL Exception can be seen in atlassian-bamboo.log file.
1
2
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
1
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 and90503.
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_ARTIFACTtable.
Bamboo will run the below SQL Insert queries as part of the upgrade process.
SQL queries
Upgrade task : 90502
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT
INTO
DEPLOYMENT_PROJECT_ARTIFACT
(
DEPLOYMENT_PROJECT_ART_ID,
DEPLOYMENT_PROJECT_ID,
ARTIFACT_DEFINITION_ID
)
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_IDUpgrade task : 90503
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
INSERT
INTO
DEPLOYMENT_VERSION_ARTIFACT
(
DEPLOYMENT_VERSION_ART_ID,
DEPLOYMENT_VERSION_ID,
PLAN_KEY,
BUILD_NUMBER,
ARTIFACT_ID
)
SELECT
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_IDThe problem is that both the upgrade tasks fail with the below exception in atlassian-bamboo.log file
1
2
3
4
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 work1
2
3
4
2024-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 first 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 please raise a support ticket via https://support.atlassian.com/contact/ as it would need investigation to understand where are the duplicates coming from and how to remove those.
For Upgrade task 90502
1
2
3
4
5
6
7
8
9
10
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_IDFor Upgrade task 90503
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
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
Please make sure to backup the database in case if you wish to rollback
1) Run the below SQL query and check if it returns any results.
1
2
SELECT * FROM DEPLOYMENT_PROJECT_ARTIFACT
SELECT * FROM DEPLOYMENT_VERSION_ARTIFACT2) If the above SQL query returns any results, please run the below delete queries
1
2
DELETE FROM DEPLOYMENT_PROJECT_ARTIFACT
DELETE FROM DEPLOYMENT_VERSION_ARTIFACT3) Once the above is done, please rerun the upgrade and check if the issue is resolved.
Was this helpful?