Kill or stop MySQL queries after quitting PHP script

I am running a statistics server at work that sometimes gets quite slow because of 开发者_开发问答the amount of queries run on it.

Our marketing team uses it as its main stats tool. Some people in the team sometimes just quit the script before it ended (by closing their browser or tab). Meanwhile, SQL queries continue to be executed.

How can I kill or stop the associated queries of a PHP script when someone closes or leaves this script?

We of course do not want to kill these queries by hand (which we already do) but automatically as soon as the person quits the script.

Most of our statistics scripts are based on precalculations done every night for the heaviest of them, and these ones are not problematic for us. For the others, we have not yet implemented precalculations and we wanted to be able to kill orphan queries before doing it.

You can check if the connection from the browser to your server is still open, using the Connection Handling mechanisms, and react appropriately.

Note however that any SQL queries that have started so far will run to completion - if they are complex, this can take a while, and there's no in-band way to terminate them: you'd need to watch which thread runs what and harvest the abandoned threads periodically.

You sometimes need to be able to kill a query that goes on too long. If you want to do that in your OS, you can do this-- it is fairly safe as MySQL runs all these in child processes. In Unix, this is ps and kill.

You can also use MysqlAdmin to do it, and it should be basically the same across OSes. You get a list of the processes that are running:

$ mysqladmin processlist # password if necessary
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 2  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Then look up mysqladmin kill to remove them.

EDIT

Okay, if it's about detecting the browser closed, I see a few approaches:

1. Check out PHP's connection handling. It looks like you can register a handle that is called if the user closes the browser.
2. If the handler doesn't get called, maybe you can call an interstitial page that says simply "Running query..." and then the rendering of that page can sit in a loop and wait for the query to complete or the connection to end. If the query completes, redirect to a "results" page, and if the connection goes down, manually kill the query (see above).
3. If those approaches prove fruitless, there is another option. The browser offers a Javascript "on unload" event that gets fired in the browser closest. A quick Ajax post from that could off the long-running query.

(Assignment: discuss the merits and disadvantages of all approaches.)

None of these strike me as beautiful, but with some work they can be made to work. To be honest, when we had this problem we just kept a processlist up and killed what looked like errant queries.

unfortunately there isn't much you can do if they are just hitting the close button on their browser. Listed already are some built in functions that will check the session. Maybe try caching the most often used queries. Google "caching php mysql".

If this doesn't work you could build it with AJAX and every time a query is finished - the server will send back a message keeping the user updated on the progress of their request. This will likely reduce the amount of people just giving up on their session.

The best way (I think) is to close connection immediately after using. Also wait_timeout session variable can be set (for example decreased to 20 seconds); in this case connection will be closed automatically.

With some colleagues of mine, we came up with the following idea:

• once MySQL connection is established, get connection_id() in SQL - CONNECTION_ID() doc on MySQL
• store it in some variable
• once the user quits the page (may it be by quitting it or loading another page), issue a js event that triggers a KILL query based on the previous variable

We will have this tested in the next few hours, I will update here as soon as we are sure it works.