Symptoms

Many hundreds/thousands files were added to a Crucible Review and users are not able to open the Review any more

Diagnosis

Run the following SQL command to check how many revisions added to the review:

SELECT * FROM cru_frx_revision WHERE cru_frx_id IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = (SELECT cru_review_id FROM cru_revpermaid t WHERE t.cru_proj_key='<Crucible_Project_Key>' and t.cru_number=<Review_Number>));

Cause

Hundreds/Thousands files would require long and heavy operations to be run in the database, which may take hours to finish and overload the database server. The operation may timeout.

Resolution

Since the Review can't be opened from the user interface, the best solution would be to remove it from the database:

1. Using the review title search for the review_id for the review that needs to deleted:

   SELECT cru_review_id
   FROM cru_review
   WHERE cru_name = "<Review_Title>";

   OR:

   SELECT * FROM cru_revpermaid t WHERE t.cru_proj_key='<Crucible_Project_Key>' and t.cru_number=<Review_Number>;

2. Using the cru_review_id obtained in the first command, replace string <REVIEW_ID> in all the following commands and then run them in the database:

   DELETE FROM cru_invitees WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_logitem WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_revpermaid WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_recipient WHERE cru_notification_id IN (SELECT cru_notification_id FROM cru_notification WHERE cru_review = <REVIEW_ID>);
   DELETE FROM cru_notification WHERE cru_review = <REVIEW_ID>;
   DELETE FROM cru_patch_revision WHERE cru_patch_id IN (SELECT cru_patch_id FROM cru_patch WHERE cru_review = <REVIEW_ID>);
   DELETE FROM cru_patch WHERE cru_review = <REVIEW_ID>;
   DELETE FROM cru_inline_comment_to_frx_rev WHERE cru_frx_rev_id IN (SELECT cru_frx_rev_id FROM cru_frx_revision WHERE cru_frx_id IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = <REVIEW_ID>));
   DELETE FROM cru_frx_revision WHERE cru_frx_id IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_file_read_status WHERE cru_frx IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_inline_comment WHERE cru_frx_id IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_frx_comment WHERE cru_frx_id IN (SELECT cru_frx_id FROM cru_frx WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_frx WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_review_field WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_review_comment WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_comment_read_status WHERE cru_comment IN (SELECT cru_comment_id FROM cru_comment WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_comment_field WHERE cru_comment_id IN (SELECT cru_comment_id FROM cru_comment WHERE cru_review_id = <REVIEW_ID>);
   DELETE FROM cru_comment WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_review_participant WHERE cru_review_id = <REVIEW_ID>;
   DELETE FROM cru_review WHERE cru_review_id = <REVIEW_ID>;

3. Upgrade to Fisheye/Crucible 2.10.0+ that has limit the number of files in review CRUC-5628 - Getting issue details... STATUS