[
https://issues.apache.org/jira/browse/CAY-2701?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17922549#comment-17922549
]
Andrus Adamchik commented on CAY-2701:
--------------------------------------
I fixed this for MySQL 8 and newer. MySQL 5 will still add an hour during DST,
but it is no longer a relevant DB to support. Note that the fix ended up being
very clean. Instead of converting to/from String, we can use this:
{noformat}
rs.getObject(i, LocalDateTime.class);
st.setObject(pos, val, type);
{noformat}
So it no longer looks like hack.
> MySQL DST-related LocalDateTime issues
> --------------------------------------
>
> Key: CAY-2701
> URL: https://issues.apache.org/jira/browse/CAY-2701
> Project: Cayenne
> Issue Type: Bug
> Affects Versions: 4.0.2, 4.1, 4.2.M2
> Environment: MySQL 5.7.x, table column with type "datetime"
> JDBC Driver: mysql:mysql-connector-java:5.1.46
> JVM timezone: "America/New_York"
> Reporter: Andrus Adamchik
> Priority: Major
> Fix For: 5.0-M2
>
>
> Just ran into a whole collection of annoying problems related to
> daylight-savings time. As mentioned in the "Environment" section, the test
> environment is MySQL 5.7.x, mysql:mysql-connector-java:5.1.46,
> "America/New_York" timezone. Some of the issues described here are universal,
> others are DB and driver and JVM TZ sensitive.
> h2. Problem 1: Lossy conversion from LocalDateTime to Timestamp
> Say a LocalDateTime value corresponds to a UTC timezone (so no DST there),
> and want to save a value of "2021-03-14T02:00:00". The JVM is located in
> "America/New_York" timezone, where this specific hour ("2021-03-14T02:XX:XX")
> is skipped due to EST to EDT switchover. This combination prevents Cayenne
> from saving such as local date correctly because LocalDateTime to Timestamp
> conversion (used by Cayenne to bind datetime value to JDBC) would actually
> use the JVM TZ (!!) and the hour will be increased by 1 (so
> "2021-03-14T03:35:00" will be saved). Here is a JDBC-agnostic test to
> demonstreate that:
> {noformat}
> @Test
> public void test() {
> TimeZone defaultTz = TimeZone.getDefault();
> TimeZone.setDefault(TimeZone.getTimeZone("America/New_York"));
> try {
> LocalDateTime dt = LocalDateTime.parse("2021-03-14T02:35:00");
> assertEquals(dt, Timestamp.valueOf(dt).toLocalDateTime());
> } finally {
> TimeZone.setDefault(defaultTz);
> }
> }
> {noformat}
> There seems to be on way around it (this is an expected java.sql.Timestamp
> behavior!!), except to replace LocalDateTime-to-Timestamp conversion with
> LocalDateTime-to-String conversion. This causes some downstream driver
> issues. MySQL 5.1.x driver throws on "PreparedStatement.setObject(i, string,
> Types.TIMESTAMP)", and "setString(..)" should be called instead. Not sure
> about other DBs and 8.x MySQL driver.
> With the above in mind, LocalDateTime ValueObjectType should be reimplemented
> as an ExtendedType, and we need to test it across the DBs.
> h2. Problem 2: MySQL 5.1.x driver will add an hour on read
> The ExtendedType above allows to write LocalDateTime properly, DST or not.
> But when reading it back, MySQL Driver interferes. When reading a column that
> is a "datetime" as String, it first does a conversion to Timestamp, and then
> converts it to String. So again - an hour is added unexpectedly.
> There's no Cayenne-side fix for that. But the DB connection string must
> contain "noDatetimeStringSync=true". This seems to be fixed in the 8.x
> driver, so this flag is no longer required there.
> _(Update for MySQL 8, driver version 9.1.0: This is still an issue, and an
> hour is still added. But_ _"noDatetimeStringSync=true"_ _is no longer
> required for the workaround described below - reading datetime as a String
> before converting it to a LocalDate)_
> h2. Workarounds / Best Practices
> While we need to address this craiziness with a new ExtendedType, I suspect
> if a user sets his Java server TZ to UTC, they should avoid all this
> insanity. Though of course YMMV when e.g. running unit tests in a specific TZ
> you may run into this problem.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
