Mysql – show thesql theisam locks

MySQL

when dropping or renaming a table with myisam storage engine, I realize that it's waiting for table metadata lock, however, show full processlist doesn't reveal the offending query. any idea?

| 462 | root | xxx.xxx.xxx.xx:54658 | mydb | Sleep   | 1162 |                                 | NULL                                                 |
| 465 | root | localhost           | mydb | Query   |    0 | NULL                            | show full processlist                                |
| 466 | root | localhost           | mydb | Query   |  125 | Waiting for table metadata lock | alter table mytable rename to mytable_junk |

Best Answer

If you are using MySQL 5.5, there is a bug report on it.

If fact, click here for an explanation of metadata locking. It is meant to protect tables from getting DDL executed against it while in the middle of a transaction.

What seems strange is that you are getting this on a MyISAM table.

Related Solutions

MySQL – How to Show MySQL Locks

See Marko's link for InnoDB tables and the caveats.

For MyISAM, there isn't a dead easy "this is the offending query" solution. You should always start with a processlist. But be sure to include the full keyword so that the printed queries aren't truncated:

SHOW FULL PROCESSLIST;

This will show you a list of all current processes, their SQL query and state. Now usually if a single query is causing many others to lock then it should be easy to identify. The affected queries will have a status of Locked and the offending query will be sitting out by itself, possibly waiting for something intensive, like a temporary table.

If it's not obvious then you will have to use your powers of SQL deduction to determine which piece of offending SQL may be the cause of your woes.

Mysql – App server takes a long time to connect to MySQL Server

Can you log in without delay from the database server? If so, it is likely a network problem.

What network hardware sits between these devices?
Have the NICs been tried and tested on other subnets/connections?

If you cannot login quickly, check the db server logs (out of disk space, full /tmp dir, timeouts etc).

It could also be a DNS issue, although 20 minutes seems outrageous for a DNS lookup. You could try adding

skip-name-resolve

to your my.cnf to see if that helps.

Best Answer

Related Solutions

MySQL – How to Show MySQL Locks

Mysql – App server takes a long time to connect to MySQL Server

Related Topic