php 如何解决一般错误：2006 MySQL 服务器已消失
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33250453/
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
How to solve General error: 2006 MySQL server has gone away
提问by
I'm doing an operation that inserts hundreds of records into a MySQL database.
我正在执行将数百条记录插入 MySQL 数据库的操作。
After inserting exactly 176 records I get that error.
准确插入 176 条记录后，我收到该错误。
[PDOException] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
[PDOException] SQLSTATE[HY000]：一般错误：2006 MySQL 服务器已经消失
Any ideas of how could I solve it?
我该如何解决它的任何想法？
The process is with PHP.
这个过程是用PHP。
Thanks.
谢谢。
采纳答案by mseifert
I would venture to say the problem is with wait_timeout. It is set to 30 seconds on my shared host and on my localhost is set for 28800.
我敢说问题出在wait_timeout. 它在我的共享主机上设置为 30 秒，在我的本地主机上设置为 28800。
I found that I can change it for the session, so you can issue the query: SET session wait_timeout=28800
我发现我可以为会话更改它，因此您可以发出查询：SET session wait_timeout=28800
UPDATEThe OP determined that he also needed to change the variable interactive_timeoutas well. This may or may not be needed for everyone.
更新OP 确定他还需要更改变量interactive_timeout。每个人都可能需要，也可能不需要。
The code below shows the setting before and after the change to verify that it has been changed.
下面的代码显示了更改前后的设置，以验证它是否已更改。
So, set wait_timeout=28800 (and interactive_timeout = 28800) at the beginning of your query and see if it completes.
因此，在查询开始时设置wait_timeout=28800（和interactive_timeout = 28800）并查看它是否完成。
Remember to insert your own db credentials in place of DB_SERVER, DB_USER, DB_PASS, DB_NAME
请记住插入您自己的数据库凭据代替 DB_SERVER, DB_USER, DB_PASS, DB_NAME
UPDATEAlso, if this does work, you want to be clear on what you are doing by setting wait_timeout higher. Setting it to 28800 is 8 hours and is a lot.
更新此外，如果这确实有效，您希望通过将 wait_timeout 设置得更高来清楚自己在做什么。将其设置为 28800 是 8 小时，而且很多。
The following is from this site. It recommends setting wait_timeout to 300 - which I will try and report back with my results (after a few weeks).
以下内容来自本站。它建议将 wait_timeout 设置为 300 - 我将尝试并报告我的结果（几周后）。
wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection. The default wait_timeout variable is 28800 seconds, which is 8 hours. That's a lot.
I've read in different forums/blogs that putting wait_timeout too low (e.g. 30, 60, 90) can result in MySQL has gone away error messages. So you'll have to decide for your configuration.
wait_timeout 变量表示 MySQL 在终止空闲连接之前将等待的时间量。默认的 wait_timeout 变量是 28800 秒，也就是 8 小时。好多啊。
我在不同的论坛/博客中读到过将 wait_timeout 设置得太低（例如 30、60、90）会导致 MySQL 已消失错误消息。所以你必须决定你的配置。
<?php
$db = new db();
$results = $db->query("SHOW VARIABLES LIKE '%timeout%'", TRUE);
echo "<pre>";
var_dump($results);
echo "</pre>";
$results = $db->query("SET session wait_timeout=28800", FALSE);
// UPDATE - this is also needed
$results = $db->query("SET session interactive_timeout=28800", FALSE);
$results = $db->query("SHOW VARIABLES LIKE '%timeout%'", TRUE);
echo "<pre>";
var_dump($results);
echo "</pre>";
class db {
public $mysqli;
public function __construct() {
$this->mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if (mysqli_connect_errno()) {
exit();
}
}
public function __destruct() {
$this->disconnect();
unset($this->mysqli);
}
public function disconnect() {
$this->mysqli->close();
}
function query($q, $resultset) {
/* create a prepared statement */
if (!($stmt = $this->mysqli->prepare($q))) {
echo("Sql Error: " . $q . ' Sql error #: ' . $this->mysqli->errno . ' - ' . $this->mysqli->error);
return false;
}
/* execute query */
$stmt->execute();
if ($stmt->errno) {
echo("Sql Error: " . $q . ' Sql error #: ' . $stmt->errno . ' - ' . $stmt->error);
return false;
}
if ($resultset) {
$result = $stmt->get_result();
for ($set = array(); $row = $result->fetch_assoc();) {
$set[] = $row;
}
$stmt->close();
return $set;
}
}
}
回答by mseifert
Thanks @mseifert.
谢谢@mseifert。
Your idea worked by doing the same with two variables.
你的想法是通过对两个变量做同样的事情来实现的。
interactive_timeout& wait_timeout
交互超时和等待超时
I copied the config from a local database:
我从本地数据库复制了配置：
SHOW VARIABLES LIKE '%timeout%'
Local db:
本地数据库：
Remote db:
远程数据库：
I did this inside the connect and disconnect and worked:
我在连接和断开连接内部做了这个并工作：
mysql_query("SET SESSION interactive_timeout = 28800;");
$result = mysql_query("SHOW VARIABLES LIKE 'interactive_timeout';");
$row = mysql_fetch_array($result);
$interactive_timeout = $row["Value"];
echo("interactive_timeout" . " = " . $interactive_timeout . "\n");
mysql_query("SET SESSION wait_timeout = 28800;");
$result = mysql_query("SHOW VARIABLES LIKE 'wait_timeout';");
$row = mysql_fetch_array($result);
$wait_timeout = $row["Value"];
echo("wait_timeout" . " = " . $wait_timeout . "\n");
Surprisingly it worked with GoDaddy.
令人惊讶的是，它与 GoDaddy 一起工作。
I will accept your answer as valid @mseifert since you gave me the original idea.
我将接受您的回答作为有效的@mseifert，因为您给了我最初的想法。
Thanks a lot.
非常感谢。
Let us hope this is useful in the future to solve the 2006 MySQL error for other developers.
让我们希望这对将来为其他开发人员解决 2006 MySQL 错误有用。
回答by Amin
Assume your codes is:
假设您的代码是：
// your codes
$pdo = db::connection()->getPdo();
$stmt = $pdo->prepare($sql);
$result = $stmt->execute($params);
So add below codes before your sql query:
因此，在您的 sql 查询之前添加以下代码：
$pdo = db::connection()->getPdo();
// Increase interactive_timeout
$prepend_sql = "SET SESSION interactive_timeout = 28800;";
$stmt = $pdo->prepare($prepend_sql);
$stmt->execute($params);
// Increase wait_timeout
$prepend_sql = "SET SESSION wait_timeout = 28800;";
$stmt = $pdo->prepare($prepend_sql);
$stmt->execute($params);
// your codes
/* $pdo = db::connection()->getPdo(); */
$stmt = $pdo->prepare($sql);
$result = $stmt->execute($params);
