LexoRank integrity is breached by values set outside the markers

Summary

There's a couple of support cases where exceptions are being raised by the LexoRankBalancingService. Here's a tidbit of the exceptions being raised:

com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected the first rank row to be of type MINIMUM_MARKER_ROW. Found row[LexoRankRow{id=464416, fieldId=11474, issueId=253307, lockHash='null', lockTime=null, rank='0|10000g:', type=ISSUE_RANK_ROW}]
    at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.getMinimumMarkerRowAndNextRow(LexoRankDaoImpl.java:393)
    at com.atlassian.greenhopper.service.lexorank.balance.LexoRankBalanceOperation.moveMarkerRowToNextBucket(LexoRankBalanceOperation.java:318)
    at com.atlassian.greenhopper.service.lexorank.balance.LexoRankBalanceOperation.execute(LexoRankBalanceOperation.java:103)
    at com.atlassian.greenhopper.service.lexorank.balance.LexoRankBalancer.balanceFieldId(LexoRankBalancer.java:170)
    at com.atlassian.greenhopper.service.lexorank.balance.LexoRankBalancer.balanceFieldIds(LexoRankBalancer.java:92)
    at com.atlassian.greenhopper.service.lexorank.balance.LexoRankScheduledBalanceHandler.run(LexoRankScheduledBalanceHandler.java:48)

Here's another:

com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected the first rank row to be of type MAXIMUM_MARKER_ROW. Found row[LexoRankRow
{id=748, fieldId=10200, issueId=11196, lockHash='null', lockTime=null, rank='0|zzzzzz:i', type=ISSUE_RANK_ROW}
]
at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.getMaximumMarkerRowAndPreviousRow(LexoRankDaoImpl.java:418)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:169)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.execute(LexoRankOperation.java:111)
at com.atlassian.greenhopper.service.lexorank.LexoRankService.performRankOperation(LexoRankService.java:239)
at com.atlassian.greenhopper.service.lexorank.LexoRankService.getRankOrRankInitially(LexoRankService.java:130)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:122)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:30)

Cause

Somehow, on these instances, an issue was given a value for its rank that occurred outside the minimum- and maxiumum-marker row's values.

Here's an example of the breached values:

I'm not sure how the rank of 0|zzzzzz:i was set, but it causes problems: The LexoRankBalanceOperation expects the MAXIMUM_ or MINIMUM_MARKER_ROW to be the first row returned in a SQL query where the rows are ordered by RANK. If any value appears before the marker row in their respective queries, integrity exceptions will be raised upon most LexoRank operations.

Since LexoRank operations occur when issues are deleted, these integrity exceptions actively prevent issues from being deleted.

The integrity violation also prevents a re-balancing from occurring, so attempts to heal the rankings probably won't work, either.

Workaround

You can manually edit the rank values in the LEXORANK ActiveObjects table.

1. Get all the values in the lexorank table:

   SELECT * FROM "AO_60DB71_LEXORANK" ORDER BY "RANK" DESC;

2. Determine the # of rows that appear above the maximum value (i.e., the row of type='2'). In the above example, there is just one:
   

   | 10200 | 748 | 11196 |  |  | 0|zzzzzz:i | 1 |

3. Determine the rank value of the maximum value. In the above example, it is 0|zzzzzz:
4. Determine the highest rank value below the maximum. In the above example, it is 0|zzzzzv:
5. Determine the possible values between the maximum value and the value you found in step 3.
   In our example, the values are 0|zzzzzz: and 0|zzzzzv:, respectively.
   The possible values between here are 0|zzzzzw:, 0|zzzzzx: and 0|zzzzzy:.
6. For each issue above the maximum value, update the row to use one of the values determined in step 4.

   UPDATE "AO_60DB71_LEXORANK" SET "RANK"=<that value> WHERE "ID"=<the row's id>;

   Do the same for any rows that fall below the minimum value:

   SELECT * FROM "AO_60DB71_LEXORANK" ORDER BY "RANK" ASC;

7. Once all of that is done, trigger a LexoRankBalanceOperation to occur. You can do this by issuing a GET request to /rest/greenhopper/1.0/lexorank/balance with an admin's credentials.