突然“MySQL服务器不见了"PHP 站点中的错误 [英] Sudden "MySQL server has gone away" error in PHP site

问题描述

今天我的一个网站开始展示

Today one of my websites started showing

Error Number: 2006
MySQL server has gone away

这是一个在 Apache 2.2.9 (Debian)、PHP 5.2.6-1+lenny3(使用 CodeIgniter 1.7.1 框架)和 MySQL 5.0.51a 下运行的低流量客户端站点.我显然对错误进行了重新搜索，但所有可能的解决方案都意味着正在进行的大型查询可能会超时并重置连接，或达到数据包限制.然而，事实并非如此，它是一个用最简单的查询处理的小型数据库.为了确保这一点，我编写了一些查询来返回一行，但仍然是同样的错误.

It's a low-traffic client site running under Apache 2.2.9 (Debian), PHP 5.2.6-1+lenny3 (using CodeIgniter 1.7.1 framework) and MySQL 5.0.51a. I obviously reasearched about the error but all the possible solutions imply that there are big queries going on that may time out and reset the connection, or hit the packet limits. However, this is not the case, it's a small database processed with the simplest queries. To be sure about this I made up a few queries to return one row, still the same error.

数据库凭据很好，我什至可以直接登录到 mysql，运行一些站点查询并立即获得正确的数据.同一台服务器上还有其他几个站点并连接到数据库，还有更大的站点，它们都没有问题.

Database credentials are fine, I can even login directly into mysql, run some of the site's queries and get the right data instantly. There are several other sites on the same server and connections to the database, much larger sites, and they all have no problems.

我试过了:

• 重启 MySQL
• 重启整个服务器
• 在日志中查找错误(Apache 和 MySQL，无)
• 检查数据库用户权限
• 在 PHP 中更改 mysql.connect_timeout 和 default_socket_timeout
• 在 MySQL 中更改 max_allowed_pa​​cket
• 阅读官方文档、论坛和所有内容在 SO 中说MySQL 服务器已经消失"

• Restarting MySQL
• Restarting the whole server
• Looking for errors in the logs (both Apache and MySQL, none)
• Checking db user permissions
• Changing mysql.connect_timeout and default_socket_timeout in PHP
• Changing max_allowed_packet in MySQL
• Reading the official docs, forum and everything in SO that says "MySQL server has gone away"

新功能:

• 在 PHP 中禁用持久连接
• 在 MySQL 中更改 wait_timeout 和 connect_timeout

更新:

这似乎与我的脚本的执行时间有关:它使用 Facebook PHP 客户端检索了一些信息，今天这个调用似乎随机失败，所以我要么没有来自 Facebook 的数据，要么出现 MySQL 错误.但令我惊讶的是，给定的解决方案似乎都没有处理超时.

It seems to be related to the execution time of my script: it retrieves some info using the Facebook PHP client and this call seems to be failing randomly today, so I either have no data from Facebook or the MySQL error. But to my surprise, none of the given solutions seems to deal with the timeout.

有什么想法吗?谢谢你的时间！

Any ideas? thank you for your time!

推荐答案

正如我在更新中所说的，我得出的结论是，当 Facebook 链接的时间超过与数据库的最长连接时间时，就会出现 MySQL 的问题.没有任何建议可以克服这个限制，所以我决定解决它并在每次我认为链接可能消失时重新连接.

As I said in my update, I concluded that the problem with MySQL arises when the link to Facebook takes longer than the maximum connection time with the DB. None of the suggestions could beat this limitation, so I decided to work around it and reconnect every time I presumed the link maybe gone.

所以每次调用 Facebook 后，我都会使用以下代码:

So after each call to Facebook, I used to following code:

$this->load->database();
$this->db->reconnect();

这是使用 CodeIgniter 时的特殊解决方案，AFAIK db->reconnect() 函数仅在 1.7.2 版后可用，因此我对其进行了更新以使其正常工作.

This is the particular solution when using CodeIgniter, and AFAIK the db->reconnect() function is only available since version 1.7.2 so I updated it in order to work.

感谢大家的回答！

这篇关于突然“MySQL服务器不见了"PHP 站点中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！