MySQL的"Gone Away"永久PHP连接出错 [英] MySQL "Gone Away" Error with Persistent PHP Connection

问题描述

我在WAMP堆栈上本地托管一个网站.我最近通过在PDO构造函数选项参数中添加array(PDO::ATTR_PERSISTENT => true)来将PHP连接更改为持久连接.结果，我注意到响应时间出现了实质性的下降(万岁！).

I'm hosting a website locally on a WAMP stack. I recently switched the PHP connection to be persistent by adding array(PDO::ATTR_PERSISTENT => true) to the PDO constructor options argument. I've noticed a material drop in the response time as a result (hooray!).

当机器唤醒时，不利的一面似乎是消失的错误.在更改连接样式之前，从未发生过这种情况.

The downside seems to be a gone away error when the machine wakes up. This never happened before changing the connection style.

是否可能关闭了缓存的连接，但是继续返回?是否可以在catch块内通过PHP重置PDO连接或重置连接池?

Is it possible that the cached connection is closed, but continues to be returned? Is it possible to reset a PDO connection or reset the connection pool via PHP inside a catch block?

推荐答案

我已经讨论了几天，基于网络上类似问题的普遍性，这似乎是PDO的缺陷，无法有效管理持久连接.

I've kicked this around for a few days and based on the prevalence of similar issues on the web, this appears to be a deficiency of PDO preventing efficient managing of persistent connections.

回答明显的问题:

• PHP 5.4.22
• php.ini中的驱动程序设置已打开持久连接
• 会话限制不受限制(设置为-1)
• 池限制不受限制(设置为-1)

我可以通过执行以下操作来重新创建问题:

I can recreate the issue by doing the following:

在MySQL数据库上发布以下语句.

Issue the following statements on the MySQL database.

set @@GLOBAL.interactive_timeout := 10;
set @@GLOBAL.wait_timeout := 10;

针对服务器发出一些请求以生成一些缓存的连接.与通过以下方式进行非持久连接相比，您可以看到线程数增加:

Issue a few requests against the server to generate some cached connections. You can see the thread count increase compared to doing this with non-persistent connections via:

echo $conn->getAttribute(PDO::ATTR_SERVER_INFO);

至少等待10秒钟，然后开始发出更多请求.您应该开始收到离开"消息.

Wait at least 10 seconds and start issuing more requests. You should start receiving 'gone away' messages.

问题是SQL关闭了连接，随后对PDO构造函数的调用返回了这些关闭的连接，而没有重新连接它们.

The issue is SQL closes the connections and subsequent calls to the PDO constructor return these closed connections without reconnecting them.

这是PDO不足的地方.无法强制打开连接，也无法检测状态.

This is where PDO is deficient. There is no way to force a connection open and no good way to even detect state.

我目前正在解决这个问题的方式(当然有点hack)是发出这些MySQL语句

The way I'm currently getting around this (admittedly a bit of a hack) is issuing these MySQL statements

set @@GLOBAL.interactive_timeout := 86400;

set @@GLOBAL.wait_timeout := 86400;

这些变量设置为28800秒(8小时)默认情况下.请注意，您将需要重新启动Apache来清除缓存的连接，否则您将不会注意到差异，直到池中的所有连接都已循环(我不知道这种情况的发生方式/时间).我选择了24小时的86400，我每天都在这台机器上，所以这应该可以满足基本需求.

These variables are set to 28800sec (8 hours) by default. Note that you'll want to restart Apache to clear out cached connections or you wont notice a difference until all connections in the pool have been cycled (I have no idea how / when that happens). I chose 86400 which is 24 hours and I'm on this machine daily so this should cover the basic need.

此更新后，我让机器至少静置了12个小时，这是之前我开始收到消失消息"时坐了多长时间.看来问题已解决.

After this update I let my machine sit for at least 12 hours which was how long it sat previously when I started getting 'gone away message'. It looks like problem solved.

我一直在想，虽然我不能强制打开连接，但是有可能从池中删除错误的连接.我还没有尝试过，但是稍微更优雅的解决方案可能是检测消失"消息，然后将该对象设置为NULL，告诉PHP销毁资源.如果数据库逻辑进行了这样的尝试(如果发生更严重的错误，则必须有一个限制)，这可能有助于将这些错误降至最低.

I've been thinking that while I cant force open a connection, it may be possible to remove a bad connection from the pool. I haven't tried this, but a slightly more elegant solution might be to detect the 'gone away' message then set the object to NULL telling PHP to destroy the resource. If the database logic made a few attempts like this (there'd have to be a limit in case a more severe error occurred), it might help keep these errors to a minimum.

这篇关于MySQL的"Gone Away"永久PHP连接出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！