"canceling statement due to user request while updating tuple" while dragging an issue in the kanban board
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When the user drags the issue under the kanban board, the operation will hang for 1-2 minutes, then the following message pops up in the UI:
Communications Breakdown
-----------------------------------------
The call to the JIRA server did not complete within the timeout period.Environment
Any Jira versions
Database: PostgreSQL
Diagnosis
The following error is noticed in the atlassian-greenhopper.log:
canceling statement due to user request,,,,0,while updating tuple (7745,239) in relation "AO_60DB71_LEXORANK",UPDATE public."AO_60DB71_LEXORANK" SET "LOCK_HASH" = $1, "LOCK_TIME" = $2 WHERE "ID" IN ($3, $4) AND ("LOCK_HASH" IS NULL OR "LOCK_TIME" < $5)The above message is usually associated with a deadlock on the database, but in this situation, we see that a deadlock is not occurring because the reference does change over time:
xyz@RPGWTMW02R application-logs % grep -h 'while updating tuple' atlassian-greenhopper.log* | uniq -c
456 Where: while updating tuple (7448,62) in relation "AO_60DB71_LEXORANK"
328 Where: while updating tuple (7792,39) in relation "AO_60DB71_LEXORANK"
704 Where: while updating tuple (7448,62) in relation "AO_60DB71_LEXORANK"
4182 Where: while updating tuple (7792,39) in relation "AO_60DB71_LEXORANK"Cause
We have a bug that is very similar to the issue based on the error messages in the logs-> JSWSERVER-20691. Performing full vacuum analyse on the database resolves the issue when the database has performance issues.
Another cause of the issue is incorrect db configuration
I see the following value is set up which is not what we ask to set up for the Postgresql database:
<validation-query>select 1</validation-query>whereas the documentation connecting Jira applications to PostgreSQL mentions the following value in sample dbconfig.xml for a PostgreSQL database:
<validation-query>select version();</validation-query>As PostgreSQL does not have a configurable method of setting the Validation Query, setting it to an incorrect value would play a role in causing the above error.
Solution
If the full vacuum does not resolve the problem, check the dbconfig.xml and perform the following steps, if the parameter is set to an incorrect value:
- Stop Jira
- Take a backup of the dbconfig.xml
- update the validation-query parameter to the correct value
- Start Jira