Uploaded image for project: 'Jira Service Management Data Center'
  1. Jira Service Management Data Center
  2. JSDSERVER-6324

Deleting a JIRA Service Desk project will not remove Automation rule data in the database and will add a lot of noise to the JIRA logs

      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

            [JSDSERVER-6324] Deleting a JIRA Service Desk project will not remove Automation rule data in the database and will add a lot of noise to the JIRA logs

            Queries that worked on mysql:

             

            # identify 
            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);
            
            # act
            update AO_9B2E3B_RULE set ENABLED='0' where ID in ( select * from ( 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)) a );
            

             

             

            Jan Szczyra added a comment - Queries that worked on mysql:   # identify 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); # act update AO_9B2E3B_RULE set ENABLED= '0' where ID in ( select * from ( 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)) a );    

            David Yu added a comment - - edited

            In case anyone is googling, on Jira 8.1, it'll generate a lot of excess errors in the logs when transitioning issues.

            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
            at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.httpStatusCodeToException(EitherExceptionUtils.java:107)
            at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.anErrorToException(EitherExceptionUtils.java:52)
            at io.atlassian.fugue.Either$Left.fold(Either.java:586)
            at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.anErrorEitherToException(EitherExceptionUtils.java:45)
            

            David Yu added a comment - - edited In case anyone is googling, on Jira 8.1, it'll generate a lot of excess errors in the logs when transitioning issues. 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 at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.httpStatusCodeToException(EitherExceptionUtils.java:107) at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.anErrorToException(EitherExceptionUtils.java:52) at io.atlassian.fugue.Either$Left.fold(Either.java:586) at com.atlassian.servicedesk.plugins.automation.internal.EitherExceptionUtils.anErrorEitherToException(EitherExceptionUtils.java:45)

              esantos2 Elton Santos
              mmuthusamy Moga
              Affected customers:
              9 This affects my team
              Watchers:
              20 Start watching this issue

                Created:
                Updated:
                Resolved: