要約

Project deletion in Jira Data Center is a slow operation, because it is a single threaded process and to maintain data consistency.

When each issue is deleted in the project, Jira needs to delete:

• Comments;
• Work logs;
• History items;
• Sub-tasks;
• Issue Links;
• History of moved issue keys;
• Issue properties;
• Attachments;
• Custom field values;
• Other internal DB entries;
• Index entry

Some of these items are stored in typically large tables (such as comments and history) and issue link removal triggers a reindex in the other linked issue, to clear the issue link completely.

Usually, the slowness is not a problem if the admin removes the Browse Project permission from the project before starting the project delete operation - the users will already have the perception the project isn't available anymore.

This page is a procedure to delete data before starting the project deletion, so it is sped up, which is particularly useful when deleting several projects. Keep in mind we are accepting that the data for the issues in the project will be in an inconsistent state before deletion - for example, after we delete the comments in the database, the search will still find comments because we didn't remove them from the index yet.

In our tests, the procedure below was able to make the project deletion take only 10% of the original time, but your mileage might vary depending on how the data is distributed - for better or worse.

環境

Jira Data Center

Queries were tested in PostgreSQL.

ソリューション

In the queries and commands below, we'll refer the project key to be deleted as <project_key>. For example, if your project key is ABC, just replcace <project_key> by ABC

These queries will lock data and might take a while to finish, depending on the number of entries in each table. Avoid running them during business hours.

1. Take a database backup.
2. Remove the Browse Project permission from the users in the project, or remove the users from the project roles (depending on how it was set up).

3. Let's start by deleting comments:

   DELETE FROM comment_reaction
   WHERE id in (SELECT cr.id
       FROM comment_reaction cr
   	JOIN jiraaction a on comment_id=a.id
       JOIN jiraissue i ON a.issueid=i.id
       JOIN project p on i.project=p.id
       WHERE p.pkey='<project_key>');

   DELETE FROM comment_pin
   WHERE id in (SELECT c.id
       FROM comment_pin c
       JOIN jiraissue i ON c.issue_id=i.id
       JOIN project p on i.project=p.id
       WHERE p.pkey='<project_key>');

   DELETE FROM comment_version
   WHERE comment_id in (SELECT cv.comment_id
       FROM comment_version cv
   	JOIN jiraaction a on comment_id=a.id
       JOIN jiraissue i ON a.issueid=i.id
       JOIN project p on i.project=p.id
       WHERE p.pkey='<project_key>' );

   DELETE FROM jiraaction
   WHERE id in (SELECT a.id
       FROM jiraaction a
       JOIN jiraissue i ON a.issueid=i.id
       JOIN project p on i.project=p.id
       WHERE p.pkey='<project_key>');

4. Remove issue history:

   DELETE FROM changeitem
   WHERE id in (
   	SELECT ci.id
   	FROM changegroup cg
   	JOIN changeitem ci ON ci.groupid=cg.id
   	JOIN jiraissue i ON cg.issueid=i.id
   	JOIN project p on i.project=p.id
   	WHERE p.pkey='<project_key>');

   DELETE FROM changegroup
   WHERE id in (
   	SELECT cg.id
   	FROM changegroup cg
   	JOIN jiraissue i ON cg.issueid=i.id
   	JOIN project p on i.project=p.id
   	WHERE p.pkey='<project_key>');

5. Remove work logs:

   DELETE FROM worklog_version
   WHERE worklog_id in (
   	SELECT wv.worklog_id
   	FROM worklog_version wv
   	JOIN jiraissue i ON wv.parent_issue_id=i.id
   	JOIN project p on i.project=p.id
   	WHERE p.pkey='<project_key>');

   DELETE FROM worklog
   WHERE id in (
   	SELECT w.id
   	FROM worklog w
   	JOIN jiraissue i ON w.issueid=i.id
   	JOIN project p on i.project=p.id
   	WHERE p.pkey='<project_key>');

6. Remove attachment references from the DB:

   DELETE FROM fileattachment
   WHERE id in (
   	SELECT f.id
   	FROM fileattachment f
   	JOIN jiraissue i ON f.issueid=i.id
   	JOIN project p on i.project=p.id
   	WHERE p.pkey='<project_key>');

7. Remove attachment references from the file system:
   

   1. Get the original project key using the query:

      SELECT originalkey FROM project WHERE pkey='<project_key>';

   2. Delete the directory <shared-home>/data/attachmnents/<original_project_key>

8. Remove issue links:

   DELETE FROM issuelink
   WHERE id in (
       SELECT il.id
   	FROM issuelink il
   	JOIN jiraissue iss ON il.source=iss.id
   	JOIN project ps on iss.project=ps.id
   	JOIN jiraissue id ON il.destination=id.id
   	JOIN project pd on id.project=pd.id
   	WHERE pd.pkey='<project_key>' OR ps.pkey='<project_key>');

9. Initiate the project delete from Jira's UI.
10. If deleting several projects, repeat steps 2-9 for each project;
11. Perform a full reindex. Otherwise, the objects we've deleted from the database will linger in the index files. These shouldn't cause immediate issues, but they're supposed to be removed and because there is no DB reference to them anymore, only a full reindex will do the job.