Summary

Jira fails to startup, due to the following ORA-01873 SQLDataException is thrown in the Jira UI:

Full error message: ORA-01873: the leading precision of the interval is too small

This issue has been partially fixed starting on Jira 9.3.0 with a way to avoid being unable to startup. You'll get the error message but Jira can start. More details on the JAC bug below:

• JRASERVER-74296 (Oracle DB only) Node with index older than 3 months fails to start in healthy state due to SQLDataException (ORA-01873)

Environment

Issue observed on Jira Data Center using at least to node, and on version 8.20.1 (but the issue might also occur in lower versions).

Diagnosis

When checking the Jira application logs, the following INFO/ERROR messages can be found:

• SQL exception (also thrown in the UI during Jira startup):

  2021-12-03 07:49:06,798-0700 localhost-startStop-1 ERROR      [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
  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 '-20030247' 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$15(VersioningDao.java:279)
  	at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:68)
  	at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:105)
  	at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)
  	at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:100)
  	at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:67)
  	at com.atlassian.jira.versioning.VersioningDao.findVersionsUpdatedInTheLast(VersioningDao.java:271)
  	at com.atlassian.jira.versioning.EntityVersioningManagerImpl.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerImpl.java:259)
  	at com.atlassian.jira.versioning.EntityVersioningManagerImpl.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerImpl.java:251)
  	at com.atlassian.jira.versioning.EntityVersioningManagerWithStats.findEntityVersionsUpdatedInTheLast(EntityVersioningManagerWithStats.java:275)
  	at com.atlassian.jira.index.ha.DefaultIndexRecoveryManager.reindexWithVersionCheckEntitiesUpdatedInTheLast(DefaultIndexRecoveryManager.java:248)
  	at com.atlassian.jira.index.ha.DefaultIndexRecoveryManager.reindexWithVersionCheckEntitiesUpdatedInTheLast(DefaultIndexRecoveryManager.java:225)
  	at com.atlassian.jira.index.ha.DefaultIndexRecoveryManager.recoverIndexFromBackup(DefaultIndexRecoveryManager.java:185)
  	at com.atlassian.jira.index.DefaultIndexFetcher.recoverIndexFromMostRecentSnapshot(DefaultIndexFetcher.java:80)
  	at com.atlassian.jira.cluster.DefaultClusterManager.checkIndex(DefaultClusterManager.java:157)
  	at com.atlassian.jira.startup.ClusteringLauncher.start(ClusteringLauncher.java:37)
  	at com.atlassian.jira.startup.DefaultJiraLauncher.postDBActivated(DefaultJiraLauncher.java:166)
  	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:144)
  	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:307)
  	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:202)
  	at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135)
  	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102)
  	at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
  	at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100)
  	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.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
  	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
  	at java.base/java.lang.Thread.run(Thread.java:834)
  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:553)
  	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:83)
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
  	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:70)
  	at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
  	at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59)
  	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
  	... 36 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 '-20030247' 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 '-20030247' second, Error Msg = ORA-01873: the leading precision of the interval is too small
  	... 58 more

  If the number in the query WHERE clause is positive, please check Slow Jira Data Center startup due to issue updated in the future instead

• The Jira logs are showing that the node is trying to recover an index snapshot supposedly from December 1st, located in <JIRA_SHARED_HOME_FOLDER>export/indexsnapshots:

  2021-12-03 07:48:55,721-0700 localhost-startStop-1 INFO      [c.a.jira.index.DefaultIndexFetcher] [INDEX_FETCHER] Jira will try to recover the most recent snapshot: <JIRA_SHARED_HOME_FOLDER>/export/indexsnapshots/IndexSnapshot_2021-Dec-01--0200.tar.sz, chosen based on "last modified" file property from 3 existing snapshots: <JIRA_SHARED_HOME_FOLDER>/export/indexsnapshots/IndexSnapshot_2021-Dec-01--0200.tar.sz, <JIRA_SHARED_HOME_FOLDER>/export/indexsnapshots/IndexSnapshot_2021-Nov-30--0200.tar.sz, <JIRA_SHARED_HOME_FOLDER>/export/indexsnapshots/IndexSnapshot_2021-Nov-29--0200.tar.sz

• The Jira logs are showing that in reality the last index date is from April 16th (even though the time stamp of the most recent index snapshot is from December 1st):

  2021-12-03 07:48:59,257-0700 localhost-startStop-1 INFO      [c.a.j.index.ha.DefaultIndexRecoveryManager] [INDEX-FIXER] Latest index date: {2021-04-16 11:51:39}, Latest DB issue-version date: {2021-11-03 06:44:41}
  2021-12-03 07:48:59,258-0700 localhost-startStop-1 WARN      [c.a.j.index.ha.LegacyIndexFixer] [INDEX-FIXER] [LEGACY] Could not calculate recovery duration based on snapshot metadata & latest db (jiraissue table) update

Cause

Even though timestamp on the most recent index snapshot suggest that the indexes are recent (from December 1st, while the Jira instance was started on December 3rd in the example above), but the actual indexes that were saved in the snapshot are from a much older date (April 4th in the example above, which is 7 months ago). Due to the big time gap between the current time and the time of the indexes, some SQL query overflows and triggers a SQL exception, preventing the Jira application from properly startup. The exact reason why the index snapshots got into this situation is currently unclear.

Solution

The steps below should help fix the error and allow Jira to start up:

• Remove the content of the folder <JIRA_SHARED_HOME_FOLDER>/export/indexsnapshots (we recommend to copy its content in a temporary directory)
• Start the Jira application
• Verify that the Jira application starts successfully and that the SQL exception mentioned above is no longer mentioned in the Jira logs
• If the startup is successful, you might want to either perform a full re-index on the node that was failing to start via the page ⚙ > System > Indexing