While doing ranking operation Issue ranking request has two limits
- Total time limit for retry for whole method: 4000 ms
- More specific timeout for rank lock: 1500 ms
In some cases (see below) issue ranking operation fails to save new rank due to rank lock expire. This is caused by doing number of SQL calls to AO_60DB71_LEXORANK table under lock and some of them are slow (see below).
At this stage, we believe this is not a JIRA bug. That being said, we will keep ticket open for some time to monitor the problem.
- Large JIRA instance: 1M+ issue
- Basically we need large AO_60DB71_LEXORANK table
- MySQL 5.6.x
- We identified problem for MySQL (5.6.x only), other DBs might be also affected, needs more checking
- Try to rank issue at the bottom of the RANK
- Currently problem is easily reproducible for EPIC ranking, since they are mostly at the bottom
Rank operation is successful.
- EPIC ranking operation fails and that give error in 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
- Rest call: /rest/agile/1.0/epic/<Epic_ID>/rank returns 500
- Detailed debug logging shows that EPIC rank fails to store new rank in DB due to lock timeout.
- To enable logging set DEBUG for com.atlassian.greenhopper.service.lexorank and com.atlassian.greenhopper.manager.lexorank
- slow SQL (3.47 sec)
- Note that not all calls are slow, response time depends on actual RANK value (position in the table).
To help investigate problem like this in the future, please see KB based on this bug:
After some additional deep research from our side, we found a MySQL bug with very similar description which was fixed in 5.7:
- fixed in 5.7
- Quote from https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html
For the index merge access method, the optimizer could make a suboptimal choice of indexes to use. (Bug #14095506)
- In case of MySQL, please upgrade to MySQL 5.7. We have a confirmation from the client that fixes the problem.
- In case of MSSQL, problem was caused by wrong column type: RANK was varchar, it should be nvarchar.
Since not all clients will be able quickly upgrade to MySQL 5.7, we decided to implement workaround for the problem in JIRA itself. In corresponding fixed version, code was changes to avoid SQL "RANK <= 'abc' ORDER BY RANK DESC"