MySQL Bugs: #93059: server timezone gets applied in PreparedStatment but not when reading ResultSet

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

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'.

Just did run my test with connectorJ 5.1.47 and it works perfectly fine.

A possible solution would be to perform the timezone adjustment _only_ in MySqlFieldTypes.DATETIME and TIMESTAMP. But leave it as is for DATE columns.

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.