Automation For Jira - All the rules are stuck in the "In Progress" status
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
All automation rule fail to complete, as they are stuck in the "In Progress" status as per the Audit Logs:
Additionally, when using the "Run Rule" button for a rule using the "Scheduled" trigger, the rule fails to complete due to the error below:
1
There was an unexpected error while executing rule. Please contact your administrator.
The purpose of this KB article is to describe a possible root cause for these symptoms, how to identify it, and how to fix the issue.
Diagnosis
Look for the following error in the Jira application log, which indicated that the Jira application is failing to INSERT new rows to the DB table AO_589059_RULE_STAT, which is used by Automation For Jira:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2023-11-07 08:33:27,425+0000 http-nio-8080-exec-32 ERROR admin 513x394x1 e7r8xg 172.29.213.228,172.50.0.2 /rest/cb-automation/latest/project/GLOBAL/rule/1/execute [c.c.j.p.a.service.execution.JiraThreadLocalExecutor] Unexpected error in thread local executor with actorKey 'admin'.
com.querydsl.core.QueryException: Caught SQLIntegrityConstraintViolationException for insert into "AO_589059_RULE_STAT" ("AUDIT_ID", "CATEGORY", "CREATED", "DURATION", "RULE_ID", "EVENT_HASH", "QUEUED_ITEM_COUNT")
values (?, ?, ?, ?, ?, ?, ?)
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:445)
at com.codebarrel.jira.plugin.automation.store.JiraRuleStatisticsStore.lambda$storeRuleStatistic$1(JiraRuleStatisticsStore.java:78)
at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$executeWithRetry$1(JiraDbConnectionManager.java:55)
at com.atlassian.fugue.retry.RetrySupplier.get(RetrySupplier.java:93)
at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$executeWithRetry$2(JiraDbConnectionManager.java:55)
...
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("ATLASSIAN"."AO_589059_RULE_STAT"."ID")Check if the Oracle Trigger for the DB table AO_589059_RULE_STAT is missing by following the steps below:
Log into the Oracle DB as the same user that the Jira application uses to connect to the Database, and run the following SQL query:
1select * from user_triggers where "TRIGGER_NAME" = 'AO_589059_RULE_STAT_ID_autoinc';Normally, this query should return a row similar to the one below, which means that there is a trigger in the user_triggers table used for the table AO_589059_RULE_STAT:
1 2 3 4 5 6 7"TRIGGER_NAME","TRIGGER_TYPE","TRIGGERING_EVENT","TABLE_OWNER","BASE_OBJECT_TYPE","TABLE_NAME","COLUMN_NAME","REFERENCING_NAMES","WHEN_CLAUSE","STATUS","DESCRIPTION","ACTION_TYPE","TRIGGER_BODY","CROSSEDITION","BEFORE_STATEMENT","BEFORE_ROW","AFTER_ROW","AFTER_STATEMENT","INSTEAD_OF_ROW","FIRE_ONCE","APPLY_SERVER_ONLY" "AO_589059_RULE_STAT_ID_autoinc","BEFORE EACH ROW","INSERT","ATLASSIAN","TABLE","AO_589059_RULE_STAT",,"REFERENCING NEW AS NEW OLD AS OLD",,"ENABLED","""AO_589059_RULE_STAT_ID_autoinc"" BEFORE INSERT ON ""AO_589059_RULE_STAT"" FOR EACH ROW ","PL/SQL ","BEGIN SELECT ""AO_589059_RULE_STAT_ID_SEQ"".NEXTVAL INTO :NEW.""ID"" FROM DUAL; ","NO","NO","NO","NO","NO","NO","YES","NO"If this query does not return any row, then this KB article is relevant and you can move on to the Cause and Solution sections.
Cause
The Oracle DB trigger for the table AO_589059_RULE_STAT is missing, preventing the Jira application from inserting new rows to this table, and causing the SQL exception Error Msg = ORA-01400: cannot insert NULL into ("ATLASSIAN"."AO_589059_RULE_STAT"."ID") in the Jira logs.
Normally this trigger should be there by default in any fresh Jira installation using Automation For Jira. However, it is possible that the trigger got accidentally deleted manually in the Oracle DB, which is outside of Jira's control.
Solution
To fix this error, we need to manually re-create the missing trigger in the Oracle DB by following the steps below:
Log into the Oracle DB as the same user that the Jira application uses to connect to the Database, and run the following SQL query:
Run the following command to re-create the missing trigger:
1
2
3
4
5
6
create or replace TRIGGER "AO_589059_RULE_STAT_ID_autoinc"
BEFORE INSERT
ON "AO_589059_RULE_STAT" FOR EACH ROW
BEGIN
SELECT "AO_589059_RULE_STAT_ID_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
END;Run the following SQL query and confirm that you are now getting some result:
1
select * from user_triggers where "TRIGGER_NAME" = 'AO_589059_RULE_STAT_ID_autoinc';
Try again to trigger some automation rules and verify that they complete successfully
Was this helpful?