Slow Jira Data Center startup due to issue updated in the future
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
In Jira 9.3 or later, any node restart leads it to unexpectedly restore the index snapshot, leading to longer startup times.
Environment
Jira Data Center 9.1 or later.
Diagnosis
When Jira starts, we see the java.sql.SQLDataException: ORA-01873: the leading precision of the interval is too small error in the logs:
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
024-10-12 00:08:05,772-0700 main ERROR [c.a.jira.cluster.DefaultClusterManager] Current node: node1. There was an error while trying to rebuild local index.
com.querydsl.core.QueryException: Caught SQLDataException for select ISSUE_VERSION.issue_id, ISSUE_VERSION.parent_issue_id, ISSUE_VERSION.update_time, ISSUE_VERSION.index_version, ISSUE_VERSION.deleted
from issue_version ISSUE_VERSION
where ISSUE_VERSION.update_time > current_timestamp + interval '257574288' second
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:502)
at com.atlassian.jira.versioning.VersioningDao.lambda$findVersionsUpdatedInTheLast$16(VersioningDao.java:288)
at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:64)
at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:108)
at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:77)
at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:103)
at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:63)
at com.atlassian.jira.versioning.VersioningDao.findVersionsUpdatedInTheLast(VersioningDao.java:280)
at com.atlassian.jira.versioning.EntityVersioningManagerImpl.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerImpl.java:274)
at com.atlassian.jira.versioning.EntityVersioningManagerImpl.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerImpl.java:266)
at com.atlassian.jira.versioning.EntityVersioningManagerWithStats.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerWithStats.java:300)
at com.atlassian.jira.index.ha.DefaultIndexRecoveryManager.reindexWithVersionCheckEntitiesUpdatedInTheLast(DefaultIndexRecoveryManager.java:227)
at com.atlassian.jira.index.ha.DefaultIndexRecoveryManager.reindexWithVersionCheckEntitiesUpdatedInTheLast(DefaultIndexRecoveryManager.java:203)
at com.atlassian.jira.cluster.DefaultClusterManager.rebuildLocalIndex(DefaultClusterManager.java:415)
at com.atlassian.jira.cluster.DefaultClusterManager.checkIndexOnStart(DefaultClusterManager.java:223)
at com.atlassian.jira.startup.ClusteringLauncher.clusterSynchronizedCheckIndex(ClusteringLauncher.java:99)
at com.atlassian.jira.startup.ClusteringLauncher.start(ClusteringLauncher.java:130)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDBActivated(DefaultJiraLauncher.java:173)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:150)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:305)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:202)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:141)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:107)
at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:105)
at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)
at com.atlassian.jira.startup.LauncherContextListener.initSlowStuffInBackground(LauncherContextListener.java:139)
at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:101)
... 5 filtered
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
... 5 filtered
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
... 11 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
... 2 filtered
Caused by: java.sql.SQLDataException: ORA-01873: the leading precision of the interval is too small
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
... 2 filtered
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:42)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59)
at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91)
at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59)
at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
... 57 more
Caused by: Error : 1873, Position : 236, Sql = select ISSUE_VERSION.issue_id, ISSUE_VERSION.parent_issue_id, ISSUE_VERSION.update_time, ISSUE_VERSION.index_version, ISSUE_VERSION.deleted
from issue_version ISSUE_VERSION
where ISSUE_VERSION.update_time > current_timestamp + interval '257574288' second, OriginalSql = select ISSUE_VERSION.issue_id, ISSUE_VERSION.parent_issue_id, ISSUE_VERSION.update_time, ISSUE_VERSION.index_version, ISSUE_VERSION.deleted
from issue_version ISSUE_VERSION
where ISSUE_VERSION.update_time > current_timestamp + interval '257574288' second, Error Msg = ORA-01873: the leading precision of the interval is too small
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 79 moreNotice that the query has a WHERE clause that has the current_timestamp summed to a positive interval:
1
where ISSUE_VERSION.update_time > current_timestamp + interval '257574288' secondIn the logs, we can also see this message a bit before the error. Notice the interval mentioned here is negative:
1
2024-10-13 18:13:47,858-0700 main INFO [c.a.jira.cluster.DefaultClusterManager] [INDEX-FIXER] Re-indexing issues modified in the last {-2979 days, -10 hours, -19 minutes, and -10 seconds}. (Versioning short-circuit checks are enabled.)⚠️ If the interval in the query is negative and in the INDEX-FIXER message above is positive, please check Jira fails to startup with Oracle DB due to a corrupted index snapshot causing a SQLDataException
Cause
Jira has an issue with the updated column as being in the future.
Jira 9.1 and later always checks the latest updated issue and reindexes all issues 24 hours before that date to make sure the index is consistent during startup. Because the date is in the future, the error happens.
Reference: Changes to index management on the Jira startup in version 9.1
This is unexpected in Jira's normal behavior and can be caused by direct DB manipulation, for example.
Solution
First, we need to find the problemmatic issue. Run the query below and take note of the issues in with the dates in the future:
1
2
3
4
5
select p.pkey,i.issuenum,i.updated
from jiraissue i
JOIN project p on p.id=i.project
order by i.updated desc
fetch first 100 rows only;Make sure all nodes are up. If a node is down while the step below is executed, its next startup will still have the error, but the following ones should be clear.
Edit the affected issue or issues in Jira's UI. For example, add a space to the description.
ℹ️ Note: Adding comments will not change the "updated" property, so we need an update to the issue fields/custom fields.
The next node restarts should not throw the error anymore.
Was this helpful?