Kill MySQL query on user abort

Question

When running a long query from PHP, [how] can I kill the query if the user presses stop in their browser?

Take into consideration that I cannot call any other PHP functions because PHP is blocked while waiting for MySQL.

Also I cannot make any more requests to the server (via Ajax) because of session locking.

So one solution could be:

ignore user abort
run the long query in the back ground and have PHP check every 100ms if it has finished
get the pid from the query
if the user aborts, kill the pid
else return the result when finished

The 2 thing that I dont know how to do in that is:

run a non blocking (background) query
get the pid of a query

i don't know whether my answer will help you or not but if you will find any solution the plz post it here also — Peeyush, Sep 28 '11 at 12:53
You can run a non blocking query if you have `PHP 5.3.x` with `mysqlnd` using `mysqli_query` by passing `MYSQLI_ASYNC`. have a look at my answer. — danishgoel, Sep 28 '11 at 13:09

score 18 · Accepted Answer · answered Sep 28 '11 at 14:21

18

For those who are interested, here is what I used:

<?php
// Connection to query on
$query_con = mysqli_connect($host, $user, $password, $name, $port);

// Connection to kill on
$kill_con = mysqli_connect($host, $user, $password, $name, $port);

// Start the query
$query_con->query($slow_query, MYSQLI_ASYNC);

// Get the PID
$thread_id = $query_con->thread_id;

// Ignore user abort so we can kill the query
ignore_user_abort(true);

do  {
    // Poll MySQL
    $links = $errors = $reject = array($mysqli->mysqli);
    $poll = mysqli_poll($links, $errors, $reject, 0, 500000);

    // Check if the connection is aborted and the query was killed
    if (connection_aborted() && mysqli_kill($kill_con, $thread_id)) {
        die();
    }
} while (!$poll);

// Not aborted, so do stuff with the result
$result = $link->reap_async_query();
if (is_object($result)) {
    // Select
    while ($row = $result->fetch_object()) {
        var_dump($row);
    }
} else {
    // Insert/update/delete
    var_dump($result);
}

answered Sep 28 '11 at 14:21

Petah

45,477
28
157
213

so it will continuously poll mysql server on a time interval of 5 sec am i correct? – Peeyush Sep 29 '11 at 05:04
@Peeyush, it will poll every 1/2 second (500,000 microseconds) – Petah Sep 29 '11 at 21:11
@Petah, I am here because I have an issue that I think this could solve. If you happen to still be around almost 10 years later ha. In your code sample above there is an assigment of $mysqli->mysqli and I cannot see where $mysqli is first declared?... And also when you call $link->reap_async_query I cannot see the first declaration of $link? Would you mind explaining. Thank you. – Joeseppi Aug 05 '21 at 08:10
@Joeseppi this is what I'm using now https://gist.github.com/Petah/fd69978af055e86d64f9066dcbb6d2b7 although it based on a timeout instead of connection abort, you should be able to adapt it. – Petah Aug 05 '21 at 21:56

Rijk · Answer 2 · 2011-09-28T11:35:39.357

Once PHP notices the user has stopped the request (this usually will not happen until the script tries to output something to the user), your script will terminate. Before shutting down, PHP calls any shutdown functions you've activated. You can implement a shutdown function that kills your query, and register it with register_shutdown_function()

An other way you might be able to do this, is by running your script with ignore_user_abort() turned on, and checking if the user has aborted by calling connection_aborted() periodically. If the user has aborted, kill the query and gracefully exit your script.

More information on connection handling here.

The problem was not connection handling, it was the fact the PHP is blocked until MySQL returns. — Petah, Sep 28 '11 at 12:25

score 3 · Answer 3 · answered Sep 28 '11 at 13:04

3

If the time taken by query is because of the large dataset returned, then you can use mysql_unbuffered_query. And then in shutdown_function you can free the result and disconnect form mysql.

And if you are using PHP 5.3.x and have mysqlnd you can probably use MYSQLI_ASYNC in mysqli_query and then use mysqli_poll to get the result.

The first option will only help in case of time taken in retrieval of dataset. But if the mysql is actually taking a long time in parsing and creating the execution plan and loading tables to memory it wont help. In that case you need MYSQLI_ASYNC which is only available on PHP 5.3.x WITH mysqlnd.

Also if your main problem is session locking have a look at this post on PHP's documentation http://php.net/manual/en/ref.session.php#64525
You might find some helpful advice there.

answered Sep 28 '11 at 13:04

danishgoel

3,650
1
18
30

Nice answer so far. I have got a non blocking query running with `MYSQLI_ASYNC`, the only problem is now, how do I get the PID to kill? – Petah Sep 28 '11 at 13:30
And in regards to session locking, I want to keep it enabled for other reasons. – Petah Sep 28 '11 at 13:30
1

Ok, figured it out `mysqli_kill($mysqli, $mysqli->thread_id)` – Petah Sep 28 '11 at 14:13
@Petah glad you figured it out. was away for a while so could not see your comments. So if you feel my answer helped you in coming up with a solution, maybe you can mark it as the accepted answer ? Its totally up to you though. – danishgoel Sep 28 '11 at 14:37

score 0 · Answer 4 · answered Jan 21 '19 at 02:53

This is an example of doing it with PDO.

$id = $connection->executeQuery("SELECT CONNECTION_ID()")->fetchColumn();
ignore_user_abort(true);
while($row = $result->fetch()) {
  /* Processing ... */
  if (connection_aborted()) {
    $this->connection->executeQuery("KILL CONNECTION $id");
    break;
  }
}

Note: Keep an eye out for multiple connections, you may kill the wrong one.

Peeyush · Answer 5 · 2011-09-28T12:50:35.480

i think you can give a try to mysql KILL

http://dev.mysql.com/doc/refman/5.0/en/kill.html

if you are able to call something over user abort then you can use KILL to kill you mysql connection or query by fetching it's process id.

And as you said in your comment that PHP is blocked until mysql returns

so you can take help from javascript in this way:

1.detect the user abort using JS 2.call a function on user abort 3.in that function you can kill you previous query but here you need the process id of the previous query so you can get that by a trick that you can select the process id when you run you first long query and pass it to JS function and use it in killing you query.

Hope it will help you.

The problem is, I cannot call a function (or run another query) because PHP is blocked by the MySQL query. (even with Ajax, because of session locking) — Petah, Sep 28 '11 at 12:54

score 0 · Answer 6 · answered Sep 28 '11 at 13:27

PHP, in most cases, won't know that a connection was closed (since the user aborted) until it tries to send something over the network and then receives a SIGPIPE. The only thing you can do is try working with timeouts aborting to long queries or killing them from another process.

score -2 · Answer 7 · edited Apr 01 '22 at 11:55

-2

You can unset session variable using:

<?php session_unset(); ?>

edited Apr 01 '22 at 11:55

Wai Ha Lee

8,598
83
57
92

answered Apr 01 '22 at 09:08

Allen Kuro

1
2

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 01 '22 at 09:15

Kill MySQL query on user abort

7 Answers7

Linked