MySQL “wait_timeout” not being honored for long qu

Question

I'm trying to test the wait_timeout MySQL setting which seems to be ignored.

PHP script:

<?php
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

$sql = mysqli_connect('localhost','root','root','mysql');
$query = "SHOW VARIABLES WHERE Variable_name='wait_timeout';";
$result = $sql->query($query) or die($query.'<br />'.$sql->error);
$row = $result->fetch_object();
echo "wait_timeout = " . $row->Value . "<br/>\n";

$time_start = microtime_float();
$query = "SELECT SLEEP(2) FROM mysql.user;";
$sql->query($query) or die($query.'<br />'.$sql->error);
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "Query completed in $time seconds<br/>\n";
echo "You got the page";

Script output:

wait_timeout = 2
Query completed in 8.0005459785461 seconds
You got the page

My configuration

mariadb-server-5.3.5
php5.3.6

What do I need to do in order to force MySQL to time out queries after a certain amount of time?

Answer 1

Both wait_timeout and interactive_timeout is the time of inactivity before the connection is dropped. So, the connection must be idle (not running a query) before it will be dropped. MySQL SLEEP() does not count, since you're running a query.

You'll have to manually kill long running queries (there's no setting to have MySQL do it for you). You can script this. Use SHOW PROCESSLIST (or external tools like Innotop) and KILL.