How to resolve issue Drop ,Delete ,Alter commands taking long time?

 

Why this issue arises ?

This is probably due to Metadata locking .It is most common to see table gets struck in "Waiting for Metadata Locks". ..It comes basically due to some uncommitted Transactions.

Solution

In order to resolve this first we should wait for sometime for transaction to complete that might be using that table.If it is taking very long time then following are the steps to resolve this :

1.) Login Mysql 

     mysql -u root -p 

     ******

2.) RUN  

     Show Full Processlist.

     (Here you will get list of transactions that are creating lock on other transactions )

+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 404 | root | localhost | example           | Sleep   | 297  |       |                  |
| 410 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

Here process 404 is creating lock on other transactions.

3.) kill process 404 by running:

      kill 404

4.) If above process kill doesn't works then you can go for killing other processes as mentioned in  Step 3.

5.) Now try executing Drop ,Delete,Alter or any command it would now work correctly.

Above approach is a better approach instead of MySQL server restart.

Hope the above solution works .Looking forward for your questions and suggestions ..  :)