Indexing Jira throws the "LocalDate only handles the Common Era - no BC dates are allowed" error in the logs

Summary

Symptoms

The following errors are thrown in the Jira logs when trying to reindex:

Environment

Jira Server / Data Center on any version from 8.0.0.

Diagnosis

• Go to the ⚙ > System > Logging and profiling page

  • Click on Configure logging level for another package

  • Add the package below with the DEBUG level

    1 com.atlassian.jira.index

  • Repeat these steps for the 2 other packages below

    1 2 com.atlassian.jira.issue.index.DefaultIndexManager com.atlassian.jira.index.AccumulatingResultBuilder

• Start a new re-indexing and check the logs again. The DEBUG packages that were enabled will provide the exact table where the error is encountered

  • Taking the log snippet below as an example, the table causing the error is the worklog table (indicated after [c.a.j.issue.index.DefaultWorklogDocumentFactory] Indexing):

    1 2 3 4 5 6 2023-03-05 22:18:50,906+0000 IssueIndexer:thread-12 DEBUG xxxxxx 1338x283x1 19d10fk 172.19.2.10,10.42.2.0 /secure/admin/IndexReIndex!reindex.jspa [c.a.j.issue.index.DefaultWorklogDocumentFactory] Indexing worklog: issueId=35744, worklogId=11918, version=18 2023-03-05 22:18:50,908+0000 IssueIndexer:thread-12 WARN xxxxxx 1338x283x1 19d10fk 172.19.2.10,10.42.2.0 /secure/admin/IndexReIndex!reindex.jspa [c.a.jira.index.AccumulatingResultBuilder] java.lang.IllegalArgumentException: LocalDate only handles the Common Era - no BC dates are allowed. java.lang.RuntimeException: java.lang.IllegalArgumentException: LocalDate only handles the Common Era - no BC dates are allowed. at com.atlassian.jira.index.DefaultIndex$Failure.<init>(DefaultIndex.java:100) at com.atlassian.jira.issue.index.DefaultIssueIndexer$EntityOperation.lambda$perform$6(DefaultIssueIndexer.java:862) at java.base/java.util.HashMap.forEach(HashMap.java:1337)

Cause

There is an issue in Jira that has a date set in the BC era. e.g. 0001-12-07 22:24:30+05:53:28 BC. This could happen for any of the tables that has a date column, therefore it is necessary to go through the Diagnosis steps above to identify the problematic table(s).

Solution

• To identify the incorrect date entries, refer to the table from the debug logs and execute the corresponding queries in the database. Below are examples of queries designed to detect incorrect date formats in the jiraissue table and the worklog table for startdate.:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 select * from jiraissue where duedate < '2000-01-01'; OR select * from worklog where id = <worklogid from the debug logs> OR // This query is tested on Postgres, please make possible changes for respective databases select CONCAT(project_key.project_key, CONCAT('-', jiraissue.issuenum)) AS issuekey, TO_CHAR(worklog.created, 'yyyy-mm-dd bc') AS created, TO_CHAR(worklog.updated, 'yyyy-mm-dd bc') AS updated, TO_CHAR(worklog.startdate, 'yyyy-mm-dd bc') AS startdate from worklog inner join jiraissue ON jiraissue.id = worklog.issueid inner join project_key ON project_key.project_id = jiraissue.project where worklog.startdate < TO_DATE('2000-01-01', 'yyyy-mm-dd');

• Stop Jira

• Change the date to an appropriate format. e.g. 2012-12-07 22:24:30+05:30

• Start Jira and perform full reindexing again.