Aligning serverZoneId Behavior with connectionTimeZone(mysql-connector-j)

[jchrys]

Issue

The current behavior in r2dbc-mysql when the serverZoneId property is not explicitly set is to use the server's timezone. However, this differs from the behavior of the connectionTimeZone(previously serverTimeZone) property in mysql-connector-j, causing inconsistency for users familiar with MySQL's JDBC driver.

Proposal

I propose aligning the behavior of serverZoneId in r2dbc-mysql with the equivalent property in mysql-connector-j, 's connectionTimeZone. Specifically, when serverZoneId is not set, it should default to using the JVM's default timezone, similar to the behavior of connectionTimeZone.

Rationale

Consistency across different MySQL drivers is crucial for users who may work with multiple technologies or migrate between them. Aligning r2dbc-mysql with the behavior of mysql-connector-j would reduce surprises for users and make it easier for them to switch between these drivers without encountering unexpected timezone-related issues. (e.g., micronaut-projects/micronaut-data#2388)

Refs

• https://dev.mysql.com/blog-archive/support-for-date-time-types-in-connector-j-8-0/

[jchrys]

WDYT? @mirromutth

[jchrys]

Hello @mirromutth, I would appreciate your input on this proposed change. Could you please share your thoughts? May I kindly join the discussion below, if you don't mind?). micronaut-projects/micronaut-data#2388 (comment).

(additional ref: #166)

[mirromutth]

Currently, the default behavior should be same as connectionTimeZone=SERVER in JDBC MySQL.

If user set the serverZoneId=X, it should be same as connectionTimeZone=X in JDBC MySQL.

As I said in #166 , JDBC driver ResultSet.getObject(int) returns LocalDateTime, and the timezone of LocalDateTime will not be converted. Like following:

// JVM is not in UTC

Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/r2dbc?connectionTimeZone=UTC";
String user = "root";
String password = "******";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT CURRENT_TIMESTAMP(6) AS n")) {
    int count = 0;

    while (rs.next()) {
        assertThat(rs.getObject(1)).isInstanceOf(LocalDateTime.class)
            .asInstanceOf(new InstanceOfAssertFactory<>(LocalDateTime.class, Assertions::assertThat))
            .isCloseToUtcNow(new TemporalUnitLessThanOffset(200, ChronoUnit.MILLIS));

        ++count;
    }

    assertThat(count).isOne();
}

This shows whatever the user sets connectionTimeZone, it always returns LocalDateTime / ZonedDateTime as the server time zone.

If the user wants to use time zone conversion, they need to use java.sql.Timestamp or set forceConnectionTimeZoneToSession=true.

The forceConnectionTimeZoneToSession does not cause the driver to "convert" the time zone when receiving a date time, but instead changes the session variable @@time_zone. If it is set to true, driver will initialize the session time zone as the connectionTimeZone set. e.g. connectionTimeZone=LOCAL&forceConnectionTimeZoneToSession=true means set the session timezone to local timezone.

Maybe we should:

• Change serverZoneId type as String, add LOCAL and SERVER options . Default is SERVER, just like before
• Add an option forceServerZoneIdToSession. If it is set, the driver will try to set session timezone immediately after login, this should be initialized with sessionVariables, see also [feature]Support sessionVariables configuration properties #210

Or we can create an alias connectionTimeZone for serverZoneId, and just name the new option forceConnectionTimeZoneToSession.

Additionally, when the driver sends a date time, it will judge whether to convert through the preserveInstants option.

Simply put:

• preserveInstants will force the driver to send the server a date time in connectionTimeZone. Otherwise, the datetime will be in the JVM local time zone
• forceConnectionTimeZoneToSession will force the MySQL server to send the driver a date time in connectionTimeZone