the timezone parameter in jdbc url is not used

[MichaelScofield]

What type of bug is this?

User Experience

What subsystems are affected?

Frontend

Minimal reproduce step

create table:

create table test(ts timestamp time index, a DateTime);

then insert data:

insert into test values(1, '2024-05-08 08:00:00 +0800');

then execute:

String sql = "select * from test";

try (Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:4002?serverTimezone=Asia/Shanghai")) {
    Statement statement = conn.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()) {
        System.out.println(resultSet.getObject(2));
    }
}

What did you expect to see?

the right (+8 timezone) date time: "2024-05-08 08:00:00"

What did you see instead?

utc date time: "2024-05-08T00:00"

What operating system did you use?

macos

What version of GreptimeDB did you use?

main

Relevant log output and stack trace

No response

[killme2008]

From the MySQL connector doc: https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-datetime-types-processing.html

This option itself does not set MySQL server session variable 'time_zone' to the given value. To do that the 'forceConnectionTimeZoneToSession' connection option must be set to "true".

So the correct URL is jdbc:mysql://127.0.0.1:4002?serverTimezone=Asia/Shanghai&forceConnectionTimeZoneToSession.

The connection option 'serverTimezone' is an alias of connectionTimeZone but may be deprecated in the future.

After setting as above, the JDBC driver sends SET SESSION time_zone='Asia/Shanghai' to the server, but unfortunately, we don't process it properly.

[killme2008]

According to https://dev.mysql.com/doc/refman/8.3/en/set-variable.html

To assign a value to a session system variable, precede the variable name by the SESSION or LOCAL keyword, by the @@SESSION., @@Local., or @@ qualifier, or by no keyword or no modifier at all:

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';
A client can change its own session variables, but not those of any other client.

So SET LOCAL and SET SESSION must be processed too.

[tisonkun]

#4064 should close this issue? cc @killme2008 @MichaelScofield

[killme2008]

#4064 should close this issue? cc @killme2008 @MichaelScofield

Wait for GreptimeTeam/docs#988

[killme2008]

The doc is https://docs.greptime.com/user-guide/client-libraries/java#time-zone