MySQL "wait_timeout" not honored for long query?
I am trying to check a wait_timeout
MySQL parameter that 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 config
mariadb-server-5.3.5
php5.3.6
What do I need to do to get MySQL to time out queries after a certain amount of time?
source to share
Both wait_timeout
and interactive_timeout
are the time of inactivity before the connection is dropped. Thus, the connection must be idle (not triggering a request) until it is deleted. MySQL is SLEEP()
not counted as you are executing a query.
You will have to manually kill long queries (there is no setup for MySQL to do this for you). You can script this. Use SHOW PROCESSLIST
(or external tools like Innotop ) and KILL
.
source to share