Description:
KILL / KILL CONNECTION terminates the client session but does not always stop the query that was started by that session, which may continue running in the background. However, per the documentation, the query should be terminated when the associated connection is killed.
KILL permits an optional CONNECTION or QUERY modifier:
KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given processlist_id, after terminating any statement the connection is executing.
Documentation URL: https://dev.mysql.com/doc/refman/8.4/en/kill.html
How to repeat:
1) Start a long-running query (Session 1)
mysql [localhost:8407] {msandbox} ((none)) > select sleep(600);
2) Identify the running query (Session 2)
mysql [localhost:8407] {root} ((none)) > show full processlist;
Example output:
| 8 | msandbox | localhost | NULL | Query | 28 | User sleep | select sleep(600)|
3) Kill the connection/query from Session 2
mysql [localhost:8407] {root} ((none)) > kill 8;
Result:
Query OK, 0 rows affected
4) Observe Session 1 behavior
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***
5) Verify whether the query is still running (Session 2)
mysql [localhost:8407] {root} ((none)) > show full processlist;
Observed:
The SLEEP(600) query is still running
It appears under a new connection/thread ID:
Example output:
| 10 | msandbox | localhost | NULL | Query | 10 | User sleep | select sleep(600) |
Actual Result
KILL <id> disconnects the client session, but the SELECT SLEEP(600) continues running and reappears under a new thread/connection ID.
B) MariaDB 11.8.3 – Works as Expected (Comparison)
1) Start the query (Session 1)
mysql [localhost:11803] {msandbox} ((none)) > select sleep(600);
2) Check processlist (Session 2)
mysql [localhost:11803] {root} ((none)) > show full processlist;
Example output:
| 3 | msandbox | localhost | NULL | Query | 6 | User sleep | select sleep(600) |
3) Kill the query
mysql [localhost:11803] {root} ((none)) > kill 3;
4) Confirm it is gone
mysql [localhost:11803] {root} ((none)) > show full processlist;
Observed:
Only the root session remains
The sleep(600) query is terminated
Result
MariaDB terminates the running query as expected when the connection is killed.
Suggested fix:
Expected Result
KILL <id> should terminate the executing query associated with that connection, so it should no longer appear in SHOW FULL PROCESSLIST.
