Bug #93059 server timezone gets applied in PreparedStatment but not when reading ResultSet
Submitted: 2 Nov 2018 11:59 Modified: 3 Dec 2018 22:57
Reporter: Mark Struberg Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.13 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: jdbc, timezone

[2 Nov 2018 11:59] Mark Struberg
Description:
There seems to be a nasty timezone glitch in the latest MySQL JDBC drivers. 
We did not see this before when testing Apache OpenJPA with older MySQL versions.

My workstation is running on europes CET (+1) timezone. The server is setup to GMT (Docker mysql/mysql-server-5.7)

I create a new java.util.Date which is set to 1978-04-26 00:00:00 CET.
The database column is DATE.
In ClientPreparedQueryBindings.java#418 this gets converted to the server timezone

> this.ddf = TimeUtil.getSimpleDateFormat(this.ddf, "''yyyy-MM-dd''", cal, cal != null ? null : this.session.getServerSession().getDefaultTimeZone());

which results in adjustment from the +1 to GMT and then printing the date:
1978-04-25:23:00:00GMT -> 1978-04-25

SqlTimestampValueFactory#100 uses a Calendar which is again uses the Server timezone. Now the way back is rougly the same. But the result differes.
'1978-04-25' is taken from the DB. It gets expanded to 1978-04-25_00:00:00GMT.
Then the Timezone is applied -> 1978-04-25_01:00:00GMT
And when reading this back to Java we are stuck with a 1978-04-25. 

That means that the SELECT reads a day less than we did store.
Note that older JDBC drivers used to work fine afaict.

How to repeat:
1.) create a table with a DATE column in the db.
2.) have the server set up to GMT
3.) have the java client setup to CET (+1)
4.) store a new java.sql.Date parsed from '1978-04-26'
5.) cut the connection, flush the EntityManager, etc
6.) select the row from the DB and read the field. You are now back at '1978-04-25'.
[2 Nov 2018 12:24] Mark Struberg
Just did run my test with connectorJ 5.1.47 and it works perfectly fine.
[2 Nov 2018 12:33] Mark Struberg
A possible solution would be to perform the timezone adjustment _only_ in MySqlFieldTypes.DATETIME and TIMESTAMP. But leave it as is for DATE columns.
[3 Dec 2018 22:57] Filipe Silva
Hi Mark,

Thank you for your interest in MySQL and Connector/J. My apologies for taking so long to reply to this report.

This may or may not be a bug, depending on your interpretation of the JDBC specification and Java documentation.

As of now, I'm considering this report as a duplicate of Bug#91112, where you can find some more details about this issue. Please share your thoughts with us on this other bug report.