如何解决一般错误:2006 MySQL 服务器已消失 [英] How to solve General error: 2006 MySQL server has gone away

问题描述

我正在执行将数百条记录插入 MySQL 数据库的操作.

在准确插入 176 条记录后，我收到该错误.

[PDOException] SQLSTATE[HY000]:一般错误:2006 MySQL 服务器已经消失

关于如何解决它的任何想法?

该过程使用 PHP.

谢谢.

解决方案

我敢说问题出在 wait_timeout 上.在我的共享主机上设置为 30 秒，在我的本地主机上设置为 28800.

我发现我可以为会话更改它，因此您可以发出查询:SET session wait_timeout=28800

UPDATE OP 确定他还需要更改变量 interactive_timeout.每个人可能需要也可能不需要.

下面的代码显示了更改前后的设置，以验证是否已更改.

因此，在查询开始时设置 wait_timeout=28800(和 interactive_timeout = 28800)，看看它是否完成.

记得插入你自己的数据库凭证来代替DB_SERVER、DB_USER、DB_PASS、DB_NAME

更新此外，如果这确实有效，您希望通过将 wait_timeout 设置得更高来清楚自己在做什么.设置为 28800 是 8 小时，很多.

以下来自本站.它建议将 wait_timeout 设置为 300 - 我将尝试并报告结果(几周后).

<块引用>

wait_timeout 变量表示 MySQL 将在杀死空闲连接之前等待.默认的wait_timeout变量是 28800 秒，也就是 8 小时.太多了.

我在不同的论坛/博客中读到过将 wait_timeout 设置得太低(e.g. 30, 60, 90) 会导致 MySQL has away 错误消息.所以您必须决定自己的配置.

query("SHOW VARIABLES LIKE '%timeout%'", TRUE);echo "
";var_dump($results);echo "</pre>";$results = $db->query("SET session wait_timeout=28800", FALSE);//更新 - 这也是需要的$results = $db->query("SET session interactive_timeout=28800", FALSE);$results = $db->query("SHOW VARIABLES LIKE '%timeout%'", TRUE);echo "
";var_dump($results);echo "</pre>";类数据库{公共 $mysqli;公共函数 __construct() {$this->mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);如果(mysqli_connect_errno()){出口();}}公共函数 __destruct() {$this->disconnect();未设置($this->mysqli)；}公共函数断开(){$this->mysqli->close();}函数查询($q，$resultset){/* 创建一个准备好的语句 */如果 (!($st​​mt = $this->mysqli->prepare($q))) {echo("Sql 错误:" . $q . 'Sql 错误 #: ' . $this->mysqli->errno .' - ' . $this->mysqli->error);返回假；}/* 执行查询 */$stmt->execute();如果 ($stmt->errno) {echo("Sql 错误:" . $q . ' Sql 错误 #: ' . $stmt-> errno . ' - ' . $stmt->error);返回假；}如果($结果集){$result = $stmt->get_result();for ($set = array(); $row = $result->fetch_assoc();) {$set[] = $row;}$stmt->close();返回 $set;}}}
I'm doing an operation that inserts hundreds of records into a MySQL database.

After inserting exactly 176 records I get that error.

[PDOException] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Any ideas of how could I solve it?

The process is with PHP.

Thanks.
解决方案
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. 

I found that I can change it for the session, so you can issue the query: SET session wait_timeout=28800

UPDATE The OP determined that he also needed to change the variable interactive_timeout as well. This may or may not be needed for everyone.

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.

Remember to insert your own db credentials in place of DB_SERVER, DB_USER, DB_PASS, DB_NAME

UPDATE Also, 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.

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 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.


<?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;
        }
    }
}


                        
这篇关于如何解决一般错误:2006 MySQL 服务器已消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！