Check how to set the timeout when connecting with Spring + HikariCP + MySQL and executing SQL

Thing you want to do

Check how to set the timeout when connecting with Spring + HikariCP + MySQL and executing SQL.

environment

An environment that takes action

• Environment to confirm "what you want to do"

Spring relations: 5.0.9 HikariCP：2.7.9 MySQL：5.7

Time out when connecting to DB

Items to set (when describing DataSource settings in Java)

Set with setConnectionTimeout () for the instance of HikariDatasource.

@Bean
HikariDataSource datasource() {
    
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl("jdbc:mysql://localhost:3306/test?useSSL=false&socketTimeout=10");
    ds.setConnectionTimeout(250); //★ Here
    ds.setUsername("user");
    ds.setPassword("password");
    return ds;
}

Items to be set (when using Spring-boot automatic setting)

↓ setting of application.properties

spring.datasource.hikari.connection-timeout=Timeout (milliseconds)

Time out if SQL result is not returned after DB connection

Add the socketTimeout parameter to the JDBC URL.

Items to set (when describing DataSource settings in Java)

@Bean
HikariDataSource datasource() {
    
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl("jdbc:mysql://localhost:3306/test?useSSL=false&socketTimeout=10"); //★ Here
    ds.setConnectionTimeout(250);
    ds.setUsername("user");
    ds.setPassword("password");
    return ds;
}

Items to be set (when using Spring-boot automatic setting)

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&socketTimeout=Timeout (milliseconds)

Try out

See ↓ for how to reproduce network delay in windows environment. How to reproduce network delay locally in windows

The code written as a sample is Connection timeout: 250ms Communication timeout after connection: 10ms Therefore, check that the exception appears as expected by following the procedure below.

Below are the steps.

1. Before the connection is established, if you set the lag to 400ms and execute the process that involves the execution of SQL, you can see the timeout in the output exception. (** Connection timeout **)
2. Next, stop the occurrence of the delay once and execute the process that involves the execution of SQL (connect the connection pool to the DB).
3. After that, if you set the lag to 100ms , start the delay, and execute the process that involves the execution of SQL, you can see the timeout in the output exception. (* Timeout when SQL is not returned after connection **)

• Note that if you set a value that exceeds 250 ms, you will not be able to confirm that the connection timeout setting does not mean that the timeout has occurred. In step 4, it is good to confirm that if you remove the timeout setting at the end, it will not time out even if you execute it in a delayed state.

However,,

Looking at the execution time of the log, the exception log does not appear according to the specified millisecond. I think that the timeout setting value and the delay setting value time out as intended, so I think that the timeout judgment itself can be done, but ... A mystery.