MySQL不尊重my.cnf中的wait_timeout设置 [英] mysql not respecting wait_timeout setting in my.cnf

问题描述

我已经在my.cnf中设置了一个wait_timeout并重新启动了服务器，但是空闲连接上的时间仍然比我设置的默认100s长.任何想法为什么会发生这种情况?

I've set a wait_timeout in my.cnf and restarted the server, but the time on idle connections continues to grow larger than the default 100s I set. Any ideas why this is happening?

PS:我正在运行ubuntu 12.04和Mysql Server 5.5.结合使用Rails 3和mysql2 gem.

PS: I'm running ubuntu 12.04 and Mysql Server 5.5. Using Rails 3 with the mysql2 gem.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME DESC LIMIT 20;
+------+------------------+-------------------------------------+--------------------------+---------+------+-------+------+
| ID   | USER             | HOST                                | DB                       | COMMAND | TIME | STATE | INFO |
+------+------------------+-------------------------------------+--------------------------+---------+------+-------+------+

|  760 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:45852 | x | Sleep   | 4202 |       | NULL |
|  912 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:40929 | y  | Sleep   | 4194 |       | NULL |
|  976 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:40970 | y  | Sleep   | 4193 |       | NULL |
|  836 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:49903   | z | Sleep   | 4189 |       | NULL |
| 1743 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:44585  | z | Sleep   | 4157 |       | NULL |
| 1778 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:48714    | a  | Sleep   | 4154 |       | NULL |

推荐答案

原来问题出在mysql2 gem中. 它确实做到了这一点真棒 ...

Turns out the problem is in the mysql2 gem. It does this really awesome thing ...

本质上，它将连接所使用的默认wait_timeout值(大约25天(2147483s))替换为该连接使用的wait_timeout值(可能是session_waitout的会话版本)，基本上覆盖了我们在服务器上指定的任何设置.

Essentially it substitutes a default wait_timeout value of about 25 days (2147483s) of its own as the wait_timeout value (probably the session version of wait_timeout) that the connection uses, basically overriding whatever setting we specify on the server.

通过在config/database.yml中设置 wait_timeout:值，超时可以正常工作.但是，当连接被终止时，您会收到"mysql服务器已消失"错误.

By setting a wait_timeout: value in config/database.yml the timeout works as it should. When the connection gets killed, however, you get "mysql server has gone away" errors.

通过指定伴随的重新连接:是 database.yml中的选项，每次重新使用连接时，都会重置连接的计时器，但之后它会直接使用mysql的GLOBAL wait_timeout设置

By specifying an accompanying reconnect: true option in database.yml, everytime the connection is reused the connection's timer is reset, BUT after that it uses the GLOBAL wait_timeout setting from mysql directly

希望这对某人有帮助

这篇关于MySQL不尊重my.cnf中的wait_timeout设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！