Communication link failure..Connection reset

Question

My application occasionally sees this exception while talking to MySQL database using Hibernate. I tried tuning in C3p0 properties but it does not seem to work.

Here are C3p0 related settings:

<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.idle_test_period">60</property><!-- in seconds -->
<property name="hibernate.c3p0.min_size">2</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.timeout">180</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1</property>

Connection url looks like this:

jdbc:mysql://<DB endpoint>?autoReconnect=true&useUniCode=true&characterEncoding=UTF-8&useSSL=true&requireSSL=true&verifyServerCertificate=false

Stacktrace is:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 1549998 ms ago.

Caused by: javax.net.ssl.SSLException: Connection has been shutdown: javax.net.ssl.SSLException: java.net.SocketException: 
Connection reset

Do I need to tweak MySQL system variables? Any pointers how can we avoid this exception?

Thanks Jitendra

Answer 1

hibernate.c3p0.idle_test_period -- or c3p0.idleConnectionTestPeriod will test every 60 secs only if a Connection is idle for 60 secs. if your app is very active, it won't be idle for so long. c3p0 tries to notice when Exceptions received by the application mean the Connection should be invalidated, but that depends on the SQLState of the Exception, which is inconsistent across databases/JDBC drivers and not entirely reliable. It's rare for a dead Connection to survive multiple rounds of checkin/checkout, but under your settings it could happen.

the simplest solution is to add c3p0.testConnectionOnCheckin to your config. that ensures, regardless of whether Connections are busy or idle, that Connections will be tested before being allowed into the pool, without incurring the synchronous overhead of testing on checkout.

please do see http://www.mchange.com/projects/c3p0/#configuring_connection_testing

also please verify that the config params you are setting are getting through to the underlying Connection pool. c3p0 dumps its config to your logs at INFO level on pool init: check your logs to verify that your settings are what you think they are. (alternatively you can inspect your pools while your application is running via jmx.)

good luck!