Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-15917

Ranking operations fail with the error message '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.'

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Answered
    • Icon: Highest Highest
    • None
    • 7.2.9, 7.5.3, 8.4.1, 7.6.9, 8.8.1
    • AgileBoard, Backlog
    • 7.02
    • 105
    • Severity 1 - Critical
    • 35
    • Hide

      Dear All,
       
      We're closing this bug. Please use the proposed solution as the official fix:
      Fixing a failure of ranking operations when Jira can't execute the ranking
       
      Why we're choosing this path:

      • We want you to solve the issue without upgrading. You can proceed with the fix in any version.
      • We are following the Zero Downtime policy (ZDU).
      • This is a one-time activity you should perform.
      • Implementing the database change in the product is way more complex and risky than if a database admin does the fix locally.

       
      We’re giving you full control over the process to solve the issue of ranking operation failures. You can schedule the fix and manage it on your own.

      Kind regards

      Anna Scisłowska
      Jira DC Product Manager

      Show
      Dear All,   We're closing this bug. Please use the proposed solution as the official fix: Fixing a failure of ranking operations when Jira can't execute the ranking   Why we're choosing this path: We want you to solve the issue without upgrading. You can proceed with the fix in any version. We are following the Zero Downtime policy ( ZDU ). This is a one-time activity you should perform. Implementing the database change in the product is way more complex and risky than if a database admin does the fix locally.   We’re giving you full control over the process to solve the issue of ranking operation failures. You can schedule the fix and manage it on your own. Kind regards Anna Scisłowska Jira DC Product Manager

      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.

            ca9fab6787b7 Anna Scisłowska
            vfontes Vinicius Fontes
            Votes:
            21 Vote for this issue
            Watchers:
            34 Start watching this issue

              Created:
              Updated:
              Resolved: