MySql timeouts - Should I set autoReconnect=true in Spring application?

Question

After periods of inactivity on my website (Using Spring 2.5 and MySql), I get the following error:

org.springframework.dao.RecoverableDataAccessException: The last packet sent successfully to the server was 52,847,830 milliseconds ago. 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.

According to this question, and the linked bug, I shouldn't just set autoReconnect=true. Does this mean I have to catch this exception on any queries I do and then retry the transaction? Should that logic be in the data access layer, or the model layer? Is there an easy way to handle this instead of wrapping every single query to catch this?

Answer 1

I would suggest that you use a connection pool instead. They improve performance and can take care of the low-level details such as reconnect after timeout, etc.

A good one is c3p0, but there are others. Spring has support for that, though I don't know all the details. Here is the configuration of a DataSource for Spring.

Answer 2

This exception can have 2 causes:

1. You aren't closing the JDBC resources properly. All of the Connection, Statement and ResultSet must be closed in reversed order in the finally block of the try block where they're been acquired. This is regardless of the fact whether you're using a connection pool or not.

2. You are closing the JDBC resources properly, but using a connection pool with poor settings. You need to make sure that the connection pool don't hold connections open longer than the DB configrured timeout. Decrease the one or increase the other in the configuration.

Answer 3

We needed to set the following properties in order to avoid the timeout even with connection pooling; we use the Apache Commons DBCP.

<property name="validationQuery"> <value>SELECT 1</value>  </property>
<property name="testOnBorrow">    <value>true</value>      </property>