HikariCP - MYSQL No operations allowed after connection closed. Possibly consider using a shorter maxLifetime value

Question

I am using mysql database. It has the default wait timeout of 28800

mysql> SHOW VARIABLES like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.00 sec)

mysql>

In my spring boot application, hikariCP is used for managing database connections. I manually create the data source bean.

@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceConfiguration extends HikariConfig {

        @Bean
        public DataSource dataSource()  {
            return new HikariDataSource(this);
        }

}

These are my application.properties

spring.datasource.jdbcUrl=jdbc:mysql://host.docker.internal:3306/demo
spring.datasource.username=sa
spring.datasource.password=test
spring.datasource.connection-timeout=3000
spring.datasource.minimum-idle=2
spring.datasource.maximum-pool-size=6
spring.datasource.idle-timeout=40000
spring.datasource.max-lifetime=900000

These are my start up logs

2020-06-03 15:44:12 - Starting DemoApplication on 48e5fb8724cb with PID 1 (/demo.jar started by root in /)
2020-06-03 15:44:12 - No active profile set, falling back to default profiles: default
2020-06-03 15:44:13 - For Jackson Kotlin classes support please add "com.fasterxml.jackson.module:jackson-module-kotlin" to the classpath
2020-06-03 15:44:13 - Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2020-06-03 15:44:13 - Finished Spring Data repository scanning in 27ms. Found 0 JPA repository interfaces.
2020-06-03 15:44:15 - Tomcat initialized with port(s): 8000 (http)
2020-06-03 15:44:15 - Starting service [Tomcat]
2020-06-03 15:44:15 - Starting Servlet engine: [Apache Tomcat/9.0.33]
2020-06-03 15:44:15 - Initializing Spring embedded WebApplicationContext
2020-06-03 15:44:15 - Published root WebApplicationContext as ServletContext attribute with name [org.springframework.web.context.WebApplicationContext.ROOT]
2020-06-03 15:44:15 - Root WebApplicationContext: initialization completed in 2581 ms
2020-06-03 15:44:15 - HikariPool-1 - Starting...
2020-06-03 15:44:15 - HikariPool-1 - Start completed.
2020-06-03 15:44:17 - Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-06-03 15:44:17 - Initializing AWS S3 Client...
2020-06-03 15:44:19 - spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2020-06-03 15:44:19 - Initializing ExecutorService 'applicationTaskExecutor'
2020-06-03 15:44:19 - ControllerAdvice beans: 0 @ModelAttribute, 0 @InitBinder, 1 RequestBodyAdvice, 1 ResponseBodyAdvice
2020-06-03 15:44:20 - 3 mappings in 'requestMappingHandlerMapping'
2020-06-03 15:44:20 - Patterns [/webjars/**, /**] in 'resourceHandlerMapping'
2020-06-03 15:44:20 - ControllerAdvice beans: 0 @ExceptionHandler, 1 ResponseBodyAdvice
2020-06-03 15:44:20 - Tomcat started on port(s): 8000 (http) with context path ''
2020-06-03 15:44:20 - Started DemoApplication in 8.957 seconds (JVM running for 10.294)

Answer 1

connect_timeout is defined in seconds

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

Hikari docs says max-lifetime (miliseconds) should be less:

We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit.

So you should define it for example as 6 seconds (or increase your MySQL connect time):

spring.datasource.max-lifetime=6000