Killing my softly with QUERY

The MySQL KILL command as the name suggests kills queries that are running.

After identifying the Id using the SHOW PROCESSLIST command, the User of the connection/thread or a database user with SUPER privileges can execute KILL [id]; to remove the connection/thread.

However, there is an ability to kill just the query that is being executed rather the entire connection. The default when not specified is to kill the connection, however you can optional specify the CONNECTION or QUERY keywords.

For example, below is an interactive test.

Thread 1:

mysql> select sleep(10);

Thread 2:

mysql> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------+
| Id | User | Host      | db   | Command | Time | State     | Info             |
+----+------+-----------+------+---------+------+-----------+------------------+
| 23 | root | localhost | NULL | Query   |    2 | executing | select sleep(10) |
| 24 | root | localhost | NULL | Query   |    0 | NULL      | show processlist |
+----+------+-----------+------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql> kill query 23;

Thread 1:
Notice, the query exits, in less then 10 seconds, but the connection is still valid.

+-----------+
| sleep(10) |
+-----------+
|         1 |
+-----------+
1 row in set (7.27 sec)

mysql> select sleep(10);

Thread 2:

mysql> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------+
| Id | User | Host      | db   | Command | Time | State     | Info             |
+----+------+-----------+------+---------+------+-----------+------------------+
| 23 | root | localhost | NULL | Query   |    4 | executing | select sleep(10) |
| 24 | root | localhost | NULL | Query   |    0 | NULL      | show processlist |
+----+------+-----------+------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql> kill 23;
Query OK, 0 rows affected (0.00 sec)

Thread 1:
Query is killed and connection is lost. An attempt to run the command again within the interactive client causes the system to re-get a connection.

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select sleep(10);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    25
Current database: *** NONE ***

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

Comments

  1. Mark Callaghan says

    Until recently, killing DML or DDL with the binlog enabled could log a binlog event with an error code set even though the statement completed. This is something to watch out for depending on the version that you use. When the event is logged with an error slaves will halt replication on it. And if you skip the event on the slave, then the slave is out of sync with the master.

    We ended up adding an ‘IF IDLE’ clause to the KILL command as a workaround until deploying a fix.

    http://bugs.mysql.com/bug.php?id=37145
    http://bugs.mysql.com/bug.php?id=22725
    http://bugs.mysql.com/bug.php?id=27571

  2. says

    Not just with kill query, even InnoDB dead lock queries I noticed them logged to binlog all the time, and then we notice the following error from slave as ..

    090713 15:15:37 [ERROR] Slave: Query caused different errors on master and slave. Error on master: ‘Deadlock found when trying to get lock; try restarting transaction’ (1213), Error on slave: ‘no error’