SpringBoot 3 Webflux R2DBC MySQL Timezone issue

Question

I'm using r2dbc with mysql to connect DB for spring boot webflux service. My application.yml is below:

spring:
  r2dbc:
    url: r2dbc:mysql://localhost:3306/testdb?useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=America%2BNew_York
    username: root
    password: root
  webflux:
    problemdetails:
      enabled: true

and I'm getting below exception. Looks like it's a bug in r2dbc. I tried both io.asyncer.r2dbc.mysql and dev.miku:r2dbc-mysql dependency. But getting same timezone error I'm getting.

2023-04-15T03:11:22.448-04:00  WARN 4608 --- [actor-tcp-nio-2] io.asyncer.r2dbc.mysql.MySqlConnection   : The server timezone is unknown <Eastern Daylight Time>, trying to use system default timezone

java.time.DateTimeException: Invalid ID for region-based ZoneId, invalid format: Eastern Daylight Time
    at java.base/java.time.ZoneRegion.checkName(ZoneRegion.java:152) ~[na:na]
    at java.base/java.time.ZoneRegion.ofId(ZoneRegion.java:117) ~[na:na]
    at java.base/java.time.ZoneId.of(ZoneId.java:410) ~[na:na]
    at java.base/java.time.ZoneId.of(ZoneId.java:358) ~[na:na]
    at java.base/java.time.ZoneId.of(ZoneId.java:314) ~[na:na]
    at io.asyncer.r2dbc.mysql.MySqlConnection.convertZoneId(MySqlConnection.java:513) ~[r2dbc-mysql-1.0.0.jar:1.0.0]
    at io.asyncer.r2dbc.mysql.MySqlConnection.lambda$null$2(MySqlConnection.java:100) ~[r2dbc-mysql-1.0.0.jar:1.0.0]
    at io.asyncer.r2dbc.mysql.MySqlResult.lambda$map$1(MySqlResult.java:94) ~[r2dbc-mysql-1.0.0.jar:1.0.0]
    at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:178) ~[reactor-core-3.5.4.jar:3.5.4]

If I check the timezone using SHOW VARIABLES LIKE '%zone%';, getting below result:

If I use dev.miku:r2dbc-mysql, then the warning is printing 9 times like below:

2023-04-23T20:13:37.839-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.841-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.843-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.845-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.847-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.855-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.858-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.860-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone
2023-04-23T20:13:37.862-04:00  WARN 34476 --- [actor-tcp-nio-2] dev.miku.r2dbc.mysql.MySqlConnection     : The server timezone is <Eastern Daylight Time> that's unknown, trying to use system default timezone

Answer 1

create ConnectionFactoryOptionsBuilderCustomizer bean and change to other zoneId. eg ->

@Bean
    public ConnectionFactoryOptionsBuilderCustomizer mysqlCustomizer() {
        return (builder) -> 
        builder.option(MySqlConnectionFactoryProvider.SERVER_ZONE_ID, ZoneId.of(    
           "UTC"));
    }

Answer 2

You can to set it on ConnectionFactory Bean as bellow:

.serverZoneId(ZoneId.of("UTC"))

import io.asyncer.r2dbc.mysql.MySqlConnectionConfiguration;
import io.asyncer.r2dbc.mysql.MySqlConnectionFactory;
import io.asyncer.r2dbc.mysql.MySqlConnectionFactoryProvider;
import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import org.reactivestreams.Publisher;
import org.springframework.boot.autoconfigure.r2dbc.ConnectionFactoryOptionsBuilderCustomizer;
import org.springframework.boot.r2dbc.ConnectionFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;
import org.springframework.r2dbc.connection.init.CompositeDatabasePopulator;
import org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer;
import org.springframework.r2dbc.connection.init.ResourceDatabasePopulator;

import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import org.springframework.r2dbc.core.DatabaseClient;
import reactor.core.publisher.Mono;

import java.time.Duration;
import java.time.ZoneId;

import static io.r2dbc.spi.ConnectionFactoryOptions.*;


import static io.r2dbc.spi.ConnectionFactoryOptions.*;

@Configuration
public class R2dbcConfiguration extends AbstractR2dbcConfiguration {


    @Bean
    @Override
    public ConnectionFactory connectionFactory() {
            return MySqlConnectionFactory.from(
                MySqlConnectionConfiguration.builder()
                        .host("127.0.0.1")
                        .port(3306)
                        .username("root")
                        .password("administrator")
                        .database("testdb")
                        .serverZoneId(ZoneId.of("UTC"))
                        .connectTimeout(Duration.ofSeconds(3))
                        .createDatabaseIfNotExist(true)
                        .useServerPrepareStatement()
                        .build()
        );
    }

}