CODEWITHSUNDEEP
javamysqlhibernateconnection
Skyterix
Skyterix

Reputation: 107

Java Hibernate connection to database timed out after few minutes

Hibernate loses connection to the database after a few minutes and sends such an error:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN:

SQL Error: 0, SQLState: 08S01 paź 18, 2018 11:17:40 PM

org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR:

The last packet successfully received from the server was 363 452 milliseconds ago. The last packet sent successfully to the server was 363 493 milliseconds ago. is longer than the server configured value of 'interactive_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.

I added autoreconnect=true but it did not work and the error still occurred.

Then I created DatabaseConnectionController which has the following function:

public static EntityManagerFactory getEntityManagerFactory() {

    if (entityManagerFactory != null && entityManagerFactory.isOpen()) {
        return entityManagerFactory;
    } else {
        return requestNewConnection();
    }
}

The problem still occurred and I started to search for answers in google, I added a few lines to persistance.xml:

<property name="hibernate.dbcp.validationQuery" value="SELECT 1" />
<property name="hibernate.dbcp.testOnBorrow" value="true" />
<property name="hibernate.dbcp.validationInterval" value="60000" />
<property name="hibernate.dbcp.testOnReturn" value="true" />

It also did not solve my problem.

Hibernate does not automatically reconnect to the database the first time it just send error message, but when it is sent the second query automatically reconnects to the database.

How can I set it to reconnect automatically after disconnection or how can I catch this error and repeat the query in the code?

Upvotes: 0

Views: 2328

Answers (1)

Alain Cruz
Alain Cruz

Reputation: 5097

The problem is that databases will drop idle connections. How long can a connection be idle depends on your DB's configuration. In order to fix this you need to use a connection pool, like c3p0. To use it, you would need to add the following dependencies in your Maven.

<!-- c3p0 -->
<!-- Session manager -->
<!-- Check that the version works for you -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>5.2.12.Final</version>
</dependency>

Then you need to add the following configurations to your hibernate.cfg.xml.

<!-- c3p0 -->
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.timeout">600</property>
<property name="hibernate.c3p0.max_size">25</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_statement">0</property>
<property name="hibernate.c3p0.acquireRetryAttempts">1</property>
<property name="hibernate.c3p0.acquireRetryDelay">250</property>

This should be enough to keep creating new connections and solve your connectivity issues.

Upvotes: 1

Related Questions