Possibly Consider Using a Shorter maxLifetime Value - Hikari Connection Pool Spring Boot

Question

After starting my SpringBoot application, getting an exception on few minutes of the server startup. Did not use any HikariPool Configuration externally, Spring Boot is using HikariPool by default This is the error I am getting in the console:

2020-02-20 03:16:23 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@4c4180c8 (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:28 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@679c2f50 (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:33 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@16083061 (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:38 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@4fcaf421 (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:43 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@33df5d54 (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:48 - HikariPool-4 - Failed to validate connection 
com.mysql.cj.jdbc.ConnectionImpl@373d288c (No operations allowed after connection closed.). 
Possibly consider using a shorter maxLifetime value.
2020-02-20 03:16:48 - SQL Error: 0, SQLState: 08003
2020-02-20 03:16:48 - HikariPool-4 - Connection is not available, request timed out after 
30156ms.
2020-02-20 03:16:48 - No operations allowed after connection closed.
2020-02-20 03:16:48 - Servlet.service() for servlet [dispatcherServlet] in context with path 
[] threw exception [Request processing failed; nested exception is 
org.springframework.dao.DataAccessResourceFailureException: Unable to acquire JDBC 
Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to 
acquire JDBC Connection] with root cause

Accepted Answer

The problem is that the default value of the spring.datasource.hikari.maxLifetime property (default of 30 minutes, ) is higher than the database's wait_timeout, 10 minutes in my case.
So you have two options, either decrease the hikari.maxLifetime below 10 minutes, or increase the database's wait_timeout property.

Answer 2

You can set the value as below in the application.properties file

spring.datasource.hikari.maxLifeTime=600000 #10 minutes wait time

Answer 3

In my case, I solved the problem through this setting

@Configuration
public class HikariSetting{

    @Bean
    public HikariConfig config() {
        HikariConfig hikariConfig = new HikariConfig();
        
        // other setting
        
        hikariConfig.addDataSourceProperty("socketTimeout", 600000);
        hikariConfig.setMaxLifetime(600000);
        
        return hikariConfig;
    }
    
}

reference this

Answer 4

This warning comes when the database connection is closed after some time of being idle but the connetion thread tries making a call after that. I use below Hikari configs in application yml to keep my connetion alive.

hikari:
  connectionTestQuery: SELECT 1
  connection-timeout: 300000
  idle-timeout: 15000
  max-lifetime: 35000
  validationTimeout: 300000

connectionTestQuery will be used to query DB after some interval and won't let the db connection get closed.

Then just point these configs in the db configuration class like below.

@ConfigurationProperties(prefix = "hikari")
public DataSource dataSource() {... }

Note: This is just a warning and does not impacts your code as such other than opening a new connection resuming the task.

Possibly Consider Using a Shorter maxLifetime Value - Hikari Connection Pool Spring Boot

Must Read

4 Answers

Sophia Al-Mansoor