PDO with PDOStatement reconnect on "mysql server gone" error

Question

When mysql's wait_timeout has been exceeded, I lose connection on my PHP CLI script. I can't change wait_timeout, so how would one build a try/catch statement that reconnects when I use PDOStatement to execute my queries?

Answer 1

Reconnecting to a DB after an error is actually a much more complicated problem than it would at first seem.

My first idea was to write a simple wrapper class for PDO that proxies methods onto an internal PDO object and can handle connection errors itself:

class BetterPDO extends PDO 
{
    private $realPDO  = NULL;
    private $dsn      = "";
    private $username = "";
    private $password = "";
    private $options  = [];

    public function __construct ($dsn, $username = "", $password = "", $options = [])
    {
        $this -> dsn = $dsn;
        $this -> username = $username;
        $this -> password = $password;
        $this -> options = $options;
    }

    private function getRealPDO ()
    {
        if (is_null ($this -> realPDO))
        {
            $this -> realPDO = new PDO ($this -> dsn, $this -> username, $this -> password, $this -> options);
        }
        return $this -> realPDO;
    }

    // We're only implementing exec for brevity but you have to do this for all public methods of PDO
    public function exec ($sql)
    {
        $retries = 0;
        while (true)
        {
            try 
            {
                return $this -> getRealPDO () -> exec ($sql);
            }
            catch (PDOException $ex) 
            {
                $this -> realPDO = NULL;
                if (++$retries > 5)
                {
                    // We've passed our retry limit
                    throw $ex;
                }
            }
        }
    }
}

As this class extends PDO, it can be used anywhere the generic PDO class can be used.

As you can see, this approach will give you a few retries before the exec() method gives up, allowing for reconnection after transient errors (this is just for demonstration and lacks some features a real implementation would need, like a backoff between retries, adequate error logging, etc). This approach would also require that you check the specifics of the PDO exception thrown on the grounds that you don't want things like MySQL syntax errors to cause the connection to be reset and a retry attempted. You only want it to happen on things such as "Server has gone away".

As you can also see, implementing all the proxied PDO methods would get to be a chore, though as you only have to do it once it's probably worth investing the effort to do so.

There is a much bigger problem though, which is pretty much a universal problem for any code that talks to a database, not just PDO. What happens if a connection is lost in the middle of a transaction? You don't want your script to reconnect and pick up where it left off in that case because all the work you've done up to the last commit will have been lost, and the chances are that it would make no logical sense to resume after reconnecting, you'd have to start over. Therefore you would probably just want the entire script to start over, and attempting a reconnect just wouldn't make any sense. This is probably why mySQLI supports reconnecting but PDO doesn't.

If your script only does reads, or nontransactional writes, then the above approach still has value, but as soon as you throw transactions into the mix you're actually a lot better off not attempting to reconnect.

Answer 2

The best approach is to wrap the PDO instance creation into a singleton (i.e. MyPDOFactory) that stores both the instance and the time of creation, that way, you can reuse it or recreate it after a TTL has been reached (2 or 3 seconds is more than enough for most applications). You'll just have to call MyPDOFactory::get() to get a valid PDO that you can use to prepare the PDOStatement, just make sure you execute it ASAP.