Bug #31937 drop table causes all threads to hang in 'Waiting for table' state
Submitted: 30 Oct 2007 10:39 Modified: 2 Nov 2007 13:41
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.22, 4.1.24 OS:Windows (XP 64-bit)
Assigned to: Heikki Tuuri CPU Architecture:Any
Tags: hang

[30 Oct 2007 10:39] Shane Bester
Description:
With innodb tables, a single DROP TABLE is causing all threads to hang forever:

mysql> show processlist;
+-------------+------+--------------------+-----------------------------------
| Id  Command | Time | State              | Info                              
+-------------+------+--------------------+-----------------------------------
|  5  Query   |    0 | NULL               | show processlist                  
| 35  Sleep   |    8 |                    | NULL                              
| 36  Query   |  345 | Waiting for table  | select * from t1 where id = -6248 
| 37  Query   |  345 | Waiting for table  | drop table if exists t1           
| 38  Query   |  345 | Waiting for tables | select * from t1 where id = -8663 
| 39  Query   |  345 | Waiting for table  | insert ignore into t1(id,name) val
| 40  Query   |  345 | Waiting for table  | select * from t1 where id = -2188 
+-------------+------+--------------------+-----------------------------------

mysql> show open tables;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t1    |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysqld-nt: ready for connections.
Version: '4.1.22-pro-nt'  socket: ''  port: 3306  MySQL Pro

How to repeat:
Will upload a testcase shortly. It will consist of 5 threads all running the following queries over and over:

drop table if exists t1;
create table t1(id int,name varchar(255),primary key(id))engine=innodb;
select * from t1 where id = <some number>;
insert ignore into t1(id,name) values (<some number>,'<some number>');
[30 Oct 2007 10:46] MySQL Verification Team
innodb status and processlist

Attachment: bug31937_innodb_status_processlist.txt (text/plain), 4.89 KiB.

[30 Oct 2007 10:53] MySQL Verification Team
testcase. point to a windows 4.1.22 server and run it until the output hangs (then check processlist)

Attachment: bug31937.c (text/plain), 5.04 KiB.

[30 Oct 2007 12:29] MySQL Verification Team
I can still repeat the hang on 4.1.24-release build.  So, it was just luck that the debug build didn't hang.  Uploading thread stack traces shortly.
[30 Oct 2007 12:50] MySQL Verification Team
thread stacks during a hangup on 4.1.24-debug-nt

Attachment: bug31937_4.1.24_thread_stacks.txt (text/plain), 35.13 KiB.

[30 Oct 2007 12:58] MySQL Verification Team
setting to verified with the following notes:

could only repeat on 4.1.x on windows.
4.1.x on linux doesn't appear affected.
5.x doesn't appear affected.
[31 Oct 2007 12:00] Heikki Tuuri
Shane,

what does SHOW INNODB STATUS show in the hang? If you are able to run it.

'Waiting for a table' suggests a hang in MySQL's lock table. It may have been that a bug like this was present already years ago.

Can you repeat the hang in 5.0?

Regards,

Heikki
[31 Oct 2007 17:37] MySQL Verification Team
heikki, everything is attached to the bug report. no, could not repeat in 5.x at all.  also, could not repeat on 4.x on linux....

http://bugs.mysql.com/file.php?id=7682
[31 Oct 2007 18:16] Heikki Tuuri
Shane,

I am sorry, I did not notice you had already posted it. All the threads seem to be inside MySQL. Thus, this hang is probably a MySQL bug.

I think this hang bug on Windows was reported already a few years ago. Since then, the MySQL table locking has been changed.

Since 5.0 probably does not have this bug, I suggest we put this to the 'Won't fix' status? I suspect a new MySQL release of 4.1 on Windows will never appear?

Regards,

Heikki
[2 Nov 2007 13:41] Heikki Tuuri
Changing this to the Won't fix status. 4.1 is already old.