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

        Dieter Peeters created issue -
        Dieter Peeters made changes -
        Field Original Value New Value
        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)

        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?)
        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?)
        Dieter Peeters made changes -
        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?)
        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
        Attachment reconnect_layer.tar.gz [ 11189 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13698 ] jira-feedback2 [ 17609 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17609 ] jira-feedback3 [ 19962 ]
        Julien Pauli made changes -
        Attachment doctrine-connection.php [ 11348 ]
        Kosta Korenkov made changes -
        Status Open [ 1 ] Awaiting Feedback [ 10000 ]
        Benjamin Eberlei made changes -
        Status Awaiting Feedback [ 10000 ] Open [ 1 ]
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.5 [ 10523 ]
        Resolution Fixed [ 1 ]

          People

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

            Dates

            • Created:
              Updated:
              Resolved: