Upgrading Bamboo Data Center to version 9.5 or higher fails with SQL Exception linked to Deployment_version_artifact and Deployment_project_artifact tables
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
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 can be seen in atlassian-bamboo.log file.
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 work環境
Current Version : 9.4.x , Upgrade version : 9.5.x
診断
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 work原因
原因 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.
原因 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.
ソリューション
ソリューション 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 as it would need investigation to understand where are the duplicates coming from and how to remove those.
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_IDソリューション 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_ARTIFACTPlease 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.