Why doesn't HikariCP establish a new connection to database after the previous one is closed?

Question

To better understand how HikariCP works I decided to make a Spring Boot project to do some database data manipulation tests with a reduced wait_timeout in the database. Here are the HikariCP configurations in the application.properties file:

spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.validationTimeout=5000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.maximumPoolSize=20
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG 
logging.level.com.zaxxer.hikari=TRACE

And below are some values on MySQL DB:

SHOW GLOBAL VARIABLES LIKE "%wait%";
+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout                          | 50       |
| innodb_log_wait_for_flush_spin_hwm                | 400      |
| innodb_spin_wait_delay                            | 6        |
| innodb_spin_wait_pause_multiplier                 | 50       |
| lock_wait_timeout                                 | 31536000 |
| mysqlx_wait_timeout                               | 28800    |
| performance_schema_events_waits_history_long_size | 10000    |
| performance_schema_events_waits_history_size      | 10       |
| wait_timeout                                      | 5        |
+---------------------------------------------------+----------+

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 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+

The project has an entity class called Test that contains a primary key "id" and an attribute "value":

@Entity
public class Test {
    
    @Id
    @Column(length = 50)
    private String id;
    
    private String value;
    
}

We also have a TestRepository that extends JpaRepository and a TestDAO that saves entities using JDBC Template:

@Repository
public interface TestRepository extends JpaRepository<Test,String>{
}

@Repository
public class TestDAO {
   
    @Value("${spring.jpa.properties.hibernate.jdbc.batch_size:10000}") // default = 1000
    private Integer batchSize;
    
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    TestRepository testRepository;
    
    public void saveAllDAO(List<Test> entities) {
        
        jdbcTemplate.batchUpdate(
                "INSERT INTO test "
                + "(id, value)"
                + "VALUES (?,?)",
                entities,
                batchSize,
                new ParameterizedPreparedStatementSetter<Test>() {
                    @Override
                    public void setValues(PreparedStatement ps, Test testEntity) throws SQLException {
                        ps.setObject(1, testEntity.getId(), java.sql.Types.VARCHAR);
                        ps.setObject(2, testEntity.getValue(), java.sql.Types.VARCHAR);
                    }
                });
        
    }
    
}

To perform the tests I created a TestService class with four methods that execute some SELECTs, INSERTs and UPDATEs in the MySQL Local Database.

Method1 -> SELECT ALL via Repository class -> wait 20 seconds -> UPDATE entities on database via Repository class

Method2 -> SELECT ALL via Repository class -> wait 20 seconds -> SAVE new entities on database via DAO class that uses a JDBC Template

Method3 -> SELECT ALL via Repository class -> wait 20 seconds -> SELECT ALL via Repository class again

Method4 -> UPDATE entities on database via Repository class -> wait 20 seconds -> SELECT ALL via Repository class again

@Service
public class TestService {
  
    @Autowired
    TestRepository testRepository;
    @Autowired
    TestDAO testDAO;
    
    public void method1() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL");
        
        Thread.sleep(20000);
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setValue("ValueRep" + Integer.toString(i));
        }
        
        testRepository.saveAll(entities);
        System.out.println("SAVE REP");
        
    }
    
    public void method2() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL");
        
        Thread.sleep(20000);
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setId("DAO" + Integer.toString(i));
            entity.setValue("ValueDAO" + Integer.toString(i));
        }
        
        testDAO.saveAllDAO(entities);
        System.out.println("SAVE DAO");
        
    }
    
    public void method3() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL 1");
        
        Thread.sleep(20000);
        
        List<Test> entities2 = testRepository.findAll();
        System.out.println("FINDALL 2");
        
    }

    public void method4() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setValue("METHOD4ValueRep" + Integer.toString(i));
        }
        
        testRepository.saveAll(entities);
        System.out.println("SAVE REP METHOD4");
        
        Thread.sleep(20000);
        
        List<Test> entities2 = testRepository.findAll();
        System.out.println("FINDALL 2");
        
    }
    
}

During the tests I changed the global variable wait_timeout in the database to 5 seconds to see how HikariCP would behave having to establish a new connection after the timeout.

Here is the problem: when I call any of these methods the first operation works as intended but when the code tries to execute the second operation after the 20 seconds wait an CommunicationsException is generated.

2022-05-20 13:30:21.049  WARN 876 --- [nio-8080-exec-3] com.zaxxer.hikari.pool.ProxyConnection   : HikariPool-1 - Connection com.mysql.cj.jdbc.ConnectionImpl@3313efdd marked as broken because of SQLSTATE(08S01), ErrorCode(0)

com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 20.015 milliseconds ago. The last packet sent successfully to the server was 20.015 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.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2155) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:2139) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:423) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.setReadOnly(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:189) ~[spring-jdbc-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:153) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:421) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174) ~[spring-data-jpa-2.7.0.jar:2.7.0]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.20.jar:5.3.20]
    at jdk.proxy2/jdk.proxy2.$Proxy109.findAll(Unknown Source) ~[na:na]
    at com.example.test.TestService.method3(TestService.java:59) ~[classes/:na]
    at com.example.test.TestController.method3(TestController.java:27) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) ~[tomcat-embed-core-9.0.63.jar:4.0.FR]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.63.jar:4.0.FR]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) ~[spring-boot-actuator-2.7.0.jar:2.7.0]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 20.015 milliseconds ago. The last packet sent successfully to the server was 20.015 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.
    at jdk.internal.reflect.GeneratedConstructorAccessor41.newInstance(Unknown Source) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) ~[na:na]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:581) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:761) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:700) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1051) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:997) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:663) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2148) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    ... 76 common frames omitted
Caused by: java.net.SocketException: An established connection was aborted by the software in your host machine
    at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:325) ~[na:na]
    at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:350) ~[na:na]
    at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:803) ~[na:na]
    at java.base/java.net.Socket$SocketInputStream.read(Socket.java:966) ~[na:na]
    at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at java.base/java.io.FilterInputStream.read(FilterInputStream.java:132) ~[na:na]
    at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:575) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    ... 82 common frames omitted

2022-05-20 13:30:21.056 DEBUG 876 --- [nnection closer] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@3313efdd: (connection is broken)
2022-05-20 13:30:21.059 ERROR 876 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: ] with root cause

java.sql.SQLException: Connection is closed
    at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515) ~[HikariCP-4.0.3.jar:na]
    at jdk.proxy3/jdk.proxy3.$Proxy92.setAutoCommit(Unknown Source) ~[na:na]
    at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:414) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.setAutoCommit(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
    at
Sorry, log too long

Shouldn't HikariCP try to establish a new connection after checking that the old one is not open anymore? Why doesn't this happen?

Note: the code works normally with a wait_timeout of 28800 as it was previously, I am just trying to understand why HikariCP doesn't try to establish a new connection to the database with the wait_timeout reduced in this test case.

The complete code: https://github.com/MarcosL20/HikariCPTest/tree/main

Answer 1

My instinct in the comment above was correct. Spring is executing each method in a single transaction. Therefore, no matter how many separate queries there are within the method, a single connection will be used. It is the JpaTransactionManager that ensures that the same connection is returned for each invocation. The method as a whole will either fail or succeed as a unit (single-transaction).