Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on that particular table. Does anybody know how to resolve it?

share|improve this question
add comment

2 Answers

If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.

According to the MySQL Documentation on MetaData Locking:

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

That being said, you should determine whether or not another DB Session is holding locks on the table. If such a session is an uncompleted transaction, there is where the hold up may be.

If you simply need the diskspace back quickly, you could run TRUNCATE TABLE.

SUGGESTION

Try renaming the table before attempting to drop it as following:

ALTER TABLE mydb.mytable RENAME mydb.mytabletodrop;
TRUNCATE TABLE mydb.mytabletodrop;
DROP TABLE mydb.mytabletodrop;
share|improve this answer
add comment

You should find which query causes a transaction lock by executing:

SHOW ENGINE INNODB STATUS

and see a section named TRANSACTION. Later you should kill that query to drop a database.

Reference: How do I find which transaction is causing a “Waiting for table metadata lock” state?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.