Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
4.0.2, 4.1, 4.2.M2
-
None
-
None
-
MySQL 5.7.x, table column with type "datetime"
JDBC Driver: mysql:mysql-connector-java:5.1.46
JVM timezone: "America/New_York"
Description
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.
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:
@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);
}
}
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.
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.
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.