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

jchrys · Answer 1 · 2023-05-31T11:35:51.880

0

serverTimezone is not supported but serverZoneId is supported. Would you try using serverZoneId option?

Url Connection

ConnectionFactory connectionFactory = ConnectionFactories.get(
    "r2dbcs:mysql://root:database-password-in-here@127.0.0.1:3306/r2dbc?" +
    "serverZoneId={serverZoneId}"

Programmatic way

MySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder()
    .host("127.0.0.1")
    .user("root")
    .port(3306) // optional, default 3306
    .password("database-password-in-here") // optional, default null, null means has no password
    .database("r2dbc") // optional, default null, null means not specifying the database
    .serverZoneId(ZoneId.of("Continent/City")) // optional, default null, null means query server time zone when connection init

edited May 31 '23 at 11:35

answered Apr 23 '23 at 07:50

jchrys

11
2

Thank you for the response. I tried with America%2FNew_York as well as UTC also. Getting the same error. But, I tried with `dev.miku:r2dbc-mysql`. Not getting errors atleast. But getting warning. Looks like the parameter from the URL for the timezone is not used while creating the connector object I guess. – Shakthi Apr 24 '23 at 17:03
yes both of r2dbc-mysql currently does not support `serverTimezone` option. I just wrote the answer that addressing the root cause of the issue. could you cehck? – jchrys Apr 24 '23 at 19:08
I'm using `mysql-8.0.28` . `SHOW VARIABLES LIKE '%zone%';` result is Updated with the question . – Shakthi Apr 24 '23 at 22:07
While I try to set the timezone in mysql, it gives the error like `18:16:46 SET GLOBAL time_zone = 'America/Toronto' Error Code: 1298. Unknown or incorrect time zone: 'America/Toronto' 0.000 sec`. Same for `America/New_York` as well. – Shakthi Apr 24 '23 at 22:18
in that case, I think you could refer below stackoverflow question. https://stackoverflow.com/questions/42589490/sqlstatehy000-general-error-1298-unknown-or-incorrect-time-zone-utc-windo – jchrys Apr 25 '23 at 04:49
I do not want to touch from the db side. It'd be great if I can handle by the code or your library. – Shakthi Apr 26 '23 at 01:18
could you let me know which os you're using? – jchrys Apr 26 '23 at 02:30
When you execute the following query `SELECT b.name, a.time_zone_id FROM mysql.time_zone a, mysql.time_zone_name b WHERE a.time_zone_id = b.time_zone_id; ` and you cannot find the desired timezone, it means you need to install the timezone. If you are using windows make sure the to download the `non-posix-with-leap-seconds-package`. (link: https://dev.mysql.com/downloads/timezones.html`). after that you can query `SET GLOBAL time_zone = 'America/Toronto'` once for all. – jchrys Apr 26 '23 at 02:41
I'm using windows. I didn't face any issue while I was using normal mysql-connector. does this really matter? Is it not possible to enforce your library configuration should have the timezone if it's mandatory? – Shakthi Apr 26 '23 at 08:11
I understand that you wish to use the America/New_York timezone. To proceed, please ensure that this timezone is available in your MySQL server by checking the output of the following query: `SELECT b.name, a.time_zone_id FROM mysql.time_zone a, mysql.time_zone_name b WHERE a.time_zone_id = b.time_zone_id;` If America/New_York isn't listed, kindly install the timezone. Without America/New_York in the installed timezones, supporting serverTimezone='America/New_York' in our library wouldn't be feasible. (We can only support timezones that are included in your installed timezone list anyway.) – jchrys Apr 26 '23 at 13:49

SpringBoot 3 Webflux R2DBC MySQL Timezone issue

1 Answers1