Import & Export fail due to SQL Corruption.
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
Exporting & Importing Bamboo data may fail due to database corruption; you might see errors similar to:
1
2
3
4
5
6
7
8
bamboo=# insert into BRS_LINKEDJIRAISSUES (BUILDRESULTSUMMARY_ID, JIRA_ISSUE_KEY, JIRA_ISSUE_LINK_TYPE, LINKEDJIRAISSUE_ID) values
('58720339', 'SS-13395', '0', '58786689');
ERROR: insert or update on table "brs_linkedjiraissues" violates foreign key constraint "fk45b7017da958b29f"
DETAIL: Key (buildresultsummary_id)=(58720339) is not present in table "buildresultsummary".
The solution is to clean the orphan rows that refer to primary keys in other tables (which no longer exist).
Here is a list of SQL commands that you could use to check for orphaned records.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * FROM TEST_ERROR WHERE RESULT_ID in (SELECT TEST_CASE_RESULT_ID FROM TEST_CASE_RESULT WHERE TEST_CASE_ID in (SELECT TEST_CASE_ID FROM TEST_CASE WHERE TEST_CLASS_ID in (SELECT TEST_CLASS_ID FROM TEST_CLASS_RESULT WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY))));
SELECT * FROM BUILDRESULTSUMMARY_LABEL WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
SELECT * FROM COMMIT_FILES WHERE COMMIT_ID in (SELECT COMMIT_ID FROM USER_COMMIT WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY));
SELECT * FROM VARIABLE_CONTEXT WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
SELECT * FROM BRS_LINKEDJIRAISSUES WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
SELECT * FROM BUILDRESULTSUMMARY_CUSTOMDATA WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
SELECT * FROM USER_COMMENT WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
SELECT * FROM BRS_ARTIFACT_LINK WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY) and PRODUCERJOBRESULT_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
SELECT * FROM VARIABLE_SUBSTITUTION WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);When the discrepancies are found, use these SQL commands to delete them:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELETE FROM TEST_ERROR WHERE RESULT_ID in (SELECT TEST_CASE_RESULT_ID FROM TEST_CASE_RESULT WHERE TEST_CASE_ID in (SELECT TEST_CASE_ID FROM TEST_CASE WHERE TEST_CLASS_ID in (SELECT TEST_CLASS_ID FROM TEST_CLASS_RESULT WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY))));
DELETE FROM BUILDRESULTSUMMARY_LABEL WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
DELETE FROM COMMIT_FILES WHERE COMMIT_ID in (SELECT COMMIT_ID FROM USER_COMMIT WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY));
DELETE FROM VARIABLE_CONTEXT WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
DELETE FROM BRS_LINKEDJIRAISSUES WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
DELETE FROM BUILDRESULTSUMMARY_CUSTOMDATA WHERE BUILDRESULTSUMMARY_ID not in (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY);
DELETE FROM USER_COMMENT WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
DELETE FROM BRS_ARTIFACT_LINK WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
DELETE FROM VARIABLE_SUBSTITUTION WHERE BUILDRESULTSUMMARY_ID not in (SELECT BUILDRESULTSUMMARY_ID FROM BUILDRESULTSUMMARY);
Was this helpful?