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

Answer 1

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

Click to copy

<?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);
}

Answer 2

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.