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

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

Solution 2:

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

I will accept your answer as valid @mseifert since you gave me the original idea.

Thanks a lot.

Let us hope this is useful in the future to solve the 2006 MySQL error for other developers.