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 ]
        Hide
        Nils Adermann added a comment -

        Sounds like a rather useful addition to me, would be cool to see this as a default feature.

        Show
        Nils Adermann added a comment - Sounds like a rather useful addition to me, would be cool to see this as a default feature.
        Hide
        Julien Pauli added a comment -

        We used this at work, it's simple, it could need more reflection

        Show
        Julien Pauli added a comment - We used this at work, it's simple, it could need more reflection
        Julien Pauli made changes -
        Attachment doctrine-connection.php [ 11348 ]
        Hide
        Benjamin Eberlei added a comment -

        The problem with a generic solution here are the risks involved, we need to answer questions:

        1. did a transaction get aborted beccause of this
        2. is it safe to continue at this point with a second, new connection

        I am not sure we can guarantee the 100% working.

        Show
        Benjamin Eberlei added a comment - The problem with a generic solution here are the risks involved, we need to answer questions: 1. did a transaction get aborted beccause of this 2. is it safe to continue at this point with a second, new connection I am not sure we can guarantee the 100% working.
        Hide
        Dieter Peeters added a comment - - edited

        @Julien:
        I assume you are referring to the elaborated switch-statement? Well, since this is a DBAL I think data-integrity and performance are the more important things. In my experience when performance is important, you avoid reflection in PHP as much as possible. In the light of performance the switch-statement was a very deliberate substitution of a call to call_user_func_array. The number of arguments was finite and I am fairly sure all possible cases where covered.

        @Benjamin:
        It seems I forgot to mention this in my statement above. Transactions are indeed an issue I deliberately avoided in this early version. As far as I know, transactions in MySQL are connection-based so the transaction will be rolled back when the server disconnects. A solution would be to incorporate a cache of all statements in a transaction. I was waiting on feedback of the Doctrine-developers on incorporating this code into the DBAL itself, before extending it with more functionality... and I now notice that it got assigned

        I'll try to answer your two questions:
        1. If the connection is dropped when inside a transaction, the behaviour will revert to the normal Doctrine-behaviour, i.e. no reconnection-attempt will be made and the exception 'MySQL server has gone away' will be thrown.
        2. To be clear, I always try to write bug-free code, but I do not guarantee anything about this code. To answer your question: yes, it is safe to continue if you try-catch your transaction in your application-code. Upon the exception you reconnect to MySQL and repeat the lost transaction.

        And as a last note, this code is just to have a workable solution. I tend to agree with anyone who thinks that the correct place to fix this problem is in the MySQL-client itself.

        Dieter

        Show
        Dieter Peeters added a comment - - edited @Julien: I assume you are referring to the elaborated switch-statement? Well, since this is a DBAL I think data-integrity and performance are the more important things. In my experience when performance is important, you avoid reflection in PHP as much as possible. In the light of performance the switch-statement was a very deliberate substitution of a call to call_user_func_array. The number of arguments was finite and I am fairly sure all possible cases where covered. @Benjamin: It seems I forgot to mention this in my statement above. Transactions are indeed an issue I deliberately avoided in this early version. As far as I know, transactions in MySQL are connection-based so the transaction will be rolled back when the server disconnects. A solution would be to incorporate a cache of all statements in a transaction. I was waiting on feedback of the Doctrine-developers on incorporating this code into the DBAL itself, before extending it with more functionality... and I now notice that it got assigned I'll try to answer your two questions: 1. If the connection is dropped when inside a transaction, the behaviour will revert to the normal Doctrine-behaviour, i.e. no reconnection-attempt will be made and the exception 'MySQL server has gone away' will be thrown. 2. To be clear, I always try to write bug-free code, but I do not guarantee anything about this code. To answer your question: yes, it is safe to continue if you try-catch your transaction in your application-code. Upon the exception you reconnect to MySQL and repeat the lost transaction. And as a last note, this code is just to have a workable solution. I tend to agree with anyone who thinks that the correct place to fix this problem is in the MySQL-client itself. Dieter
        Hide
        Benjamin Eberlei added a comment -

        My idea would be to throw an exception on reconnect like it is done atm, when transactionNestingLevel > 0, and otherwise proceed with doing the reconnect. I am not sure i am missing something here though.

        Show
        Benjamin Eberlei added a comment - My idea would be to throw an exception on reconnect like it is done atm, when transactionNestingLevel > 0, and otherwise proceed with doing the reconnect. I am not sure i am missing something here though.
        Hide
        Dieter Peeters added a comment - - edited

        @Benjamin: I noticed after commenting that you're the assignee ... and corrected my comment a bit.

        Now, if you want I can extend the functionality to support transactions. But I prefer to do this directly into the DBAL, not as a layer on top. The resulting code should be a bit cleaner than this layer now.

        What do you think?

        *edit*
        A bit of crossposting here

        The way you suggest is the way this layer is implemented
        *edit*

        Dieter

        Show
        Dieter Peeters added a comment - - edited @Benjamin: I noticed after commenting that you're the assignee ... and corrected my comment a bit. Now, if you want I can extend the functionality to support transactions. But I prefer to do this directly into the DBAL, not as a layer on top. The resulting code should be a bit cleaner than this layer now. What do you think? * edit * A bit of crossposting here The way you suggest is the way this layer is implemented * edit * Dieter
        Hide
        Dieter Peeters added a comment -

        @Benjamin
        It's been a while since I wrote the code. Above answers are from memory. I will revise the code this evening and give you exact answers. Also, related to transactions, I think the way I handle the savepoints should be revised.

        Show
        Dieter Peeters added a comment - @Benjamin It's been a while since I wrote the code. Above answers are from memory. I will revise the code this evening and give you exact answers. Also, related to transactions, I think the way I handle the savepoints should be revised.
        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.
        Kosta Korenkov made changes -
        Status Open [ 1 ] Awaiting Feedback [ 10000 ]
        Benjamin Eberlei made changes -
        Status Awaiting Feedback [ 10000 ] Open [ 1 ]
        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.
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.5 [ 10523 ]
        Resolution Fixed [ 1 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-275, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated:
              Resolved: