Automation for Jira Support

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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), users are no longer able to publish, edit, or save rules. Saving results in the error below:

An unknown error occurred. Please reload and try again.

If Automation For Jira was upgraded from any version below 9.0.1 to version 9.0.1 or above, the following issues might also 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:
```
Error
Error while updating rule. Error
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.
```

Environment

Environment for Root cause 1

Jira Server/Data Center on any version from 8.20.0
Automation For Jira (A4J) is:
- either currently on the version 9.0.1
- or was upgraded at some point to 9.0.1, and then later upgraded to a higher version
The Jira application is configured with one of the 2 types of Database listed below:
- MySQL
- MS SQL Server

Environment for Root cause 2

Jira Server/Data Center on a version lower than 9.17.0
Automation For Jira (A4J) is on the version 9.2.1 or any higher version

Diagnosis

Diagnosis for Root Cause 1

Either of the upgrade 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

Either of the 2 errors below can be found in the Jira application logs:

Error 1 (If Jira is configured with MS SQL)

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)
	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)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)

Error 2 (If Jira is configured with 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)
	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)

Diagnosis for Root Cause 2

Automation For Jira was upgraded to the version 9.2.1 or any higher version
Jira is on a version lower than 9.17.0

The error below (or a similar error is found in the Jira application logs:

2024-11-05 06:25:39,287-0600 http-nio-8080-exec-17 ERROR admin 385x663832x1 1vnu1k7 127.0.0.1 /rest/cb-automation/latest/project/GLOBAL/rule/1 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: com/atlassian/cache/ReadThroughCache
java.lang.NoClassDefFoundError: com/atlassian/cache/ReadThroughCache
	at com.codebarrel.jira.plugin.automation.service.ManualTriggerServiceImpl.ruleModified(ManualTriggerServiceImpl.java:245)
	at com.codebarrel.jira.plugin.automation.event.EventRuleRegistryLauncher$2.onRuleChanged(EventRuleRegistryLauncher.java:87)
	at com.codebarrel.automation.api.service.AutomationConfigServiceImpl.notifyChangeListenersRuleModified(AutomationConfigServiceImpl.java:566)
	at com.codebarrel.automation.api.service.AutomationConfigServiceImpl.validateAndStore(AutomationConfigServiceImpl.java:559)
	at com.codebarrel.automation.api.service.AutomationConfigServiceImpl.updateRule(AutomationConfigServiceImpl.java:322)
	at com.codebarrel.jira.plugin.automation.rest.RuleResource.updateRule(RuleResource.java:254)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

Cause

Root Cause 1

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

The reason why the table failed to be created is because the SQL script that needed to be manually executed before upgrading A4J from a version lower to 7.3.0 has 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

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 the A4J version 9.0.2, and it 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.

Root Cause 2

The reason why rules can't be enabled is because of the bug https://jira.atlassian.com/browse/JIRAAUTOSERVER-1079.

Due to this bug, A4J 9.2.1 does not work with Jira versions below 9.17.0.

Solution

Solution for Root Cause 1

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 ( Make sure you are running the scripts on the correct database where your current Jira is connected to).
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:

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:

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

Solution for Root Cause 2

Rollback the A4J upgrade to the previous stable version, by performing the steps below:

Go to Automation for Jira's Version history page and download A4J on a version lower than 9.2.1, for example 9.2.0
In the Jira application
- Go to the page ⚙ > Manage Apps > Manage Apps
- Look for the Automation For Jira app
- Un-install it
- Use the upload app button to install the A4J version downloaded from the version history page

Automation for Jira Support

Knowledge base

Products

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

Resources

Documentation

Community

System Status

Marketplace

Billing and licensing

"An unknown error occurred" when enabling or saving Jira Automation rules after upgrade

Still need help?

Summary

Environment

Environment for Root cause 1

Environment for Root cause 2

Diagnosis

Diagnosis for Root Cause 1

Diagnosis for Root Cause 2

Cause

Root Cause 1

Root Cause 2

Solution

Solution for Root Cause 1

Notes about the SQL scripts

Note 1

Note 2

Solution for Root Cause 2

Page

Viewport

Confluence

"An unknown error occurred" when enabling or saving Jira Automation rules after upgrade

Related content

Still need help?

Summary

Environment

Environment for Root cause 1

Environment for Root cause 2

Diagnosis

Diagnosis for Root Cause 1

Diagnosis for Root Cause 2

Cause

Root Cause 1

Root Cause 2

Solution

Solution for Root Cause 1

Notes about the SQL scripts

Note 1

Note 2

Solution for Root Cause 2

Related content