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

```sql -- your SQL here; ```

Steps to reproduce

Input Code

```java // 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]

https://github.com/r2dbc/r2dbc-pool/blob/3ee034a65e888e0e3fcb330777afc3715ced6797/src/main/java/io/r2dbc/pool/PooledConnection.java#L66 seems to be hard-set to use LOCAL validation regardless of configuration options. Should allow for REMOTE validation shouldn't it?

@mp911de