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.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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)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)Similar issue can also occur during operations like editing, cloning, or deleting of an affected issue can lead to this error (LexoRankIntegrityException: Expected exactly one rank row for issue x for rank field y, but found 2 rows):
2021-12-13 09:27:10,188-0800 http-nio-8080-exec-42 ERROR xxxxxx 567x9834037x8 19nc603 /browse/XXX-50 [c.a.j.web.component.ModuleWebComponentImpl] An exception occured while rendering the web panel: com.pyxis.greenhopper.jira:greenhopper-epics-issue-web-panel (null)
com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly one rank row for issue[id=618949] for rank field[id=10106], but found 2 rows
at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.findByFieldAndIssueId(LexoRankDaoImpl.java:224)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:137)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.execute(LexoRankOperation.java:108)
at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.performRankOperation(LexoRankManagerImpl.java:260)
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" ) as row_number 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 ) ;Note that the above query may give a syntax error if the subquery does not find any records to fix. It's best to verify with the first SELECT query that there are duplicate ISSUE_ID values.
- Restart JIRA
- Re-index JIRA