MySQL Bugs: #22695: MySQL doesn't update function of checking validation when daylight saving time

Bug #22695	MySQL doesn't update function of checking validation when daylight saving time
Submitted:	26 Sep 2006 7:57	Modified:	3 Oct 2006 8:49
Reporter:	Yongliang Ai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.24a	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	check, DST, time

Description:
'2007-04-01 02:49:54' can't be inserted into a timestamp field with error "Data truncation: Incorrect datetime value: '2007-04-01 02:49:54' for column 'EVENT_TIME' at row 1".

After my investigation, I found it is due to mysql's checking validation of time when inserting. And '2007-04-01 02:49:54' is invalidation because that we use GMT+5:00 Easttime US/Canada, when 2007-04-01, time will move from 01:59:59 to 03:00:00 because of DST, so that local time is never anything between 2:00:00 - 2:59:59 at the transition from normal to DST.

But, America and Canada have change start and end time of DST.
Original: "in Canada and the U.S., move their clocks ahead by one hour on the first Sunday of April and move back an hour on the last Sunday of October."
Now: "beginning in 2007, when daylight time will begin earlier and end later. U.S. President George W. Bush signed legislation in August 2005 calling for daylight time to start on the second Sunday in March, three weeks earlier than the traditional start. The bill also extends daylight time by a week to the first Sunday in November. " (http://www.cbc.ca/news/background/daylightsavingtime/)

So, mysql doesn't update to date.

How to repeat:
Insert'2007-04-01 02:49:54' into a timestamp field.

Thank you for the report.

Could you please provide output of SHOW CREATE TABLE your_table_with_timestamp_field;, SHOW VARIABLES LIKE 'time_zone'; and SELECT @@sql_mode; statements?

...
`EVENT_TIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
...

mysql> SHOW VARIABLES LIKE 'time_zone'
    -> ;
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

System time_zone is GMT-05:00 Eastern time (US&Canada).

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.03 sec)

Thank you for the feedback.

Did you have timezone tables installed? Your table with TIMESTAMP field is InnoDB table?

No, I don't download timezone tables.

Yes, I use InnoDB.

Did you install http://www.microsoft.com/windows/timezone/dst2007.mspx?

If not, please, do it and try to repeat the issue.

Please, don't forget to restart MySQL server after installing timezone update.

There's no update package for download. How to update windows? Thank you!

Looks like you should use automatic Windows Update tool (you can found it in the Control Panel).

I have update my WindowsXP sp2 to the latest. But the problem still exists. And in that page, MS doesn't release its final update for DST. So I think windows doesn't update this problem.

Do you mean MySQL just use windows's function to check time validation? Thank you!

Yes, MySQL uses windows's function to check time validation for SYSTEM timezone. So we can do nothing here.

I am facing the same problem as described. But the problem is that I am on Linux (Cent OS 4). Any suggestions?

Thanks!!

Manish,

same answer: as MySQL uses operating system's function to check time validation for SYSTEM timezone, upgrade your system