Handling PDO Lost MySQL Connection Error

Published: 22 April, 2013

I ran into a a problem using PDO for a long running PHP script the other day. I kept getting a "MySQL server went away" exception that would crash my script.

Well I looked up this issue online and found that the PDO connection was timing out and causing statement execution to throw that error: StackOverflow

One proposed solution was to catch that exception and attempt to reconnect to the database. But now my problem is that I have abstracted away the database implementation details and am working off an abstract data repository in my script.

So my long running script looks like this:

    $pdo = new PDO(...connection details...);
    $repository = new ItemRepository($pdo);

    while (! $terminationRequested) {
        $item = findNextItem();
        $repository->save($item);

        // ...delay for some time...
    }
    

And the problem is $repository->save($item) can throw an exception if the MySQL database server "goes away" (in other words times out). One solution is to renew the PDO database connection every loop so that is never times out:

    while (! $terminationRequested) {
        $pdo = new PDO(...connection details...);
        $repository = new ItemRepository($pdo);

        $item = findNextItem();
        $repository->save($item);

        // ...delay for some time...
    }
    

And that works, but it doesn't feel right - why does my calling code need to be changed in order to accommodate a leaky abstraction? A better solution is to handle the reconnect at the data access layer, inside the Repository class itself. Unfortunately that is not all that simple.

For one, PDO has no option for reconnecting to the database if a connection is lost. It also does not save the connection parameters in the object itself, so you cannot just extend the class and add reconnecting to it. You also can't extend the PDOStatement object PDO returns because it is hard coded into the PDO class. It seems like the best thing you can do is put try/catch blocks everywhere you get a PDOStatement:

    class ConnectionManager
    {
        private $dsn;
        private $username;
        private $password;
        private $options;
        private $pdo;

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

        public function getConnection() {
            if ($this->pdo === null) {
                $this->reconnect();
            }
            return $this->pdo;
        }

        public function reconnect() {
            $this->pdo = new PDO($this->dsn, $this->username, $this->password,
                $this->options);
        }
    }
    
    class ItemRepository
    {
        private $connectionManager;

        public function __construct(ConnectionManager $connectionManager) {
            $this->connectionManager = $connectionManager;
        }

        public function save($item) {
            try {
                $this->doSave($item);
            }
            catch (PDOException $e) {
                $this->reconnectingPdo->reconnect();
                $this->doSave($item);
            }
        }

        private function doSave($item) {
            $pdo = $this->connectionManager->getConnection();
            $stmt = $pdo->prepare(
                'INSERT INTO items (id, name) VALUES (:id, :name)'
            );
            $stmt->bindValue(':id', $item->getId());
            $stmt->bindValue(':name', $item->getName());
            $stmt->execute();
        }
    }
     

Comments