Issue Summary

This is reproducible on Data Center: yes 

Steps to Reproduce

1. Set up DC environment (v9.12.4) with MySQL v8 and A4J built in version v9.1.1.
2. Create an automation rule from (tested on global automation) and create actions to trigger the rule. 
3. We can see the tables "AO_589059_RULE_STAT_ROLLUP_DAY" and "AO_589059_RULE_STAT_ROLLUP_HR" updated with increments in ID column. 
4. Set value of last ID column to a bigint value (eg: 4591732034) that's out of integer range.
5. Wait for the Insight scheduled jobs (System --> Scheduler Details) to run:

• com.codebarrel.jira.plugin.automation.schedule.RuleInsightsUpdateScheduler.DAY.job
• com.codebarrel.jira.plugin.automation.schedule.RuleInsightsUpdateScheduler.HOUR.job

Expected Results

The Schedule runs successfully

Actual Results

Job fails and there is error thrown in the application logs. It's a bit complex to trigger - in order to reproduce the error, it's necessary for one of the table entries with a large ID value to also have its category (i.e. state) column set to IN_PROGRESS

The below exception is thrown in the atlassian-jira.log file:

2024-05-03 09:39:33,238+0200 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)

-----snip-----

Caused by: java.sql.SQLDataException: Value '4591732034' is outside of valid range for type java.lang.Integer

-----snip-----

Caused by: com.mysql.cj.exceptions.NumberOutOfRange: Value '4591732034' is outside of valid range for type java.lang.Integer

With postgres DB

----snip-----
Caused by: org.postgresql.util.PSQLException: Bad value for type int : 3213385379 

Workaround

The work around from this KB article should work here since it helps to truncate the table and reset ID value. Since MySQL incremental value is different from Postgres, truncate the tables with below queries.
Always take a backup of your database before applying any scripts or changes to the PROD database.

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;