KILL [HARD | SOFT] [CONNECTION | QUERY [ID] ] [thread_id | USER user_name | query_id]
The options HARD | SOFT
and USER username
were introduced in MariaDB 5.3.2
KILL QUERY ID query_id
, which permits killing a query by query id rather than thread id, was introduced in MariaDB 10.0.5.
Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST
statement and kill a thread with the KILL thread_id
statement. KILL
allows the optional CONNECTION
or QUERY
modifier:
KILL CONNECTION
is the same as KILL
with no modifier: It terminates the connection associated with the given thread or query id. KILL QUERY
terminates the statement that the connection thread_id is currently executing, but leaves the connection itself intact. KILL QUERY ID
(introduced in MariaDB 10.0.5) terminates the query by query_id, leaving the connection intact. If a connection is terminated that has an active transaction, the transaction will be rolled back. If only a query is killed, the current transaction will stay active. See also idle_transaction_timeout.
If you have the PROCESS
privilege, you can see all threads. If you have the SUPER
privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.
Killing queries that repair or create indexes on MyISAM and Aria tables may result in corrupted tables. Use the SOFT
option to avoid this!
The HARD
option (default) kills a command as soon as possible. If you use SOFT
, then critical operations that may leave a table in an inconsistent state will not be interrupted. Such operations include REPAIR
and INDEX
creation for MyISAM and Aria tables (REPAIR TABLE, OPTIMIZE TABLE).
KILL ... USER username
will kill all connections/queries for a given user. USER
can be specified one of the following ways:
CURRENT_USER
or CURRENT_USER()
If you specify a thread id and that thread does not exist, you get the following error:
ERROR 1094 (HY000): Unknown thread id: <thread_id>
If you specify a query id that doesn't exist, you get the following error:
ERROR 1957 (HY000): Unknown query id: <query_id>
However, if you specify a user name, no error is issued for non-connected (or even non-existing) users. To check if the connection/query has been killed, you can use the ROW_COUNT() function.
A client whose connection is killed receives the following error:
ERROR 1317 (70100): Query execution was interrupted
To obtain a list of existing sessions, use the SHOW PROCESSLIST
statement or query the Information Schema PROCESSLIST
table.
Note: You cannot use KILL
with the Embedded MySQL Server library because the embedded server merely runs inside the threads of the host application. It does not create any connection threads of its own.
Note: You can also use mysqladmin kill thread_id [,thread_id...]
to kill connections. To get a list of running queries, use mysqladmin processlist
. See mysqladmin.
Percona Toolkit contains a program, pt-kill that can be used to automatically kill connections that match certain criteria. For example, it can be used to terminate idle connections, or connections that have been busy for more than 60 seconds.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/kill/