Reconnectable PDO

reconnecting_pdo.php

<?php
class ReconnectingPDO
{
    protected $dsn, $username, $password, $pdo, $driver_options;
    
    public function __construct($dsn, $username = "", $password = "", $driver_options = array())
    {
        $this->dsn = $dsn;
        $this->username = $username;
        $this->password = $password;
        $this->driver_options = $driver_options;
    }

    public function __call($name, array $arguments)
    {
        try {
            $this->connection()->query("SHOW STATUS;")->execute();
        } catch(\PDOException $e) {
            if($e->getCode() != 'HY000' || !stristr($e->getMessage(), 'server has gone away')) {
                throw $e;
            }

            $this->reconnect();
        }

        return call_user_func_array(array($this->connection(), $name), $arguments);
    }

    protected function connection()
    {
        return $this->pdo instanceof \PDO ? $this->pdo : $this->connect();
    }

    public function connect()
    {
        $this->pdo = new PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options); 
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $this->pdo;
    }

    public function reconnect()
    {
        $this->pdo = null;
        return $this->connect();
    }
}

Adding to what @mindplay-dk wrote, "buffering queries within a transaction" might not always work as well. Some queries might use data from previous queries, such as last_insert_id's, which will become different when the transaction is replayed. Besides that, another process could change the state of the database and make some of the buffered queries fail, resulting in missing data, duplicate keys etc. When you lose the transaction, you also lose all the read/write locks for that transaction.

Generally mysql errors such as "gone away" or "deadlock found" are a part of life and sometimes are difficult to avoid. Your code should be ready to replay entire transaction with all the logic that created sql statements. You can write a simple transaction repeater class, which will repeat an anonymous function up to several times in case of recoverable errors.

@sven100 just put "use PDO;" at the top of the file and you won't need the backslash.

In a long-running app, likely the most appropriate thing to do, is don't even attempt to "replay" the query - instead, just throw an exception immediately after reconnecting. Assuming you have an error-handler that can gracefully fail and handle the exception, the current request will fail, but the long-running app itself can continue running and handle the next request.

It's no ideal, but probably only happens if the DB server is down - this at least ensures the long-running app is up and handling requests again as soon as the DB server is back.