How to Solve General Error: 2006 MySQL Server Has Gone Away

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

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

MySQL error 2006: mysql server has gone away

It may be easier to check if the connection exists and re-establish it if needed.

See PHP:mysqli_ping for info on that.

MySQL server has gone away Error No: 2006

SOLUTION I FOUND:

Mysqli is controlled by your host, not by opencart itself.

Could have been a temporary issue as hosting companies do restart servers at various times.

This could also be a timeout issue when the server never gets the result from a MySQLI call because the process has stalled.

The latter can happen because the automatic currency update when in the admin backend,

Disable the automatic currency update at System > Settings > Local > Auto Update Currency and then try again.

NOTE: Refresh your dashboard and the errors resolved. Hope this helps!

PDO general error: 2006 mysql server has gone away

$db->exec('SET session wait_timeout=60');

Rare reasons for MySQL server has gone away error

Two common possibilities come to mind:

1) Out Of Memory error. Check syslog for evidence of it.

2) Bug or some other crash in mysqld thread. Check your MySQL error log.

The "server has gone away" almost always means a back end thread crash. And that should leave something obvious in the logs.

Getting MYSQL Error: Error Code: 2006 - MySQL server has gone away

Here you can read more about this error and various ways to avoid/solve it

From the docs:

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection

ERROR 2006 (HY000): MySQL server has gone away

max_allowed_packet=64M

Adding this line into my.cnf file solves my problem.

This is useful when the columns have large values, which cause the issues, you can find the explanation here.

On Windows this file is located at: "C:\ProgramData\MySQL\MySQL Server
5.6"

On Linux (Ubuntu): /etc/mysql

---

Related Topics