PDO: MySQL server has gone away

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.

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

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

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
    */
}

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.

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.