症状

Searches may be failing.

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

 We have had reports of this issue causing high load on systems in the form of out of memory issues. If you find information to either confirm or deny this, please add this to the comments.

原因

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.

診断

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"

回避策

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.

ソリューション

オプション 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.

オプション 2

1. Take XML backup of your data.
2. 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.