Automation for Jira not working after upgrading Jira or updating Automation for Jira
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
Automation is not working as expected. Errors are seen in the UI when trying to modify existing rules.
Diagnosis
Possibly recently upgraded Jira
Recently updated Automation for Jira to v7.3 or above
Automation for Jira is not working as expected. When viewing project automation, you might face the following error
1 2 3Error Error while updating rule. Error Please reload and try again.
Automation tables-related errors are seen in the logs. Some examples are below
1
2
3
4
5
6
7
8
9
10
11
12
13
2022-06-02 16:58:01,559+0000 http-nio-127.0.0.1-8082-exec-23 ERROR admin 1018x25070x1 1govjlw 123.45.678.901,10.11.12.13,127.0.0.1 /rest/cb-automation/latest/project/13800/rule/3470 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught MySQLSyntaxErrorException for select distinct `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID`, `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID`
from `AO_589059_AUTOMATION_QUEUE` `AO_589059_AUTOMATION_QUEUE`
where `AO_589059_AUTOMATION_QUEUE`.`RULE_ID` = ? and `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID` is not null and `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID` is not null
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for select distinct `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID`, `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID`
from `AO_589059_AUTOMATION_QUEUE` `AO_589059_AUTOMATION_QUEUE`
where `AO_589059_AUTOMATION_QUEUE`.`RULE_ID` = ? and `AO_589059_AUTOMATION_QUEUE`.`AUDIT_ITEM_ID` is not null and `AO_589059_AUTOMATION_QUEUE`.`EXECUTION_UUID` is not null
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AO_589059_AUTOMATION_QUEUE.EXECUTION_UUID' in 'field list'
at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...1
2
3
4
5
6
7
8
9
10
11
12
2022-06-02 16:58:15,786+0000 Caesium-1-3 ERROR ServiceRunner [c.c.j.p.automation.schedule.RuleInsightsUpdateJob] Error executing rule insights update job
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for select `AO_589059_RULE_STAT_ROLLUP_HR`.`CREATED`
from `AO_589059_RULE_STAT_ROLLUP_HR` `AO_589059_RULE_STAT_ROLLUP_HR`
order by `AO_589059_RULE_STAT_ROLLUP_HR`.`CREATED` desc
limit ?
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jiradb.AO_589059_RULE_STAT_ROLLUP_HR' doesn't exist
at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...1
2
3
4
5
6
7
8
9
10
11
12
2022-06-02 17:00:15,781+0000 Caesium-1-1 ERROR ServiceRunner [c.c.j.p.automation.schedule.RuleInsightsUpdateJob] Error executing rule insights update job
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for select `AO_589059_RULE_STAT_ROLLUP_MIN`.`CREATED`
from `AO_589059_RULE_STAT_ROLLUP_MIN` `AO_589059_RULE_STAT_ROLLUP_MIN`
order by `AO_589059_RULE_STAT_ROLLUP_MIN`.`CREATED` desc
limit ?
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jiradb.AO_589059_RULE_STAT_ROLLUP_MIN' doesn't exist
at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...1
2
3
4
5
6
7
8
9
10
2022-06-02 17:00:24,465+0000 Caesium-1-2 ERROR anonymous [c.c.j.p.automation.schedule.AutomationRuleSchedulerJob] Error executing rule scheduler service job
com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException for insert into `AO_589059_AUTOMATION_QUEUE` (`CLAIM_COUNT`, `CLIENT_KEY`, `CREATED`, `PAYLOAD`, `PRIORITY`, `RULE_ID`, `EXECUTION_UUID`)
values (?, ?, ?, ?, ?, ?, ?)
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'EXECUTION_UUID' in 'field list'
at sun.reflect.GeneratedConstructorAccessor745.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...Cause
The update of Automation for Jira to v7.3 or above was not performed in accordance with the requirements as per this documentation: Upgrading to Automation for Jira 7.3
Automation for Jira 7.3 uses different types of IDs in database tables—
bigintegerinstead ofinteger. If you're upgrading from earlier versions, the IDs will be migrated to the new type automatically after upgrading, but we need you to complete some prerequisite steps to make it successful. More details in the linked documentation Upgrading to Automation for Jira 7.3
Solution
Perform the following steps to ensure that the update of Automation for Jira is successfully performed.
Uninstall Automation for Jira as per Uninstalling an app
Stop Jira
Create a backup of your Jira database
Delete data from database tables as per Upgrading to Automation for Jira 7.3
For Postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- AUTOMATION QUEUE TABLE
delete from "AO_589059_AUTOMATION_QUEUE";
-- AUDIT LOG TABLES
delete from "AO_589059_AUDIT_ITEM_PROJECT";
delete from "AO_589059_AUDIT_ITEM_ASC_ITEM";
delete from "AO_589059_AUDIT_ITEM_CGE_ITEM";
delete from "AO_589059_AUDIT_ITEM_COMP_CGE";
delete from "AO_589059_AUDIT_ITEM";
-- RULE STAT TABLES
delete from "AO_589059_RULE_STAT";
delete from "AO_589059_RULE_STAT_ROLLUP_DAY";
delete from "AO_589059_RULE_STAT_ROLLUP_HR";
delete from "AO_589059_RULE_STAT_ROLLUP_MIN";
For SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- AUTOMATION QUEUE TABLE
delete from jiraschema.AO_589059_AUTOMATION_QUEUE;
-- AUDIT LOG TABLES
delete from jiraschema.AO_589059_AUDIT_ITEM_PROJECT;
delete from jiraschema.AO_589059_AUDIT_ITEM_ASC_ITEM;
delete from jiraschema.AO_589059_AUDIT_ITEM_CGE_ITEM;
delete from jiraschema.AO_589059_AUDIT_ITEM_COMP_CGE;
delete from jiraschema.AO_589059_AUDIT_ITEM;
-- RULE STAT TABLES
delete from jiraschema.AO_589059_RULE_STAT;
delete from jiraschema.AO_589059_RULE_STAT_ROLLUP_DAY;
delete from jiraschema.AO_589059_RULE_STAT_ROLLUP_HR;
delete from jiraschema.AO_589059_RULE_STAT_ROLLUP_MIN;For SQL Server, you'll need to replace the "jiraschema" name used above with the name of your database schema.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- AUTOMATION QUEUE TABLE
delete from AO_589059_AUTOMATION_QUEUE;
-- AUDIT LOG TABLES
delete from AO_589059_AUDIT_ITEM_PROJECT;
delete from AO_589059_AUDIT_ITEM_ASC_ITEM;
delete from AO_589059_AUDIT_ITEM_CGE_ITEM;
delete from AO_589059_AUDIT_ITEM_COMP_CGE;
delete from AO_589059_AUDIT_ITEM;
-- RULE STAT TABLES
delete from AO_589059_RULE_STAT;
delete from AO_589059_RULE_STAT_ROLLUP_DAY;
delete from AO_589059_RULE_STAT_ROLLUP_HR;
delete from AO_589059_RULE_STAT_ROLLUP_MIN;6. Restart Jira
7. Install Automation for Jira a. Go to Administration > Find apps, search for and install Automation for Jira
If using MySQL or SQL Server database, continue with the steps below.
Stop Jira again
Download the relevant scripts for your database as per step 5 in Upgrading to Automation for Jira 7.3
Run the scripts on your database
Restart Jira
Note about scripts
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.
1
2
3
4
5
--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>');
Was this helpful?