|
could you indicate the value of option `useLegacyDatetimeCode` if set ?
|
|
I tried it with both true & false but seems no effect.
BTW - in mysql this parameter is not used anymore (https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-properties-changed.html)
|
|
There has been documentation issue.
see https://mariadb.com/kb/en/mariadb-connector-j-303-release-notes/#option-changed
or https://mariadb.com/kb/en/about-mariadb-connector-j/#timezone-consideration
Correction is to set timezone option. This permit to correct some issue that where existing with previous implementation.
|
|
@Diego Dupin: I get following error when run with mariadb docker
Error: 1298-HY000: Unknown or incorrect time zone: 'auto'
How to install and use mysql_tzinfo_to_sql in mariadb docker?
|
|
So I've been doing some testing and I think I'm experiencing the same problem. Java.sql.Timestamp is simply wrong, the connection timezone is incorrectly applied, here is my test code:
import java.sql.*;
|
import java.time.*;
|
|
public class test
|
{
|
public static void main(String[] args) {
|
String tzList[] = { "UTC", "America/New_York", "America/Los_Angeles", "Europe/Berlin"};
|
System.out.println("Mariadb connector");
|
for (String tz : tzList) {
|
tryTz(tz, true);
|
}
|
System.out.println("Mysql connector");
|
for (String tz : tzList) {
|
tryTz(tz, false);
|
}
|
|
}
|
|
private static void tryTz(String tz, boolean mariadb){
|
String connectorType = mariadb ? "mariadb" : "mysql";
|
try {
|
Connection connection = DriverManager.getConnection("jdbc:" + connectorType + "://localhost:3306/openhab?user=openhab&&timezone=" +
|
tz + "&password=XXX");
|
String sql = "CREATE TEMPORARY TABLE timeTest (`id` int(11) NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `dt` datetime NOT NULL) ";
|
Statement stmt = connection.createStatement();
|
stmt.execute(sql);
|
sql = "INSERT INTO timeTest (id, ts, dt) VALUES(1, NOW(), NOW())";
|
stmt.execute(sql);
|
long insertTime = System.currentTimeMillis();
|
sql = "SELECT id, ts, dt FROM timeTest";
|
ResultSet rs = stmt.executeQuery(sql);
|
while (rs.next()) {
|
Timestamp dt = rs.getTimestamp("dt");
|
long dtMillis = dt.getTime();
|
Timestamp ts = rs.getTimestamp("ts");
|
long tsMillis = ts.getTime();
|
System.out.println("wall:" + (new java.util.Date(insertTime)).toInstant().atZone(ZoneId.of("America/New_York")) +
|
", dt: " + dt.toInstant().atZone(ZoneId.of("America/New_York")) +
|
", ts: " + ts.toInstant().atZone(ZoneId.of("America/New_York")));
|
long dtDiff = Math.abs(dtMillis - insertTime);
|
long tsDiff = Math.abs(tsMillis - insertTime);
|
System.out.println("dtDiff: " + dtDiff + ", tsDiff: " + tsDiff );//if timezones are handled correctly, these should be under 1000
|
|
}
|
} catch (SQLException e) {
|
System.out.println("Exception " + e);
|
}
|
|
}
|
}
|
|
I ran that is mariadb-java-client-3.1.0.jar and mysql-connector-j-8.0.31.jar and got this output:
Mariadb connector
|
wall:2022-12-04T19:57:53.051-05:00[America/New_York], dt: 2022-12-05T00:57:53-05:00[America/New_York], ts: 2022-12-05T00:57:53-05:00[America/New_York]
|
dtDiff: 17999949, tsDiff: 17999949
|
wall:2022-12-04T19:57:53.123-05:00[America/New_York], dt: 2022-12-04T19:57:53-05:00[America/New_York], ts: 2022-12-04T19:57:53-05:00[America/New_York]
|
dtDiff: 123, tsDiff: 123
|
wall:2022-12-04T19:57:53.128-05:00[America/New_York], dt: 2022-12-04T16:57:53-05:00[America/New_York], ts: 2022-12-04T16:57:53-05:00[America/New_York]
|
dtDiff: 10800128, tsDiff: 10800128
|
wall:2022-12-04T19:57:53.133-05:00[America/New_York], dt: 2022-12-05T01:57:53-05:00[America/New_York], ts: 2022-12-05T01:57:53-05:00[America/New_York]
|
dtDiff: 21599867, tsDiff: 21599867
|
Mysql connector
|
wall:2022-12-04T19:57:53.341-05:00[America/New_York], dt: 2022-12-04T19:57:53-05:00[America/New_York], ts: 2022-12-04T19:57:53-05:00[America/New_York]
|
dtDiff: 341, tsDiff: 341
|
wall:2022-12-04T19:57:53.356-05:00[America/New_York], dt: 2022-12-04T19:57:53-05:00[America/New_York], ts: 2022-12-04T19:57:53-05:00[America/New_York]
|
dtDiff: 356, tsDiff: 356
|
wall:2022-12-04T19:57:53.361-05:00[America/New_York], dt: 2022-12-04T19:57:53-05:00[America/New_York], ts: 2022-12-04T19:57:53-05:00[America/New_York]
|
dtDiff: 361, tsDiff: 361
|
wall:2022-12-04T19:57:53.367-05:00[America/New_York], dt: 2022-12-04T19:57:53-05:00[America/New_York], ts: 2022-12-04T19:57:53-05:00[America/New_York]
|
dtDiff: 367, tsDiff: 367
|
The connector should return a timestamp that refers to an instant in time, that is it's underlying value needs to be in UTC, the same that the database stores it in. Since the timezone is configured by the connector, the connector needs to be aware of the connection timezone and do the conversions. It appears that it's not doing the conversions at all when storing into a timestamp.
|
|
After more research on this issue, the test I wrote for the mysql connector is incorrect (it doesn't use `timezone` to set the timezone, should be connectionTImeZone for mysql), however, the mysql connector has the `preserveInstants` option which appears to control this functionality, and the output I provided is corrected for `preserveInstants=false` on the mysql connector, so I think the issue still stands (why setting it to false makes it work, I don't know, I would expect the logic to be inverted).
|
|
I think the test exactly show why 3.x correct this issue.
Here is your test slightly changed :
@Test
|
public void tt() throws Exception {
|
TimeZone initialTz = TimeZone.getDefault();
|
|
String tzList[] = {"UTC", "America/New_York", "America/Los_Angeles", "Europe/Berlin"};
|
System.out.println("Mariadb connector");
|
for (String tz : tzList) {
|
tryTz(tz, true);
|
}
|
System.out.println("Mysql connector");
|
for (String tz : tzList) {
|
tryTz(tz, false);
|
}
|
TimeZone.setDefault(initialTz);
|
}
|
|
private static void tryTz(String tz, boolean mariadb) {
|
String url;
|
if (mariadb) {
|
url = String.format("jdbc:mariadb://localhost:3306/testj?user=%s&timezone=%s", "root", tz);
|
} else {
|
url =
|
String.format(
|
"jdbc:mysql://localhost:3306/testj?user=%s&connectionTimeZone=%s&forceConnectionTimeZoneToSession=true",
|
"root", tz);
|
}
|
|
ZoneId testedTz = ZoneId.of(tz);
|
TimeZone.setDefault(TimeZone.getTimeZone(testedTz));
|
try (Connection connection = DriverManager.getConnection(url)) {
|
String sql =
|
"CREATE TEMPORARY TABLE timeTest (`id` int(11) NOT NULL, `ts` timestamp(6) NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `dt` datetime(6) NOT NULL) ";
|
Statement stmt = connection.createStatement();
|
stmt.execute(sql);
|
|
long insertTime = System.currentTimeMillis();
|
try (PreparedStatement preparedStatement =
|
connection.prepareStatement("INSERT INTO timeTest (id, dt) VALUES(1, ?)")) {
|
preparedStatement.setTimestamp(1, new Timestamp(insertTime));
|
preparedStatement.execute();
|
}
|
|
sql = "SELECT id, ts, dt FROM timeTest";
|
ResultSet rs = stmt.executeQuery(sql);
|
while (rs.next()) {
|
Timestamp dt = rs.getTimestamp("dt");
|
Timestamp ts = rs.getTimestamp("ts");
|
|
long dtMillis = dt.getTime();
|
long tsMillis = ts.getTime();
|
long tsDiff = Math.abs(tsMillis - insertTime);
|
long dtDiff = Math.abs(dtMillis - insertTime);
|
System.out.println(
|
String.format(
|
"driver %s - tz:%s: "
|
+ "\n\tnow : %s"
|
+ "\n\tserver now(): %s"
|
+ "\n\tclient now : %s"
|
+ "\n\tdiffs : %s %s",
|
mariadb ? "MariaDB" : "MySQL",
|
tz,
|
new java.util.Date(insertTime).toInstant().atZone(testedTz),
|
ts.toInstant().atZone(testedTz),
|
dt.toInstant().atZone(testedTz),
|
tsDiff,
|
dtDiff));
|
}
|
} catch (SQLException e) {
|
System.out.println("Exception " + e);
|
}
|
}
|
output is then :
ariadb connector
|
driver MariaDB - tz:UTC:
|
now : 2023-01-09T13:30:03.503Z[UTC]
|
server now(): 2023-01-09T13:30:03.535342Z[UTC]
|
client now : 2023-01-09T13:30:03.503Z[UTC]
|
diffs : 32 0
|
driver MariaDB - tz:America/New_York:
|
now : 2023-01-09T08:30:03.543-05:00[America/New_York]
|
server now(): 2023-01-09T08:30:03.544143-05:00[America/New_York]
|
client now : 2023-01-09T08:30:03.543-05:00[America/New_York]
|
diffs : 1 0
|
driver MariaDB - tz:America/Los_Angeles:
|
now : 2023-01-09T05:30:03.548-08:00[America/Los_Angeles]
|
server now(): 2023-01-09T05:30:03.549023-08:00[America/Los_Angeles]
|
client now : 2023-01-09T05:30:03.548-08:00[America/Los_Angeles]
|
diffs : 1 0
|
driver MariaDB - tz:Europe/Berlin:
|
now : 2023-01-09T14:30:03.553+01:00[Europe/Berlin]
|
server now(): 2023-01-09T14:30:03.554382+01:00[Europe/Berlin]
|
client now : 2023-01-09T14:30:03.553+01:00[Europe/Berlin]
|
diffs : 1 0
|
Mysql connector
|
driver MySQL - tz:UTC:
|
now : 2023-01-09T13:30:03.855Z[UTC]
|
server now(): 2023-01-09T13:30:03.866482Z[UTC]
|
client now : 2023-01-09T13:30:03Z[UTC]
|
diffs : 11 855
|
driver MySQL - tz:America/New_York:
|
now : 2023-01-09T08:30:03.889-05:00[America/New_York]
|
server now(): 2023-01-09T08:30:03.889954-05:00[America/New_York]
|
client now : 2023-01-09T08:30:03-05:00[America/New_York]
|
diffs : 0 889
|
driver MySQL - tz:America/Los_Angeles:
|
now : 2023-01-09T05:30:03.908-08:00[America/Los_Angeles]
|
server now(): 2023-01-09T05:30:03.908851-08:00[America/Los_Angeles]
|
client now : 2023-01-09T05:30:03-08:00[America/Los_Angeles]
|
diffs : 0 908
|
driver MySQL - tz:Europe/Berlin:
|
now : 2023-01-09T14:30:03.923+01:00[Europe/Berlin]
|
server now(): 2023-01-09T14:30:03.923794+01:00[Europe/Berlin]
|
client now : 2023-01-09T14:30:03+01:00[Europe/Berlin]
|
diffs : 0 923
|
MariaDB set session timezone when timezone option is set. That's the only good solution to handle timezone properly. This correspond to the new option "connectionTimeZone=<my timezone>&forceConnectionTimeZoneToSession=true" in MySQL connector.
Why that's the only good solution ? because some SQL command can use server "now()" for example. Or Date to "2023-01-09" won't be changed in "2023-01-08" because client won't convert it since it doesn't now that it was 01h minus 5h for timezone.
And about 'auto', it's a bug that will be fixed by (CONJ-1026) this week
So fundamentally using mariadb, if client is using the same timezone than server, nothing to do, if not, just set option 'timezone=auto'
|
|
The documentation https://mariadb.com/kb/en/about-mariadb-connector-j/#timezone-consideration is now updated. Please tell if you think something is still not right, or documentation can be more explicit !
|
|
1. Same scenario as explained in the bug
@@global.time_zone = SYSTEM and system_time_zone = UTC
2. Insert from spring using *.sql file (machine with different time zone (i.e. GMT+5))
INSERT INTO TEST (creation_time) VALUES ('2022-04-12 15:22:20.000');
3. From the same machine/jvm where insert was executed. On performing
jdbcTemplate.query("select * from TEST", (rs, rowNum) ->
new Tes().creationTime(rs.getTimestamp("creation_time").toInstant()));
2.7.x - Time stamp inserted according to session time zone
rs.getTimestamp("creation_time"): 2022-04-12 20:52:20.0
rs.getTimestamp("creation_time").toInstant(): 2022-04-12T15:22:20Z
3.1.1 - Time stamp not corrected according to insert time zone
rs.getTimestamp("creation_time"): 2022-04-12 15:22:20.0
rs.getTimestamp("creation_time").toInstant(): 2022-04-12T09:52:20Z
Shouldn't timestamp be corrected same as in 2.7? Is this not breaking backward?
|
|
Bhagya and what are your timezone settings ?
It would seems timezone is not set for 3.1.1, and it would have to be set, like '&timezone=GMT+5' or to value 'auto'
|
|
I'm using timezone=auto with 3.1.1
|
|
Diego, is this expected behavior that its breaking backward?
|
|
There is always some configuration to retrieved data right.
When having a client with a timezone that differ from server:
- With 2.7 connector, configuration can be "&useLegacyDatetimeCode=false" or even "&serverTimezone=UTC&useLegacyDatetimeCode=false"
- with 3.1 : "timezone=auto"
for example with a client in GMT+2 and an UTC server :
Server contains a table "test" with a single date value "2023-06-07 13:20:57", inserted directly from server.
try (Connection con = DriverManager.getConnection("jdbc:mariadb://xx.xx.xx.xx/testj?user=xx&serverTimezone=UTC&useLegacyDatetimeCode=false")) {
|
Statement stmt = con.createStatement();
|
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
|
while (rs.next()) {
|
System.out.println(rs.getTimestamp(1));
|
// 2023-06-07 15:20:57.0
|
System.out.println(rs.getTimestamp(1).toInstant());
|
// 2023-06-07T13:20:57Z
|
}
|
}
|
using for connector 3.1.4 and replacing "&serverTimezone=UTC&useLegacyDatetimeCode=false" by "timezone=auto" will return the same exact values.
sorry for late response, i've missed the notification
|