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?
Best Answer
You need to look at what default values are in place for timeouts:
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:
Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include:
UPDATEs
DELETEs
ENABLE KEYS
on a Large MyISAMTo 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.