Details: Written by Nam Ha Minh; Last Updated on 17 July 2019 | Print Email

When developing Java database web applications with Hibernate/JPA and MySQL, you will face a connection error if the user has not used the application for a long time. The exception stack trace would look like this:

The last packet successfully received from the server was 297,215,018 milliseconds ago. 
The last packet sent successfully to the server was 35 milliseconds ago.
...
javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: 
Unable to acquire JDBC Connection
...
Caused by: org.hibernate.exception.JDBCConnectionException: 
Unable to acquire JDBC Connection
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
...
Caused by: java.net.SocketException: Software caused connection abort: recv failed
…

If you refresh or try to connect again, the application successfully connected to the database. And the problem keeps happening again if the user left the application for a quite long time and come back. Though this is not a serious problem in development, but it’s not good under the end-user’s perspective and they will see the application is buggy and their work is interrupted. So this problem should be solved completely.

So why is JDBCConnectionException thrown?

By default, Hibernate uses its internal database connection pool library. That means it keeps a database connection open to be reused later. And MySQL database server has a timeout value for each connection (default is 8 hours or 28,800 seconds). So if a connection has been idle longer than this timeout value, it will be dropped by the server.

Therefore, when the Java database application has been idle longer than MySQL server’s connection timeout value, and the end user tries to connect again, Hibernate reuses the idle connection which was already dropped by the server, hence JDBCConnectionExceptionis thrown.

Solutions to fix JDBCConnectionException

If you look closely at the exception stack trace, you will see some suggestions to fix the problem:

The last packet sent successfully to the server was 390,061 milliseconds ago.
is longer than the server configured value of 'wait_timeout'. 
You should consider either expiring and/or testing connection validity before use in your application, 
increasing the server configured values for client timeouts, 
or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Let’s consider each suggestion mentioned above.

Expiring and/or testing connection validity before use in your application:

This requires changing or re-structuring the existing code, which is difficult to implement. Furthermore, opening a database connection is an expensive operation so it’s not optimal to open and close database connection for every user’s request - that’s why database connection pooling comes into play.

Increasing the server configured values for client timeouts:

This is possible by updating the setting file (my.ini) on MySQL server machine - changing the wait_timeoutvalue with longer value. However, the problem can occur again if the connection has been idle longer than the timeout value.

Using the Connector/J connection property 'autoReconnect=true'

I tried this solution by appending the property autoReconnect=true to the database URL but it doesn’t work. The end user still sees the exception before it is successfully connected again. Also, the user of this feature is not recommended - accordingly to MySQL documentation.

If you look around on the web, you will see someone suggest adding the following properties to Hibernate configuration file:

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

I already tried this, and it doesn’t work.

A solution that really works to solve JDBCConnectionException problem:

Finally I found a solution that actually works to avoid an error occur if the database connection is idle too long. It is using the c3p0 database connection pooling library.

If you project is Maven-based, add the following dependency to use c3p0:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>5.3.6.Final</version>
</dependency>

If not, let find, download on Maven Central Repository add the following JAR files to your project’s classpath:

hibernate-c3p0-5.3.6.Final.jar
c3p0-0.9.5.2.jar
mchange-commons-java-0.2.11.jar

Then add the following properties to Hibernate/JPA configuration file of your project:

<property name="hibernate.c3p0.min_size" value="5" />
<property name="hibernate.c3p0.max_size" value="20" />
<property name="hibernate.c3p0.timeout" value="300" />

Let me explain these properties:

hibernate.c3p0.min_size: the minimum number of connections maintained in the pool at any given time.
hibernate.c3p0.max_size: the maximum number of connections maintained in the pool at any given time.
hibernate.c3p0.timeout: the number of seconds an idle connection is kept in the pool. If a connection is idle longer than this timeout value, then it will be replaced by a new one.

So that means you have to set the value of hibernate.c3p0.timeout less than the wait_timeout value on MySQL server. And the value 300 seconds in the above example is pretty good. This definitely solves the problem because the pool maintains no connections that are idle longer than the MySQL server’s timeout value.

And c3p0 can be used in production to replace Hibernate’s default internal pool.

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.