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

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.