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

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

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

Answer 3

In my case, when I got this error on the client side, the server side was

(Got a packet bigger than 'max_allowed_packet' bytes)

So I increase the value of the max_allowed_packet, and so far, no more issues.

On Google Cloud Platform, I edit the DB and add a Database flag and set the value to max_allowed_packet=134217728

(which is 2^27 = 128M)

As you can only input numbers.

On regular instances, you can follow the doc here :

https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

Answer 4

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:

$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);

Answer 5

Another possible reason would be your client is trying to connect using SSL. while the MySQL/MariaDB server is not expecting that.

Answer 6

a solution is to check if the connection is active, if not re-establishing the connection, here it worked perfectly

<?php

require_once ('config.php');

class DB {

    private static $instance;
    
    private function __construct() {
        ;
    }
    
    public static function getInstance() {
        if (!isset(self::$instance)) {
            try {
                self::$instance = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
                self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
        try {
            $testConn = self::$instance->prepare('SELECT 1');
            $testConn->execute();
            $testConn = $testConn->fetchAll(PDO::FETCH_ASSOC)[0];
        } catch (Exception $ex) {
            try {
                self::$instance = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
                self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        return self::$instance;
    }

    public static function prepare($sql) {
        return self::getInstance()->prepare($sql);
    }
    
    public static function lastInsertId() {
        return self::getInstance()->lastInsertId();
    }

}