If you're coding with Zend Framework 2, and have adopted Doctrine 2 for all the right reasons, you've probably done a good deal of brain bending to rid yourself of table gateway thinking, to set up your Entities with proper annotations, and have done some slick DI to inject the right dependencies in the right places.  This was pretty much the path I'd taken, thoroughly embedding the lot into a framework I've been developing for a few months.

Integral to that framework, is a whole reporting aspect that blends entities from many of my ZF Modules, crunches up a bunch of good stats for my partners, co-workers and clients, and drops them into an AWS Redshift BI warehouse for later grinding.  I'd contend that I had done everything by the book, using Memcache and Gearman to parallelize data gathering with these PHP jobs, to one Monday roll into work and see a stalled status flag: a big old 2006 in one of the Gearman task logs.

I immediately guessed that the connections being used by Doctrine were dying with no auto-reconnect, and Googled very quickly thinking "Someone out there has to have solved this."  Makes sense, it's related to wait_timeout; but how to fix?

Everything I read at that point pointed to using a dummy select in conjunction with some try catch blocks to assert the connection's heartbeat.  Coded like such:

 

    /**
     * Assert/Revive a Doctrine repository connection
     * @param \Application\Mapper\AbstractDoctrineMapper $R Doctrine repository
     * @throws PDOException
     */
    protected function assertDatabaseRepositoryConnection(\Application\Mapper\AbstractDoctrineMapper &$R)
    {
        $EM = $R->getEntityManager();
        $DBC = $EM->getConnection();

        // connections die because of process length sometimes, have to kick them back into shape
        try {

            $DBC->query("SELECT 1");

            if (self::DEBUG)
                $this->setStatus(" ++ EM connection was alive, continuing");

        } catch (\Exception $e) {

            if (self::DEBUG)
                $this->setStatus(" ++ EM connection had died.  Reviving.");

            $newEM = $this->getServiceLocator()->create('doctrine.entitymanager.orm_default');

            if (self::DEBUG)
                $this->setStatus(" ++ Created New EM.");

            $R->setEntityManager($newEM);

        }
    }

I tested locally, writing a few unit tests to make that select fail to ensure that the connection would be recreated with a new SPL hash, and it was.  Seemed right, so the code began its march up our GIT repositories, right to our stat-grinding fleet.  Reset, start, wait... 

Approximately 2 days later, same old "MySQL server has gone away" error. Poking some more, an IRC chat on Freenode's #doctrine had someone identify that may be caused by Zend Framework's shared services.  I edited my \Application's getServiceConfiguration() and set shared to false for my database orm connection.

 public function getServiceConfig()
    {
        return array(
            'invokables' => array(
                ...
            ),
            'initializers' => array(
                ...
            ),
            'factories' => array(
                ...
            ),

            'shared' => array(
                'doctrine.entitymanager.orm_default' => false
            ),
        );
    }

You guessed it.  I run the Unit Tests, things seem fine (no difference in fact), so I push it up to our cloud and restart the process.  2 days later, same darned thing!  

It was probable at this point that the mass of queries I was running at such irregular intervals caused the Dummy SQL to validate, but the connections would die in the time that exists between the Dummy SQL and the ER commit.  That or gremlins.  Trying to find documentation for connection wrappers for Doctrine 2, I found an enhancement request on the Doctrine portal that was pretty darned close to what I needed.  Do I think this should be a standard option too?  OH YES!  In the interim though I adjusted the older files at that link so that they comply with the version of Doctrine 2 that I am using.  It's been in production usage for about a day now, and it's working well, so I wanted to give back & share.  Big thanks to Marco Pivetta from Freenode's #Doctrine for chipping in, and to Dieter Peeters for writing up that JIRA thread & code. Here's my take on it:

 

1. Adjust your Doctrine DB Config

 

'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                //'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
                'driverClass' => '\Application\Model\Doctrine\DBAL\Driver\PDOMySql\Driver',
                'wrapperClass' => '\Application\Model\Doctrine\DBAL\Connection',
                'params' => array(
                    'host' => 'host',
                    'port' => '3306',
                    'user' => 'user',
                    'password' => 'password',
                    'dbname' => 'dbname',
                    'driverOptions' => array(
'x_reconnect_attempts' => 10,
                    ),
                )
            )
        )
    ),

2. Install the Following Connection and Driver Wrappers

Click here to download the source code

Note: Updated on Sep 16, included more verbose error output, and a "stall retrying" condition for when our ISP's DNS would hit the proverbial fan.

3. Keep Calm, and Use Doctrine Normaly

No need for example, for the database connection assertion routines that are recommended in quite a few posts you'll find on this subject, that I pasted at the start of this article.  It was a nice feeling to peel that stuff out of the code. 

This does get rid of the problem, and given the nature of the sandbox where these problems surface, it's usually a few days in the toilet if you're digging blind.  I hope this helps!  One note, it only works for statements outside of transactions.  That was a moot point for me given the nature of the long tasks.