Integer out of range database error on Automation for Jira app
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
When the Automation for Jira app (A4J) inserts new entries to its statistics tables, we see "ERROR: integer out of range" in the atlassian-jira.log
Symptom 1
ERROR: integer out of range appears in the atlassian-jira.log when the Automation for Jira app (A4J) inserts new entries to its statistics tables; AO_589059_RULE_STAT_ROLLUP_DAY and/or AO_589059_RULE_STAT_ROLLUP_HR.
1
2
3
4
5
6
7
8
9
10
Caesium-1-1 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_DAY" ("CATEGORY", "CLIENT_KEY", "CREATED", "DURATION", "END_TO_END_DURATION", "EXECUTION_COUNT", "QUEUED_ITEM_COUNT", "RULE_ID")
values (?, ?, ?, ?, ?, ?, ?, ?)
...
Suppressed: org.postgresql.util.PSQLException: ERROR: integer out of range
...
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."AO_589059_RULE_STAT_ROLLUP_DAY" ("CATEGORY", "CLIENT_KEY", "CREATED", "DURATION", "END_TO_END_DURATION", "EXECUTION_COUNT", "QUEUED_ITEM_COUNT", "RULE_ID")
values ('NO_ACTIONS_PERFORMED', 'com.codebarrel.tenant.global', '2020-11-07 00:00:00+00'::timestamp, 57, 57, 1, 1, 110) was aborted: ERROR: integer out of range Call getNextException to see other errors in the batch.
...
[CIRCULAR REFERENCE:org.postgresql.util.PSQLException: ERROR: integer out of range]Symptom 2
ERROR: integer out of range appears in the atlassian-jira.log on tables; AO_589059_RULE_STAT_ROLLUP_DAY and/or AO_589059_RULE_STAT_ROLLUP_HR when Insight Scheduled jobs are run. However, this time a value is returned from DB and it's not exactly a DB error.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Caesium-1-3 ERROR anonymous [c.c.j.p.automation.schedule.RuleInsightsUpdateJob] Error executing rule insights update job
com.querydsl.core.QueryException: Caught SQLDataException for select `AO_589059_RULE_STAT_ROLLUP_DAY`.`ID`, `AO_589059_RULE_STAT_ROLLUP_DAY`.`RULE_ID`, `AO_589059_RULE_STAT_ROLLUP_DAY`.`CREATED`, `AO_589059_RULE_STAT_ROLLUP_DAY`.`AUDIT_ID`
from `AO_589059_RULE_STAT_ROLLUP_DAY` `AO_589059_RULE_STAT_ROLLUP_DAY`
where `AO_589059_RULE_STAT_ROLLUP_DAY`.`CATEGORY` = ? and `AO_589059_RULE_STAT_ROLLUP_DAY`.`CREATED` <= ?
order by `AO_589059_RULE_STAT_ROLLUP_DAY`.`CREATED` asc
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:493)
at com.codebarrel.jira.plugin.automation.store.JiraRuleInsightStore.lambda$getInsightCategoryDetailsEarlierThan$10(JiraRuleInsightStore.java:311)
at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47)
...
Caused by: java.sql.SQLDataException: Value '4591732034' is outside of valid range for type java.lang.Integer
...
Caused by: com.mysql.cj.exceptions.NumberOutOfRange: Value '4591732034' is outside of valid range for type java.lang.IntegerDiagnosis
Check the sequence for both table AO_589059_RULE_STAT_ROLLUP_DAY and AO_589059_RULE_STAT_ROLLUP_HR, to see if:
It last_value has gone over the limit for the integer datatype
If
CYCLEis turned on, andIf max_value is configured for the sequence
1
2
select last_value, max_value, is_cycled from "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq";
select last_value, max_value, is_cycled from "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq";For example:
If last_value is more than
2147483647, this means the sequence has gone over the integer datatype limit.If there is no value for max_value, there is no limit set for the sequence.
If is_cycled is set to
f, cycling is not turned on and will not cycle back to the start when the limit is reached.
Cause
Integer out of range errors occur because the last_value column in the affected A4J table(s)'s sequence went over its default limit - which is any integer between -2147483648 to +2147483647.
Solution
Workaround
Truncate the associated tables, reset the sequence, turn on CYCLE, and set a max value for the sequence using database queries, with the following steps:
Always back up your data before performing any modifications to the database. If possible, test any alter, truncate, insert, update, or delete SQL commands on a staging server first.
1. Make sure the that related tables are empty before this (to avoid ID conflicts). The commands below will truncate or delete all the contents of AO_589059_RULE_STAT_ROLLUP_DAY and AO_589059_RULE_STAT_ROLLUP_HR. This is will only impact A4J statistics whose tables are cleared every 35 days automatically. This step will ensure that future increments of the sequence would not hit any conflicts with existing IDs.
1
2
TRUNCATE "AO_589059_RULE_STAT_ROLLUP_DAY" CASCADE;
TRUNCATE "AO_589059_RULE_STAT_ROLLUP_HR" CASCADE2. Reset sequence value to 1. This will set the sequence to 1, and start from that value again.
1
2
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" RESTART;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" RESTART;3. Enable cycle.
1
2
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" CYCLE;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" CYCLE;4. Set maximum sequence value to 2147483647 (max possible value for integer data type). When the sequence hits this value, it should then cycle back to 1.
1
2
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" MAXVALUE 2147483647;
ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" MAXVALUE 2147483647;MySQL specific:
MySQL works on 'increment' differently as explained on MySQL documentation page - https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html. You can truncate and alter table with below commands to fix the error.
Always back up your data before performing any modifications to the database. If possible, test any alter, truncate, insert, update, or delete SQL commands on a staging server first.
1
2
3
4
TRUNCATE AO_589059_RULE_STAT_ROLLUP_HR;
TRUNCATE AO_589059_RULE_STAT_ROLLUP_DAY;
ALTER TABLE AO_589059_RULE_STAT_ROLLUP_HR AUTO_INCREMENT = 1;
ALTER TABLE AO_589059_RULE_STAT_ROLLUP_DAY AUTO_INCREMENT = 1;Was this helpful?