Bug #80927 InnoDB Last Detected Deadlock Not showing full query
Submitted: 31 Mar 2016 15:24 Modified: 1 Apr 2016 6:31
Reporter: Trevor Thackston Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5, 5.5.48 OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock, engine status, innodb

[31 Mar 2016 15:24] Trevor Thackston
The last detected deadlock section of the engine status log is only showing 1024 characters (combined) of the thread information and the query. Because we are using Hibernate, some of our generated queries get pretty long - this 1024 character limit is cutting them off before they even reach "where" which is making it very hard to debug these issues. There was an earlier bug about a very similar issue - https://bugs.mysql.com/bug.php?id=7819 - and it looks like the limit was raised to 3000 characters in 5.0.12 as a result of that, but it seems that was overwritten by some later change.

How to repeat:
Write a long query that deadlocks with another query, then use SHOW ENGINE INNODB STATUS. You will see in the Last Detected Deadlock section that the query and its thread information combined do not exceed 1024 characters.

Suggested fix:
Don't limit the query. Since there was hesitation (in regards to cluttering the log) about showing the full query in the previous issue, though, perhaps make the limit on what's printed a configurable setting. It can default to whatever it needs to default to, but it would be useful if we were able to make it show the full query when needed. If a configurable limit is the solution you land upon, though, please do include some option to make it not limit itself. Perhaps a MAX_DEADLOCKED_QUERY_LENGTH variable that, when set to 0 or -1 or so, does not hinder the writing of the log at all.
[1 Apr 2016 6:31] MySQL Verification Team
Hello Trevor,

Thank you for the report.