3

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

crickeys
  • 3,075
  • 3
  • 26
  • 27
  • 2
    [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – esqew Jul 23 '14 at 04:06
  • To add to the above comment, newer client libraries (like PDO and *mysqli*) probably handle the exit process better. I would at the very least, retry your attempts with *mysqli* and see if it makes a difference. – Phil Jul 23 '14 at 04:16
  • 2
    I'm aware. This is old code. I re-ran a test using mysqli instead and it has the same behavior, so I don't think it's related to using mysql_query instead of mysqli_query – crickeys Jul 23 '14 at 04:16
  • "I know I'm not supposed to use that" – scrowler Jul 23 '14 at 04:17
  • Ah well, at least you tried. Looking around, there doesn't seem to be any way to kill long running query processes if the client quits. All the advice I see relates to opening up a new session and killing the processes manually. Not really what you're after, sorry – Phil Jul 23 '14 at 04:21

3 Answers3

4

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); ?>
kakoma
  • 1,179
  • 13
  • 17
  • But doesn't php close any connections to the mysql database when the script ends? If so, does mysql not cancel a query if the connection to it is closed? – crickeys Jul 23 '14 at 06:19
  • No, PHP doesn't close the connections because they aren't in its hands anymore. A connection can only be closed by PHP when it's handed back to it i.e. when results are sent back by MySQL. Between the time of sending the query and receiving results, MySQL is boss. mysqli_close($connection) is run after results are retrieved; before that, you need to manage the connection at the MySQL level. – kakoma Jul 23 '14 at 11:24
  • 1
    Actually, yes, PHP, or the OS, would "close" the connection if it isn't running any more, because a thing that isn't running can't have an open connection... but that doesn't matter, because MySQL will not know or care what PHP has done until the next time it tries to *write something* to the closed connection. When that happens, the connection gets fully reset and MySQL's client thread disappears from the processlist. Though slightly imprecise, this answer does explain the high level nature of the problem correctly. – Michael - sqlbot Jul 23 '14 at 12:00
  • Note that with the `mysql` command line client, if you control-C to kill a slow query, the client actually disconnects, reconnects, and kills its own *former* connection ID (which saved when it originally connected) in order to solve issues like this. – Michael - sqlbot Jul 23 '14 at 12:02
  • Very interesting. Might there be a way to have mysql ping the connection before it is done with the long query which would force it to check the connection sooner? – crickeys Jul 23 '14 at 14:42
  • Someone from tokudb is also doing some more investigation on this and believes this might be a known issue. I'll post more here as soon as I know. – crickeys Jul 24 '14 at 03:20
0

Well you can use a destructor to call

mysql_close(); function. I hope I understood your question...

Padmanathan J
  • 4,614
  • 5
  • 37
  • 75
Ramy Tamer
  • 618
  • 1
  • 7
  • 16
  • 1
    Even if I put this in a register_shutdown event and it calls mysql_close on the original database connection, it still runs on mysql. – crickeys Jul 23 '14 at 04:18
0

You can use KILL.

  • KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread_id.
  • KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.

You should KILL QUERY in the shutdown event, and then do a mysqli_close().

You might get some valuable information from this question about timeouts: Client times out, while MySQL query remains running?

Community
  • 1
  • 1
sampathsris
  • 21,564
  • 12
  • 71
  • 98
  • I tried that in a register_shutdown and the database connection appears to already be gone, because it gives: "MySQL server has gone away". Not sure what that means, if that signals an issue on mysql or php. – crickeys Jul 23 '14 at 04:45
  • Then I think you have to do the `KILL` with a separate process. Hope [this](http://dba.stackexchange.com/a/2637) helps you. – sampathsris Jul 23 '14 at 04:51