Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to terminate a client connection to MySQL. You can use the SHOW PROCESSLIST statement to obtain a connection thread identifier for use in this statement. As of version 5 of MySQL, you can use CONNECTION or QUERY keywords to distinguish between terminating a connection or terminating just the current query associated with the given connection.

Some processes cannot be terminated immediately. Instead, this statement flags the process for termination. The system may not check the flag until the process is completed. This will occur with statements such as REPAIR TABLE. Besides, you shouldn’t attempt to terminate the execution of the REPAIR TABLE or the OPTIMIZE TABLE statements. That will corrupt a MyISAM table. The utility mysqladmin with the options processlist and KILL may be used from the command line to execute these related statements.

Here is an example of the SHOW PROCESSLIST and the KILL statements used together:

SHOW PROCESSLIST \G
...
     Id: 14397
   User: reader
   Host: localhost
     db: russell_dyer
Command: Query
   Time: 7
  State: Sending data
   Info: SELECT COUNT(*) AS hits
           FROM apache_log
           WHERE SUBDATE(NOW(), INT....

KILL QUERY 14397;

The results of the SHOW PROCESSLIST are truncated. Using the thread identifier 14397 from the results, the KILL statement is used with the QUERY keyword to terminate the SQL statement that’s running, without terminating the client connection. If the CONNECTION keyword or no keyword is given, the entire connection is terminated. In that case, if the client attempts to issue another SQL statement, it receives a 2006 error message stating that the MySQL server has gone away. Then it typically will try to reconnect to the server, establish a new thread, and run the requested query.