Bamboo import into clone instance fails with fk_qi40i74ayf29ovlos9vigoown constraint error
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
Import into clone instance fails with foreign key fk_qi40i74ayf29ovlos9vigoown constraint error while migrating Bamboo from one server to other.
Diagnosis
Below error messages can be seen in <Bamboo-home>/logs/atlassian-bamboo.log file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2023-03-14 07:29:19,023 INFO [http-nio-8085-exec-17] [XmlMigrator] Importing with mapper: com.atlassian.bamboo.migration.stream.RelevantChangesetsMapper@1f59f058
2023-03-14 07:29:19,038 INFO [http-nio-8085-exec-17] [XmlMigrator] Reading file: /home/bamboo/export_bamboo_80210_20230314.zip/db-export/relevantChangesets.xml
2023-03-14 07:29:19,083 INFO [http-nio-8085-exec-17] [BambooStAXMappingHelperAbstractImpl] Importing relevantChangesets.relevantChangeset: 1000, used memory 664 MB
2023-03-14 07:29:19,120 WARN [http-nio-8085-exec-17] [BambooStAXMappingHelperAbstractImpl] Exception during processing relevantChangesets.relevantChangeset #2000
org.springframework.dao.DataIntegrityViolationException: error executing work; SQL [n/a]; constraint [fk_qi40i74ayf29ovlos9vigoown]; nested exception is org.hibernate.exception.ConstraintViolationException: error executing work
at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:212)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:370)
at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:320)
at com.atlassian.bamboo.resultsummary.vcs.RepositoryChangesetHibernateDao.saveRelevantChangesets(RepositoryChangesetHibernateDao.java:108)
.....
Caused by: java.sql.BatchUpdateException: Batch entry 600 insert into RELEVANT_CHANGESETS values(198401681, 198306623) was aborted: ERROR: insert or update on table "relevant_changesets" violates foreign key constraint "fk_qi40i74ayf29ovlos9vigoown"
Detail: Key (buildresultsummary_id)=(198401681) is not present in table "buildresultsummary". Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
.....
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "relevant_changesets" violates foreign key constraint "fk_qi40i74ayf29ovlos9vigoown"
Detail: Key (buildresultsummary_id)=(198401681) is not present in table "buildresultsummary".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
... 225 more
2023-03-14 07:29:19,141 ERROR [http-nio-8085-exec-17] [ImportExportManagerImpl] Exception occurred during import
java.lang.RuntimeException: Unexpected exception
at com.atlassian.bamboo.util.BambooObjectUtils.asRuntimeException(BambooObjectUtils.java:119)
at com.atlassian.bamboo.migration.XmlMigrator.lambda$loadXmlData$4(XmlMigrator.java:361)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:367)The above error says that the import process fails with a foreign key constraint violation on key "fk_qi40i74ayf29ovlos9vigoown". This foreign key is defined on the table RELEVANT_CHANGESETS, linking to Primary key of the table BUILDRESULTSUMMARY.
The import fails when Bamboo tries to insert the values (i.e. 198401681, 198306623) into table RELEVANT_CHANGESETS (in new Bamboo instance DB) while processing the import (in the new Bamboo instance) of this export file /home/bamboo/export_bamboo_80210_20230314.zip/db-export/relevantChangesets.xml(created from old Bamboo instance):
1
2023-03-14 07:29:19,038 INFO [http-nio-8085-exec-17] [XmlMigrator] Reading file: /home/bamboo/export_bamboo_80210_20230314.zip/db-export/relevantChangesets.xmlBut the primary key id's for these values do not exists in table BUILDRESULTSUMMARY (in new Bamboo instance DB).
Cause
This happens due to orphan rows available in tableRELEVANT_CHANGESETS (in old Bamboo instance database) and same are created in export file(relevantChangesets.xml) when created a export from the old Bamboo instance. And the import to new Bamboo instance fails due to the data inconsistency between tables RELEVANT_CHANGESETS and BUILDRESULTSUMMARY.
Solution
Alwaysback upyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The solution is to clean the orphan rows from the table RELEVANT_CHANGESETS (inold Bamboo instance Database) that refer to primary key in table BUILDRESULTSUMMARY (which no longer exist).
Here is the SQL command that you could use to check for orphaned records:
1
SELECT * FROM RELEVANT_CHANGESETS WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);When the discrepancies are found(mostly these will match with the Id's available in error logs, for example values 198401681, 198306623 in this case), use the below SQL command to delete them:
1
DELETE FROM RELEVANT_CHANGESETS WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);Once there are no more discrepancies found, create a new export file from old Bamboo instance again and import it into the new Bamboo instance from the start.
Was this helpful?