Import & Export fail due to SQL Corruption.

Still need help?

The Atlassian Community is here for you.

Ask the community

Exporting & Importing Bamboo data may fail due to database corruption; you might see errors similar to:

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.

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:

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);

 

 

 

 

Last modified on Jun 19, 2013

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.