price.mit.edu/blog

Sometimes a MySQL query doesn’t die when you think it does.

If you’ve spent much time with MySQL, you’ve probably tried a query from the mysql command line, changed your mind after it didn’t return for a while, and hit C-c:

$ mysql -h youtomb-sql.mit.edu -u guest youtomb

mysql> SELECT COUNT(DISTINCT status) FROM artifacts;
^CCtrl-C -- sending "KILL QUERY 183" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

That kills the query. But notice what the mysql program is telling you after you hit that C-c: it’s sending a separate command, namely the “query” “KILL QUERY 183“, to the server.

In fact, that KILL query is the only way to get the MySQL server to stop running a query. In particular, the MySQL server is really bad at noticing when a client goes away. Suppose instead of hitting C-c, which the mysql program traps and handles in a smart way, I simply kill the program by hitting C-\:

mysql> SELECT COUNT(DISTINCT status) FROM artifacts;
^\Aborted

Then in fact the server keeps running the query. If I fire up the MySQL client anew and issue the query SHOW PROCESSLIST, I can see the query still chugging away:

$ mysql -h youtomb-sql.mit.edu -u guest youtomb

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 183
   User: guest
   Host: OPUS.MIT.EDU:37938
     db: youtomb
Command: Query
   Time: 4
  State: Sending data
   Info: SELECT COUNT(DISTINCT status) FROM artifacts
*************************** 2. row ***************************
     Id: 185
   User: guest
   Host: OPUS.MIT.EDU:37940
     db: youtomb
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

That \G is an alternative to the semicolon that makes the format more readable here.

If I want to actually kill the query, I can do it with the same meta-query my client used automatically upon C-c:

mysql> KILL QUERY 183;
Query OK, 0 rows affected (0.02 sec)

Et voilà:

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 185
   User: guest
   Host: OPUS.MIT.EDU:37940
     db: youtomb
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
1 row in set (0.00 sec)

Now, why would you or I care? After all, nobody in their right mind goes about hitting control-backslash or employing equally messy means to kill their MySQL clients. And control-C behaves just as you’d hope — so long as you are using the mysql command-line client.

Where the story isn’t so good is on a typical other client program. The KILL behavior on control-C is a feature of the mysql program, not of the MySQL C API. (If you think about it, it involves installing a signal handler — not something a well-behaved library will just do.) And because it’s not a feature of the MySQL C API, it’s probably not a feature of your favorite language’s MySQL bindings, which wrap that API. In particular, I know it’s not a feature of MySQLdb, the leading Python bindings.

So suppose you write a Python script to do some MySQL queries… and you have a big honking table in your database, and you write an inefficient query… and the query planner resorts to copying most of the table to a temporary table… and after a couple of hours you kill the Python script with control-C or kill or some other means because it’s taking forever. The query will keep running. And the next day maybe it’s copied enough that it fills up your disk, and the database has an outage.

I wish that were a hypothetical. Fortunately, the MySQL server will then remove the temporary table and the disk will have space again. If you’re lucky, the server will even come back up.

Lesson: when you want to kill a MySQL query, make sure it dies. Use SHOW PROCESSLIST to check and KILL QUERY to kill.