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

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.