Bug #82894 Date not correct when update to mysql-connector-java 6.0.3
Submitted: 7 Sep 2016 12:54 Modified: 14 Sep 2016 15:45
Reporter: Even L Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:6.0.3 OS:CentOS (7.2)
Assigned to: CPU Architecture:Any
Tags: date

[7 Sep 2016 12:54] Even L
Description:
When my project update maven module:
mysql-connector-java 5.1.39 to mysql-connector-java 6.0.3

Save field "closeTime":Date type (2016-09-30),
Log save field "closeTime" value:"2016-09-30 08:00:00.0(Timestamp)"

But select from mysql database,"closeTime" value is:2016-09-29
It happen in mysql-connector-java 6.0.3, date always less one day!

Mysql version is:mysql  Ver 15.1 Distrib 5.5.47-MariaDB, for Linux (x86_64) using readline 5.1

How to repeat:
email
[8 Sep 2016 6:20] Chiranjeevi Battula
Hello Even,

Thank you for the bug report.
I tried to reproduce the issue at my end using MySQL Connector / J 6.0.3 but not seeing any issues in selecting Date/Timestamp values.
Could you please provide repeatable test case (sample code etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[8 Sep 2016 16:14] Even L
bug demo project

Attachment: mysql-connector-java-6.0.3-bug.tar.gz (application/gzip, text), 361.76 KiB.

[13 Sep 2016 12:21] Chiranjeevi Battula
Hello Even,

Thank you for your feedback.
Could you please describe your setup wrt to server and client timezone and how those are configured and what connection properties are using.
Could you try with adding the property serverTimezone=<same-as-client>(needs to be replaced by the client time zone).

Thanks,
Chiranjeevi.
[14 Sep 2016 10:43] Even L
eclipse version:eclipse-jee-mars-2-win32-x86_64
jdk version:jdk-8u101-windows-x64
client system:Windows 10 zh-cn
client timezone is:Asia/Shanghai 中国标准时间
client jdbc config:
jdbc:mysql://192.168.1.201:3306/test1?useUnicode=true&amp;characterEncoding=utf8&amp;mysqlEncoding=utf8&amp;zeroDateTimeBehavior=convertToNull

server system:CentOS Linux release 7.2.1511 (Core)
server i18n:LANG="en_US.UTF-8"
server timezone is:Time zone: Asia/Shanghai (CST, +0800)
mariadb server version:mariadb-server-5.5.50-1.el7_2.x86_64
mariadb server cnf:
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8

If I add "&amp;serverTimezone=Asia/Shanghai" to jdbc options, it solved!
It seems to be component's local time zone recognition error!
I try my web app run in same server base on CentOS (database, client in the same machine), the problem persists without add "&amp;serverTimezone=Asia/Shanghai".So add "&amp;serverTimezone=Asia/Shanghai" as temporary solution.
[14 Sep 2016 11:38] Chiranjeevi Battula
Hello Even,

Thank you for your feedback.
This is most likely duplicate of Bug #82192, please see Bug #82192.

Thanks,
Chiranjeevi.
[14 Sep 2016 15:45] Even L
I continue remove propertie serverTimezone from jdbc options, using mysql-connector-java 6.0.3.
And update database field "time DATE" to "time DATETIME".
And try insert new date "2016-09-30 00:00:00", databse result "2016-09-29 11:00:00".

Server timezone is same as client timezone, just:
-------------------------------------------------------
Client timezone is: Asia/Shanghai 中国标准时间(CST)
get from java:
	Calendar cal = Calendar.getInstance();
	TimeZone timeZone = cal.getTimeZone();
	System.out.println(timeZone.getID());
	System.out.println(timeZone.getDisplayName());
-------------------------------------------------------

-------------------------------------------------------
Server system timezone is: Asia/Shanghai (CST, +0800)
get from cmd:
	timedatectl
	      Local time: Wed 2016-09-14 23:41:30 CST
	  Universal time: Wed 2016-09-14 15:41:30 UTC
	        RTC time: Wed 2016-09-14 15:41:30
	       Time zone: Asia/Shanghai (CST, +0800)
	     NTP enabled: n/a
	NTP synchronized: no
	 RTC in local TZ: no
	      DST active: n/a
-------------------------------------------------------

-------------------------------------------------------
Mariadb timezone is: CST
get from:
	MariaDB [test1]> show variables like '%time_zone%';
	+------------------+--------+
	| Variable_name    | Value  |
	+------------------+--------+
	| system_time_zone | CST    |
	| time_zone        | SYSTEM |
	+------------------+--------+
	2 rows in set (0.00 sec)
-------------------------------------------------------

What is the different between mysql-connector-java 5.1.39 and mysql-connector-java 6.0.3, due to different result?