cannot reconnect after mysql closed connection

[jyp85]

Bug Report

Versions

• Driver: pool
• Database: mysql
• Java:11
• OS:

Current Behavior

sometimes i got exceptioin as below.
MySql's wait_timeout value is 600s. so the connection which not used over 10 minutes closed by remote.
after that application generate exception.
how can i fix it?? (is there any way to autoreconnect? or can i retain all connection?)

org.springframework.transaction.CannotCreateTransactionException: Could not open R2DBC Connection for transaction; nested exception is io.r2dbc.spi.R2dbcNonTransientResourceException: Connection validation failed
	at org.springframework.r2dbc.connection.R2dbcTransactionManager.lambda$null$5(R2dbcTransactionManager.java:226)
	Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):

my configuration

initial-pool-size: 5
maximum-pool-size: 5
max-lifetime: 35000
idle-timeout: 10000
connection-timeout: 10000
////////////////////////////////////////////////
val connectionFactory = createSlaveConnectionFactory()
        val configuration = ConnectionPoolConfiguration.builder(connectionFactory)
            .name(slaveDatasource.poolName)
            .initialSize(slaveDatasource.initialPoolSize)
            .maxSize(slaveDatasource.maximumPoolSize)
            .maxIdleTime(Duration.ofMillis(slaveDatasource.idleTimeout))
            .maxLifeTime(Duration.ofMillis(slaveDatasource.maxLifetime))
            .maxCreateConnectionTime(Duration.ofMillis(slaveDatasource.connectionTimeout))
            .validationQuery("SELECT 1")
            .validationDepth(ValidationDepth.REMOTE)
            .build()
        return ConnectionPool(configuration)

Table schema

Input Code

-- your SQL here;

Steps to reproduce

Input Code

// your code here;

Expected behavior/code

Possible Solution

Additional context

[mp911de]

When exceeding the connection acquisition retries, you see Connection validation failed errors. When you configure a background eviction interval, you should be able to eliminate this type of failure.

[earandap]

I am experiencing the same issue. Is any workaround for this?

[mnuss]

r2dbc-pool/src/main/java/io/r2dbc/pool/PooledConnection.java

Line 66 in 3ee034a

return Validation.validate(this, ValidationDepth.LOCAL).then(Mono.defer(() -> {

seems to be hard-set to use LOCAL validation regardless of configuration options. Should allow for REMOTE validation shouldn't it?

@mp911de