-
Bug
-
Resolution: Answered
-
Highest (View bug fix roadmap)
-
None
-
7.2.9, 7.5.3, 8.4.1, 7.6.9, 8.8.1
-
7.02
-
105
-
Severity 1 - Critical
-
35
-
-
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
- Upgrade a JIRA instance having 1M+ issues from 6.4 to 7.2.
- Start a re-balance operation to generate load on the database, navigating to Cog Icon > System > LexoRank Management > Balance all fields.
- 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
- Stop JIRA.
- Backup your database.
- Delete all indexes from the table AO_60DB71_LEXORANK, except for the primary key.
- 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;
- 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;
- Start JIRA. The indexes for the table AO_60DB71_LEXORANK will be automatically recreated.
- is caused by
-
-
- Closed
-
- is related to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Gathering Impact
-
- relates to
-
-
- Gathering Impact
-
-
BJBR-20 Loading...
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[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.'
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
edgar.garcia
Thanks for sharing your experience. Glad to hear that DB maintenance job fixes problem for you 
On an update... our DBA ran
UPDATE STATISTICS with fullscan;
on our MS SQL 2017 DB and that fixed the issue. He also updated the weekend maintenance job with that, and the command runs daily.
For now that fixed the issue.
This happened again in MS SQL 2017, we re-run the maintenance jobs and it is still failing.
We migrated the infrastructure and moved from MS SQL 2014 to MS SQL 2017, this problem got corrected after Jira Full reindex, some general Jira use and running the maintenance jobs. Still, our instance has the VARCHAR types assigned to RANK and LOCK_HASH, we have that work in the queue.
We are affected by this on MS SQL Server 2016, and we don't even have 1M issues.
I can assure you this was never changed.
Looking at the definitions in fieldtype-mssql.xml under \WEB-INF\classes\entitydefs for 7.9.2, and just like 7.2.x, it still has:
<!-- Special types for the QUARTZ tables -->
<field-type-def type="quartz-char-8" sql-type="VARCHAR(8)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-16" sql-type="VARCHAR(16)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-40" sql-type="VARCHAR(40)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-80" sql-type="VARCHAR(80)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-95" sql-type="VARCHAR(95)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-120" sql-type="VARCHAR(120)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-200" sql-type="VARCHAR(200)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-250" sql-type="VARCHAR(250)" java-type="String"></field-type-def>
<field-type-def type="quartz-char-512" sql-type="VARCHAR(512)" java-type="String"></field-type-def>
<field-type-def type="quartz-boolean" sql-type="VARCHAR(1)" java-type="Boolean"></field-type-def>
If you instruct SQL to create varchar columns, it will obey you, and that is totally what it is about that 
I can confirm that creating a new DB from an xml restore does NOT fix this problem, and never will unless we update the "Special types for the QUARTZ tables" section in your field type definitions.
Example: <field-type-def type="quartz-char-8" sql-type="NVARCHAR(8)" java-type="String"></field-type-def>
Of course, we customers could do this manually, but then we may find ourselves in an unsupported state.
Ideally, you would update this file yourself, and then we wouldn't be asked to tamper with a vendor database, a la JSWSERVER-12221
Thanks!
Looks like this might be the same as JSWSERVER-15874 though this one seems to be MSSQL.
Atlassian team, you cannot close this highest priority ticket with a solution "restart Jira". We hit this problem regularly and restarts are costly due to other dependencies. Please reopen and get to the root cause!