Ever running “DROP TABLE” query in MySql

While fixing an existing script and rerunning it, I noticed that at a particular “DROP TABLE IF EXISTS..” ,  the command just gets struck. It never comes out. After worrying for a while, I finally figured the problem – the tables were locked from previous session. 

Do these steps to make sure all the tables are unlocked –

a) show open tables;

If in_use > 1, it means that the table is locked and hence any operation on the table will go to a wait mode.

b) show processlist;

Get all the process_id’s from here. And then use the below command –

kill <process_id>;

Once all the processes are killed, run a) again to check all the in_use are 0.

Run your query again, it would sure be quick!!



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s