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 root cleaning upTrx read view will not see trx with id >= 7491, sees < 7491


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 note

How to purge logs in MYSQL

1.Login as root or admin privileged user

$ mysql -u root -p password

2. Purge the Logs with log number

test$ PURGE BINARY LOGS TO “mysql-bin.0900”
The above command will purge all logs before 0900 and keep the logs 900 and above

3. Purge the logs until specific date and time

test$ PURGE BINARY LOGS BEFORE “2009-09-02 22:00:00”;
This command will purge the logs before 2nd Sep 22:00 Hrs