While using C3P0 as your connection pool (either with Spring or otherwise), sometimes you might get an exception like this:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
This is quite a common problem which shows up when C3P0 is configured incorrectly
or by using the default settings. The problem occurs when C3P0 is trying to use
a connection already established by the pool but was closed by the database server.
When C3P0 tries to use this already-closed connection, the error is thrown.
In c3p0.properties, try experimenting with the following values:
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckout=true
This will force C3P0 to test each connection before passing it to the application.
If you are configuring using spring/hibernate, do note that *only* these properties are configurable in the applicationContext.xml file:
hibernate.c3p0.acquire_increment
hibernate.c3p0.idle_test_period
hibernate.c3p0.timeout
hibernate.c3p0.max_size
hibernate.c3p0.max_statements
hibernate.c3p0.min_size
hibernate.c3p0.validate
The other properties should go to the c3p0.properties file located in the webapp class root folder.
If you get an exception such as:
java.sql.SQLException: com.mchange.v2.resourcepool.ResourcePoolException: Attempted to use a closed or broken resource pool
Ensure that the timeout value configured for c3p0 does not not exceed the database server's wait timeout.