Jira is in continuous maintenance mode due to ORA-01878
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Jira fails to take the index snapshot from the shared-home and on every restart triggers a full reindex to bring it out of Maintenance mode.
Environment
Jira Data Center 8.x and above.
Diagnosis
Copying the indexing data from another node by navigating to the indexing page fails with the error ORA-01878. Many occurrences of the following warning message are observed in the atlassian-jira.log
2023-12-04 16:33:57,402+0200 main ERROR [c.a.jira.cluster.DefaultClusterManager] Current node: node1. Couldn't recover index even though it had been found in shared. Current list of other nodes: [node2]
com.querydsl.core.QueryException: Caught SQLDataException for select WORKLOG_VERSION.worklog_id, WORKLOG_VERSION.parent_issue_id, WORKLOG_VERSION.update_time, WORKLOG_VERSION.index_version, WORKLOG_VERSION.deleted
from worklog_version WORKLOG_VERSION
where WORKLOG_VERSION.update_time > current_timestamp + interval '-133911' second
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
....
Caused by: java.sql.SQLDataException: ORA-01878: specified field not found in datetime or interval
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
....
Caused by: Error : 1878, Position : 157, Sql = select WORKLOG_VERSION.worklog_id, WORKLOG_VERSION.parent_issue_id, WORKLOG_VERSION.update_time, WORKLOG_VERSION.index_version, WORKLOG_VERSION.deleted
from worklog_version WORKLOG_VERSION
where WORKLOG_VERSION.update_time > current_timestamp + interval '-133911' second, OriginalSql = select WORKLOG_VERSION.worklog_id, WORKLOG_VERSION.parent_issue_id, WORKLOG_VERSION.update_time, WORKLOG_VERSION.index_version, WORKLOG_VERSION.deleted
from worklog_version WORKLOG_VERSION
where WORKLOG_VERSION.update_time > current_timestamp + interval '-133911' second, Error Msg = ORA-01878: specified field not found in datetime or interval
... 92 moreCause
- The above error may results in due to SQLDataException as the update_time field is not correctly set.
- Similar error could results due to invalid DST change date for comment_version, issue_version and worklog_version table as well
Solution
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
- Use the below query SQL1 to check for missing/null in update_time in worklog_version
SQL1
select * from worklog_version where UPDATE_TIME is NULL;- Also, fetch the invalid records i.e. Issues created/updated during DST changes using below query SQL2, the below SQL is for US/Pacific, You might want to change the dates for the DST for other timezone based on your the Jira timezone in below SQL2.
SQL2
SELECT worklog_id, * FROM Worklog_VERSION
WHERE UPDATE_TIME BETWEEN TIMESTAMP '2024-03-10 01:59:00' AND TIMESTAMP '2024-03-10 03:01:00'
OR UPDATE_TIME BETWEEN TIMESTAMP '2023-03-12 01:59:00' AND TIMESTAMP '2023-03-12 03:01:00'
OR UPDATE_TIME BETWEEN TIMESTAMP '2022-03-13 01:59:00' AND TIMESTAMP '2022-03-13 03:01:00'
OR UPDATE_TIME BETWEEN TIMESTAMP '2021-03-12 01:59:00' AND TIMESTAMP '2021-03-12 03:01:00'
OR UPDATE_TIME BETWEEN TIMESTAMP '2020-03-13 01:59:00' AND TIMESTAMP '2020-03-13 03:01:00';- Stop Jira servers to perform an update on Database tables.
- For the missing/null values found in SQL1, set the update_time to the previous day using the SQL3
SQL3
update WORKLOG_VERSION set update_time = TO_DATE('<currentDate-1>','DD-MM-YY') where WORKLOG_VERSION.update_time < TO_DATE('<currentDate-1>','DD-MM-YY') and UPDATE_TIME is NULL;- For DST invalid records fetched in SQL2, you may find the
worklog_idand add it in below query SQL4 to update.
SQL4
update WORKLOG_VERSION set update_time = TO_DATE('<currentDate-1>','DD-MM-YY') where WORKLOG_VERSION.update_time < TO_DATE('<currentDate-1>','DD-MM-YY') and worklog_id in (<worklog_id>) -- from above SQL2- Restart Jira