Connection pool issue

Question

If I launch my application after it was idle for some time, I used to get below error. ( I am using Spring+Hibernate+MySQL as DB )

ERROR [org.hibernate.util.JDBCExceptionReporter]The last packet successfully received from the server was 74,188,684 milliseconds ago. 
The last packet sent successfully to the server was 74,188,685 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.
org.hibernate.exception.JDBCConnectionException: could not execute query

I solved this issue by adding below to my servlet-context.xml.

<beans:property name="validationQuery" value="SELECT 1"/>

I had asked this question here which was more specific to solution.I need to know why I was getting that error.

I tried the 1st (Configure the connection string with autoReconnect=true ) and 3rd option (Configuring the connection pool to test the validity of the connection) provided in the above link and both worked. Still I dont get why in first place I was getting the error.

Here is my updated servlet-context.xml file and I am using ApacheDBCP for connection pooling.

<beans:bean id="MyID" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <beans:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <beans:property name="url" value="jdbc:mysql://localhost:17761/myDB"/>
        <beans:property name="username" value="myname"/>
        <beans:property name="password" value="mypwd"/>
        <beans:property name="maxIdle" value="5"/>
        <beans:property name="maxActive" value="20"/>
        <beans:property name="minIdle" value="5"/>
        <beans:property name="validationQuery" value="SELECT 1"/>
</beans:bean>

Is it some connection expiry issue ? Please help me to understand.

Answer 1

Here is the flow of events to illustrate what's happening:

1. A connection is requested and used by the caller (application or connection pool)
2. The caller keeps a reference to it so that the connection can be re-used
3. The caller goes through a period of inactivity (for example, a dev system overnight or a QA system over the weekend).
4. Once that database connection is not in use, the database considers the connection to be idle. Because it is idle, after a certain amount of time (MySQL default is 8 hours) the database closes the connection.
5. The caller still has a handle to the connection, and when the caller tries to use the connection again unpleasantly discovers that connection has been closed.

The reason autoReconnect=true works, and that the pool testing the validity of the connection works, is that you are instructing the calling system to test the connection for this situation and to try again if this situation happens.

As for whether the validation query will affect performance: In theory it is using a connection to do something. In practice that something is so trivial that its effect is negligible in the context of your entire system.

[EDIT]

In this case Apache DBCP is the connection pool hanging on to the connection, but you do NOT want DBCP to close the connection after every call. The point of the connection pool is to keep a connection ready for the next call because creating connections is expensive. The connection objects maintained by the pool are backed by actual database connections, and the database is the one who closes that actual connection after the idle timeout period. Note that the timeout to close idle connections is configured on the database, not on the connection pool. Because of this, DBCP has no way of knowing whether the connection has been closed or not unless it actually tries to connect with it. That’s why you need a validation query.

For more information about configuring DBCP, see the configuration page and the API docs.