MySQL Bugs: #64376: metadata locking

Bug #64376	metadata locking
Submitted:	19 Feb 2012 8:55	Modified:	7 Mar 2012 14:09
Reporter:	Illya Rudenko	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.5.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Waiting for table metadata lock

Description:
MyISAM table with no active transactions with _autocommit=1_ hangs with "Waiting for table metadata lock". SHOW FULL PROCESSLIST doesn't show any running queries. We don't use transactions at all.

How to repeat:
This bug is floating happened from time to time, and I don't know how to catch him. Probably if you write me how to get information about what can block table I'll try to catch this bug.

Please, send the results of SHOW FULL PROCESSLIST when this happens.

Same situation with memory tables. Dedicated server with 2 memory tables got same problem. Restarting of server solves it for a while.

I've added 5 CSV files in one of them "Query_Result1.csv" you can see result of executing "show full processlist" command. Other represents data from different tables in performance_schema database.

Operating system: CentOS release 5.7 (Final)
Kernel: 2.6.18-274.18.1.el5 x64
select @@version = 5.5.20-55-log

Another one server with the same bug

Attachment: 2show_full.csv (application/vnd.ms-excel, text), 13.86 KiB.

maybe related, bug #57474

This bug is awful, it makes incredible downtime for all services :(
The bug http://bugs.mysql.com/bug.php?id=57474 haven't been solved for a year.

It's look like if two processes at the same time trying to acquire lock of one table mysql hangs.

Thank you for the feedback.

Just to check if this is same as bug #57474 or not: do you use LOCK TABLES and PREPARE in your application?

Yes, we use LOCK TABLES in our application, but we didn't use PREPARE statements.

Yes it's look like my bug is related to bug #57474

Is somebody in MySQL understand that it is critical bug? People who wrote this part of code could you please check your code and fix bug? Please....

Simple operation:
ALTER TABLE `joomla_content_frontpage` ADD INDEX `content_id` (`content_id`)
hangs with "Waiting for table metadata lock"
- killing and executing again this statement dosen't help.
- flushing all tables doesen't help.
only restarting MySQL solves this problem for a while.

Thank you for update.

Closed as duplicate for now. Please subscribe to bug #57474, add comments there and test fix when it is available.