Identity Limit for table AO_589059_RULE_STAT_ROLLUP_DAY ID

Issue Summary

The table AO_589059_RULE_STAT_ROLLUP_DAY contains information on Automation for Jira Rules’(A4J) run for statistics and audit log. Every time an action is performed against an Automation for Jira Rule, an entry is added to this table, data older than 35 days is removed from this table by an A4J internal job, however the ID just keeps on incrementing.

Not all DBMSs are able to automatically revert the ID back to starting point, this generates warnings and could lead to impact on A4J functionality as the data type limit is reached, at this point this issue was already identified in MSSQL, MySQL and ORACLE.

Steps to Reproduce

Have table AO_589059_RULE_STAT_ROLLUP_DAY ID column reach maximum value (2147483647) of the data type

Expected Results

ID reset back to initial value, normal Automation execution

Actual Results

ID gets stuck at maximum value, automation starts reporting issues and failing. The below exception is thrown in the atlassian-jira.log file:

System Error message :
com.codebarrel.jira.plugin.automation.schedule.RuleInsightsUpdateScheduler.DAY.scheduler MessageQueryException: Caught BatchUpdateException for insert into `AO_589059_RULE_STAT_ROLLUP_DAY` (`CATEGORY`, `CLIENT_KEY`, `CREATED`, `DURATION`, `END_TO_END_DURATION`, `EXECUTION_COUNT`, `QUEUED_ITEM_COUNT`, `RULE_ID`) values (?, ?, ?, ?, ?, ?, ?, ?) BatchU

Workaround

The following SQL queries seek to revert the ID back to initial value, allowing A4J to continue working as expected. As there are 35 days worth of data is unlikely that the ID will reach maximum value before the table is automatically purged.

1. Stop all Jira nodes.
2. Backup the database.
3. Run one of SQL queries below, depending on your DBMS type
4. Start Jira.

MSSQL

DBCC CHECKIDENT ('<SCHEMA>.AO_589059_RULE_STAT_ROLLUP_DAY', RESEED, 0);

Update <SCHEMA> to match Jira's schema

MySQL

ALTER TABLE AO_589059_RULE_STAT_ROLLUP_DAY AUTO_INCREMENT = 1;

According to - https://dev.mysql.com/doc/refman/8.0/en/alter-table.html under section To reset the current auto-increment value, you cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

This may mean we may have to purge all the records in the table (meaning you won't have statistics for 1d report).