LexoRank upgrade tasks fail to create markers in table when upgrading to Agile 6.4.x while running JIRA below 6.2

Summary

When upgrading JIRA Agile to 6.4.3 with JIRA below 6.2, the upgrade task to convert the Global Rank field into LexoRank is not setting up the marker rows in the LexoRank table. This prevents ranking operations from working in any Agile board.

You may see a lot of entries in the log file similar to the below:

2014-07-30 13:28:07,855 http-bio-8328-exec-25 ERROR XXXXX 807x2456x3 37cs93 172.22.2.65 /rest/greenhopper/1.0/sprint/rank [event.listeners.search.IssueIndexListener] Error re-indexing changes for issue 'XX-#'
java.lang.RuntimeException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=######]

Diagnosis

Run the below SQL to detect the condition:

select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-lexo-rank';
select * from "AO_60DB71_LEXORANK" where ("ISSUE_ID" = -9223372036854775808 and "RANK" LIKE '%|000000:' and "TYPE" = 0) or ("ISSUE_ID" = 9223372036854775807 and "RANK" like '%|zzzzzz:' and "TYPE" = 2);

If there are not exactly two results in the second query, for each result returned in the first query, then you are affected.

Workaround

You'll need to insert the marker fields into the table manually:

1. Backup JIRA before making any changes.
2. Stop JIRA.
3. Find out the field ID or IDs for LexoRank fields:

   select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-lexo-rank';
   

4. For each field ID returned by the previous query, run a query like below, replacing <FIELD_ID> by the field ID:

   INSERT INTO "AO_60DB71_LEXORANK" ("ID", "FIELD_ID", "ISSUE_ID", "RANK", "TYPE") VALUES (DEFAULT, <FIELD_ID>, -9223372036854775808, '0|000000:', 0);
   INSERT INTO "AO_60DB71_LEXORANK" ("ID", "FIELD_ID", "ISSUE_ID", "RANK", "TYPE") VALUES (DEFAULT, <FIELD_ID>, 9223372036854775807, '0|zzzzzz:', 2);
   

5. Start JIRA.