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.

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

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

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

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.)

In conclusion:
The recommended approach to fix this issue, is to use the DBCP "validationQuery".
This will in fact work exactly the same as the old behaviour we had in DBCP v1.2.1

There is a small performance hit associated with this approach, however, (every time JIRA borrows a connection, it sends an extra SQL request) and so some customers may choose to use other DBCP settings to fix this issue.
This is more difficult, and should only be undertaken by users with strong database experience.

I have raised an Improvement request (JRA-15776) to attempt to detect possible problems of this kind in a running JIRA instance.

I have investigated the Apache DBCP source code for versions 1.2.1, and 1.2.2, and found the cause of the changing behaviour.
Basically in v1.2.1, there was a "happy accident" that meant the MySQL connections that timed out would be thrown away and replaced before they were borrowed from the Connection Pool.
In v1.2.2, this no longer occurs.

So, DBCP sits on top of Apache Commons Pool, which is a generic Object pool.
Indeed, it even has a GenericObjectPool class which provides the borrowObject() method that is called to borrow an Object from the pool.
borrowObject() will take an object from the pool (in our case a DB Connection), and "activate" it (it is "passivated" when it is returned).
Now, if any Exceptions are thrown during the activateObject() method, the GenericObjectPool will throw that object away and get a new one.
This is where the magic reconnection was happening for us in pre-DBCP v1.2.2 days.

PoolableConnectionFactory is the DBCP class that does the DB Connection-specific work for the object pool.
Here is PoolableConnectionFactory.activateObject() in v1.2.1:

    public void activateObject(Object obj) throws Exception {
        ...
        if(obj instanceof Connection) {
            Connection conn = (Connection)obj;
            conn.setAutoCommit(_defaultAutoCommit);
            ...
        }
    }

and here is PoolableConnectionFactory.activateObject() in v1.2.2:

    public void activateObject(Object obj) throws Exception {
        ...
        if(obj instanceof Connection) {
            Connection conn = (Connection)obj;
            if (conn.getAutoCommit() != _defaultAutoCommit) {
                conn.setAutoCommit(_defaultAutoCommit);
            }
            ...
        }
    }

Seems like a fairly innocuous change.
However, with the MySQL JDBC drivers it made all the difference.
Under DBCP v1.2.1, we would always call conn.setAutoCommit() on activate.
The JDBC drivers would send a message to the server (even if the value was not changing), an SQLExpection would be raised because of the server-side timeout and this would be caught by the underlying GenericObjectPool, which would then recover silently, grabbing another connection from the pool, or creating a new one for us.
Now, like I mentioned above, this is really a "happy accident". It was only luck that the MySQL driver wanted to do a network call on any call to setAutoCommit(). In fact, it is arguably a poor JDBC implementation. The JDBC driver knows that the value really hasn't changed, and so it should not spend time doing a network round-trip.
You can see in the DBCP 1.2.2 code above that the DB pool now does this test for you, and saves that wasted network trip, with the consequence being that we don't find out if the connection has died during the activate phase.
No magic reconnect happens any more - and the connection timeout Exception is thrown at a later stage when it actually affects the application, and we get those "The last packet successfully received from the server was xxx seconds ago." errors that have been reported.

After investigating this issue, I need to first note that the behaviour of the MySQL "autoReconnect=true" setting does not actually behave the way we expected.
A closer reading of the MySQL documentation referred to in the description above points out the following:

If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. (sic)

So, the first use of the connection after the server side has timed out the connection will throw an SQLException. Then, if you re-use the connection the JDBC driver will re-establish the connection. This behaviour can be verified with the code included below.

However, this was not the behaviour we observed pre JIRA v3.13. The autoReconnect=true was a red herring.
Something else was keeping our connections alive.

    public void testAutoReconnect() throws Exception
    {
        // Create a DB connection.
        Connection con = getConnection();
        System.out.println("First read:");
        doStuff(con);

        Thread.sleep(40000);
        System.out.println("---------------------------------");
        System.out.println("Second Read:");
        try
        {
            doStuff(con);
        }
        catch (SQLException ex)
        {
            System.out.println("SQLException: " + ex.getMessage());
            // Expected timeout - we should autoreconnect on the next use of this connection.
            System.out.println("---------------------------------");
            System.out.println("Timeout Exception occured as expected.");
            System.out.println("Auto Reconnect Read:");
            doStuff(con);
        }
    }

    private void doStuff(Connection con) throws SQLException
    {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            ps = con.prepareStatement("SELECT 'x'");
            rs = ps.executeQuery();
            rs.next();
            System.out.println("Retrieved data: " + rs.getString(1));
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException e)
                {
                    // Ignore
                }
            }
            if (ps != null)
            {
                try
                {
                    ps.close();
                }
                catch (SQLException e)
                {
                    // Ignore
                }
            }
        }
    }

    private Connection getConnection()
            throws ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException
    {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        // Obviously you will need to alter these settings dependant on your DB configuration:
        return DriverManager.getConnection(
            "jdbc:mysql://localhost/jira_trunk?autoReconnect=true&useUnicode=true&characterEncoding=UTF8",
            "jira_trunk", "jira_trunk");
    }

(Note that to get the above to time out, I set the MySQL wait_timeout to 30 seconds in the MySQL Configuration file, hence the 40 second sleep.)

In the previous case where the validation query did not solve the problem, the server.xml file was misconfigured, and the validation query wasn't used. It seems that in all support cases we have had, the setting up the validation query has been the solution to the problem.

Hi David,

We need to understand why setting the validationQuery parameter did not fix the issue for you. Can you please attach your conf/server.xml (if you are running JIRA Standalone) or your jira.xml (if you are running JIRA EAR/WAR in Tomcat), so that we can check it.

I understand that you already have a support case for this issue. Can you please attach the file to the support case.

Also, did you restart your JIRA instance after setting the validationQuery? You need to.

Please get back to us on this, as we are very anxious to ensure that we have a viable workaround for this problem.

Kind regards,
Ian

—
Ian Daniel
JIRA Support Lead
ATLASSIAN - http://www.atlassian.com