Bug #60202 Connector/J returns invalid timestamp values on DST switch day
Submitted: 22 Feb 2011 10:26 Modified: 18 Jul 2013 21:28
Reporter: Holger Stolzenberg Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Documentation Severity:S2 (Serious)
Version:5.1.15 OS:Any
Assigned to: Daniel So CPU Architecture:Any
Tags: connector, DST, java, timestamp, timezone

[22 Feb 2011 10:26] Holger Stolzenberg 
Description:
When loading timestamp values for 2010-03-28, MySQL Con/J fails to handle Hour 3 correctly. We have the following scenario:

SERVER:
MySQL 5.0.90-log Source distribution
Debian 5.0.8 (Kernel 2.6.26-2-686)
Server Timezone: UTC

CLIENT:
MySQL Connector/J 5.1.15
Mac OS X 10.6
Local Timezone: CEST (Europe/Berlin)
Connection properties:
autoReconnect=true
useUnicode=true
characterEncoding=UTF-8
zeroDateTimeBehavior=convertToNull
useTimezone=true
serverTimezone=UTC
noTimezoneConversionForTimeType=false

We have a simple table holding datetime values for the whole day '2010-03-28'. 

CREATE TABLE `test_data` (
  `timepoint` datetime NOT NULL,
  `quantity` decimal(12,1) DEFAULT NULL,
  PRIMARY KEY (`timepoint`),
  KEY `WEBSITE_MBWI7_TP` (`timepoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(SQL Inserts ommited here, see attached test project)

On this day the DST switch happens. Selecting all datetime values for this day from MySQL console shows the correct results in UTC, with every hour shown in the result.

> SET time_zone='UTC';
> SELECT t1.timepoint FROM test_data AS t1;

+---------------------+
| timepoint           |
+---------------------+
| 2010-03-28 00:00:00 |
| 2010-03-28 00:15:00 |
| 2010-03-28 00:30:00 |
| 2010-03-28 00:45:00 |
| 2010-03-28 01:00:00 |
| 2010-03-28 01:15:00 |
| 2010-03-28 01:30:00 |
| 2010-03-28 01:45:00 |
| 2010-03-28 02:00:00 |
| 2010-03-28 02:15:00 |
| 2010-03-28 02:30:00 |
| 2010-03-28 02:45:00 |
| 2010-03-28 03:00:00 |
| 2010-03-28 03:15:00 |
| 2010-03-28 03:30:00 |
| 2010-03-28 03:45:00 |
| 2010-03-28 04:00:00 |
| 2010-03-28 04:15:00 |
| 2010-03-28 04:30:00 |
| 2010-03-28 04:45:00 |
| 2010-03-28 05:00:00 |
| 2010-03-28 05:15:00 |
| 2010-03-28 05:30:00 |
| 2010-03-28 05:45:00 |
| 2010-03-28 06:00:00 |
| 2010-03-28 06:15:00 |
| 2010-03-28 06:30:00 |
| 2010-03-28 06:45:00 |
| 2010-03-28 07:00:00 |
| 2010-03-28 07:15:00 |
| 2010-03-28 07:30:00 |
| 2010-03-28 07:45:00 |
| 2010-03-28 08:00:00 |
| 2010-03-28 08:15:00 |
| 2010-03-28 08:30:00 |
| 2010-03-28 08:45:00 |

+---------------------+

But issuing these two queries to the DB via Con/J and using getTimestamp() on the resultset returns invalid timestamps regarding hour 3:

[OK]	[2010-03-28 01:00:00] <> [2010-03-28 01:00:00.0]
[OK]	[2010-03-28 01:15:00] <> [2010-03-28 01:15:00.0]
[OK]	[2010-03-28 01:30:00] <> [2010-03-28 01:30:00.0]
[OK]	[2010-03-28 01:45:00] <> [2010-03-28 01:45:00.0]
[OK]	[2010-03-28 02:00:00] <> [2010-03-28 03:00:00.0]
[OK]	[2010-03-28 02:15:00] <> [2010-03-28 03:15:00.0]
[OK]	[2010-03-28 02:30:00] <> [2010-03-28 03:30:00.0]
[OK]	[2010-03-28 02:45:00] <> [2010-03-28 03:45:00.0]
[ERR]	[2010-03-28 03:00:00] <> [2010-03-28 05:00:00.0]
[ERR]	[2010-03-28 03:15:00] <> [2010-03-28 05:15:00.0]
[ERR]	[2010-03-28 03:30:00] <> [2010-03-28 05:30:00.0]
[ERR]	[2010-03-28 03:45:00] <> [2010-03-28 05:45:00.0]
[OK]	[2010-03-28 04:00:00] <> [2010-03-28 05:00:00.0]
[OK]	[2010-03-28 04:15:00] <> [2010-03-28 05:15:00.0]
[OK]	[2010-03-28 04:30:00] <> [2010-03-28 05:30:00.0]
[OK]	[2010-03-28 04:45:00] <> [2010-03-28 05:45:00.0]
[OK]	[2010-03-28 05:00:00] <> [2010-03-28 06:00:00.0]
[OK]	[2010-03-28 05:15:00] <> [2010-03-28 06:15:00.0]
[OK]	[2010-03-28 05:30:00] <> [2010-03-28 06:30:00.0]
[OK]	[2010-03-28 05:45:00] <> [2010-03-28 06:45:00.0]
[OK]	[2010-03-28 06:00:00] <> [2010-03-28 07:00:00.0]
[OK]	[2010-03-28 06:15:00] <> [2010-03-28 07:15:00.0]
[OK]	[2010-03-28 06:30:00] <> [2010-03-28 07:30:00.0]
[OK]	[2010-03-28 06:45:00] <> [2010-03-28 07:45:00.0]
[OK]	[2010-03-28 07:00:00] <> [2010-03-28 08:00:00.0]
[OK]	[2010-03-28 07:15:00] <> [2010-03-28 08:15:00.0]
[OK]	[2010-03-28 07:30:00] <> [2010-03-28 08:30:00.0]
[OK]	[2010-03-28 07:45:00] <> [2010-03-28 08:45:00.0]
[OK]	[2010-03-28 08:00:00] <> [2010-03-28 09:00:00.0]
[OK]	[2010-03-28 08:15:00] <> [2010-03-28 09:15:00.0]
[OK]	[2010-03-28 08:30:00] <> [2010-03-28 09:30:00.0]
[OK]	[2010-03-28 08:45:00] <> [2010-03-28 09:45:00.0]

Regarding the result it is ok that hour 2 is missing in the CEST timestamps because of the DST switch. It is also ok the the time offset switches from +0 to +1. What is not ok is that H3 maps to H5(3+2), which means H5 is duplicated in the result and H4 is missing.

How to repeat:
See attached maven project for bug reproduction.

Suggested fix:
I do not have the time to track down the bug in detail. After some short investigations I could narrow down the problem to the following locations:

ResultSetRow:990 -> getTimestampFast( ... )

Seems like something is going wrong within 

return TimeUtil.changeTimezone(
conn, 
sessionCalendar, 
targetCalendar, 
rs.fastTimestampCreate(sessionCalendar,year,month,day,hour,minutes,seconds,nanos), conn.getServerTimezoneTZ(),
tz,
rollForward );

found at line ResultSetRow:1326
[22 Feb 2011 10:30] Holger Stolzenberg 
Simple maven project for bug reproduction

Attachment: MySqlBug.zip (application/zip, text), 6.67 KiB.
[22 Feb 2011 10:31] Holger Stolzenberg 
Simply run 'mvn test' to execute the attached project.
[21 Mar 2011 15:58] Tonci Grgin 
Hi Holger and thanks for your report. I think this is related to Bug#60550 but will have to think about it a bit.
[22 Mar 2011 12:29] Tonci Grgin 
Sveta suggests Bug#24762 as more likely cause.
[26 Sep 2011 7:10] Tonci Grgin 
Holger,

Please do check if, what I presume, test from cl client is run with empty SQL_MODE while c/J test is run with "STRICT_TRANS_TABLES". If so, then the culprit is described in Bug#24762 and setting jdbcCompliantTruncation=false might help.
[26 Oct 2011 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 Oct 2011 15:46] Holger Stolzenberg 
Same maven project with jdbcCompliantTruncation connection

Attachment: MySqlBug-jdbcCompliantTruncation.zip (application/zip, text), 20.07 KiB.
[28 Oct 2011 15:47] Holger Stolzenberg 
I have tested your suggestion with "jdbcCompliantTruncation=false" with the attached maven project. Still it does not work!
[17 Aug 2012 9:16] Michael Maass 
After some serious debugging of the mysql jdbc driver regarding timezones lately, i'm quite confident I've solved your problem. You've got to use these parameters with the jdbc URL:

useTimezone=true
useLegacyDatetimeCode=false
serverTimezone=UTC

I've verified this "fix" running the test you've provided. 

Starting Bug reproduction ...
[OK] [2010-03-28 00:00:00] <> [2010-03-28 01:00:00.0]
[OK] [2010-03-28 00:15:00] <> [2010-03-28 01:15:00.0]
[OK] [2010-03-28 00:30:00] <> [2010-03-28 01:30:00.0]
[OK] [2010-03-28 00:45:00] <> [2010-03-28 01:45:00.0]
[OK] [2010-03-28 01:00:00] <> [2010-03-28 03:00:00.0]
[OK] [2010-03-28 01:15:00] <> [2010-03-28 03:15:00.0]
[OK] [2010-03-28 01:30:00] <> [2010-03-28 03:30:00.0]
[OK] [2010-03-28 01:45:00] <> [2010-03-28 03:45:00.0]
[FIXED] [2010-03-28 02:00:00] <> [2010-03-28 04:00:00.0]
[FIXED] [2010-03-28 02:15:00] <> [2010-03-28 04:15:00.0]
[FIXED] [2010-03-28 02:30:00] <> [2010-03-28 04:30:00.0]
[FIXED] [2010-03-28 02:45:00] <> [2010-03-28 04:45:00.0]
[OK] [2010-03-28 03:00:00] <> [2010-03-28 05:00:00.0]
[OK] [2010-03-28 03:15:00] <> [2010-03-28 05:15:00.0]
[OK] [2010-03-28 03:30:00] <> [2010-03-28 05:30:00.0]
[OK] [2010-03-28 03:45:00] <> [2010-03-28 05:45:00.0]
[OK] [2010-03-28 04:00:00] <> [2010-03-28 06:00:00.0]
[OK] [2010-03-28 04:15:00] <> [2010-03-28 06:15:00.0]
[OK] [2010-03-28 04:30:00] <> [2010-03-28 06:30:00.0]
[OK] [2010-03-28 04:45:00] <> [2010-03-28 06:45:00.0]
[OK] [2010-03-28 05:00:00] <> [2010-03-28 07:00:00.0]
[OK] [2010-03-28 05:15:00] <> [2010-03-28 07:15:00.0]
[OK] [2010-03-28 05:30:00] <> [2010-03-28 07:30:00.0]
[OK] [2010-03-28 05:45:00] <> [2010-03-28 07:45:00.0]
[OK] [2010-03-28 06:00:00] <> [2010-03-28 08:00:00.0]
[OK] [2010-03-28 06:15:00] <> [2010-03-28 08:15:00.0]
[OK] [2010-03-28 06:30:00] <> [2010-03-28 08:30:00.0]
[OK] [2010-03-28 06:45:00] <> [2010-03-28 08:45:00.0]
[OK] [2010-03-28 07:00:00] <> [2010-03-28 09:00:00.0]
[OK] [2010-03-28 07:15:00] <> [2010-03-28 09:15:00.0]
[OK] [2010-03-28 07:30:00] <> [2010-03-28 09:30:00.0]
[OK] [2010-03-28 07:45:00] <> [2010-03-28 09:45:00.0]
[OK] [2010-03-28 08:00:00] <> [2010-03-28 10:00:00.0]
[OK] [2010-03-28 08:15:00] <> [2010-03-28 10:15:00.0]
[OK] [2010-03-28 08:30:00] <> [2010-03-28 10:30:00.0]
[OK] [2010-03-28 08:45:00] <> [2010-03-28 10:45:00.0]
[OK] [2010-03-28 09:00:00] <> [2010-03-28 11:00:00.0]
[OK] [2010-03-28 09:15:00] <> [2010-03-28 11:15:00.0]
[OK] [2010-03-28 09:30:00] <> [2010-03-28 11:30:00.0]
[OK] [2010-03-28 09:45:00] <> [2010-03-28 11:45:00.0]
[OK] [2010-03-28 10:00:00] <> [2010-03-28 12:00:00.0]
[OK] [2010-03-28 10:15:00] <> [2010-03-28 12:15:00.0]
[OK] [2010-03-28 10:30:00] <> [2010-03-28 12:30:00.0]
[OK] [2010-03-28 10:45:00] <> [2010-03-28 12:45:00.0]
[OK] [2010-03-28 11:00:00] <> [2010-03-28 13:00:00.0]
[OK] [2010-03-28 11:15:00] <> [2010-03-28 13:15:00.0]
[OK] [2010-03-28 11:30:00] <> [2010-03-28 13:30:00.0]
[OK] [2010-03-28 11:45:00] <> [2010-03-28 13:45:00.0]
[OK] [2010-03-28 12:00:00] <> [2010-03-28 14:00:00.0]
[OK] [2010-03-28 12:15:00] <> [2010-03-28 14:15:00.0]
[OK] [2010-03-28 12:30:00] <> [2010-03-28 14:30:00.0]
[OK] [2010-03-28 12:45:00] <> [2010-03-28 14:45:00.0]
[OK] [2010-03-28 13:00:00] <> [2010-03-28 15:00:00.0]
[OK] [2010-03-28 13:15:00] <> [2010-03-28 15:15:00.0]
[OK] [2010-03-28 13:30:00] <> [2010-03-28 15:30:00.0]
[OK] [2010-03-28 13:45:00] <> [2010-03-28 15:45:00.0]
[OK] [2010-03-28 14:00:00] <> [2010-03-28 16:00:00.0]
[OK] [2010-03-28 14:15:00] <> [2010-03-28 16:15:00.0]
[OK] [2010-03-28 14:30:00] <> [2010-03-28 16:30:00.0]
[OK] [2010-03-28 14:45:00] <> [2010-03-28 16:45:00.0]
[OK] [2010-03-28 15:00:00] <> [2010-03-28 17:00:00.0]
[OK] [2010-03-28 15:15:00] <> [2010-03-28 17:15:00.0]
[OK] [2010-03-28 15:30:00] <> [2010-03-28 17:30:00.0]
[OK] [2010-03-28 15:45:00] <> [2010-03-28 17:45:00.0]
[OK] [2010-03-28 16:00:00] <> [2010-03-28 18:00:00.0]
[OK] [2010-03-28 16:15:00] <> [2010-03-28 18:15:00.0]
[OK] [2010-03-28 16:30:00] <> [2010-03-28 18:30:00.0]
[OK] [2010-03-28 16:45:00] <> [2010-03-28 18:45:00.0]
[OK] [2010-03-28 17:00:00] <> [2010-03-28 19:00:00.0]
[OK] [2010-03-28 17:15:00] <> [2010-03-28 19:15:00.0]
[OK] [2010-03-28 17:30:00] <> [2010-03-28 19:30:00.0]
[OK] [2010-03-28 17:45:00] <> [2010-03-28 19:45:00.0]
[OK] [2010-03-28 18:00:00] <> [2010-03-28 20:00:00.0]
[OK] [2010-03-28 18:15:00] <> [2010-03-28 20:15:00.0]
[OK] [2010-03-28 18:30:00] <> [2010-03-28 20:30:00.0]
[OK] [2010-03-28 18:45:00] <> [2010-03-28 20:45:00.0]
[OK] [2010-03-28 19:00:00] <> [2010-03-28 21:00:00.0]
[OK] [2010-03-28 19:15:00] <> [2010-03-28 21:15:00.0]
[OK] [2010-03-28 19:30:00] <> [2010-03-28 21:30:00.0]
[OK] [2010-03-28 19:45:00] <> [2010-03-28 21:45:00.0]
[OK] [2010-03-28 20:00:00] <> [2010-03-28 22:00:00.0]
[OK] [2010-03-28 20:15:00] <> [2010-03-28 22:15:00.0]
[OK] [2010-03-28 20:30:00] <> [2010-03-28 22:30:00.0]
[OK] [2010-03-28 20:45:00] <> [2010-03-28 22:45:00.0]
[OK] [2010-03-28 21:00:00] <> [2010-03-28 23:00:00.0]
[OK] [2010-03-28 21:15:00] <> [2010-03-28 23:15:00.0]
[OK] [2010-03-28 21:30:00] <> [2010-03-28 23:30:00.0]
[OK] [2010-03-28 21:45:00] <> [2010-03-28 23:45:00.0]
[OK] [2010-03-28 22:00:00] <> [2010-03-29 00:00:00.0]
[OK] [2010-03-28 22:15:00] <> [2010-03-29 00:15:00.0]
[OK] [2010-03-28 22:30:00] <> [2010-03-29 00:30:00.0]
[OK] [2010-03-28 22:45:00] <> [2010-03-29 00:45:00.0]

There is now a hour 4.  (Kung Fu Panda reference)
[25 Apr 2013 8:01] Alexander Soklakov 
This bug is duplicate of Bug#32577, please look there for more details.

But since questions are still around maybe we should describe this behavior in our documentation. So I change category of this report to MySQL Connector/J Documentation.
[18 Jul 2013 21:28] Daniel So 
Created a new "Known Issues and Limitation" section in the Connector/J documentation and added the following entry inside:

When Connector/J retrieves timestamps for a daylight saving time (DST) switch day using the getTimeStamp() method on the result set, some of the returned values might be wrong. The errors can be avoided by using the following connection options when connecting to a database:

        useTimezone=true
        useLegacyDatetimeCode=false
        serverTimezone=UTC