Fetched timestamp field via MySQL Connector/J 8.0.23 is incorrect

user_374198 0
Viewed 421

In the data source URL we include serverTimezone=UTC, and the server is also running in UTC. We were using version 6.0.6 of the connector and had no issues with TIMESTAMP fields coming back just as they went into the database. However, upgrading to version 8.0.23 causes the TIMESTAMP field to be incorrect;

Expected :2018-09-18T00:00:00Z
Actual   :2018-09-17T04:00:00Z

This is after a round-trip to/from the database (MySQL 5.7), and note that the field in the database is TIMESTAMP, and on the Java side it is Instant.

I tested this with a bunch of different 8.x versions of MySQL Connector/J and found that it was working in all that I tested except for the latest, 8.0.23.

Viewing the release notes for this version, they call out some changes to timezone handling:

release notes

Strange that a patch version would introduce such a change! However, they state that the way we are connecting should result in the same behavior as before (though it does not):

Users who had serverTimeZone=user-defined-time-zone and keep it the same, without configuring the new connection properties, can expect the same behavior as before, but testing is recommended.

I've tried updating the connection string with a variety of options e.g. connectionTimeZone=UTC, forceConnectionTimeZoneToSession=true, preserveInstants=true -- but nothing seems to work for this version.

Am I missing something here, or does this seem like a bug? FWIW, no bug is listed which covers this.

mysql-5.7 mysql java
1 Answers

You could also check https://insidemysql.com/support-for-date-time-types-in-connector-j-8-0/ for more details.

For migration from c/J 6.0.6 it should be enough to additionally set preserveInstants=true. But the question is how do you store and retrieve the Instant, Connector/J does not have a built-in support for that. Connector/J 8.0.23 preserves instant values only in certain cases, please check "Preserving instants" chapter, probably you need to use a different getter.

user_26450600
Related