Doctrine2 connection timeout in daemon

Question

I have a long running daemon (Symfony2 Command) that gets work off a work queue in Redis, and performs those jobs and writes to the database using the orm.

I noticed that when that there is a tendency for the worker to die because the connection to MySQL timed out when worker is idling waiting for work.

Specifically, I see this in the log: MySQL Server has gone away.

Is there anyway I can have doctrine automatically reconnect? Or is there some way I can manually catch the exception and reconnect the doctrine orm?

Thanks

Answer 1

This with this wrapper it worked for me:

https://github.com/doctrine/dbal/issues/1454

Answer 2

I'm using this in my symfony2 beanstalkd daemon Command worker:

$em = $this->getContainer()->get('doctrine')->getManager();
if ($em->getConnection()->ping() === false) {
    $em->getConnection()->close();
    $em->getConnection()->connect();
}

Answer 3

I had the same problem with a PHP Gearman worker and Doctrine 2.

The cleanest solution that I came up with is: just close and reopen the connection at each job:

<?php
public function doWork($job){
   /* @var $em \Doctrine\ORM\EntityManager */
   $em = Zend_Registry::getInstance()->entitymanager;
   $em->getConnection()->close();
   $em->getConnection()->connect();
}

Update

The solution above doesn't cope with transaction status. That means the Doctrine\DBAL\Connection::close() method doesn't reset the $_transactionNestingLevel value, so if you don't commit a transaction, that will lead to Doctrine not being in sync on the translation status with the underlying DBMS. This could lead to Doctrine silently ignoring begin/commit/rollback statements and eventually to data not being committed to the DBMS.

In other words: be sure to commit/rollback transactions if you use this method.

Answer 4

In your daemon you can add method to restart the connection possibly before every query. I was facing similar problmes using gaerman worker:

I keep me connection data in zend registry so it looks like this:

private function resetDoctrineConnection() {
    $doctrineManager = Doctrine_Manager::getInstance();
    $doctrineManager->reset();
    $dsn = Zend_Registry::get('dsn');
    $manager = Doctrine_Manager::getInstance();
    $manager->setAttribute(Doctrine_Core::ATTR_AUTO_ACCESSOR_OVERRIDE, true);
    Doctrine_Manager::connection($dsn, 'doctrine');
}

If it is damenon you need perhaps call it statically.

Answer 5

It appears that whenever there is any error/exception encountered by the EntityManager in Doctrine, the connection is closed and the EntityManager is dead.

Since generally everything is wrapped in a transaction and that transaction is executed when $entityManager->flush() is called, you can try and catch the exception and attempt to re-excute or give up.

You may wish to examine the exact nature of the exception with more specific catch on the type, whether PDOException or something else.

For a MySQL has Gone Away exception, you can try to reconnect by resetting the EntityManager.

$managerRegistry = $this->getContainer()->get('doctrine');
$em = $managerRegistry->getEntityManager();
$managerRegistry->resetEntityManager();

This should make the $em usable again. Note that you would have to re-persist everything again, since this $em is new.