MySQL connection 8 hours timeout issue

[lz000]

MySQL close idle connections after 8 hours. I have 2 questions regarding this.

1. With the nonBlocking pool, if I specify healthCheck and idleTimeBeforeHealthCheck, does it mean it will keep the connections alive? for example

Pools.nonBlocking()
                        .connectionProvider(ConnectionProvider.from(url, username, password))
                        .healthCheck(DatabaseType.MYSQL)
                        .idleTimeBeforeHealthCheck(5, TimeUnit.SECONDS)
                        .build();

2. If I use the blocking pool and pass spring boot auto configured dataSource, will it keep the connection alive? for example:

@Configuration
public class DataSourceConfig {

    @Bean
    public Database db(DataSource ds){
        return Database.fromBlocking(ds);
    }
}

in application.properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://...
spring.datasource.username=username
spring.datasource.password=password

[davidmoten]

1. The health check only occurs on checkout of a connection from a pool (and it won't occur if the last checkout of that connection happened less than 5 seconds ago according to your code). So if you don't use connections for a while they are not kept alive by rxjava2-jdbc. Moreover, if a connection is unused for more than maxIdleTime it will be discarded from the pool. Note that if a connection fails health check it will be thrown away and a new connection added to the pool.

2. Nope, and no health check either.

[lz000]

Thank you!. Does that means I will not run into the connection closed error with the nonBlocking pool when I query DB after 8 hours of idle since it will create a new connection on health check? If I don't specify health check, will I run into connection closed error when I query DB after 8 hours of idle? Do you mean if I use the blocking pool, I will run into the connection closed error in above case? If so, is there a way to prevent this?

[davidmoten]

Does that means I will not run into the connection closed error with the nonBlocking pool when I query DB after 8 hours of idle since it will create a new connection on health check?

Yes, that's what should happen.

If I don't specify health check, will I run into connection closed error when I query DB after 8 hours of idle?

Possibly. For example in our corporate environment F5 chops idle TCP connections after 30 mins. It will depend on your network.

Do you mean if I use the blocking pool, I will run into the connection closed error in above case? If so, is there a way to prevent this?

As above, it may happen because there is no health-check. The way to prevent it would be to use a non-blocking connection pool or use a blocking connection pool on top of another pool like Hikari (and get it to do the health check).

[lz000]

Thank you again! Just to confirm the 3rd question, if I were to use a blocking pool on top of a Hikari pool, I just need to pass a HikariDataSource to Database.fromBlocking right? like below code where ds is a HikariDataSource.

    public Database db(DataSource ds){
        return Database.fromBlocking(ds);
    }

[davidmoten]

Yep but use a singleton Database (don't create a new one via that method every time you want one!)