Bug #41335 queries to MySQL 5.1.x block in state "opening tables" and "closing tables"
Submitted: 9 Dec 2008 18:33 Modified: 8 Dec 2009 19:41
Reporter: Hristo Erinin Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.1.25 and 5.1.29 OS:Linux
Assigned to: CPU Architecture:Any
Tags: closing tables, innodb, lock-up, lockup, myisam, opening tables

[9 Dec 2008 18:33] Hristo Erinin
Description:
I'm sorry for the lack of information in this bug report. I don't know how to reproduce and locate the bug myself. 

MySQL sometimes locks up and queries hang indefinitely in state of either "opening tables" or "closing tables". SHOW PROCESSLIST and other such queries work, so it's not a complete blockage of the whole server. 

The server has 2*Quad Core Xeons (E5345) with 8GB of RAM. Datafiles are located on a NetApp storage filer with iSCSI 1Gig ethernet interconnect. Server is running Red Hat Enterprise Linux AS 4 update 6. The kernel is 2.6.9-67.0.15.ELsmp. There is plenty of free space.

The tables are a mixture of InnoDB and MyISAM tables and queries that hang are trying to query both InnoDB and MyISAM tables (even select * from mysql.User), so it doesn't look like an InnoDB specific problem (but I could be wrong).

In the attached processlist I can see two queries simultaneously trying to update the field in an InnoDB table so this could be helpful.

update ts.querycontrol set lastseen = '2008-12-01 03:00:28', endtime = '2008-12-01 03
:00:28', lastcompleted = '2008-12-01 03:00:28', lastcompletedstart = '2008-12-01 03:00:24', status = 'complete' where id = '6711'
update ts.querycontrol set lastseen = now() where id = '6711'

When this problem occured I've been using MySQL Community version 5.1.25 RPMs downloaded from this site. I've recently upgraded to 5.1.29 but the problem still exists. 

See the attachments for my.cnf, processlist, innodb status, variables and status dumps. In case there is more info necessary please advise.

How to repeat:
I don't know what triggers this bug it so I can't easily reproduce it. If you have any ideas what could be causing it I'm willing to try reproducing the bug.
[10 Dec 2008 7:24] Sveta Smirnova
Thank you for the report.

But there is not any attachment. Please attach.
[15 Dec 2008 19:21] Hristo Erinin
The server locked up again today. During the lock-up no queries that involve any table operation can complete. Even select * from information_schema.tables; locks up indefinitely. SHOW * queries work without problem. I've uploaded another set of files on the ftp server. bug-data-20081215-41335.zip is the filename on ftp.mysql.com. Also, attached to this bug report you can find the output of SHOW ENGINE INNODB STATUS command. 

Thanks,
Hristo
[8 Nov 2009 19:41] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.40. In case of the same problem, please, send the results of SHOW FULL PROCESSLIST and your my.cnf file content.
[9 Dec 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".