IndexingFailureException thrown during reindex of Jira server
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
Symptoms
Running re-indexing will lead to an error similar to the following, shown in the JIRA UI:
com.atlassian.jira.index.IndexingFailureException: Indexing completed with 2 errors
at com.atlassian.jira.index.AccumulatingResultBuilder$CompositeResult.await(AccumulatingResultBuilder.java:147)
at com.atlassian.jira.issue.index.DefaultIndexManager.doIndexIssuesInBatchMode(DefaultIndexManager.java:877)
at com.atlassian.jira.issue.index.DefaultIndexManager.doStopTheWorldReindex(DefaultIndexManager.java:844)Also other operations like cloning or deleting of an affected issue can lead to this error.
The following appears in the atlassian-jira.log file:
2016-12-08 14:36:51,495 JiraTaskExectionThread-1 WARN anonymous [c.a.j.issue.index.DefaultIssueDocumentFactory] Error indexing issue CRM-761: Dropping 'customfield_12230'
com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly one rank row for issue[id=44214] for rank field[id=12230], but found 2 rows
at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.findByFieldAndIssueId(LexoRankDaoImpl.java:234)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:147)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.execute(LexoRankOperation.java:112)
at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.performRankOperation(LexoRankManagerImpl.java:286)
at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.getRankOrRankInitially(LexoRankManagerImpl.java:86)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:121)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:29)
at com.atlassian.jira.issue.fields.ImmutableCustomField.getValue(ImmutableCustomField.java:349)Diagnosis
You can run the following SQL query to verify if there are duplicate ISSUE_ID values in the AO_60DB71_LEXORANK table.
SELECT "ISSUE_ID" FROM "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = <Rank Custom Field ID> GROUP BY "ISSUE_ID" HAVING count("ISSUE_ID") > 1;The <Rank Custom Field ID> value can be identified in the error message - e.g. in the error message above, the error refers to customfield_12230, so the Rank Custom Field ID is 12230
Cause
There are duplicate ISSUE_ID values in AO_60DB71_LEXORANK table for one Rank custom field.
SOLUTION
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.
After completing the resolution steps described below, please check the JIRA instance before bringing JIRA online. For e.g.
- Do all the boards show up correctly
- Are all the earlier sprints intact
- Are the Story Points, Epic, Ranking and Business Value are correct in issues by doing a random check
- Are the Sprint Reports and Burn-down charts intact (both Classic boards and Rapid boards)
- Shutdown JIRA
Delete the duplicate ID using query similar to the statement below. Do this for any Rank field, that you experience this issue with.
DELETE from "AO_60DB71_LEXORANK" WHERE "ID" in ( WITH temp AS ( SELECT "ID", "ISSUE_ID", ROW_NUMBER() OVER ( PARTITION BY "ISSUE_ID" ORDER BY "ISSUE_ID" ) from "AO_60DB71_LEXORANK" WHERE "ISSUE_ID" in (SELECT "ISSUE_ID" from "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = <Rank Custom Field ID> GROUP BY "ISSUE_ID" HAVING count("ISSUE_ID") > 1)) select "ID" from temp where row_number = 1 ) ;- Restart JIRA
- Re-index JIRA