Summary

Automation is not working as expected. Errors are seen in the UI when trying to modify existing rules.

Environment

Automation for Jira v7.3 and above

Jira 7.7.0 and above

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

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

  

  
  

• Automation tables related errors are seen in the logs. Some examples are below

  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)
  	...

  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)
  	...

  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)
  	...

  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 to 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—biginteger instead of integer. 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.

1. Uninstall Automation for Jira as per Uninstalling an app
2. Stop Jira
3. Create a backup of your Jira database

4. Delete data from database tables as per Upgrading to Automation for Jira 7.3

   For Postgres

   -- 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

   -- 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.

5. For MySql

   -- 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
   1. Go to Administration > Find apps, search for and install Automation for Jira

If using MySQL or SQL Server database, continue with the steps below. 

1. Stop Jira again
2. Download the relevant scripts for your database as per step 5 in Upgrading to Automation for Jira 7.3
3. Run the scripts on your database
4. 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.

--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>');