When MySQL Just Won't Stop!

Last night I was running a big import on an Azure VM and thought that my shutdown at 11pm would leave enough time to complete it. I was wrong :-)

Sadly, when I came in the next day, the database I’d been importing into was in an unstable state and my attempt to drop the database led to a loooooong wait that just didn’t seem to ever want to finish. Fortunately, if that happens to you, or something like it, there is something you can do about it.

Firstly, let’s take a look at what processes are running by connecting to your MySQL database and running this command:

mysql> show full processlist;

| Id | User         | Host            | db               | Command    | Time | State                            | Info                                                                                                                                                                                                                                                                                                |
+----+--------------+-----------------+------------------+------------+------+----------------------------------+-----------------------------------------+
|  5 | root         | localhost:41632 | cheekyDb         | Query      | 1839 | Repair by sorting                | SHOW COLUMNS FROM `cheekyDb`.`searches`                                                                                                                                                                                                                                                     |
|  7 | root         | localhost       | cheekyDb         | Field List | 1788 | Waiting for table metadata lock  |                                                                                                                                                                                                                                                                                                     |
|  8 | root         | localhost       | NULL             | Query      | 1734 | Waiting for schema metadata lock | drop database cheekyDb                                                                                                                                          |
|  9 | db_xyz_write | localhost:44648 | cheekyDb         | Query      |  313 | Waiting for schema metadata lock | SELECT `content`.`body\`, blah, blah     
| 10 | root         | localhost       | NULL             | Query      |    0 | starting                         | show full processlist                                                                                                                                                                                                                                                                               |

I’ve done some careful editing here, but you can see that we have 5 processes and process Id 10 is the command I had just run. 7, 8 and 9 were all waiting for…you guessed it, Id number 5. That one was complaining about repairing (good grief, what had I done?) so the only humane thing I could do was to open a new terminal and type:

$ mysqladmin kill 5

On UNIX, killing processes is usually super fast, but I found that I needed to re-run my command to show the processes a few times in MySQL to see anything other than the process was supposedly ‘killed’. This may be because rather than a kill -KILL process-id it might be more like kill -SIGTERM process-id. If that means nothing to you, think of it as a brutal murder spree in Call of Duty compared to an act of mercy. After about a minute (your mileage may vary), everything cleaned up and the problem was resolved. Phew.

---

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com