CertGrid CertGrid
Troubleshooting·MySQL

MySQL Runaway Queries and KILL

`KILL QUERY` stops the statement and leaves the connection alive; `KILL` drops the whole connection and rolls back its transaction. The victim sees `ERROR 1317` and the difference between the two matters more than it sounds.

Troubleshooting Guide 37 of 45 Intermediate

Written against the versions above. A killed statement rolls back, and rolling back a large transaction can take longer than the statement did. Killing is not always the fast option.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Start something expensive, find it, and stop it

    A self-join of a 400,000-row table on a column with ten distinct values - each row matches roughly forty thousand others, so this is billions of comparisons. A realistic accident: a missing join condition or a forgotten filter.

    The process list is searched by the statement's own text rather than by a guessed id, which is how you find the right connection when there are dozens. INFO holds the running statement, so LIKE on it is a reliable way to identify a specific query.

    KILL QUERY stops the statement. The victim sees ERROR 1317: Query execution was interrupted - an unmistakable message, distinct from a timeout or a syntax error, so a well-behaved application can log it as "cancelled" rather than "failed".

    The connection survives. KILL without QUERY would have closed it entirely and rolled back any open transaction - which is what you want for a leaked connection and not what you want for an application mid-request.

    bash Example session 
    sudo bash -c 'mysql appdb -e "SELECT COUNT(*) FROM events e1 JOIN events e2 ON e1.customer_id = e2.customer_id" >/tmp/runaway.out 2>&1 & sleep 3; ID=$(mysql -N -e "SELECT ID FROM information_schema.PROCESSLIST WHERE INFO LIKE \"%e1 JOIN events e2%\" LIMIT 1"); echo "runaway connection id: $ID"; mysql -e "KILL QUERY $ID"; sleep 1; cat /tmp/runaway.out; wait'runaway connection id: 97ERROR 1317 (70100) at line 1: Query execution was interrupted

    Expected resultThe connection id, then ERROR 1317 (70100): Query execution was interrupted.

    Success conditionYou can stop one statement without dropping the connection running it.

  2. Now use KILL without QUERY, and watch the difference

    The same setup, but the target is inside an open transaction that has already updated a row - and this time the statement is bare KILL .

    The victim sees ERROR 2013: Lost connection to MySQL server during query, not 1317. That is a different failure entirely: 1317 says *your query was cancelled and you are still connected*; 2013 says *the connection is gone*. An application distinguishing them will retry one and reconnect for the other.

    The count afterwards is 0 - the connection is no longer in the process list. And because the connection closed with a transaction open, that transaction was rolled back: nothing it had done survives.

    So the choice is about scope. KILL QUERY for a runaway statement on a connection you want to keep. KILL for a leaked or hostile connection, accepting that its open work is discarded.

    bash Example session 
    sudo bash -c 'mysql appdb -e "START TRANSACTION; UPDATE customers SET credit=credit+1 WHERE id=1; SELECT SLEEP(20);" >/tmp/k1.out 2>&1 & sleep 3; ID=$(mysql -N -e "SELECT ID FROM information_schema.PROCESSLIST WHERE INFO LIKE \"%SLEEP(20)%\" LIMIT 1"); echo "target id: $ID"; mysql -e "KILL $ID"; sleep 1; echo "--- victim saw ---"; cat /tmp/k1.out; echo "--- still connected? ---"; mysql -N -e "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE ID = $ID"; wait'target id: 12--- victim saw ---ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query--- still connected? ---0

    Expected resultERROR 2013 (HY000): Lost connection to MySQL server during query, and a process-list count of 0 for that id.

    Success conditionYou can choose between cancelling a statement and closing a connection.

  3. Confirm the row is intact

    The row the killed transaction had updated, read back afterwards.

    It holds a single consistent value. The killed transaction never committed, so InnoDB rolled its change back - the same mechanism the schema track demonstrated with an explicit ROLLBACK, applied here because the connection died rather than because anyone asked.

    This is the guarantee that makes killing a connection a safe operation on a transactional engine. On a non-transactional one it would leave the row half-updated.

    bash Example session 
    sudo mysql --table appdb -e "SELECT id, credit FROM customers WHERE id = 1"+----+--------+| id | credit |+----+--------+|  1 | 126.50 |+----+--------+

    Expected resultOne row with a single consistent credit value.

    Success conditionYou know a killed connection cannot leave a half-finished transaction.

Troubleshooting

Official sources

← All MySQL guides