[转]How to fix a JIRA application that is unable to perform a background reindex "at this time error"

本文转自：https://confluence.atlassian.com/jirakb/how-to-fix-a-jira-application-that-is-unable-to-perform-a-background-re-index-at-this-time-error-316637947.html

Symptoms

When trying to access a JIRA application Indexing functionality, it displays the error "JIRA application is unable to perform a background re-index at this time because the index files are either missing or corrupted." as below:

The following appears in the atlassian-jira.log:

2012-12-27 10:04:04,083 http-bio-8082-exec-3 WARN captain.planet 604x750x1 1kx4mae 172.22.2.187 /secure/admin/IndexAdmin.jspa [jira.issue.index.IndexConsistencyUtils] Index consistency check failed for index 'Comment': expectedCount=140803; actualCount=122964

Cause

 

This problem is caused by both comments and worklogs. The resolution steps are the same, but also affects the worklog table.

The number of comments in the database do not match the number of Lucene documents created in the indexing folder. This is currently known to be caused by orphaned comment records as a result of a JIRA application issue being deleted directly from the database rather than through the GUI. This error is similar to the one as described in After upgrading JIRA indexing is disabled.

An improvement request has been raised to better handle this scenario under  JRASERVER-31091 - Provide better error descriptions for the new background indexing functionality Closed

Resolution

 

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

1. Identify the orphaned comments/worklogs with the following SQL:

   SELECT * FROM jiraaction WHERE issueid NOT IN (SELECT id FROM jiraissue);
   SELECT * FROM worklog WHERE issueid NOT IN (SELECT id FROM jiraissue);

   If your instance has a lot of issues, this query can take a long time time to run. If this is the case, you can run multiple queries limiting the id value to improve the performance of the query. If this is the case, run the query below:

   SELECT * FROM jiraaction WHERE issueid NOT IN (SELECT id FROM jiraissue) HAVING id between 0 and 100000;
   SELECT * FROM worklog WHERE issueid NOT IN (SELECT id FROM jiraissue) HAVING id between 0 and 100000;

   If you are using Postgres please run the query below:

   SELECT * FROM jiraaction WHERE issueid not in (SELECT id FROM jiraissue) group by id HAVING jiraaction.id between 0 and 100000;
   SELECT * FROM worklog WHERE issueid not in (SELECT id FROM jiraissue) group by id HAVING worklog.id between 0 and 100000;

   This query will run for the first 100.000 issues. After this is completed, you can change the value for "between 100000 and 200000". Keep doing this to reach all of your issues.

2. Stop the application.

3. If they can be deleted, remove them with the below:

   DELETE FROM jiraaction WHERE issueid NOT IN (SELECT id FROM jiraissue);
   DELETE FROM worklog WHERE issueid NOT IN (SELECT id FROM jiraissue);

   This will not fix all orphaned records in the JIRA application database for an issue deleted from jiraissue, only comments/worklogs.

4. (Oracle Only): Execute the below SQL to commit the changes:

   COMMIT;

5. Start the application.
6. Access the reindex page and verify if the problem is fixed.

The above SQL may need to be changed dependent on the DBMS used by JIRA applications.