Summary

When using Microsoft SQL Server, users might be unable to rank issues in JIRA after upgrading from JIRA 6.4 to 7.2+.

Environment

• JIRA 6.4 upgraded to 7.2+
• Microsoft SQL Server 2012 or 2014.
• 1M+ issues.

Steps to Reproduce

1. Upgrade a JIRA instance having 1M+ issues from 6.4 to 7.2.
2. Start a re-balance operation to generate load on the database, navigating to Cog Icon > System > LexoRank Management > Balance all fields.
3. Navigate to a board and change the order of any two issues to rank them.

Expected Results

• JIRA ranks the issues properly.

Actual Results

• JIRA fails to rank the issues, showing the following error message on the UI:

  JIRA Software cannot execute the rank operation at this time. Other users may be ranking the issues that you are trying to rank. Please try again later.
  

After enabling DEBUG logging level for the packages com.atlassian.greenhopper.service.lexorank and com.atlassian.greenhopper.manager.lexorank the following messages show up in the logs in the file atlassian-greenhopper.log:

2017-06-06 15:09:59,384 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.service.lexorank.LexoRankHealOperation] Executing heal operation on the following rows: [LexoRankRow{id=1650106, fieldId=17090, issueId=977912, lockHash='null', lockTime=null, bucket=2, rank='2|hznqil:', type=ISSUE_RANK_ROW}, LexoRankRow{id=1647442, fieldId=17090, issueId=976572, lockHash='null', lockTime=null, bucket=2, rank='2|hznqi7:e', type=ISSUE_RANK_ROW}, LexoRankRow{id=1650136, fieldId=17090, issueId=977927, lockHash='null', lockTime=null, bucket=2, rank='2|hznqhz:', type=ISSUE_RANK_ROW}]
2017-06-06 15:09:59,386 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM dbo.AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND ISSUE_ID = ? AND TYPE = ? params[17090, 977912, 1]
2017-06-06 15:09:59,387 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT TOP 2 FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM dbo.AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND RANK <= ? ORDER BY RANK DESC params[17090, 2|hznqi7:e]
2017-06-06 15:10:00,223 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT TOP 2 FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM dbo.AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND RANK <= ? ORDER BY RANK DESC params[17090, 2|hznqi7:e]
2017-06-06 15:10:01,053 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT TOP 2 FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM dbo.AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND RANK >= ? ORDER BY RANK ASC params[17090, 2|hznqhz:]
2017-06-06 15:10:01,595 http-nio-8080-exec-63 DEBUG jiraadmin 909x3876163x1 4buww4 10.13.201.21,172.28.20.250 /rest/greenhopper/1.0/sprint/rank [c.a.g.service.lexorank.LexoRankOperation] Save of rank row [LexoRankRow{id=1650106, fieldId=17090, issueId=977912, lockHash='78f950f0-1ad6-4131-921c-aeee88520baa', lockTime=1496779799384, bucket=2, rank='2|hznqi4:', type=ISSUE_RANK_ROW}] failed, retrying rank operation

Notes

• The ranking operation fails because all SQL queries needed for the ranking operation must complete in less than 1500ms. This timeout value is hardcoded and cannot be changed:

  public class LexoRankDaoImpl implements LexoRankDao
  {
      //(...)
      private static final int LOCK_TIMEOUT_MILLIS = 1500;
  

• On JIRA Agile 6.x the AO_60DB71_LEXORANK.RANK column has the data type VARCHAR(255).
• On new JIRA 7.2+ installations the data type on this column was changed to NVARCHAR(255).
• The issue happens because JIRA Software does not change the data type of the column AO_60DB71_LEXORANK.RANK from VARCHAR(255) to NVARCHAR(255) during the upgrade to 7.2+, forcing SQL Server to use an inefficient execution plan which is likely to take more than 1500ms to complete, which in turn will trigger the hardcoded timeout in JIRA and the ranking operation will fail.

Workaround

1. Stop JIRA.
2. Backup your database.
3. Delete all indexes from the table AO_60DB71_LEXORANK, except for the primary key.
4. Change the data type on the columns AO_60DB71_LEXORANK.RANK and AO_60DB71_LEXORANK.LOCK_HASH using these SQL queries:

   ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN RANK NVARCHAR(255) NOT NULL;
   ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN LOCK_HASH NVARCHAR(255) NULL;
   

5. Run the following SQL queries to flush caches and update statistics on the database:

   DBCC FREEPROCCACHE;
   

   ; or for one database:

   DBCC FLUSHPROCINDB (<dbid>)
   

   —

   SP_UPDATESTATS;
   

   ; or in the Jira DB:

   UPDATE STATISTICS;
   

6. Start JIRA. The indexes for the table AO_60DB71_LEXORANK will be automatically recreated.