When using Microsoft SQL Server, users might be unable to rank issues in JIRA after upgrading from JIRA 6.4 to 7.2+.
- JIRA 6.4 upgraded to 7.2+
- Microsoft SQL Server 2012 or 2014.
- 1M+ issues.
- 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.
- JIRA ranks the issues properly.
- JIRA fails to rank the issues, showing the following error message on the UI:
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:
- 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:
- 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.
- 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:
- Run the following SQL queries to flush caches and update statistics on the database:
; or for one database:
; or in the Jira DB:
- Start JIRA. The indexes for the table AO_60DB71_LEXORANK will be automatically recreated.