Automation rules cannot be enabled, disabled or published after an upgrade of Automation for Jira to 9.0.1 or a higher version

Issue Summary

After upgrading Automation for Jira (A4J) from any version below 9.0.1 to the version 9.0.1 (or any higher version), the following issues might be observed:

• When trying to enable or disable a rule, the following error is thrown in the UI:

  Error
  Error while updating rule. Error
  Please reload and try again.
  

  

• When trying to publish a rule, the following error is thrown in the UI:

  An unknown error occurred. Please reload and try again
  

  

• When trying to manage the Secret Keys via the page ⚙ > System > Automation for Jira > ... > Manage Secret Keys, the following error is thrown in the UI:

  Something went wrong during the last request, please try again later.
  

  

Environment

• Jira Server/Data Center on any version from 8.20.0
• Automation For Jira (A4J) on any version from Jira 9.0.1
• This bug might only occur if Jira is using a MSSQL or MySQL Database (other data base types are not impacted), and if Automation for Jira was installed at some point with a version lower than 7.3.0

Steps to replicate

• Install Jira Software DC on 8.20.25, with a MSSQL DB Server
• Install A4J 7.2.0
• Upgrade A4J to 8.1.2
• Upgrade again A4J to 9.0.1
• Try to manage Secret Keys (feature introduced in Jira 9.0.1) via the page ⚙  > System > Automation for Jira, by clicking on ... > Manage Secret Keys.

Expected behavior

It should be possible to access the Manage Secret Keys page.

Actual behavior

The following error is thrown in the UI:

Something went wrong during the last request, please try again later.

Additionally, the following issues might also occur:

• When trying to enable or disable a rule, an error is thrown in the UI
• When trying to publish a rule, an error is thrown in the UI

The following error is thrown in the Jira logs:

• In case of MSSQL

  2023-08-31 14:53:40,570+0200 http-nio-80-exec-3 ERROR someuser 893x784x6 k0zbpv 0:0:0:0:0:0:0:1 /rest/cb-automation/latest/secrets/GLOBAL [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLServerException for select "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_SECRET_PROJ_ASSOC"."PROJECT_ID"
      from "jira"."AO_589059_RULE_SECRET" "AO_589059_RULE_SECRET"
      left join "jira"."AO_589059_SECRET_PROJ_ASSOC" "AO_589059_SECRET_PROJ_ASSOC"
      on "AO_589059_RULE_SECRET"."ID" = "AO_589059_SECRET_PROJ_ASSOC"."RULE_SECRET_ID"
  com.querydsl.core.QueryException: Caught SQLServerException for select "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_RULE_SECRET"."ID", "AO_589059_RULE_SECRET"."KEY", "AO_589059_RULE_SECRET"."VALUE", "AO_589059_SECRET_PROJ_ASSOC"."PROJECT_ID"
  from "jira"."AO_589059_RULE_SECRET" "AO_589059_RULE_SECRET"
  left join "jira"."AO_589059_SECRET_PROJ_ASSOC" "AO_589059_SECRET_PROJ_ASSOC"
  on "AO_589059_RULE_SECRET"."ID" = "AO_589059_SECRET_PROJ_ASSOC"."RULE_SECRET_ID"
      at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
   
  ...
  
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'jira.AO_589059_RULE_SECRET'.
  

• In case of MySQL

  2023-09-05 12:15:27,855+0000 http-nio-8080-exec-128 ERROR someuser 735x28584544x4 dguzsz 0:0:0:0:0:0:0:1 /rest/cb-automation/latest/secrets/11901 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLSyntaxErrorException for select `AO_589059_RULE_SECRET`.`ID`, `AO_589059_RULE_SECRET`.`KEY`, `AO_589059_RULE_SECRET`.`VALUE`, `AO_589059_RULE_SECRET`.`ID`, `AO_589059_RULE_SECRET`.`KEY`, `AO_589059_RULE_SECRET`.`VALUE`, `AO_589059_SECRET_PROJ_ASSOC`.`PROJECT_ID`
      from `AO_589059_RULE_SECRET` `AO_589059_RULE_SECRET`
      left join `AO_589059_SECRET_PROJ_ASSOC` `AO_589059_SECRET_PROJ_ASSOC`
      on `AO_589059_RULE_SECRET`.`ID` = `AO_589059_SECRET_PROJ_ASSOC`.`RULE_SECRET_ID`
  com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select `AO_589059_RULE_SECRET`.`ID`, `AO_589059_RULE_SECRET`.`KEY`, `AO_589059_RULE_SECRET`.`VALUE`, `AO_589059_RULE_SECRET`.`ID`, `AO_589059_RULE_SECRET`.`KEY`, `AO_589059_RULE_SECRET`.`VALUE`, `AO_589059_SECRET_PROJ_ASSOC`.`PROJECT_ID`
  from `AO_589059_RULE_SECRET` `AO_589059_RULE_SECRET`
  left join `AO_589059_SECRET_PROJ_ASSOC` `AO_589059_SECRET_PROJ_ASSOC`
  on `AO_589059_RULE_SECRET`.`ID` = `AO_589059_SECRET_PROJ_ASSOC`.`RULE_SECRET_ID`
      at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
  
  ...
  
  Caused by: java.sql.SQLSyntaxErrorException: Table 'jiradb.ao_589059_rule_secret' doesn't exist
  

Workaround

Follow the workaround steps described in the KB article Automation rules cannot be enabled, disabled or published after an upgrade of Automation for Jira to 9.0.1 or a higher version

Note about the fix introduced in the version 9.0.2

The fix consists in executing the SQL scripts for Jira applications using the MySQL or the MSSQL Database which should have been executed at the time A4J was upgraded to 7.3+ as per the documentation Upgrading to Automation for Jira 7.3 or later.

 Note that this fix will only work if A4J is upgraded directly from 8.x to 9.0.2 or any higher version. If A4J has already been upgraded to 9.0.1, upgrading to 9.0.2 or any higher version will not help. In this case, the SQL scripts need to be executed manually and directly in the Jira DB. Please refer to the KB article Automation rules cannot be enabled, disabled or published after an upgrade of Automation for Jira to 9.0.1 or a higher version for more details on how to do this.