MySQL Bugs: #62868: Incorrect datetime value: '2004-03-28 02:19:08'

Bug #62868	Incorrect datetime value: '2004-03-28 02:19:08'
Submitted:	21 Oct 2011 15:55	Modified:	28 Aug 2013 23:17
Reporter:	Marco Della Rocca	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	exception on INSERT, timestamp

Description:
Tested on MySQL Server: 5.1.39 and 5.5.16, both with Connector/NET

Inserting the value '2004-03-28 02:19:08' into a timestamp field, throws an exception. Up to now, only found this on this concrete value, while all other values (some 100k from wikipedia dumps), do work as expected. 

Changing the field to datetime, does not show this behaviour.

All values are written, row by row, within a single loop, that extracts wikipedia metadata, from a compressed XML stream. 

Checked this again and again and again... Could not find a mistake, on my side.

With best regards

Marco Della Rocca

How to repeat:
Table 'table' column: 
'timestamp' timestamp NOT NULL DEFAULT '2001-01-01 00:00'

(with Connector NET 6.4.4)
CommandText = "INSERT INTO 'table' (timestamp) VALUES(?timestamp)";
Parameter["?timestamp"].value = "2004-03-28 02:19:08" // DateTime.ToString("s")
Command.ExecuteNonQuery; // throws an exception on this value (but on no other value, out of some 100k rows, all written within the same loop)

MySql.Data.MySqlClient.MySqlException: Incorrect datetime
 value: '2004-03-28 02:19:08' for column 'RevisionTimestamp' at row 1
 
MySql.Data.MySqlClient.MySqlException: Incorrect datetime
 value: '2004-03-28T02:19:08' for column 'RevisionTimestamp' at row 1

What timezone do you have set? Check with:

show variables like 'time_%';

This is probably related to the daylight saving time...

System timezone is CED.

MySQL settings:
'time_format', '%H:%i:%s'
'time_zone', 'SYSTEM'

If you mean CET (Central European Time), then you switch to summer time at the last Sunday in March, by moving from 01:59 to 03:00. Looks like 2004-03-28 is last Sunday in March that year, so... this is not a bug. Time 02:19:08 at that date just does not exist in your time zone.

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

I just got this exact same bug with files to load containing TIMESTAMP data like:
"2004/03/28 00:57:00"
"2004/03/28 02:48:00"
using LOAD DATA LOCAL INFILE '...'
    INTO TABLE pat_resp
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    lines terminated by '\r\n';
Those hours are in error but not the others the same day in the file!
My feeling is that 2004 has a February 29th and there is a numerical mishandling in the TIMESTAMP validation algorithm.
Changing 2:48 for 3:00 AM was enough to go around the bug. But not from 00:57 to 1:00 !

System info:

MySQL 5.5.14

WMIShell: Set filters for local windows cli

MySQL Workbench CE for Windows version 5.2.36  revision 8542

Cairo Version: 1.8.8

Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.

OpenGL Driver Version: 2.1.8304 Release

OS: Microsoft Windows 7  Service Pack 1 (build 7601), 32-bit

CPU: 2x Genuine Intel(R) CPU           T2400  @ 1.83GHz, 3.3 GiB RAM

Active video adapter (0): ATI Mobility Radeon X1400

Installed video RAM: 128 MB

Current video mode: 1920 x 1200 x 4294967296 couleurs

Used bit depth: 32

Driver version: 8.561.0.0

Installed display drivers: atiumdag.dll,atiumdva.cap,atitmmxx.dll

Current user language: Français (Belgique)

For question done by @Valeriy.

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

https://bugs.mysql.com/bug.php?id=96585