Automation for Jira missing data in performance insights due to sequence error
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 for Jira performance insights is missing executions and doesn't seem to match the rule audit logs after upgrading from a version previous to 7.3 to 7.3 or higher.
Environment
Automation for Jira 7.3 or higher in Jira Data Center.
Queries built for PostgreSQL. For MySQL or SQL Server, check the scripts present at Upgrading to Automation for Jira 7.3 or later
Diagnosis
In the atlassian-jira.log file, we see errors like this:
1
2
3
4
5
6
7
8
2024-05-22 19:48:23,177+0000 Caesium-1-4 ERROR ServiceRunner [c.c.j.p.automation.schedule.RuleInsightsUpdateJob] Error executing rule insights update job
com.querydsl.core.QueryException: Caught BatchUpdateException for insert into "public"."AO_589059_RULE_STAT_ROLLUP_HR" ("CATEGORY", "CLIENT_KEY", "CREATED", "DURATION", "END_TO_END_DURATION", "EXECUTION_COUNT", "QUEUED_ITEM_COUNT", "RULE_ID")
values (?, ?, ?, ?, ?, ?, ?, ?)
...
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."AO_589059_RULE_STAT_ROLLUP_HR" ("CATEGORY", "CLIENT_KEY", "CREATED", "DURATION", "END_TO_END_DURATION", "EXECUTION_COUNT", "QUEUED_ITEM_COUNT", "RULE_ID")
values ('NO_ACTIONS_PERFORMED', 'com.codebarrel.tenant.global', '2024-05-22 18:00:00+00'::timestamp, 519, 519, 1, 1, 31931) was aborted: ERROR: nextval: reached maximum value of sequence "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" (2147483647) Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2402)⚠️ Keep in mind this article is only intended to the A4J objects, which start with AO_589059.
We'd expect the IDs in the A4J tables to all be of the bigint type. To validate this, run the query:
1
2
3
SELECT table_name,column_name,data_type FROM information_schema.columns
WHERE table_name like 'AO_589059%' AND column_name LIKE '%ID' AND data_type in ('integer','bigint')
ORDER BY table_name,column_name;If there are IDs of the integer type, please check the logs of the A4J startup - we'd expect to find errors related to the ALTER TABLE statement.
Let's now find the sequences that are still of the integer type:
1
2
3
SELECT sequence_name,data_type FROM information_schema.sequences
WHERE sequence_name like 'AO_589059%'
ORDER BY sequence_name;Cause
After the upgrade to 7.3 or higher, an upgrade task to fix JIRAAUTOSERVER-193 - Identity Limit for table AO_589059_RULE_STAT_ROLLUP_DAY ID should:
Alter the A4J table IDs from
integertobigintAlter the A4J sequences from
integertobigint
The error indicates that the table update happened successfully, but the sequence updates failed.
Solution
To fix the sequences, we need to run an ALTER SEQUENCE statement for each of them.
For example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER SEQUENCE "AO_589059_AUDIT_ITEM_ASC_ITEM_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_AUDIT_ITEM_CGE_ITEM_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_AUDIT_ITEM_COMP_CGE_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_AUDIT_ITEM_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_AUDIT_ITEM_PROJECT_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_AUTOMATION_QUEUE_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_COUNTERS_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_LEGACY_RULE_LINK_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_CFG_COMPONENT_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_CFG_PROJ_ASSOC_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_CONFIG_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_LABEL_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_SCHEDULE_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_STATE_LATEST_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_STAT_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_MIN_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_TAG_ID_seq" as bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE "AO_589059_RULE_TO_LABEL_ID_seq" as bigint MAXVALUE 9223372036854775807;⚠️ Before running ALTER queries in production, validate first in a staging environment and always take a backup first!
Was this helpful?