php PDO：MySQL 服务器已经消失
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2232150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO: MySQL server has gone away
提问by Nathan H
I have a script that does a lot of legwork nightly.
我有一个每晚都要做大量跑腿工作的脚本。
It uses a PDO prepared statement that executes in a loop.
它使用在循环中执行的 PDO 准备语句。
The first few are running fine, but then I get to a point where they all fail with the error: "MySQL server has gone away".
前几个运行良好，但后来我发现它们都失败并出现错误：“MySQL 服务器已消失”。
We run MySQL 5.0.77.
我们运行 MySQL 5.0.77。
PHP Version 5.2.12
PHP 版本 5.2.12
The rest of the site runs fine.
该网站的其余部分运行良好。
采纳答案by Pascal MARTIN
The B.5.2.9. MySQL server has gone awaysection of the MySQL manual has a list of possible causes for this error.
该B.5.2.9。MySQL 服务器已消失MySQL 手册的部分列出了此错误的可能原因。
Maybe you are in one of those situations ? -- Especially considering you are running a long operation, the point about wait_timeoutmight be interesting...
也许您正处于其中一种情况？-- 特别是考虑到你正在运行一个长时间的操作，关于这一点wait_timeout可能很有趣......
回答by rustyx
Most likely you sent a packet to the server that is longer than the maximum allowed packet.
很可能您向服务器发送了一个长度超过最大允许数据包的数据包。
When you try to insert a BLOBthat exceeds your server's maximum packet size, even on a local server you will see the following error message on clientside:
当您尝试插入BLOB超过服务器最大数据包大小的数据时，即使在本地服务器上，您也会在客户端看到以下错误消息：
MySQL server has gone away
MySQL服务器已经消失
And the following error message in the server log: (if error logging is enabled)
以及服务器日志中的以下错误消息：（如果启用了错误日志记录）
Error 1153 Got a packet bigger than 'max_allowed_packet' bytes
错误 1153 得到一个大于“max_allowed_packet”字节的数据包
To fix this, you need to decide what is the size of the largest BLOBthat you will ever insert, and set max_allowed_packetin my.iniaccordingly, for example:
为了解决这个问题，你需要决定什么是最大的尺寸BLOB，你将永远插入，并设置max_allowed_packet在my.ini相应的，例如：
[mysqld]
...
max_allowed_packet = 200M
...
回答by mysticmo
I had the same problem where the hosting server administration kills connection if there is a timeout.
我遇到了同样的问题，如果超时，托管服务器管理会终止连接。
Since I have used the query in major part I wrote a code which instead of using PDO class we can include the below class and replace the classname to "ConnectionManagerPDO". I just wrapped the PDO class.
由于我在主要部分使用了查询，因此我编写了一个代码，而不是使用 PDO 类，我们可以包含以下类并将类名替换为“ConnectionManagerPDO”。我刚刚包装了 PDO 类。
final class ConnectionManagerPDO
{
private $dsn;
private $username;
private $passwd;
private $options;
private $db;
private $shouldReconnect;
const RETRY_ATTEMPTS = 3;
public function __construct($dsn, $username, $passwd, $options = array())
{
$this->dsn = $dsn;
$this->username = $username;
$this->passwd = $passwd;
$this->options = $options;
$this->shouldReconnect = true;
try {
$this->connect();
} catch (PDOException $e) {
throw $e;
}
}
/**
* @param $method
* @param $args
* @return mixed
* @throws Exception
* @throws PDOException
*/
public function __call($method, $args)
{
$has_gone_away = false;
$retry_attempt = 0;
try_again:
try {
if (is_callable(array($this->db, $method))) {
return call_user_func_array(array($this->db, $method), $args);
} else {
trigger_error("Call to undefined method '{$method}'");
/*
* or
*
* throw new Exception("Call to undefined method.");
*
*/
}
} catch (\PDOException $e) {
$exception_message = $e->getMessage();
if (
($this->shouldReconnect)
&& strpos($exception_message, 'server has gone away') !== false
&& $retry_attempt <= self::RETRY_ATTEMPTS
) {
$has_gone_away = true;
} else {
/*
* What are you going to do with it... Throw it back.. FIRE IN THE HOLE
*/
throw $e;
}
}
if ($has_gone_away) {
$retry_attempt++;
$this->reconnect();
goto try_again;
}
}
/**
* Connects to DB
*/
private function connect()
{
$this->db = new PDO($this->dsn, $this->username, $this->passwd, $this->options);
/*
* I am manually setting to catch error as exception so that the connection lost can be handled.
*/
$this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
/**
* Reconnects to DB
*/
private function reconnect()
{
$this->db = null;
$this->connect();
}
}
Then use can start using the above class as you do in PDO.
然后可以像在 PDO 中一样开始使用上面的类。
try {
$db = new ConnectionManagerPDO("mysql:host=localhost;dbname=dummy_test", "root", "");
$query = $db->query("select * from test");
$query->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e){
/*
handle the exception throw in ConnectionManagerPDO
*/
}
回答by web.developer
Nathan H, below is php class for pdo reconnection + code usage sample. Screenshotis attached.
Nathan H，下面是 pdo 重新连接的 php 类 + 代码使用示例。 附上截图。
<?php
# set errors reporting level
error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING);
# set pdo connection
include('db.connection.pdo.php');
/* # this is "db.connection.pdo.php" content
define('DB_HOST', 'localhost');
define('DB_NAME', '');
define('DB_USER', '');
define('DB_PWD', '');
define('DB_PREFIX', '');
define('DB_SHOW_ERRORS', 1);
# connect to db
try {
$dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PWD);
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
# echo $e->getMessage()."<br />";
# exit;
exit("Site is temporary unavailable."); #
}
*/
$reconnection = new PDOReconnection($dbh);
$reconnection->getTimeout();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 10 seconds..'.PHP_EOL;
sleep(10);
$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 35 seconds..'.PHP_EOL;
sleep(35);
$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 55 seconds..'.PHP_EOL;
sleep(55);
$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 300 seconds..'.PHP_EOL;
sleep(300);
$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
# *************************************************************************************************
# Class for PDO reconnection
class PDOReconnection
{
private $dbh;
# constructor
public function __construct($dbh)
{
$this->dbh = $dbh;
}
# *************************************************************************************************
# get mysql variable "wait_timeout" value
public function getTimeout()
{
$timeout = $this->dbh->query('show variables like "wait_timeout"')->fetch(); # print_r($timeout);
echo '========================'.PHP_EOL.'mysql variable "wait_timeout": '.$timeout['Value'].' seconds.'.PHP_EOL.'========================'.PHP_EOL;
}
# *************************************************************************************************
# check mysql connection
public function checkConnection()
{
try {
$this->dbh->query('select 1')->fetchColumn();
echo 'old connection works..'.PHP_EOL.'========================'.PHP_EOL;
} catch (PDOException $Exception) {
# echo 'there is no connection.'.PHP_EOL;
$this->dbh = $this->reconnect();
echo 'connection was lost, reconnect..'.PHP_EOL.'========================'.PHP_EOL;
}
return $this->dbh;
}
# *************************************************************************************************
# reconnect to mysql
public function reconnect()
{
$dbh = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PWD);
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbh;
}
}
# /Class for PDO reconnection
# *************************************************************************************************
回答by prodigitalson
Try using PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true)on your pod instance(s). Dont know that it will help but with no log data its all i got.
尝试PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true)在您的 pod 实例上使用。不知道它会有所帮助，但没有日志数据，这就是我所得到的。
回答by MarkR
It's likely that either your connection has been killed (e.g. by wait_timeout or another thread issuing a KILL command), the server has crashed or you've violated the mysql protocol in some way.
可能是您的连接已被终止（例如，由 wait_timeout 或另一个发出 KILL 命令的线程）、服务器已崩溃或您以某种方式违反了 mysql 协议。
The latter is likely to be a bug in PDO, which is extremely likely if you're using server-side prepared statements or multi-results (hint: Don't)
后者很可能是 PDO 中的错误，如果您使用服务器端准备好的语句或多结果（提示：不要），则极有可能
A server crash will need to be investigated; look at the server logs.
需要调查服务器崩溃；查看服务器日志。
If you still don't know what's going on, use a network packet dumper (e.g. tcpdump) to dump out the contents of the connection.
如果您仍然不知道发生了什么，请使用网络数据包转储程序（例如 tcpdump）转储连接的内容。
You can also enable the general query log - but do it very carefully in production.
您还可以启用一般查询日志 - 但在生产中要非常小心。
回答by maccaroo
I got this same error this morning after changing my DB properties in Laravel. I'd commented out the old settings and pasted in new ones. The problem was that the new settings where missing the DB_CONNECTIONvariable:
今天早上在 Laravel 中更改了我的数据库属性后，我遇到了同样的错误。我已经注释掉了旧设置并粘贴了新设置。问题是缺少DB_CONNECTION变量的新设置：
DB_CONNECTION=pgsql
Obviously you need to add whatever connection type you are using: sqlite, mysql, ...
显然，您需要添加您正在使用的任何连接类型：sqlite、mysql、...
回答by flang3r
I had the exact same problem. I resolved this issue by doing unset on the PDO object instead of seting it to NULL.
我有同样的问题。我通过对 PDO 对象执行 unset 而不是将其设置为 NULL 解决了这个问题。
For example:
例如：
function connectdb($dsn,$username,$password,$driver_options) {
try {
$dbh = new PDO($dsn,$username,$password,$driver_options);
return $dbh;
}
catch(PDOException $e)
{
print "DB Error: ".$e->getMessage()."<br />";
die();
}
}
function closedb($dbh) {
unset($dbh); // use this line instead of $dbh = NULL;
}
Also, it is highly recommended to unset all your PDO objects. That includes variables that contain prepared statements.
此外，强烈建议取消所有 PDO 对象的设置。这包括包含准备好的语句的变量。
回答by hunter_wyg
$pdo = new PDO(
$dsn,
$config['username'],
$config['password'],
array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
try this. It may work
尝试这个。它可能工作
