JIRA
  1. JIRA
  2. JRA-15731

Investigate issues with MySQL database connections involving autoReconnect=true

    Details

    • Type: Task Task
    • Status: Resolved (View Workflow)
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 3.13
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      JIRA 3.13, MySQL Database

      Description

      Since 3.13 we have seen an increase in cases with the following error message:

      The last packet sent successfully to the server was XXXXX seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

      In each case the autoReconnect parameter has been set to true, as per the documentation

      Note that the MySQL documentation discourage using the autoReconnect parameter

      The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.

      Need to investigate the possible cause of the problems and potential workarounds / fixes.

      Summary of Findings:

      • The MySQL "autoReconnect" parameter is useless and can be removed
      • The best (simplest) way to make JIRA able to survive MySQL connection timeouts is to set the validationQuery parameter.
        See Surviving Connection Closures for details.
      • The reason that JIRA used to survive these closures prior to v3.13, was that the DB pool we used was accidentally resetting closed connections.

        Issue Links

          Activity

          Hide
          Ian Daniel [Atlassian] added a comment -

          If the problem happens when using JBoss, this could be the fix: http://stackoverflow.com/questions/128527/is-there-any-way-to-have-the-jboss-connection-pool-reconnect-to-oracle-when-con

          (We haven't tested this yet.)

          Show
          Ian Daniel [Atlassian] added a comment - If the problem happens when using JBoss, this could be the fix: http://stackoverflow.com/questions/128527/is-there-any-way-to-have-the-jboss-connection-pool-reconnect-to-oracle-when-con (We haven't tested this yet.)
          Hide
          Grégory Joseph added a comment - - edited

          From what I gather from Mark's comments, we should avoid using the autoReconnect parameter - at least when using DBCP 1.2.2, which is my case, with Tomcat 6.0.18. However, http://confluence.atlassian.com/display/JIRA/Surviving+Connection+Closures still uses it in its examples. I am confused. Could anyone shed some light on this?

          (we currently have the autoReconnect=true AND a validation query, and getting these errors, mostly after a period of inactivity)

          Cheers

          -greg

          Show
          Grégory Joseph added a comment - - edited From what I gather from Mark's comments, we should avoid using the autoReconnect parameter - at least when using DBCP 1.2.2, which is my case, with Tomcat 6.0.18. However, http://confluence.atlassian.com/display/JIRA/Surviving+Connection+Closures still uses it in its examples. I am confused. Could anyone shed some light on this? (we currently have the autoReconnect=true AND a validation query, and getting these errors, mostly after a period of inactivity) Cheers -greg
          Hide
          Andrew Myers [Atlassian] added a comment - - edited

          Hi Greg,

          Mark was suggesting that the autoReconnect=true parameter was not responsible for preventing problems with MySQL. Earlier versions of DBCP were always sending a request to the server to set the autocommit property and if that request failed a new connection would be opened.

          What happened in DBCP 1.2.2 is that behaviour changed, so the auto commit property would only be sent to the server if it had changed. So that meant JIRA would get a database connection from Tomcat and attempt to run a query on that connection. It would fail because MySQL had closed the connection. The autoReconnect=true parameter would tell the JDBC driver to reopen the connection but that only happens after the query failed. The failed query would not be executed again because neither JIRA nor Tomcat, which manages the database connection pool for JIRA, handled the situation where MySQL closed the connection. The result was the database got corrupted because the query never executed.

          MySQL suggest that the autoReconnect parameter should not be used at all, in favour of the application handling the case where connections are closed

          The validation query will tell tomcat to check all connections before trying to use them, so this situation should be avoided. It would basically be very similar to the way the old DBCP worked - Tomcat will always be making a server request to check a connection is still open before passing the connection onto JIRA.

          For my own testing, I set up two JIRA instances on Tomcat 6.0.18, both without the autoReconnect parameter. Both instances were connected to the same database server (different databases), and I set wait_timeout = 10 in /etc/my.cnf so connections would be closed after 10 seconds of inactivity. The JIRA instance with the validation query never experienced this problem, and the one without consistently reproduced the problem. Additionally setting autoReconnect=true was of no use without also implementing the validation query.

          My advice would be to check your jira.xml file and see that you have the resource correctly configured. It will be something like this (obviously different username/passwords etc)

          <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
                        username="jira"
                        password="jira"
                        driverClassName="com.mysql.jdbc.Driver"
                        url="jdbc:mysql://localhost:3306/test1?useUnicode=true&amp;characterEncoding=UTF8" 
                        validationQuery="SELECT 1" />
          

          Also you need to restart the server for this change to take effect, so you will need to copy the jira.xml into $CATALINA_HOME/conf/Catalina/localhost/jira.xml. If that still doesn't work we might need you to raise a support request.

          Kind Regards,

          Andrew

          Show
          Andrew Myers [Atlassian] added a comment - - edited Hi Greg, Mark was suggesting that the autoReconnect=true parameter was not responsible for preventing problems with MySQL. Earlier versions of DBCP were always sending a request to the server to set the autocommit property and if that request failed a new connection would be opened. What happened in DBCP 1.2.2 is that behaviour changed, so the auto commit property would only be sent to the server if it had changed. So that meant JIRA would get a database connection from Tomcat and attempt to run a query on that connection. It would fail because MySQL had closed the connection. The autoReconnect=true parameter would tell the JDBC driver to reopen the connection but that only happens after the query failed. The failed query would not be executed again because neither JIRA nor Tomcat, which manages the database connection pool for JIRA, handled the situation where MySQL closed the connection. The result was the database got corrupted because the query never executed. MySQL suggest that the autoReconnect parameter should not be used at all, in favour of the application handling the case where connections are closed http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. The validation query will tell tomcat to check all connections before trying to use them, so this situation should be avoided. It would basically be very similar to the way the old DBCP worked - Tomcat will always be making a server request to check a connection is still open before passing the connection onto JIRA. For my own testing, I set up two JIRA instances on Tomcat 6.0.18, both without the autoReconnect parameter. Both instances were connected to the same database server (different databases), and I set wait_timeout = 10 in /etc/my.cnf so connections would be closed after 10 seconds of inactivity. The JIRA instance with the validation query never experienced this problem, and the one without consistently reproduced the problem. Additionally setting autoReconnect=true was of no use without also implementing the validation query. My advice would be to check your jira.xml file and see that you have the resource correctly configured. It will be something like this (obviously different username/passwords etc) <Resource name= "jdbc/JiraDS" auth= "Container" type= "javax.sql.DataSource" username= "jira" password= "jira" driverClassName= "com.mysql.jdbc.Driver" url= "jdbc:mysql: //localhost:3306/test1?useUnicode= true &amp;characterEncoding=UTF8" validationQuery= "SELECT 1" /> Also you need to restart the server for this change to take effect, so you will need to copy the jira.xml into $CATALINA_HOME/conf/Catalina/localhost/jira.xml . If that still doesn't work we might need you to raise a support request. Kind Regards, Andrew
          Hide
          Grégory Joseph added a comment -

          Hi Andrew,

          Thanks a lot for the detailed response! I double-checked my context descriptor - i've had the validationQuery there for a while. Two differences though:

          • as I said, I still had the autoReconnect=true param in the url (now removed) - you guys might want to remove it from the examples on the "Surviving Connection Closures" page, and/or add a note about it
          • I have a maxActive attribute (value: 20) - I am not sure if this is any useful and tbh was probably copy/pasted from some example.

          Since I'm planning to upgrade our Jira instance later today or tomorrow, I won't restart now. Hopefully either or both removing autoReconnect or upgrading from 3.12.2 to the latest will help.

          Cheers,

          -greg

          Show
          Grégory Joseph added a comment - Hi Andrew, Thanks a lot for the detailed response! I double-checked my context descriptor - i've had the validationQuery there for a while. Two differences though: as I said, I still had the autoReconnect=true param in the url (now removed) - you guys might want to remove it from the examples on the "Surviving Connection Closures" page, and/or add a note about it I have a maxActive attribute (value: 20) - I am not sure if this is any useful and tbh was probably copy/pasted from some example. Since I'm planning to upgrade our Jira instance later today or tomorrow, I won't restart now. Hopefully either or both removing autoReconnect or upgrading from 3.12.2 to the latest will help. Cheers, -greg
          Hide
          Mark Lassau [Atlassian] added a comment -

          Greg,

          Thanks for the helpful comment about our documentation. I have created a request to change it (JRA-16207).

          Just to make it clear:

          • Including the autoReconnect=true in your URL should not stop the validationQuery from working.
          • The maxActive attribute sets the maximum number of DB connections in your connection pool. This is good to have in a production environment, and you will notice that although Andrew wasn't using it, it is included in the documentation page you linked to.
          Show
          Mark Lassau [Atlassian] added a comment - Greg, Thanks for the helpful comment about our documentation. I have created a request to change it ( JRA-16207 ). Just to make it clear: Including the autoReconnect=true in your URL should not stop the validationQuery from working. The maxActive attribute sets the maximum number of DB connections in your connection pool. This is good to have in a production environment, and you will notice that although Andrew wasn't using it, it is included in the documentation page you linked to.

            People

            • Assignee:
              Mark Lassau [Atlassian]
              Reporter:
              Andrew Myers [Atlassian]
            • Votes:
              0 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: