Client times out, while MySQL query remains running? We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more resources) until we had an outage.

Questions


Is there a standard way to deal with this sort of issue in MySQL?
Is there a fundamental cause that we need to avoid?
You need to look at what default values are in place for timeouts:

mysql&gt; show variables like '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 60    |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 60    |
+----------------------------+-------+
10 rows in set (0.00 sec)


Usually, I watch for several timeout variables. This is very imperative if you use MySQL remotely from MySQL Workbench, mysql client, or PHP app on an app server contacting MySQL on a DB Server.

Here is what the MySQL Documentation says one these settings:


wait_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout. 
interactive_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout. 
net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout. 
net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.


Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include:


Mass UPDATEs
Mass DELETEs
ENABLE KEYS on a Large MyISAM


To deal with queries that keep running after you lose touch with it, you have to run KILL against the process ID of the long running query. Even with the KILL command, you will have to wait for any query that is in the middle of disk-intensive steps or have internal mutexes in progress.