The idle mySQL connection pool closed issue

By WZH

This was one issue that faced sometimes when you forget something in the system config. Log it as it might help reminding in future.

Issue:

After several hours or days no body using system, the first user try to login and always failed, until several times trying.

Reason:

A further check the log found error of  java.sql.SQLException: Connection already closed.

So the real real is that JDBC connection pooling is closed after too long time idle.

Solution:

DB has ability to run a  validationQuery to detect the connection closed or not.  If the validation query fails, the bad/closed connection is dropped and another connection is created to replace it. So it will ensure connection is ready before a code query.

The validation query is a query run by the data source to validate that a Connection is still open before returning it.

For my MySQL case, you need to add this for the JDBC define in the Spring config:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"
 destroy-method="close">
    ............................
    ...........................
    <property name="validationQuery" value="select 1"/>
</bean>

 

Refer to :

http://commons.apache.org/proper/commons-dbcp/configuration.html
http://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most

 

Advertisements