php - How to solve General error: 2006 MySQL server has gone away

Question

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.

Answer 1

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