-
Bug
-
Resolution: Fixed
-
Medium
-
4.0.0, 4.0.2, 4.1.0
-
22
-
Severity 2 - Major
-
85
-
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:
- Create a new Service Desk project.
- 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
- is duplicated by
-
JSDSERVER-6341 This is a regression bug of JSDSERVER-3232
-
- Closed
-
- is related to
-
JSDSERVER-6341 This is a regression bug of JSDSERVER-3232
-
- Closed
-
- mentioned in
-
Page Failed to load
-
Page Failed to load
-
Page Failed to load
-
Page Loading...
-
Page Loading...
- relates to
-
JSMDC-3966 Loading...
Queries that worked on mysql: