Problem

When attempting to reindex JIRA, it fails with the following errors in the atlassian-jira.log (also shown in the UI):

java.util.concurrent.ExecutionException: java.lang.RuntimeException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10300]
Task completed in 24 seconds with unexpected error.
Started Today 8:22 PM.
Finished Today 8:22 PM.

java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10300]
	at com.atlassian.jira.index.FutureResult.await(FutureResult.java:35)
	at com.atlassian.jira.index.CompositeResultBuilder$CompositeResult.await(CompositeResultBuilder.java:82)
	at com.atlassian.jira.issue.index.DefaultIndexManager.doIndexIssuesInBatchMode(DefaultIndexManager.java:857)
	at com.atlassian.jira.issue.index.DefaultIndexManager.doStopTheWorldReindex(DefaultIndexManager.java:827)
	at com.atlassian.jira.issue.index.DefaultIndexManager.reIndexAll(DefaultIndexManager.java:337)
	...
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10300]
	at java.util.concurrent.FutureTask$Sync.innerGet(Unknown Source)
	at java.util.concurrent.FutureTask.get(Unknown Source)
	at com.atlassian.jira.index.FutureResult.await(FutureResult.java:31)
	... 25 more
Caused by: java.lang.RuntimeException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10300]
	at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.getMaximumMarkerRowAndPreviousRow(LexoRankDaoImpl.java:393)
	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.customfield.lexorank.LexoRankIndexer.getLexoRankValue(LexoRankIndexer.java:72)
	at com.atlassian.greenhopper.customfield.lexorank.LexoRankIndexer.getIndexValue(LexoRankIndexer.java:65)
	at com.atlassian.greenhopper.customfield.lexorank.LexoRankIndexer.addDocumentFields(LexoRankIndexer.java:51)
	at com.atlassian.greenhopper.customfield.lexorank.LexoRankIndexer.addDocumentFieldsSearchable(LexoRankIndexer.java:39)
	...

Diagnosis

Dissecting the error message: 

Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10300]

JIRA is unable to retrieve the maximum rank marker and minimum ranked value for a given Rank field. (in our case, the rank field with ID = 10300) 

Cause

This can be caused by a few things:

Root Cause #1

If this started happening after an XML backup restoring (i.e. Upgrading or migrating JIRA), this is most likely just one of the many symptoms of the restore process failing during the active objects restoring (after the 90% mark).

You can confirm that is the case by checking the log files during the restore process and verifying if we see all of these messages logged or not

2017-08-22 15:31:44,885 JiraImportTaskExecutionThread-1 INFO admin 930x1089x1 1m20ejn 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 90% complete...
2017-08-22 15:31:45,412 JiraImportTaskExecutionThread-1 INFO admin 930x1089x1 1m20ejn 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Finished storing Generic Values.
2017-08-22 15:31:45,628 JiraImportTaskExecutionThread-1 DEBUG admin 930x1089x1 1m20ejn 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] startCleaning
2017-08-22 15:31:45,637 JiraImportTaskExecutionThread-1 DEBUG admin 930x1089x1 1m20ejn 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] stopCleaning
...
2017-08-22 16:47:06,762 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 92% complete...
...
2017-08-22 16:47:06,762 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.jira.upgrade.LoggingUpgradeService] run upgrades for data import has started
...
2017-08-22 16:47:07,180 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.jira.upgrade.LoggingUpgradeService] run upgrades for data import has finished successfully, and took 418 milliseconds to process.
...
2017-08-22 16:47:07,180 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 96% complete...
...
2017-08-22 16:47:07,223 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Re-indexing is 0% complete. Current index: Issue
...
2017-08-22 16:47:48,565 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Re-indexing is 100% complete. Current index: PortalPage
...
2017-08-22 16:47:56,159 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 100% complete...
2017-08-22 16:47:56,159 JiraImportTaskExecutionThread-1 INFO admin 1004x1584x1 19fopdc 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] JIRA Data Import has finished.

Root Cause #2

There are multiple maximum and minimum rank markers. For a given Rank field, there should be only one maximum marker and one minimum ranked row. To identify if this is the case, run the following SQL query in your JIRA database:

SELECT
  "FIELD_ID",
  "TYPE",
  substring("RANK" FROM 1 FOR 1) AS bucket
FROM "AO_60DB71_LEXORANK"
WHERE "TYPE" IN (0, 2);

SELECT
  "FIELD_ID",
  "TYPE",
  SUBSTR("RANK", 1, 1) AS bucket
FROM "AO_60DB71_LEXORANK"
WHERE "TYPE" IN (0, 2);

SELECT
  "FIELD_ID",
  "TYPE",
  SUBSTRING("RANK", 1, 1) AS bucket
FROM "dbo.AO_60DB71_LEXORANK"
WHERE "TYPE" IN (0, 2);

SELECT
  FIELD_ID,
  `TYPE`,
  SUBSTRING(`RANK`, 1, 1) AS bucket
FROM AO_60DB71_LEXORANK
WHERE `TYPE` IN (0, 2);

This query should return you two rows for each Lexorank custom field, one with TYPE = 0 (minimum ranked row) and one with TYPE = 2 (maximum marker).
e.g. if you have 2 Lexorank custom fields, then the queries should return you four rows. 
They should also be in the same bucket if balancing is already completed. If you have more than 2 rows returned per custom field, there may be serious corruption on the LexoRank table. Please contact Support for further help. 

Root Cause #3

There are multiple LexoRank custom fields, presumably created from broken upgrade tasks. The relevant bug report can be found here:  GHS-10985 - Getting issue details... STATUS

You can easily identify this by navigating to Administration >> Issues >> Custom Fields , check if there are multiple Rank custom fields of type Global Rank. (Not obsolete) When you have multiple rank fields, JIRA may be using the wrong Rank field while indexing issues. Its also possible that the balance was done a Rank field that is currently NOT being used by JIRA, so while indexing JIRA fails to find the maximum marker and minimum row. (more on this below in the Resolution section)

You will need to delete the duplicated fields as per described below.

Root Cause #4

The LexoRank table does not have any rows in it.

Resolution

Root Cause #1 and #2

• Please contact Support for further help on this.

Root Cause #3

1. Firstly, identify the LexoRank custom field that JIRA is currently using with this SQL query applicable for each supported RDBMS:

   Postgres, Oracle, Microsoft SQL Server, MySQL

   SELECT propertyvalue
   FROM propertyentry
     LEFT JOIN propertynumber ON propertyentry.ID = propertynumber.ID
   WHERE property_key = 'GreenHopper.LexoRank.Default.customfield.id';
   

   This should return you the ID of the Rank custom field.

2. Next, we need to ensure that the Balancing operation is completed and all the ranking values are associated to the custom field we found in step 1.

   To verify the status of the balance operations in JIRA Agile:

   • If using JIRA Agile 6.7.0 or higher, access Understand the LexoRank managment page in Jira server and look at the balancing section.
   • If using JIRA 6.6.41 or higher, access <JIRA_BASE_URL>/rest/greenhopper/1.0/lexorank/balance, for example https://jira.atlassian.com/rest/greenhopper/1.0/lexorank/balance. It may help to copy/paste the results into jsonprettyprint.com as it will be easier to read them.

   • Otherwise, execute the below SQL if you are connecting Jira to PostgreSQL database:

     SELECT * FROM "AO_60DB71_LEXORANKBALANCER";
     SELECT 'ROWS_IN_BUCKET_0', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '0|%' GROUP BY "FIELD_ID"
     UNION
     SELECT 'ROWS_IN_BUCKET_1', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '1|%' GROUP BY "FIELD_ID"
     UNION
     SELECT 'ROWS_IN_BUCKET_2', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '2|%' GROUP BY "FIELD_ID" ORDER BY "FIELD_ID";

     For MySQL database, please execute the query below: 

     SELECT 'ROWS_IN_BUCKET_0', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM AO_60DB71_LEXORANK WHERE "RANK" LIKE '0|%' GROUP BY "FIELD_ID" UNION SELECT 'ROWS_IN_BUCKET_1', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM AO_60DB71_LEXORANK WHERE "RANK" LIKE '1|%' GROUP BY "FIELD_ID" UNION SELECT 'ROWS_IN_BUCKET_2', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM AO_60DB71_LEXORANK WHERE "RANK" LIKE '2|%' GROUP BY "FIELD_ID" ORDER BY "FIELD_ID";

   Either refreshing the balance endpoint, or re-running the SQL will show you the update on the balancing progress.

   
   This SQL should return one row if the balancing is already complete, or the results will indicate if it's finished. Also, check on the FIELD_ID value returned and ensure that this is the ID of the Rank custom field we found in Step 1. (this means that the balancing is done on the right Rank field) If multiple rows are returned, you should either wait for the balancing job to complete first, or contact Support if you are not sure.

   If Step 2 returns a different FIELD_ID than the custom field ID from Step 1, then it means that the balancing was done a Rank field that is not being used by JIRA. We will need to modify the propertyentry to switch to the correct rank field. Please contact Support for this.

3. Continuing from Step 2, unlock the Rank fields for editing by following: Unlock a locked Jira Software Server or Data Center custom field

4. Go back to JIRA's custom field administration page. (Administration >> Issues >> Custom Fields) Delete all the duplicated rank fields except for the Rank field with the ID from Step 1. To identify the ID of a custom field before deleting it, click on the Gear icon on the right-hand side of the custom field and hover your mouse over the Delete option. Notice that at the bottom of your browser, a preview of the link is shown: 
   
5. From the screenshot above, we can see the field ID = 10200.
6. Once deleted, re-run the indexing process.

Root Cause #4

1. Stop JIRA.

2. Get the ID of the "Rank" field by running the query below and ensure to ignore the "Rank Obsolete" (In case you're facing Root Cause #3 as well):

   Postgres, Oracle, Microsoft SQL Server, MySQL

   SELECT id from CUSTOMFIELD WHERE customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-lexo-rank';

3. Run the following query to insert the values into the database, and ensure to replace the "FIELD_ID" according to the step above, which in our case (10900) will be the following:

   Postgres, Oracle, Microsoft SQL Server

   INSERT INTO "AO_60DB71_LEXORANK" ("FIELD_ID", "ISSUE_ID", "LOCK_HASH", "LOCK_TIME", "RANK", "TYPE") VALUES (10900, -9223372036854775808, null, null, '0|000000:', 0);
   INSERT INTO "AO_60DB71_LEXORANK" ("FIELD_ID", "ISSUE_ID", "LOCK_HASH", "LOCK_TIME", "RANK", "TYPE") VALUES (10900, 9223372036854775807, null, null, '0|zzzzzz:', 2);

   MySQL

   INSERT INTO AO_60DB71_LEXORANK (FIELD_ID, ISSUE_ID, LOCK_HASH, LOCK_TIME, `RANK`, `TYPE`) VALUES (10900, -9223372036854775808, null, null, '0|000000:', 0);
   INSERT INTO AO_60DB71_LEXORANK (FIELD_ID, ISSUE_ID, LOCK_HASH, LOCK_TIME, `RANK`, `TYPE`) VALUES (10900, 9223372036854775807, null, null, '0|zzzzzz:', 2);

4. Make sure to commit the changes if using an Oracle database.

5. Start JIRA
6. Perform a Lexorank balance again