Bug #82192 Jdbc connector get wrong mysql server time zone
Submitted: 12 Jul 2016 3:00 Modified: 13 Jul 2016 12:33
Reporter: Liu Dong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:6.0.2+ OS:Any
Assigned to: CPU Architecture:Any

[12 Jul 2016 3:00] Liu Dong 
Description:
Jdbc connector 6.0.2+ get time zone from mysql server property time_zone, and then system_time_zone if time_zone is 'SYSTEM'. 

If system timezone is set to 'Asia/Shanghai', mysql server show system_time_zone as 'CST', which means China Standard Time, equals GMT+08:00. However, jdbc driver get time zone using TimeZone.getTimeZone(), wich produce Central Standard Time (North America), equals GMT-06:00 with DST.

ResultSet.getTimeStamp() will produce wrong result.

How to repeat:
Set system time zone to 'Asia/Shanghai'

Start mysql server,  show time zones:
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

Query with mysql cli:
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-07-12 10:48:26 |
+---------------------+

Query with jdbc:
Connection conn = DriverManager.getConnection(", "", "");
PreparedStatement statement = conn.prepareStatement("select now()");
statement.execute();
ResultSet resultSet = statement.getResultSet();
if (resultSet.next()){
    Timestamp timestamp = resultSet.getTimestamp(1);
    System.out.println(timestamp.getTime());
    System.out.println(timestamp);
}

1468338864000
2016-07-12 23:54:24.0

Suggested fix:
Maybe could find true time zone when mysql server shows 'CST; by SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) or sometings similar.
[13 Jul 2016 12:33] Chiranjeevi Battula 
Hello Liu Dong,

Thank you for the bug report.
As per internal discussion with developers it is not a bug in connector/J, to avoid those inconsistencies you can use server time zone property in connection string.
user manual for connection string properties:
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-propertie...

Thanks,
Chiranjeevi.
[8 Sep 2016 11:30] Chiranjeevi Battula 
http://bugs.mysql.com/bug.php?id=82881 marked as duplicate of this one.
[14 Sep 2016 11:38] Chiranjeevi Battula 
http://bugs.mysql.com/bug.php?id=82894 marked as duplicate of this one.
[15 Nov 2016 5:43] Chiranjeevi Battula 
http://bugs.mysql.com/bug.php?id=83815 marked as duplicate of this one.
[21 Mar 2018 8:24] Chiranjeevi Battula 
http://bugs.mysql.com/bug.php?id=90150 marked as duplicate of this one.