MySQL Bugs: #68042: MySQL wedges with "Waiting for table metadata lock"

Bug #68042	MySQL wedges with "Waiting for table metadata lock"
Submitted:	6 Jan 2013 16:24	Modified:	8 Jan 2013 0:26
Reporter:	Jon Ribbens	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.5.28	OS:	Linux (Ubuntu precise (12.04.1 LTS))
Assigned to:		CPU Architecture:	Any

Description:
Our MySQL server keeps wedging a particular MyISAM table. From "SHOW PROCESSLIST" it appears that it is hanging on a "LOCK TABLES <foo> WRITE" statement in the state "Waiting for table metadata lock", and that no other commands are operating at the moment that it wedges.

How to repeat:
Run our system for a random amount of time.

Suggested fix:
No idea.

Please, send the entire output of:

show processlist;

when you see this problem.

You don't need another running query.  What you might have is an open transaction.  So, please kill every other thread listed in processlist, if that helps, then you should fix your application to either commit or rollback, or don't use autocommit=0 ..

I will when it happens again, but it shows nothing but one thread in "waiting for table metadata lock" for "LOCK TABLES <foo> WRITE", and several other threads (whose sleep time is lower than the first) also in "waiting for metadata lock" on "SELECT * FROM <foo> ...".

ps, there is no reliable way to list all open transactions.  innodb status only shows the ones that touched innodb tables..

We never use transactions.

Maybe not intentionally.  But something does.  You could enable general query log to see exactly what queries are running...  We're looking for SET AUTOCOMMIT ... , START TRANSACTION, BEGIN , and such commands.  They could be issued by connectors, framework, etc, behind the scenes?

http://dev.mysql.com/doc/refman/5.5/en/query-log.html

We do not use any frameworks or whatever either, unless python-mysqldb has suddenly decided it wants to secretly use them without telling anyone. However I will try enabling the query log as per your suggestion.

OK it turns out that in fact python-mysql *does* in fact have an undocumented feature that it turns off autocommit! Great. So I shall try and work-around that misfeature and hopefully that will fix my problem.

However, I don't see why this is resulting in the problem seen. MyISAM does not support transactions anyway - the MySQL documentation section 1.8.5.3 says "In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode".

So I still think this is a bug in MySQL.

Thanks for your input by the way Shane, your suggestion of enabling the general query log allowed me to see the secret things python-mysql was doing behind the scenes.

The issue seen here will not happen in 4.1, 5.0, 5.1.
Consider what would happen if you modified a myisam table, then an innodb table in a transaction. In 5.5, we have MDL and this is not engine specific.  

http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

Downside is there is no way to know which connection locks what.
That functionality is having to be implemented in some future version of MySQL...

I think this is what was surprising, i.e. the change in behaviour. Plus the fact that the only "DDL"-type statement we are using is "DESCRIBE", which doesn't exactly seem like a dangerous command.

Also I don't entirely see the difference between what you said and "Consider what would happen if you modified the data in a myisam table, then the data in an innodb table in a transaction".

I 2nd this. I've been trying to deal with this ever since I upgraded to 5.5 (on a staging server, of course). I'm pretty sure I'm "commit"-ing every call, but when I try an ALTER TABLE, I have the "Waiting for table metadata lock" in my processlist. 

Even a "Metadata lock timeout" would be a nice future feature.

Perhaps a global server config way of exempting MyISAM tables from this new feature...

David, "lock_wait_timeout" is your metadata lock timeout option.
By default it is set to one year.  Consider reducing that..

So I got to the bottom of the issue.

Django's DB Handler for MySQL starts all connections with "autocommit=0". 

If I do a "select" on a table with Django's ORM, it leaves transactions ACTIVE without a "commit". Now it's a select, so it shouldn't matter.

In another Transaction, if I do an ALTER TABLE on that same table which hangs with a metadata lock until the "select" closes or, curiously enough, "commit". 

That solves things, but the issue seems to be how Django handles transaction in certain circumstances.

FLUSH TABLES is a nice work-around to eliminate the metadata lock while you troubleshoot where it is originating from.