Issue rank operations may fail due to reaching the maximum rank length

Summary

Jira Agile getRankRelativeToOtherIssue() method allocates next Rank very inefficiently in cases related to Max Rank (zzzzzz - MAX_DECIMAL). This leads on non-optimal Lexorank space allocation and leads to growth of the Lexorank length, which leads to inability to do rank operation
See related KB Troubleshooting new ranking system issues

Environment

• Jira Software 7.2.12, 7.4.4 - 7.6.3

Steps to Reproduce

This is generic steps, to fully reproduce the problem you need to high volume of newly created issues and large amount of Rank operations

1. Create new issue issueA
2. Create new issue issueB
3. Create new issue issueC
4. Rank issueA after issueC

Expected Results

• Rank length will not grow and remain stable.
• There is no gaps in the Lexorank space.

Actual Results

• Non-optimal Lexorank space allocation with gaps

  10600,2323286,2374148,"",,"1|i00xlj:",1,1
  10600,2302157,2322448,"",,"1|l00000:",1,1
  10600,2336271,2373473,"",,"1|mi0000:",1,1
  10600,2336272,2373472,"",,"1|ni0000:",1,1
  10600,2336274,2372426,"",,"1|o00000:",1,1
  

• Fast growth of the Lexorank length: from 10 to 250
  • which leads to inability to do rank operation
  • you will get the following error in the logs:

    [c.a.g.customfield.lexorank.LexoRankCFType] gh.api.rank.error.lexorank.fieldlength.exceeded.norebalance
    

Notes

• In majority of the cases, rank operation is done in the middle of the Lexorank space, so getRankRelativeToOtherIssue() will not have problem there.
• SQL to troubleshoot the problem:
  • Check Rank length

    / Find the longest RANK
    SELECT "FIELD_ID", max(length("RANK")) AS max_rank_length FROM "AO_60DB71_LEXORANK" GROUP BY "FIELD_ID";
     
     FIELD_ID | max_rank_length
    ----------+------------------------
        15530 |                     29
    
    // Check issues with RANK length over 250
    SELECT l."ISSUE_ID",j.project,j.issuenum, length(l."RANK") AS rank_length FROM "AO_60DB71_LEXORANK" l join jiraissue j on l."ISSUE_ID"=j.ID where length(l."RANK") > 250;
    

• Check table status

  // Get all LEXO sorted rows
  SELECT * FROM "AO_60DB71_LEXORANK" ORDER BY "RANK" DESC;
  
  // Check sorted Rank value of long rank
  SELECT l."ISSUE_ID", length(l."RANK") AS rank_length, l."RANK" FROM "AO_60DB71_LEXORANK" l where length(l."RANK") > 250 order by l."RANK" DESC;
  

• Extra

  // Disribution of the ranks lenths (this is expensive SQL!)
  select length("RANK") as r_len, count("RANK") from "AO_60DB71_LEXORANK" group by r_len order by length("RANK") desc;
  
  // See issues w/o rank
  select count(*) from jiraissue ji left join "AO_60DB71_LEXORANK" lr on ji.id = lr."ISSUE_ID" where lr."ISSUE_ID" is null;
  
  //  Disribution of ranks per letter (should be evenly), example for Posgresql 
  select substring("RANK" from 0 for 4),count(1) from "AO_60DB71_LEXORANK" where "FIELD_ID"=<FIELD_ID> and "TYPE" = 1 group by substring("RANK" from 0 for 4);
   substring | count
   0|h       |   2886
   0|i       | 512501
  

Workaround

None