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

Blockish

What is root cause? – Reindeer

Here's the complete exception stack: – Blockish

Answer 1

The problem is that the default value of the spring.datasource.hikari.maxLifetime property (default of 30 minutes, https://github.com/brettwooldridge/HikariCP#gear-configuration-knobs-baby) 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.

Xanthic

Do you know that is the property against which we have to set that value? – Aslam

spring.datasource.hikari.max-lifetime=600000 (in milliseconds) – Nader

I did a test using wait_timeout = 300 (5 minutes), and max-lifetime = 600000 (10 minutes). However I was not able to get this error. Do you know why ? – Assume

@CanattoFilipe The error occurs when the application tries to interact with DB with the old pool of connection(older than DB wait_timeout) which it assumes to be live. So I think you should try simulating that. – Hoist

RAVI SHANKAR's answer is best because it tell us "do this to solve" instead "find out how to do this to solve" – Suazo

@Kirby : Do we have wait_timeout in oracle also? we are having similar issue in our application but we are using oracle11g. where do we set this wait_timeout. Also the application is deployed in weblogic. is this error related to some weblogic settings? – Carmella

@Xanthic how did you find out the wait_timeout of your database? – Mitchiner

@Xanthic Run SHOW VARIABLES LIKE "%wait%" in your db client – Mantellone

Answer 2

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

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

Mcintire

Answer 3

This warning occurs when the database connection is closed after being idle for some time, but the connection thread tries to make a call afterward. I use the Hikari configurations below in the application.yml to keep my connection 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.

Santos

Answer 4

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

Parabolize

You don;t need to define the bean, just provide the proper properties in your application.properties suggesting to fix this by replacing the bean isn't the proper solution. – Chancellorsville

@M.Deinum However making people aware of the beans does not harm. I still find it useful but I agree that the best practice for configuration is to use properties file. – Singspiel

The problem with this is that when you define the bean, without further/proper knowledgeee, in Spring Boot parts of the auto configuration back off. Which often lead to surprising results for users without the proper knowledge. – Chancellorsville