Tuesday, September 7, 2010

MySQL connection pool with DBCP

I have an application using Apache Commons DBCP for connection pooling to MySQL database. It was deployed on Tomcat. The users had problem login the application (authenticated through the MySQL database) after a long period of idle time. The subsequent login always worked. The log showed the following exception.

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
...


My guess was that the connection pool went dead due to the long idle period. After some Googling, I specified two more DBCP configurations in BasicDataSource Spring configuration.

<property name="testWhileIdle">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1</value>
</property>


I was able to login successfully after 14 hours of idle time. So this looks promising, but still needs more testing.