CODEWITHSUNDEEP
javamysqlhibernatec3p0
RandomQuestion
RandomQuestion

Reputation: 6998

Communication link failure..Connection reset

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

Upvotes: 3

Views: 6638

Answers (2)

Potejciak
Potejciak

Reputation: 238

It sounds like you have some connections in your pool and use only part of them. Some of them just wait to be used, and get stale (connection gets reset for some reason - ex. some timeout on DB side). Then when in some moment you try to use them, you get the exception.

There are usually a few options to fix this, for example you can test connections before retrieving it from pool (hibernate.c3p0.testConnectionOnCheckout)

You can additionally periodically check status of connections in pool and/or check connections on checkIn. See http://www.mchange.com/projects/c3p0/#configuring_connection_testing

If it doesn't help - the whole stacktrace might be useful.

Upvotes: 3

Steve Waldman
Steve Waldman

Reputation: 14083

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!

Upvotes: 3

Related Questions