Bug #41114 DDL + insert delayed + kill causes infinite hang and 100% cpu waiting for tables

• Submitted: 28 Nov 2008 20:36
• Modified: 20 Jan 2011 7:40
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 5.0.70, 5.1.30, 5.5.0
• OS: Any
• CPU Architecture: Any
• Tags: insert delayed, KILL

[28 Nov 2008 20:36] Shane Bester

Description:
DDL + insert delayed + kill causes infinite hang and 100% cpu waiting for tables

+----------------+------+---------------------+----------------------------------
| Command        | Time | State               | Info
+----------------+------+---------------------+----------------------------------
| Query          |    0 | NULL                | show processlist
| Sleep          |    3 |                     | NULL
| Query          |   81 | rename result table | alter table `qa00` engine=MyISAM
| Delayed insert |   81 | Waiting on cond     |
+----------------+------+---------------------+----------------------------------

How to repeat:
compile and run the attached .c testcase.

[28 Nov 2008 20:53] MySQL Verification Team

testcase

Attachment: bug41114.c (text/plain), 8.22 KiB.

[22 Aug 2009 9:48] Konstantin Osipov

Is this repeatable in 5.4.4? If not, why is it SRFUTREL?
Please clarify.
Needs re-triage after that (set risk/effort).

[14 Nov 2009 15:54] Valeriy Kravchuk

Same problem still happens with recent mysql-trunc from bzr (5.5.0):

77-52-1-11:5.1 openxs$ bin/mysql -uroot test -e "show processlist"
+----+---------+-----------------+------+----------------+------+---------------------+----------------------------------+
| Id | User    | Host            | db   | Command        | Time | State               | Info                             |
+----+---------+-----------------+------+----------------+------+---------------------+----------------------------------+
|  3 | root    | localhost:63299 | test | Query          |  118 | rename result table | alter table `qa00` engine=MyISAM |
|  7 | DELAYED | localhost       | test | Delayed insert |  118 | Waiting for INSERT  |                                  |
|  8 | root    | localhost       | test | Field List     |   89 | Waiting for table   |                                  |
|  9 | root    | localhost       | test | Query          |    0 | NULL                | show processlist                 |
+----+---------+-----------------+------+----------------+------+---------------------+----------------------------------+

[19 Jan 2011 11:24] Dmitry Lenev

Hello!

Could you please re-verify this bug against latest release in 5.5 branch?
There were lot of code changes in this area since the moment it was initially verified for this branch and I suspect that this bug might have been fixed since then.

[20 Jan 2011 7:40] Valeriy Kravchuk

Original .c test case does not hang any more with 5.5.10-debug running on 32-bit Ubuntu 10.04.