Summary

Deleting a JIRA Service Desk project will not remove Automation rule data in the database.

Steps to Reproduce

Assuming that this is a newly installed JIRA Service Desk:

1. Create a new Service Desk project.
2. Delete the new project.

Expected Results

When deleting a JIRA Service Desk project, the Service Desk data related to the project should be deleted as well.

Actual Results

The Automation rules still exist in the database:

• After project creation:

  j802=# select * from "AO_54307E_SERVICEDESK";
  -[ RECORD 1 ]--------------+------------------------
  CREATED_BY_USER_KEY        | admin
  CREATED_DATE               | 2019-04-15 13:29:21.816
  CREATED_WITH_EMPTY_PROJECT | t
  DISABLED                   |
  ID                         | 1
  LEGACY_TRANSITION_DISABLED | t
  OPEN_CUSTOMER_ACCESS       | 1
  PROJECT_ID                 | 10000
  PROJECT_KEY                | _UNUSED
  PUBLIC_SIGNUP              |
  VERSION_CREATED_AT         | 4.0.2-REL-0004
  
  
  j802=# select * from "AO_9B2E3B_RSETREV_PROJ_CONTEXT";
  -[ RECORD 1 ]-------+------
  ID                  | 1
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 1
  -[ RECORD 2 ]-------+------
  ID                  | 2
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 2
  -[ RECORD 3 ]-------+------
  ID                  | 3
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 3
  -[ RECORD 4 ]-------+------
  ID                  | 4
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 4
  -[ RECORD 5 ]-------+------
  ID                  | 5
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 5
  -[ RECORD 6 ]-------+------
  ID                  | 6
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 6
  -[ RECORD 7 ]-------+------
  ID                  | 7
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 7
  -[ RECORD 8 ]-------+------
  ID                  | 8
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 8
  -[ RECORD 9 ]-------+------
  ID                  | 9
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 9
  -[ RECORD 10 ]------+------
  ID                  | 10
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 10
  -[ RECORD 11 ]------+------
  ID                  | 11
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 11
  

• After project deletion:

  j802=# select * from "AO_54307E_SERVICEDESK";
  (No rows)
  j802=# select * from "AO_9B2E3B_RSETREV_PROJ_CONTEXT";
  -[ RECORD 1 ]-------+------
  ID                  | 1
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 1
  -[ RECORD 2 ]-------+------
  ID                  | 2
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 2
  -[ RECORD 3 ]-------+------
  ID                  | 3
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 3
  -[ RECORD 4 ]-------+------
  ID                  | 4
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 4
  -[ RECORD 5 ]-------+------
  ID                  | 5
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 5
  -[ RECORD 6 ]-------+------
  ID                  | 6
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 6
  -[ RECORD 7 ]-------+------
  ID                  | 7
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 7
  -[ RECORD 8 ]-------+------
  ID                  | 8
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 8
  -[ RECORD 9 ]-------+------
  ID                  | 9
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 9
  -[ RECORD 10 ]------+------
  ID                  | 10
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 10
  -[ RECORD 11 ]------+------
  ID                  | 11
  PROJECT_ID          | 10000
  RULESET_REVISION_ID | 11
  

Notes

• When finding orphan automations, it seem that they are triggered by different events that might even be happening in other projects, and this causes an excessive logging (as all orphan automations will give one of these exceptions, having more than 50 will cause logs to be flodded pretty fast. The errors are like these:

  2019-04-19 15:51:20,494 SdOffThreadEventJobRunner:thread-102 ERROR xxxx 951x38885x1 nr5e76 127.0.0.1 /secure/CommentAssignIssue.jspa [c.a.s.p.a.modules.rulewhen.ServiceDeskCommentCreatedEventWhenHandler] isApplicableProject failed
  com.atlassian.servicedesk.api.BadRequestException: Error retrieving ProjectContext, project '17000' does not exist

Workaround

The workaround consists in disabling directly in the database all the automation rules which are associated to projects which no longer exist.
To identify orphan Automation rules, we can use this SQL:

select rpc."PROJECT_ID" as "rpc.PROJECT_ID", rr."ID" as "rr.ID", rr."NAME" as "Rule Set Name", rr."DESCRIPTION" as "Rule Set Description", rr."RULE_SET_ID" as "rr.RULE_SET_ID", rr."TRIGGER_FROM_OTHER_RULES", rs."ID" as "rs.ID", r."ID" as "r.ID", r."RULESET_REVISION_ID" as "r.RULESET_REVISION_ID", r."ENABLED"
from "AO_9B2E3B_RSETREV_PROJ_CONTEXT" rpc 
join "AO_9B2E3B_RULESET_REVISION" rr on rr."ID" = rpc."RULESET_REVISION_ID"
join "AO_9B2E3B_RULE" r on r."RULESET_REVISION_ID" = rr."ID"
join "AO_9B2E3B_RULESET" rs on rs."ID" = rr."RULE_SET_ID"
where rpc."PROJECT_ID" not in (select id from project);

The steps to disable the orphan automations are the following:

• Stop JIRA
• Backup the JIRA database
• Run the following query in the JIRA database
  Note that this query was tested on a PostgreSQL database and that it might need to be slightly changed depending on the database type

   update "AO_9B2E3B_RULE" set "ENABLED"='false' where "ID" in ( select r."ID" 
   from "AO_9B2E3B_RSETREV_PROJ_CONTEXT" pc
   left join "AO_9B2E3B_RULESET_REVISION" rr on pc."RULESET_REVISION_ID" = rr."ID"
   left join "AO_9B2E3B_RULE" r on rr."ID" = r."RULESET_REVISION_ID"
   left join "AO_9B2E3B_WHEN_HANDLER_CONFIG" whc on r."ID" = whc."RULE_ID"
   where pc."PROJECT_ID" not in (select id from project)); 
  

• Start JIRA