How to Waiting for table metadata lock on Truncate Table
How to Waiting for table metadata lock on TRUNCATE TABLE?
Problem: In MYSQL there is a long running session which is waiting for “TRUNCATE TABLE XXXXXXX”
Cause: You can check the running active sessions by show full processlist; or using any explorer tools like MYSQL Workbench. To confirm which transaction is holding the truncate table. You can execute the following command
mysql> show engine innodb status;
This will have large output but look for some transaction at the end shows waiting almost same time
Sample output portion of innodb status
—TRANSACTION 7490, ACTIVE 3047 secMySQL thread id 189, OS thread handle 0x7f03be9fb700, query id 3840 10.0.2.1 root cleaning upTrx read view will not see trx with id >= 7491, sees < 7491
Solution:
The above statment clealy says thread id 189 is holding the the transaction. Please check the MYSQL thread id 189 and take necessary action. worst case kill the session 189 will resolve your problem
mysql> KILL 189;
Note: If you are running on old version then there could be bug also. Refer the below notehttps://bugs.mysql.com/bug.php?id=61935