MySQL Bugs: #112351: Incorrect implementation while locally creating Timestamp fields value in JVM

Bug #112351	Incorrect implementation while locally creating Timestamp fields value in JVM
Submitted:	15 Sep 2023 2:17	Modified:	15 May 13:12
Reporter:	zhigang tong	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	timestamp conversion

Description:
[Env]
Tested on windows 10/hotspot 1.8/mysql server 8.0.34/connectorj 8.0.30; MySql server timezone was set as '+07:00'.

[Steps]
Insert a Timestamp field in table with value of '2023-09-12 09:00:00Z', then retrieve it through jdbc connections.

[Error scenarios]
There are 2 scenarios, in which finally get incorrect value.

1. While using any connectionTimezones(such as +06:00), which differ from server.timezone(as +07:00), and disable forceConnectionTimeZoneToSession, the finally gotten timestamp value is incorrect, no matter what value is set to the JVM timezone.
2. Dont set connectionTimezone, disable forConnectionTimeZoneToSession, and set JVM timezone to a value differs from server.timezone, the finally gotten timestamp value is incorrect.

The common condition between the 2 scenarios is that connectionTimezone is not equal with the server.timezone.

[Ana]
I've checked the source code, and may have found the cause.
NativeResultset always get the correct bytes accordant with the server.timezone. While Resultset.getTimestamp(..) is called, SqlTimestampValueFactory is used to perform the transformation. At line 153 of SqlTimestampValueFactory, connectionTimezone or JVM timezone is used to instanciate a Calendar, which is used to finally perform the operation. And the error occurs here while the connectionTimezone is not equal with the server.timezone.

How to repeat:
Refer to the error scenarios.

Suggested fix:
[Sug]
1. The server.timezone should be used to instanciate the Calendar, instead of connectionTimezone/JVM.timezone.
or
2. Forbid some parameters combination, to prevent connectorj from the above scenarios.

One more thing should be mentioned here:

[Pre]
Set MySQL server timezone to "+07:00";
Using JDBC url, "jdbc:mysql://host:port/db?connectionTimeZone=${CTZ}".

[Problem]
The corresponding NativeServerSession.sessionTimeZone field is assigned "${CTZ}".

[Sug]
Since the server timezone is '+07:00' but not '${CTZ}', the comments of NativeServerSession.sessionTimeZone is incorrect, and should be revised. 
The field name may also be changed to connectionTimeZone.

Hello Zhigang Tong,

Thank you for the bug report. However this is not enough to reproduce the issue.
Please upgrade to latest version and report us back if issue persist even in latest version along with test case. Thank you.

Regards,
Ashwini Patil