Symptoms

Searches may be failing.

The following appears in the atlassian-jira.log:

2012-06-25 23:30:58,073 IssueIndexer:thread-6 WARN admin 1406x194x2 1hi7f6 165.222.16.87,165.222.100.83 /secure/admin/IndexReIndex.jspa [index.indexers.impl.NumberCustomFieldIndexer] Unable to index custom date field 'Date of First Response(customfield_10267) with value: -61811600400000
2012-06-25 23:32:01,386 IssueIndexer:thread-9 WARN admin 1406x194x2 1hi7f6 165.222.16.87,165.222.100.83 /secure/admin/IndexReIndex.jspa [index.indexers.impl.NumberCustomFieldIndexer] Unable to index custom date field 'Date of First Response(customfield_10267) with value: -61811600400000
2012-06-25 23:32:28,181 IssueIndexer:thread-4 INFO admin 1406x194x2 1hi7f6 165.222.16.87,165.222.100.83 /secure/admin/IndexReIndex.jspa [action.admin.index.IndexAdminImpl] Re-indexing is 2% complete. Current index: Issue
2012-06-25 23:33:14,049 IssueIndexer:thread-3 WARN admin 1406x194x2 1hi7f6 165.222.16.87,165.222.100.83 /secure/admin/IndexReIndex.jspa [index.indexers.impl.NumberCustomFieldIndexer] Unable to index custom date field 'Date of First Response(customfield_10267) with value: -61811600400000

Cause

Invalid Date / Time in one of the JIRA tables. This is generally caused by having a date value which is prior to midnight January 1, 1970. This specific date is a limitation of JAVA's date implementation which is based on the unix epoch. In the above error, the JIRA Charting plugin is trying to retrieve a date from 'customfieldvalue' table and its getting an invalid date or time.

Diagnosis

To find the invalid date you must run the SQL query below in your database : 

select * from customfieldvalue where customfield = <CUSTOM_FIELD_ID> and datevalue < '1970-01-01';

select issueid, created from jiraaction where actiontype='comment' and created < '1970-01-01';

 The <CUSTOM_FIELD_ID> in the above query can be found in the error code as part of the string: "customfield_10267"

Workaround

The workaround is to re-index the instance. This should resolve the error of the failing indexing, but the error will still show up in logs for the invalid date / time.

Resolution

Option 1

1. Identify the affected JIRA issues using the following SQL query:

   For JIRA 6.1 and later:

   SELECT CONCAT(p.pkey, '-', i.issuenum) AS issuekey, i.summary, cf.cfname, cv.datevalue 
     FROM customfield cf, customfieldvalue cv, jiraissue i, project p 
     WHERE cf.id=cv.customfield AND cv.issue=i.id AND i.project=p.id AND datevalue < '1970-01-01' 
     ORDER BY p.pkey ASC, i.issuenum ASC;

   SELECT 
     CONCAT(CONCAT(p.pkey, '-'), ji.issuenum) AS issuekey, 
     ja.created 
   FROM jiraaction ja
   INNER JOIN jiraissue ji
   ON ja.issueid = ji.id
   INNER JOIN project p
   ON ji.project = p.id
   WHERE ja.actiontype='comment' 
   AND ja.created < TO_DATE('1970-01-01','YYYY-MM-DD')
   ORDER BY issuekey ASC;

   Oracle users may use this query instead.

   SELECT CONCAT(CONCAT(p.pkey, '-'), i.issuenum) AS issuekey, i.summary, cf.cfname, cv.datevalue
   FROM customfield cf, customfieldvalue cv, jiraissue i, project p
   WHERE cf.id=cv.customfield AND cv.issue=i.id AND i.project=p.id AND datevalue < TO_DATE('1970-01-01','YYYY-MM-DD')
   ORDER BY p.pkey ASC, i.issuenum ASC;

   SELECT 
     CONCAT(CONCAT(p.pkey, '-'), ji.issuenum) AS issuekey, 
     ja.created 
   FROM jiraaction ja
   INNER JOIN jiraissue ji
   ON ja.issueid = ji.id
   INNER JOIN project p
   ON ji.project = p.id
   WHERE ja.actiontype='comment' 
   AND ja.created < TO_DATE('1970-01-01','YYYY-MM-DD')
   ORDER BY issuekey ASC;

   
   For versions before JIRA 6.1:

   SELECT i.pkey AS issuekey, i.summary, cf.cfname, cv.datevalue
       FROM customfield cf, customfieldvalue cv, jiraissue i
       WHERE cf.id=cv.customfield AND cv.issue=i.id AND datevalue < '1970-01-01'
       ORDER BY i.pkey ASC;
   

2. Edit the JIRA issues to modify the custom field's value to a date later than 1970-01-01. JIRA issues are automatically reindexed when they are saved and hence this does not require any downtime. 

3. If the second query (jiraaction) are returning output, edit the comment date later than 1970-01-01.

Option 2

1. Take XML backup of your data.
2. Shutdown JIRA
3. If you found the wrong date from the database result above, you will have to copy the value from your database.
4. Open you XML backup using any editor.
5. Search for wrong date value and replace it with correct format. for example : if you find '0001-01-01' is wrong format and it should be '2001-01-01'
6. Delete the directory $JIRA_HOME/caches/indexes.

7. Import your new modified XML into JIRA.

8. Reindex JIRA through the Administration section again
   

   You can re-index to get your JIRA back and running, but to permanently fix the error you will have to do the suggested workaround.