Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-275

Automatically attempt to reconnect a dropped persistent MySQL-connection (MySQL server has gone away)

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.6
    • Fix Version/s: 2.5
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      doctrine-dbal/2.1.6, driver PDOMySql

      Description

      For php-scripts that run for a long time (a.o. daemons) persistent connections will almost always be dropped by the MySQL-server after a set timeout (depending on wait_timeout). This will have Doctrine throw an exception and have the php-script terminate if not catched. It is not practical to catch the same Exception with a try-catch around every query.

      I have fixed this for DBAL 2.1.6 by adding a custom layer of Statement-, Connection- and Driver classes.
      Key functionalities:

      • The custom layer will transparently catch dropped connections and attempt a number of times (configurable) to reconnect.
      • The behaviour will not be triggered in a transaction (in that case it will revert to throwing an exception just like before).
      • The reconnect behaviour is not MySQL specific per se. It can be triggered by any Exception for any Driver-type if a Driver provides a method 'getReconnectExceptions'.
      • Minimal performance-impact. Only when an Exception is thrown will it be searched for a matching Exception to possibly trigger the behaviour. (also the reason a single stringmatch is used)

      Why this functionality?

      • It is often not possible to change settings of a database-server.
      • In a production environment the MySQL wait_timeout is often set to mere seconds
      • Doctrine's use of persistent connections will become a little more persistent
      • More reliable and robust php-scripts built on top of the DBAL

      See files in attached archive to get an idea of the code. Enabling the layer is currently done like this (Symfony2 yml):
      doctrine:
      dbal:
      wrapper_class: DoP\DoPBundle\Doctrine\DBAL\Connection
      driver_class: DoP\DoPBundle\Doctrine\DBAL\Driver\PDOMySql\Driver
      options:
      x_reconnect_attempts: 2

      Maybe I overlook something, but I only see pro's, no cons, to this improvement. I have created this issue to poll if you think this is a welcome feature and are interested to have me rework the code into DBAL itself? Reworking it into DBAL itself would certainly greatly reduce my code.

      If agreed I'll create a github pull request when finished with the code and take comments/improvements from there.

      (Also, I have glanced over http://www.doctrine-project.org/contribute.html but did not find any coding/testing-guidelines. Can you point me in the right direction?)

      Regards,

      Dieter

      1. doctrine-connection.php
        5 kB
        Julien Pauli
      2. reconnect_layer.tar.gz
        2 kB
        Dieter Peeters

        Activity

        Hide
        Dieter Peeters added a comment -

        @Benjamin:
        Sorry for the spam...

        I couldn't help myself doing a quick verification. The answer to your question lies in the file Connection.php. The method DoP\DoPBundle\Doctrine\DBAL\Connection::validateReconnectAttempt also checks that the transactionNestingLevel < 1, so the method will always return false when in a transaction. I.o.w. when in a transaction no attempt to reconnect will be made and the exception is simply rethrown, as per the default Doctrine behaviour.

        Show
        Dieter Peeters added a comment - @Benjamin: Sorry for the spam... I couldn't help myself doing a quick verification. The answer to your question lies in the file Connection.php. The method DoP\DoPBundle\Doctrine\DBAL\Connection::validateReconnectAttempt also checks that the transactionNestingLevel < 1, so the method will always return false when in a transaction. I.o.w. when in a transaction no attempt to reconnect will be made and the exception is simply rethrown, as per the default Doctrine behaviour.
        Hide
        Peter Kruithof added a comment -

        I could really use this for kong running cronjobs and daemonized scripts. Is this being worked on right now?

        Show
        Peter Kruithof added a comment - I could really use this for kong running cronjobs and daemonized scripts. Is this being worked on right now?
        Hide
        Dieter Peeters added a comment -

        Peter, you can use the above code, but it only works for statements outside of transactions. Also, the calls for savepoints in transactions need correction. Will do that when I find the time.

        Show
        Dieter Peeters added a comment - Peter, you can use the above code, but it only works for statements outside of transactions. Also, the calls for savepoints in transactions need correction. Will do that when I find the time.
        Hide
        Alexandre Lemaire added a comment -

        This was exactly what I needed...almost! Minor tweaks where it didn't work with current version of Doctrine on ZF2 (iterators req., and mismatched signatures). It also looked for 2006s at the head of the error message, when they can appear in the middle. Lastly, our darned modem was partly to blame, dropping on DNS lookups resulting in network errors. So I'll say big thanks Dieter, and I've posted a small set of tweaks to your classes at http://circlical.com/blog/2013/9/12/mysql-server-has-gone-away-atop-doctrine2-and-zend-framework-2. If this helps another soul, glad to give back; and, thanks again.

        Show
        Alexandre Lemaire added a comment - This was exactly what I needed...almost! Minor tweaks where it didn't work with current version of Doctrine on ZF2 (iterators req., and mismatched signatures). It also looked for 2006s at the head of the error message, when they can appear in the middle. Lastly, our darned modem was partly to blame, dropping on DNS lookups resulting in network errors. So I'll say big thanks Dieter, and I've posted a small set of tweaks to your classes at http://circlical.com/blog/2013/9/12/mysql-server-has-gone-away-atop-doctrine2-and-zend-framework-2 . If this helps another soul, glad to give back; and, thanks again.
        Hide
        Benjamin Eberlei added a comment -

        Merged in https://github.com/doctrine/dbal/pull/414 through new $connection->ping() method.

        Show
        Benjamin Eberlei added a comment - Merged in https://github.com/doctrine/dbal/pull/414 through new $connection->ping() method.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Dieter Peeters
          • Votes:
            7 Vote for this issue
            Watchers:
            11 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: