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

问题描述

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

I'm doing an operation that inserts hundreds of records into a MySQL database.

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

After inserting exactly 176 records I get that error.

[PDOException] SQLSTATE [HY000]:常规错误:2006 MySQL服务器已消失

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

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

Any ideas of how could I solve it?

该过程是使用PHP的.

The process is with PHP.

谢谢.

推荐答案

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

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.

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

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

更新:OP认为他还需要更改变量interactive_timeout.也许每个人都需要或不需要.

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.

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

So, set wait_timeout=28800 (and interactive_timeout = 28800) at the beginning of your query and see if it completes.

记住要插入自己的数据库凭据代替DB_SERVER, DB_USER, DB_PASS, DB_NAME

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

更新另外，如果这确实可行，您还可以通过将wait_timeout设置得更高些来清楚自己在做什么.将其设置为28800需要8个小时，而且很多.

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.

以下内容来自此网站.建议将wait_timeout设置为300-我将尝试将其结果报告(几周后).

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变量表示MySQL将要花费的时间 等待，然后杀死一个空闲的连接.默认的wait_timeout 变量为28800秒，即8小时.很多.

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.

我在不同的论坛/博客中读到过，它们将wait_timeout设置得太低 (例如30、60、90)可能会导致MySQL消失的错误消息.所以 您必须决定自己的配置.

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屋！