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!

Genius idea. I rewrote this to suit my mysqli needs. Thank you! I'm not well versed in PDO, but when building a mysqli version, I needed to add __get and __set to enable grabbing variables from the actual db variable/class.

My implementation: https://gist.github.com/duckboy81/47db8e4bb60b6f1e2556

Also, you must be careful with var_dump, etc when using these classes as it will dump your database username/password information.

when somebody works with namespaces, the method connect() should be modified a little bit:

        $this->pdo = new PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options); 

should be:

        $this->pdo = new \PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options); 

so "new PDO" becomes "new \PDO"

when somebody works with namespaces, the method connect() should be modified a little bit:

$this->pdo = new PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options);
$this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

should be:

$this->pdo = new \PDO($this->dsn, $this->username, $this->password, (array) $this->driver_options);
$this->pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

so PDO becomes \PDO

You have an error here. For some dumb reason, PDO_MySQL will throw an ERROR, not an exception, when you try to query on a connection that is dead, so this line here: $this->connection()->query("SHOW STATUS;")->execute(); will have 2 errors:

1. Error: PDO::query(): MySQL Server has gone away
2. Fatal Error: call to a method "execute" on a non-object (because query() returns false on errors)

I discovered this when writing connection recovery functionality for my own db abstraction layer: jurchiks/dbhandler@bae2400

Nice idea, but this seems to interfere with PDO's lastInsertId() method, in that it always returns '0'.
Is checking for a connection perhaps triggering an auto commit?

@wallacio: the class is not transaction aware, in case of a disconnect in the middle of a transaction, you should write your own class which handles this scenario. This one is mainly useful for single statement / non transactional database access. IMHO, as soon as you call ->start() (or equivalent of sql 'start commit'), the reconnect option should be disabled, and you should start handling your exceptions in a specific way to what you are doing.

i use it for my little opensource project. thank you !

https://github.com/andreas83/SocialNetwork

这是一个愚蠢的做法 PHP Warning: PDO::query(): MySQL server has gone away 抛出的是 warning 而不是 exception

@jurchiks
@schaobaojie
see $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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.