Unable to rank issues in JIRA Agile

Issues are not able to be ranked in JIRA Agile 6.4.x. Basically, when you try to rank the issues you are hitting into this error message:

JIRA Agile cannot execute the rank operation. This board has recently been configured to use the Rank field. The system must be re-indexed before you can rank issues. Ask your administrator to perform a manual re-index.

The following error is thrown in the atlassian-jira.log

2014-07-02 09:11:26,257 http-bio-8080-exec-97 WARN xxxxxx 551x7454x6 1hz4f54 xxx.xxx.10.xxx,192.xxx.1.12 /secure/QuickCreateIssue.jspa [greenhopper.customfield.lexorank.LexoRankIndexer] Could not retrieve LexoRank value for issue[id=123317]. Indexing max LexoRank value instead.
2014-07-02 09:11:26,797 http-bio-8080-exec-98 WARN xxxxxx 551x7444x3 1xhugqi xxx.xxx.0.xxx,xxx.xxx.1.xxx /browse/TTPSTN-1054 [greenhopper.customfield.lexorank.LexoRankCFType] Unable to retrieve rank for field [12070] and issue [122761]
2014-07-02 09:11:26,801 http-bio-8080-exec-98 WARN xxxxxx 551x7444x3 1xhugqi xxx.156.0.xxx,192.xxx.1.12 /browse/TTPSTN-1054 [greenhopper.customfield.lexorank.LexoRankCFType] gh.lexorank.service.error.retrytimeout
2014-07-02 09:11:26,802 http-bio-8080-exec-98 ERROR xxxxxx 551x7444x3 1xhugqi xxx.156.0.xxx,192.xxx.1.12 /browse/TTPSTN-1054 [jira.web.component.ModuleWebComponentImpl] An exception occured while rendering the web panel: com.pyxis.greenhopper.jira:greenhopper-epics-issue-web-panel (null)
java.lang.NullPointerException
	at com.atlassian.greenhopper.web.contextprovider.IssuesInEpicContextProvider.getContextMap(IssuesInEpicContextProvider.java:86)
	at com.atlassian.plugin.web.descriptors.DefaultWebPanelModuleDescriptor$ContextAwareWebPanel.getHtml(DefaultWebPanelModuleDescriptor.java:144)
	at com.atlassian.jira.web.component.ModuleWebComponentImpl.renderModule(ModuleWebComponentImpl.java:87)
	at com.atlassian.jira.web.component.ModuleWebComponentImpl.renderModuleAndLetNoThrowablesEscape(ModuleWebComponentImpl.java:70)
	at com.atlassian.jira.web.component.ModuleWebComponentImpl.renderModule(ModuleWebComponentImpl.java:57)  <+2>
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.atlassian.plugin.osgi.hostcomponents.impl.DefaultComponentRegistrar$ContextClassLoaderSettingInvocationHandler.invoke(DefaultComponentRegistrar.java:129)
	at com.sun.proxy.$Proxy415.renderModule(Unknown Source)  <+2>
	at java.lang.reflect.Method.invoke(Unknown Source)

The following error is thrown on the issue view page

If you're running OnDemand, please contact support.

If you're using an Oracle database, please check https://jira.atlassian.com/browse/GHS-10910 and see if the issue applies to your database.

Upgrade Notes for JIRA Agile 6.4.2:

The upgrade involves upgrading the plugin and then checking the database for certain edge cases that can occur that are broken ranking values. If these edge cases have occurred they need to be fixed in the database, which is described below.

1. Back up JIRA DB and perform the workaround on a staging server first.
2. Upgrade JIRA Agile.
3. Stop JIRA.
4. Run the below SQL to clear up any improper ranking. This is PostgreSQL (see below for Oracle SQL). It's possible that no rows are deleted and this is not considered to be a problem.

   DELETE FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE 'zzzzzz:' AND "ISSUE_ID" != 9223372036854775807 AND "TYPE" = 1;
   

5. Run the below to identify if there are any problematic rows. If a number higher than 1 is returned they will need to be deleted, however it's possible no rows may be found.

   WITH marker_row_buckets AS (SELECT "TYPE", substring("RANK" from 1 for 1) AS bucket FROM "AO_60DB71_LEXORANK" WHERE "TYPE" IN (0, 2))
   SELECT * FROM "AO_60DB71_LEXORANK" WHERE "TYPE" = 1 AND substring("RANK" from 1 for 1) NOT IN (SELECT bucket FROM marker_row_buckets);
   

6. Delete any rows from the previous SQL, for example:

   DELETE FROM "AO_60DB71_LEXORANK" WHERE "ID" in (<n id from above>,<n+x from above>);
   

7. Start JIRA.
8. Run a GET on <JIRA-BASE-URL>/rest/greenhopper/1.0/lexorank/balance ONCE. This will trigger a background job that will perform a balance (re-distribution of rank values, preserves the rank order of issues);
   Run the REST commands while logged in as a JIRA Administrator. The REST Console can be used to generate the GET command.
9. Monitor the balance background job by running the below query until all ranks are in only 1 bucket.

   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";
   

10. Check to see if there are no more scheduled balance jobs by running the below query - it should return no rows. If it does return rows this means the balance is scheduled or running and can be monitored with the above SQL from step 10.

    SELECT * FROM "AO_60DB71_LEXORANKBALANCER";
    

11. A balance can take quite some time depending on the amount of issues and rank fields. A rough performance function

    amount of issues in system * amount of rank fields * duration of balance operation (100ms) 

12. Re-index (can be background).

Oracle SQL

If using Oracle, ensure the database collation is set as per Connecting JIRA to Oracle. It must be AL32UTF8 - please consult with the Oracle DBA if this needs to be changed.

SELECT COUNT(*) AS issue_row
FROM AO_60DB71_LEXORANK
WHERE TYPE = 1
AND SUBSTR(RANK, 1, 1) NOT IN (
    SELECT SUBSTR(RANK, 1, 1) AS bucket
    FROM AO_60DB71_LEXORANK
    WHERE TYPE IN (0, 2));

Rollback the plugin

It is possible to rollback to a previous version of the plugin as per the below steps.

The only version that can be rolled back to is 6.3.13.1. Any other version will cause significant problems.

1. Unlock the rank fields as per How to unlock a Locked field.
2. From JIRA Administration > Issues > Custom Fields (left side menu), rename the rank fields to something else, for example Rank (LexoRank).
3. Rename the old (Obsolete) fields to their original name 'Rank'.
4. From JIRA Administration > Manage Add-ons, uninstall JIRA Agile.
5. Stop JIRA.
6. Roll-back the JIRA Agile version in the database with the following SQL:

   UPDATE propertynumber SET propertyvalue = 42 WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'GreenHopper.Upgrade.Latest.Upgraded.Version');
   UPDATE propertystring SET propertyvalue = '42' WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'com.pyxis.greenhopper.jira:build');
   

7. Start JIRA.
8. Install the previous version of JIRA Agile 6.3.13.1. From JIRA Administration > Add-ons > Manage Add-ons (left side menu), choose 'Upload Add-on' and provide the URL or upload from your computer.
9. Reindex JIRA.