mysql_query() keeps running after php exits

Question

I've noticed that if I execute a long running mysql query with php using mysql_query() (I know I'm not supposed to use that) and then the php process gets killed then the query continues to run on the mysql server. This is not a persistent connection. The connection is made with:

$db = mysql_connect($host, $login, $pass, false);
$sql = 'SELECT COUNT(*) FROM `huge_table`';
$result = mysql_query($sql, $db);

For example, let's say I have a 1 billion row table and a php process does this for some reason:

SELECT COUNT(*) FROM `huge_table`

And then it times out (say because I'm running php-fpm with request_terminate_timeout=5), so it kills the process after 5 seconds to make sure it doesn't hog things.

Eventhough the process is killed, the query still runs on mysql even far after wait_timeout.

Is there anyway to make sure that if the php process exits for whatever reason it also kills any running queries that it made?

I'm using tokudb 5.5.38-tokudb-7.1.7-e which is mysql 5.5.38

Answer 1

crickeys, when a PHP script starts to execute and it gets to the part where it executes a MySQL query, that query is handed over to MySQL. The control of the query is no longer in PHP's hands....PHP at the point is only waiting for a response from MySQL then it can proceed. Killing the PHP script doesn't affect the MySQL query because well, the query is MySQL's business.

Put another way, PHP comes to the door, knocks, hands over the goods and waits for you to bring back a response so he can be on his way. Shooting him won't affect what's going on behind the door.

You could run something like this to retrieve the longest running processes and kill them:

<?php
    $con=mysqli_connect("example.com","peter","pass","my_db");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SHOW FULL PROCESSLIST");

while($row = mysqli_fetch_array($result)) {
if ($row["Time"] > $max_excution_time ) {
    $sql="KILL ".$row["Id"];
    mysql_query($sql);
    }   

}

mysqli_close($con); ?>