Automation for Jira - Performance insights graph does not contain data

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

Opening Automation performance insights graph for hourly, daily, weekly or monthly period does not show any data on the graph:

empty graph of performance insights of automation for jira

It could be that the data is present only for some periods.

Diagnosis

Looking through atlassian-jira.log, we see an error similar to the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 2024-02-22 14:42:53,411+0100 Caesium-1-2 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 (?, ?, ?, ?, ?, ?, ?, ?) at com.querydsl.sql.support.JavaSE7SQLExceptionWrapper.wrap(JavaSE7SQLExceptionWrapper.java:41) at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:48) 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.JiraRuleInsightStore.lambda$populateInsights$4(JiraRuleInsightStore.java:156) at com.codebarrel.data.api.jira.JiraDbConnectionManager.lambda$execute$0(JiraDbConnectionManager.java:47) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:77) at jdk.internal.reflect.GeneratedMethodAccessor535.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) at jdk.proxy3/jdk.proxy3.$Proxy612.executeQuery(Unknown Source) at jdk.internal.reflect.GeneratedMethodAccessor535.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) ... Suppressed: org.postgresql.util.PSQLException: ERROR: nextval: reached maximum value of sequence "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" (2147483647) at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2134) at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1491) at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1516) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:560) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:896) ...

The error tells us that the database sequence for table "AO_589059_RULE_STAT_ROLLUP_DAY" has reached its maximum (2147483647). Therefore, no new values can be inserted into this table, which means there will be data missing after this error has first appeared.

The same error can appear for "AO_589059_RULE_STAT_ROLLUP_MIN" and "AO_589059_RULE_STAT_ROLLUP_HR" tables.

To confirm the counters value, run the following queries:

1 2 3 select * from "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq"; select * from "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq"; select * from "AO_589059_RULE_STAT_ROLLUP_MIN_ID_seq";

The result for the last value column will be 2147483647 in case the the maximum sequence value has been reached.

Cause

The cause of this issue is not yet known. In a fresh Jira database, the sequence data type is "bigint", which reaches maximum value at 9223372036854775807, which makes sure that the problem virtually never occurs.

Data type of sequence counter should be "bigint" for all three tables ("AO_589059_RULE_STAT_ROLLUP_MIN", "AO_589059_RULE_STAT_ROLLUP_HR" and "AO_589059_RULE_STAT_ROLLUP_DAY"). To see whether the data type is correct for these tables, run the following queries:

1 select * from information_schema.sequences where sequence_name in ('AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq','AO_589059_RULE_STAT_ROLLUP_HR_ID_seq','AO_589059_RULE_STAT_ROLLUP_MIN_ID_seq');

If the "data_type" column returns "bigint" value, then this is the correct value. Otherwise, if the returned value is "integer" for all or any of the tables, then proceed to the Solution section in order to fix this.

Solution

The solution here is to change data type of the sequence counter to "bigint", for any affected table.

  1. Backup your database, since the table structure will be modified. If possible, do this change on a staging environment first.

  2. Stop Jira.

  3. Run the following SQL queries, depending on the affected table:

  • Table "AO_589059_RULE_STAT_ROLLUP_DAY"

ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" AS bigint;ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_DAY_ID_seq" MAXVALUE 9223372036854775807;
  • Table "AO_589059_RULE_STAT_ROLLUP_HR"

ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" AS bigint;ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_HR_ID_seq" MAXVALUE 9223372036854775807;
  • Table "AO_589059_RULE_STAT_ROLLUP_MIN"

ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_MIN_ID_seq" AS bigint;ALTER SEQUENCE "AO_589059_RULE_STAT_ROLLUP_MIN_ID_seq" MAXVALUE 9223372036854775807;

4. Start Jira.

5. Wait for some Automation rules to run, then check if the statistics appear.

Updated on June 11, 2024

Still need help?

The Atlassian Community is here for you.