Database Corruption - cleaning rows marked for deletion

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

症状

We see this type of errors in the Bamboo logs:

2012-01-03 21:05:05,283 INFO [QuartzScheduler_Worker-1] [DeletionServiceImpl] Deleting 1 TopLevelPlan(s) marked for deletion
2012-01-03 21:05:05,283 INFO [QuartzScheduler_Worker-1] [DeletionServiceImpl] Deleting MMM-INTEGRATION
2012-01-03 21:05:05,321 WARN [QuartzScheduler_Worker-1] [BuildResultsSummaryHibernateDao] Unexpected exception while executing profiler wrapper
org.postgresql.util.PSQLException: ERROR: update or delete on table "repository_changeset" violates foreign key constraint "fk34a3e6e3cbadfcbd" on table "relevant_changesets" Detail: Key (repository_changeset_id)=(6815755) is still referenced from table "relevant_changesets".
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
...

原因

Bamboo database runs deletion tasks on the background based on the marked_for_deletion column of BUILDRESULTSUMMARY table, and sometimes it fails causing database corruption.

ソリューション

The resolution of this type of database corruptions can be running SQL queries to delete the records from the database that refer to the fields that don't exist. Make sure that you have a Bamboo database backup and follow the steps below:

  1. Run Bamboo, and see on which field it is failing (usually Bamboo shows the table names, column names and the names of the constraints involved)
  2. Shut down Bamboo and delete the rows mentioned in the previous step
  3. Start Bamboo, and if there are any other errors, go to step 1.

Some SQL queries that might be useful in deleting the failing rows are mentioned here (in the example SQL queries there are many IDs and names used that need to be replaced with the ones specific to each corruption case):

DELETE FROM COMMIT_FILES WHERE COMMIT_ID in (SELECT COMMIT_ID FROM USER_COMMIT WHERE repository_changeset_id in (SELECT repository_changeset_id FROM repository_changeset WHERE vcs_location_id = 7077891));
DELETE FROM USER_COMMIT WHERE repository_changeset_id in (SELECT repository_changeset_id FROM repository_changeset WHERE vcs_location_id = 7077891);
DELETE FROM repository_changeset WHERE vcs_location_id = 7077891;
DELETE FROM plan_vcs_location WHERE vcs_location_id = 7077891;

DELETE FROM COMMIT_FILES WHERE COMMIT_ID in (SELECT COMMIT_ID FROM USER_COMMIT WHERE repository_changeset_id in (SELECT repository_changeset_id FROM repository_changeset WHERE vcs_location_id = 7077892));
DELETE FROM USER_COMMIT WHERE repository_changeset_id in (SELECT repository_changeset_id FROM repository_changeset WHERE vcs_location_id = 7077892);
DELETE FROM relevant_changesets WHERE repository_changeset_id in (SELECT repository_changeset_id FROM repository_changeset WHERE vcs_location_id = 7077892);
DELETE FROM repository_changeset WHERE vcs_location_id = 7077892;
DELETE FROM plan_vcs_location WHERE vcs_location_id = 7077892;

DELETE FROM buildresultsummary_customdata where buildresultsummary_id in (6750233, 6750236, 6750239, 6750242, 6750245, 6750248, 6750251, 6750254);
DELETE FROM variable_substitution where buildresultsummary_id in (6750233, 6750236, 6750239, 6750242, 6750245, 6750248, 6750251, 6750254);
DELETE FROM brs_artifact_link where buildresultsummary_id in (6750233, 6750236, 6750239, 6750242, 6750245, 6750248, 6750251, 6750254);
DELETE FROM brs_artifact_link where PRODUCERJOBRESULT_ID in (6750233, 6750236, 6750239, 6750242, 6750245, 6750248, 6750251, 6750254);
DELETE FROM buildresultsummary where buildresultsummary_id in (6750233, 6750236, 6750239, 6750242, 6750245, 6750248, 6750251, 6750254);
DELETE FROM chain_stage_result WHERE Name = 'Mex';

DELETE FROM buildresultsummary_customdata where buildresultsummary_id in (6750232, 6750235, 6750238, 6750241, 6750244, 6750247, 6750250, 6750253)
DELETE FROM chain_stage_result WHERE Name = 'Java';

DELETE FROM buildresultsummary WHERE build_key = 'MMM-INTEGRATION';

The whole idea behind these SQL queries is to clean the database step by step based on the error that Bamboo shows after each deletion step.

 

最終更新日 2013 年 7 月 5 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.