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

It's a good approch, but if you use FOUND_ROWS() in combination with SQL_CALC_FOUND_ROWS it will always return the number of rows from the "SHOW STATUS" in line 17... is anyone where who has a smart solution for this issue?

You can add "@mixin PDO" to the class docblock that IDE can support auto-complete.

I hate to spoil your fun, but I see a number of problems with this.

Besides breaking lastInsertId() and transactionality, as pointed out above, you've got the overhead of an extra round-trip to the database for every query you execute - hopefully this is small for SHOW STATUS, but there are blocking wait-states involved for every SQL query executed, so this could substantially impact total time to process a request with many queries.

Secondly, a try/catch block around the SHOW STATUS query only guarantees error handling for that query - the connecting could just as well fail when you execute the user-supplied query next, and you have no error-handling for that. Presumably, what you were trying to achieve, is a connection that automatically reconnects and retries the query on connection failure? This might do that in some cases, but it might not.

What you probably want is the try/catch around the user-supplied query itself, with an automatic reconnect and retry - and you can get rid of the SHOW STATUS query entirely.

As @titpetric pointed out though, reconnecting/retrying only makes sense in the first place if you're not in a transaction - since, if you lose the connection, you also lose the transaction state.

This could be addressed, but it's a somewhat more complicated issue - you would need to buffer all executed queries within a transaction, clear the buffer on commit (or rollback) and, in the catch-handler, retry the all of the buffered queries, including restarting the transaction after a reconnect.

If you were hoping for something that can work in a long-lived CLI app, this needs a lot of work still...

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.