IndexingFailureException thrown during reindex of Jira server

Still need help?

The Atlassian Community is here for you.

Ask the community

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;
tip/resting Created with Sketch.

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.


tip/resting Created with Sketch.

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)
  1. Shutdown JIRA


  2. 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.  

  3. Restart JIRA
  4. Re-index JIRA

Last modified on Sep 14, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.