PDO: MySQL server has gone away

Question

I have a script that does a lot of legwork nightly.

It uses a PDO prepared statement that executes in a loop.

The first few are running fine, but then I get to a point where they all fail with the error: "MySQL server has gone away".

We run MySQL 5.0.77.

PHP Version 5.2.12

The rest of the site runs fine.

Answer 1

Most likely you sent a packet to the server that is longer than the maximum allowed packet.

When you try to insert a BLOB that exceeds your server's maximum packet size, even on a local server you will see the following error message on clientside:

MySQL server has gone away

And the following error message in the server log: (if error logging is enabled)

Error 1153 Got a packet bigger than 'max_allowed_packet' bytes

To fix this, you need to decide what is the size of the largest BLOB that you will ever insert, and set max_allowed_packet in my.ini accordingly, for example:

[mysqld]
...
max_allowed_packet = 200M
...

Answer 2

The B.5.2.9. MySQL server has gone away section of the MySQL manual has a list of possible causes for this error.

Maybe you are in one of those situations ? -- Especially considering you are running a long operation, the point about wait_timeout might be interesting...

Answer 3

I had the same problem where the hosting server administration kills connection if there is a timeout.

Since I have used the query in major part I wrote a code which instead of using PDO class we can include the below class and replace the classname to "ConnectionManagerPDO". I just wrapped the PDO class.

final class ConnectionManagerPDO
{

    private $dsn;
    private $username;
    private $passwd;
    private $options;
    private $db;
    private $shouldReconnect;

    const RETRY_ATTEMPTS = 3;

    public function __construct($dsn, $username, $passwd, $options = array())
    {
        $this->dsn = $dsn;
        $this->username = $username;
        $this->passwd = $passwd;
        $this->options = $options;
        $this->shouldReconnect = true;
        try {
            $this->connect();
        } catch (PDOException $e) {
            throw $e;
        }
    }

    /**
     * @param $method
     * @param $args
     * @return mixed
     * @throws Exception
     * @throws PDOException
     */
    public function __call($method, $args)
    {
        $has_gone_away = false;
        $retry_attempt = 0;
        try_again:
        try {

            if (is_callable(array($this->db, $method))) {

                return call_user_func_array(array($this->db, $method), $args);
            } else {

                trigger_error("Call to undefined method '{$method}'");
                /*
                 * or
                 *
                 * throw new Exception("Call to undefined method.");
                 *
                 */
            }
        } catch (\PDOException $e) {

            $exception_message = $e->getMessage();

            if (
                ($this->shouldReconnect)
                && strpos($exception_message, 'server has gone away') !== false
                && $retry_attempt <= self::RETRY_ATTEMPTS
            ) {
                $has_gone_away = true;
            } else {
                /*
                 * What are you going to do with it... Throw it back.. FIRE IN THE HOLE
                 */
                throw $e;
            }
        }

        if ($has_gone_away) {
            $retry_attempt++;
            $this->reconnect();
            goto try_again;
        }
    }


    /**
     * Connects to DB
     */
    private function connect()
    {
        $this->db = new PDO($this->dsn, $this->username, $this->passwd, $this->options);
        /*
         * I am manually setting to catch error as exception so that the connection lost can be handled.
         */
        $this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    }

    /**
     * Reconnects to DB
     */
    private function reconnect()
    {
        $this->db = null;
        $this->connect();
    }
}

Then use can start using the above class as you do in PDO.

try {
    $db = new ConnectionManagerPDO("mysql:host=localhost;dbname=dummy_test", "root", "");
    $query = $db->query("select * from test");
    $query->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e){
    /*
        handle the exception throw in ConnectionManagerPDO
    */
}

Answer 4

Try using PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true) on your pod instance(s). Dont know that it will help but with no log data its all i got.

Answer 5

It's likely that either your connection has been killed (e.g. by wait_timeout or another thread issuing a KILL command), the server has crashed or you've violated the mysql protocol in some way.

The latter is likely to be a bug in PDO, which is extremely likely if you're using server-side prepared statements or multi-results (hint: Don't)

A server crash will need to be investigated; look at the server logs.

If you still don't know what's going on, use a network packet dumper (e.g. tcpdump) to dump out the contents of the connection.

You can also enable the general query log - but do it very carefully in production.

Answer 6

Nathan H, below is php class for pdo reconnection + code usage sample. Screenshot is attached.

<?php

# set errors reporting level
error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING);

# set pdo connection
include('db.connection.pdo.php');

/* # this is "db.connection.pdo.php" content
define('DB_HOST', 'localhost');
define('DB_NAME', '');
define('DB_USER', '');
define('DB_PWD', '');
define('DB_PREFIX', '');
define('DB_SHOW_ERRORS', 1);

# connect to db
try {
    $dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PWD);
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    # echo $e->getMessage()."<br />";
    # exit;
    exit("Site is temporary unavailable."); #
}
*/

$reconnection = new PDOReconnection($dbh);

$reconnection->getTimeout();

echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 10 seconds..'.PHP_EOL;

sleep(10);

$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 35 seconds..'.PHP_EOL;

sleep(35);

$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;
echo 'sleep 55 seconds..'.PHP_EOL;

sleep(55);

$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;

echo 'sleep 300 seconds..'.PHP_EOL;
sleep(300);
$dbh = $reconnection->checkConnection();
echo $dbh->query('select 1')->fetchColumn();
echo PHP_EOL;

# *************************************************************************************************
# Class for PDO reconnection
class PDOReconnection
{
    private $dbh;

    # constructor
    public function __construct($dbh)
    {
        $this->dbh = $dbh;
    }

    # *************************************************************************************************

    # get mysql variable "wait_timeout" value
    public function getTimeout()
    {
        $timeout = $this->dbh->query('show variables like "wait_timeout"')->fetch(); # print_r($timeout);
        echo '========================'.PHP_EOL.'mysql variable "wait_timeout": '.$timeout['Value'].' seconds.'.PHP_EOL.'========================'.PHP_EOL;
    }

    # *************************************************************************************************

    # check mysql connection
    public function checkConnection()
    {
        try {
            $this->dbh->query('select 1')->fetchColumn();
            echo 'old connection works..'.PHP_EOL.'========================'.PHP_EOL;
        } catch (PDOException $Exception) {
            # echo 'there is no connection.'.PHP_EOL;
            $this->dbh = $this->reconnect();
            echo 'connection was lost, reconnect..'.PHP_EOL.'========================'.PHP_EOL;
        }

        return $this->dbh;
    }

    # *************************************************************************************************

    # reconnect to mysql
    public function reconnect()
    {
        $dbh = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PWD);
        $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE); 
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
        return $dbh;
    }
}
# /Class for PDO reconnection
# *************************************************************************************************

Answer 7

I got this same error this morning after changing my DB properties in Laravel. I'd commented out the old settings and pasted in new ones. The problem was that the new settings where missing the DB_CONNECTION variable:

DB_CONNECTION=pgsql

Obviously you need to add whatever connection type you are using: sqlite, mysql, ...

Answer 8

I had the exact same problem. I resolved this issue by doing unset on the PDO object instead of seting it to NULL.

For example:

function connectdb($dsn,$username,$password,$driver_options) {
    try {
        $dbh = new PDO($dsn,$username,$password,$driver_options);
        return $dbh;
    }
    catch(PDOException $e)
    {
        print "DB Error: ".$e->getMessage()."<br />";
        die();
    }
    
}

function closedb(&$dbh) {
    unset($dbh);             // use this line instead of $dbh = NULL;
}

Also, it is highly recommended to unset all your PDO objects. That includes variables that contain prepared statements.

Answer 9

$pdo = new PDO(
    $dsn,
    $config['username'],
    $config['password'],
    array(
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION
    )
);

try this. It may work