Kill MySQL Query on User Abort

KILL MySQL queries using PHP if user close the browser or navigate from one page to other page

The following query also returns the current connection identifier

SELECT CONNECTION_ID();

After receiving this connection identifier we can execute our query on this identifier.

Kill MySQL query on user abort

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

Kill MySQL query on user abort

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

How can I stop a running MySQL query?

mysql> show processlist;
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| Id | User | Host      | db  | Command | Time | State               | Info                         | Progress |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| 14 | usr1 | localhost | db1 | Query   |    0 | starting            | show processlist             |    0.000 |
| 16 | usr1 | localhost | db1 | Query   |   94 | Creating sort index | SELECT  `tbl1`.* FROM `tbl1` |    0.000 |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
2 rows in set (0.000 sec)

mysql> kill 16;
Query OK, 0 rows affected (0.004 sec)

mysql> show processlist;
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| Id | User | Host      | db  | Command | Time | State    | Info             | Progress |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| 14 | usr1 | localhost | db1 | Query   |    0 | starting | show processlist |    0.000 |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)

How to find MySQL process list and to kill those processes?

Here is the solution:

1. Login to DB;
2. Run a command show full processlist;to get the process id with status and query itself which causes the database hanging;
3. Select the process id and run a command KILL <pid>; to kill that process.

Sometimes it is not enough to kill each process manually. So, for that we've to go with some trick:

1. Login to MySQL;
2. Run a query Select concat('KILL ',id,';') from information_schema.processlist where user='user'; to print all processes with KILL command;
3. Copy the query result, paste and remove a pipe | sign, copy and paste all again into the query console. HIT ENTER. BooM it's done.

What is the purpose of the `KILL QUERY` command?

Some useful pointers:

How can I stop a running MySQL query?

MySQL Kill query

How to kill all processes for a Specific user

MySQL Manaul - Kill

You need to have some clarity here KILL will kill the connection, but KILL QUERY will only kill that query and will leave that connection intact.

Really, if you are using KILL QUERY on another users command, this can only really be noticable if their query is running for longer than your Kill Query command, so if it's a slow query, because [most] queries will execute and close within a fraction of a second, so by the time you've KILLed the other users query, their query may well have completed and closed anyway.

I suspect this is what you are seeing, that you're trying to kill a query that closes and is finished before the kill command can execute.

Killing queries seems (from literature) only useful for slow and long running queries, and why this is so should be obvious from the above.

---

Edit:
(I had this ready to post on my original answer and then took it out as being irrelevant, but seems to be more relevant now due to comments!)

From the MySQL Manual:

Note

You cannot use KILL with the Embedded MySQL Server library because the embedded server merely runs inside the threads of the host application. It does not create any connection threads of its own.

So to answer your question in the comment, I think that you may be running MySQL embedded so your kill calls will not function.

---

Related Topics