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.

Thanks for this, got me started in the right direction. However as was mentioned MySQL server has gone way is a PHP Fatal Error so it breaks the try catch. To get around this issue I set four more properties:

protected string $connectStartTime;
protected int    $mySqlWaitTime = 30; // Time in seconds. Put your servers mySQL wait_timeout value here
protected int    $maxRetries    = 3;
protected int    $retryAttempts = 0;

Set $this->connectStartTime = time(); in the connect() method, and do the following in the __call() method.

public function __call( $name, $arguments ) {
    $waitTime = time() - $this->connectStartTime;
    
    if (
	$waitTime >= $this->mySqlWaitTime &&
	$this->retryAttempts < $this->maxRetries
    ) {
	    // If there has been a delay then reconnect to the db.
	    $this->retryAttempts++;
	    $this->connect();
    } else {
	    // Connection hasn't timed out, so reset the connection time
	    $this->connectStartTime = time();
    }
    
    return call_user_func_array( [ $this->pdo, $name ], $arguments );
}

I know this doesn't address some of the other users comments about restoring transactions, but in my use case this wasn't an issue. I just needed PDO to attempt to reconnect if there was a delay from a third party API.