MySQL“等待超时"长时间查询不被尊重? [英] 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/>
";

$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/>
";
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)和 杀死.

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“等待超时"长时间查询不被尊重?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！