LexoRankIntegrityException: Expected exactly one rank row for issue x for rank field y, but found 2 rows
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
Operations like editing an issue, cloning, or deleting of an affected issue can lead to this error:
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", count("ISSUE_ID") FROM "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = <Rank Custom Field ID> GROUP BY "ISSUE_ID" HAVING count("ISSUE_ID") > 1;
If the results show any issues with a count of 2, they are simple duplicates. If the results show any issues with a count of 3+, they will need the more complex resolution query.
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_10106, so the Rank Custom Field ID is 10106
Cause
There are duplicate ISSUE_ID values in AO_60DB71_LEXORANK table for one Rank custom field.
Solution (2 values returned)
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.
Same solution used on IndexingFailureException thrown during reindex of Jira server
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
Note: MS SQL Compatible query for the Delete command above:
WITH temp (ID,ISSUE_ID,row_number) 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)) DELETE from AO_60DB71_LEXORANK WHERE ID in ( select ID from temp where row_number = 1 )
Alternate Solution (2+ values returned)
In scenarios where you have more than two duplicates for the same rank field and same issue ID, the above query may have to be run multiple times. Instead, this query can be run for step 3, to leave behind only one value.
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 ) ;