Bug #62210 | memory corruption for THD after deadlock in foreign key reference constrain | ||
---|---|---|---|
Submitted: | 19 Aug 2011 11:47 | Modified: | 2 Feb 2012 19:59 |
Reporter: | liu hickey (OCA) | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S2 (Serious) |
Version: | 5.1.48 (1.0.9) | OS: | Linux (2.6.18-164.el5) |
Assigned to: | CPU Architecture: | Any | |
Tags: | deadlock; lock timeout |
[19 Aug 2011 11:47]
liu hickey
[19 Aug 2011 11:48]
liu hickey
gdb info for generated core file
Attachment: gdb.log (application/octet-stream, text), 67.00 KiB.
[19 Aug 2011 11:49]
liu hickey
show innodb status
Attachment: innodb.log (application/octet-stream, text), 54.58 KiB.
[19 Aug 2011 11:49]
liu hickey
show processlist info
Attachment: proc.log (application/octet-stream, text), 4.66 KiB.
[17 Jan 2012 17:12]
Sveta Smirnova
Thank you for the report. You have 1 long-running transaction: TRANSACTION 99E87, ACTIVE 1212 sec, process no 5325, OS thread id 1105189184 1414 lock struct(s), heap size 178616, 308451 row lock(s), undo log entries 1782 MySQL thread id 5427673, query id 108306779 172.24.113.136 cheetah Trx read view will not see trx with id >= 99E88, sees < 99E88 It holds 1414 lock structs and can easily hold locks on the parent table. You also have a lot of sleeping transactions. Do you use autocommit=0 and don't close connections? When you see same problem next time try to close all idle sleeping connections and check if this solves the issue.
[2 Feb 2012 2:18]
liu hickey
Thanks Sveta pointing out the detail info which I did not pay much attention to. Yes, it's possible to block transactions applying locks. We did set autocommit=0 and keep connections on. Killing the oldest trx for 'hang' situation on product environment is what we usually did, it's a useful quick-repair skill for running MySQL. As we failed to identify this trouble which hit once each month (twice for all), we later removed the foreign key reference later, and that system running well from then, so I could not provide further info for this problem :( If it's due to some client's uncommit logic bug, after removed FK ref, this issue should also be hit. However it's not. So I eliminate the application's potential uncommit bug. The innodb status is monitored after the parent table 'hang' 10 hours, considering the time, it seems that long trx doesn't contribute much for the problem. Hope any others hit such issue.
[2 Feb 2012 19:59]
Sveta Smirnova
Thank you for the feedback. > If it's due to some client's uncommit logic bug, after removed FK ref, this issue should also be hit. However it's not. So I eliminate the application's potential uncommit bug. Not necessary. If parent table holds the lock removing foreign key will help and this is expected. Closing report as "Can't repeat" for now.