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

Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.

Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Summary

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

it becomes impossible to enable, disable and publish some automation rules:
- When trying to enable or disable a rule, the following error is thrown in the UI:
  1 2 3Error 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.
Additionally, when trying to manage the Secret Keys (feature introduced in Jira 9.0.1) 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.

Diagnosis

Either of the upgrades listed below was performed before the errors happened:
- The add-on Automation for Jira was upgraded to 9.0.1 or any higher version (from a version below 9.0.1) via the page ⚙ > Manage Apps > Manage Apps
- OR the Jira Software application was upgraded to 9.11.0 or any higher version (from a version below 9.11.0)
- OR the Jira Service Management application was upgraded to 5.11.0 or any higher version (from a version below 5.11.0)
The Automation for Jira app was installed in the Jira application at some point with a version lower than 7.3.0
The Jira application is configured with one of the 2 types of Database listed below:
- MySQL
- MS SQL Server
Either of the 2 errors below can be found in the Jira application logs:
- Error 1 (If Jira is configured with MS SQL)
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 292023-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) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410) at com.querydsl.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:342) at com.querydsl.core.group.GroupByMap.transform(GroupByMap.java:54) at com.querydsl.core.group.GroupByMap.transform(GroupByMap.java:35) at com.querydsl.core.support.FetchableQueryBase.transform(FetchableQueryBase.java:55) at com.codebarrel.jira.plugin.automation.store.JiraRuleSecretStore.lambda$getAllSecrets$1(JiraRuleSecretStore.java:47) at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74) ... Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'jira.AO_589059_RULE_SECRET'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
- Error 2 (If Jira is configured with MySQL)
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 262023-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) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410) at com.querydsl.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:342) at com.querydsl.core.group.GroupByMap.transform(GroupByMap.java:54) at com.querydsl.core.group.GroupByMap.transform(GroupByMap.java:35) at com.querydsl.core.support.FetchableQueryBase.transform(FetchableQueryBase.java:55) at com.codebarrel.jira.plugin.automation.store.JiraRuleSecretStore.lambda$getSecretsByProject$15(JiraRuleSecretStore.java:174) at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74) ... Caused by: java.sql.SQLSyntaxErrorException: Table 'jiradb.ao_589059_rule_secret' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)

Cause

The SQL error occurs because the DB table AO_589059_RULE_SECRET, introduced with A4J 9.0.1, failed to be created during the A4J upgrade.

The table failed to be created because the SQL script that needed to be manually executed before upgrading A4J from a lower version to 7.3.0 had not been executed.

As explained in the documentation Upgrading to Automation for Jira 7.3 or later, some SQL scripts need to be manually run in the Jira DB by a DB Admin, if the 2 conditions below are met:

Jira is connected to either a MySQL or a MS SQL Database
A4J is about to be upgraded from a version lower to 7.3.0 to a version >= 7.3.0

If A4J was at some point in time installed on a version lower than 7.3.0, and if the script was never executed, then after the upgrade to 9.0.1 the following will happen:

A4J will fail to complete its upgrade tasks due to some DB constraint issues (that would have been fixed with the script)
As a result, it will fail to create the table AO_589059_RULE_SECRET
Various issues will be observed in the UI:
- accessing the page ⚙ > System > Automation for Jira > ... > Manage Secret Keys
  will fail with some error
- editing or publishing some automation rules will fail with some error

Bug Fix

A new bug ticket has been raised in our public issue tracker to keep track of this issue: https://jira.atlassian.com/browse/JIRAAUTOSERVER-844

The fix for this bug was released with A4J version 9.0.2. It consists of executing the SQL scripts for Jira applications using the MySQL or MSSQL Database, which should have been executed when 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.

Solution

Since the fix for the bug https://jira.atlassian.com/browse/JIRAAUTOSERVER-844 only applies to customers who will upgrade directly from 8.x to 9.0.2 (or a higher version), upgrading to this version will not help when A4J is already on 9.0.1.

For this reason, the only way to fix this issue is to run some SQL Scripts manually in the Jira Database, by following the steps below:

Stop the Jira application
Download the relevant script file for the type of Database used by the Jira application as per step 5 in Upgrading to Automation for Jira 7.3
Run the script on the Jira Database
Start the Jira application

Notes about the SQL scripts

Note 1

If you face errors altering the table even after dropping the foreign key constraints, you might have some unexpected additional foreign key constraints in the table which needs to be dropped. You can run the below queries to identify all constraints in the table. Please reach out to Atlassian Support and share the results of this query if you are unsure which needs to be dropped.

--MySQL
select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME = '<table_name>';
 
--MSSQL
SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('<table_name>');

Note 2

When using the SQL script that is meant for the MSSQL Database, you might run into some of the errors below:

Error indicating that an index cannot be dropped from the script because it does not exist, for example:

1
2

Error executing query:
SQL Error [3701] [S0007]: Cannot drop the index 'dbo.AO_589059_AUDIT_ITEM_PROJECT.index_ao_589059_aud2135433486', because it does not exist or you do not have permission.

Error indicating that a table column could not be altered because an index is dependent on it:

1
2
3

The index 'nci_wi_AO_589059_AUDIT_ITEM_PROJECT_XXXXXXXXXXXX' is dependent on column 'AUDIT_ITEM_ID'.
 
ALTER TABLE ALTER COLUMN AUDIT_ITEM_ID failed because one or more objects access this column.

These errors usually come from the fact that some custom indexes have been created for the A4J database tables, which don't come out-of-the-box with A4J. The SQL scripts provided in the documentation Upgrading to Automation for Jira 7.3 or later were written and tested on a Jira application using A4J out-of-the-box.

To fix the errors from the script, we recommend following the steps below:

Execute the SQL below below to get the full list of indexes used by the A4J DB tables:

SELECT
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.*
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
WHERE
     ind.is_primary_key = 0
     AND ind.is_unique = 0
     AND ind.is_unique_constraint = 0
     AND t.is_ms_shipped = 0
     AND t.name like 'AO_589059_%'
ORDER BY
t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

Update the MS SQL Script to reflect the name of the indexes

For further help about how to fix these errors:

You can check the KB article How to fix the SQL script to resolve the Automation For Jira error "Error while deleting/updating rule" which has more details
Or you can reach out to Atlassian Support via our support portal

Updated on June 7, 2024

Was this helpful?

It wasn't accurate

It wasn't clear

It wasn't relevant