Summary

When ranking an issue on a board , you might see the following error:

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.

We’ve investigated that this issue occurs in Jira 7.2 and later versions. We also know that it occurs for the SQL Server database. But we don’t rule out that the same issue might occur for other databases.

Diagnosis

From the Greenhoper logs, you may identify several occurrences of SQLServerException timing out while performing the rank operation, which indicates the database is suffering and not being able to complete the Rank operation in time, so it times out.

2023-02-01 12:47:14,349 http-nio-8080-exec-8 ERROR xxxxxx 766x37047x7 dgfe0d 10.80.16.215 /rest/greenhopper/1.0/sprint/rank [greenhopper.manager.lexorank.LexoRankDaoImpl] The query has timed out.
com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
    at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:7342)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:73)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1531)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409

From the Catalina logs, you may also find several stuck threads related to the SQLServer, as this one below:

01-Feb-2023 12:35:58.229 WARNING [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.valves.StuckThreadDetectionValve.notifyStuckThreadDetected Thread [http-nio-8080-exec-21] (id=[41]) has been active for [125,781] milliseconds (since [2/1/23 12:33 PM]) to serve the same request for [http://xxxxxxx/secure/AjaxIssueEditAction!default.jspa?decorator=none&issueId=382584&_=1675272655194] and may be stuck (configured threshold for this StuckThreadDetectionValve is [120] seconds). There is/are [4] thread(s) in total that are monitored by this Valve and may be stuck.
 java.lang.Throwable
...
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1981)
    at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6310)
    at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7545)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:465)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)

Cause

In the AO library updated in Jira 7.2, the definition of the String column changed from VARCHAR(255) to NVARCHAR(255). There were no upgrade tasks, so existing columns with VARCHAR(255) weren’t migrated.

So, in Jira 6.x, the AO_60DB71_LEXORANK.RANK column has the data type VARCHAR(255). But in Jira 7.2 and later versions, the data type on this column was changed to NVARCHAR(255).

Ranking operations fail because Jira doesn’t change the data type of the AO_60DB71_LEXORANK.RANK column from VARCHAR(255) to NVARCHAR(255) during the upgrade to version 7.2 or later. As a result, SQL Server is forced to use an inefficient execution plan which is likely to take more than 1500 ms to complete. This delay triggers a hardcoded timeout in Jira, and a ranking operation fails.

The timeout value can’t be changed:

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

See related problem JSWSERVER-15917 - Getting issue details... STATUS  

Solution

We’re giving you full control over the process to solve the issue of ranking operations failures. So, we respect the Atlassian Zero Downtime policy and don’t force the time-consuming Jira upgrade on your side.

To solve the issue on your end:

1. Stop Jira.

2. Back up your database.

3. Delete all indexes from the AO_60DB71_LEXORANK table, 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:

   1. To flush caches for Jira’s database:

      DBCC FLUSHPROCINDB (<dbid>)

   2. To rebuild statistics for the LexoRank table:
      

      UPDATE STATISTICS dbo.AO_60DB71_LEXORANK;

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