MySQL"wait_timeout"长时间查询不被尊重? [英] MySQL "wait_timeout" not being honored for long query?

问题描述

我正在尝试测试wait_timeout MySQL设置，该设置似乎被忽略了.

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

PHP脚本:

<?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";

脚本输出:

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

我的配置

mariadb-server-5.3.5
php5.3.6

要强制MySQL在一定时间后使查询超时，我需要做什么?

推荐答案

wait_timeout和interactive_timeout都是断开连接之前的不活动时间.因此，在连接被删除之前，它必须是空闲的(不运行查询). MySQL SLEEP()不计算在内，因为您正在运行查询.

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.

您将不得不手动终止长时间运行的查询(没有设置让MySQL为您完成).您可以编写脚本.使用SHOW PROCESSLIST(或 Innotop 这样的外部工具)和KILL.

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.

这篇关于MySQL"wait_timeout"长时间查询不被尊重?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！