Bug #34214 Unexpected ERROR 1292 (22007) error in strict mode for 2007-03-11
| Submitted: | 1 Feb 2008 4:47 | Modified: | 1 Feb 2008 5:54 |
|---|---|---|---|
| Reporter: | Gordon Shannon | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.0.27-log | OS: | Linux |
| Assigned to: | CPU Architecture: | Any |
[1 Feb 2008 5:38]
Valeriy Kravchuk
This is not a bug. Read http://webexhibits.org/daylightsaving/b.html, for example. There was no 2+ a.m. on March 11 2007 :)
[1 Feb 2008 5:54]
Gordon Shannon
I would like to retract this bug submission, as I just realized that March 11 2007 was the beginning of daylight savings time, and therefore 2:11 AM did not exist.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: If running with sql_mode set to STRICT_ALL_TABLES, attempts to set a timestamp column to '2007-03-11 02:41:40' results in ERROR 1292 (22007): Incorrect datetime value. Changing just about any part of this datetime constant causes the statement to succeed. This is a legal datetime constant and should not fail regardless of sql_mode. There simply seems to be something magical about March 11, 2007 at 2AM? How to repeat: mysql> create table gs01 (id int not null auto_increment, ts timestamp not null default current_timestamp, primary key (id)); Query OK, 0 rows affected (0.11 sec) mysql> set @@sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.05 sec) mysql> insert into gs01 (ts) values ('2007-03-11 02:41:40'); ERROR 1292 (22007): Incorrect datetime value: '2007-03-11 02:41:40' for column 'ts' at row 1 -- Change the year only, now it works mysql> insert into gs01 (ts) values ('2008-03-11 02:41:40'); Query OK, 1 row affected (0.09 sec) -- Change the month only, now it works mysql> insert into gs01 (ts) values ('2007-02-11 02:41:40'); Query OK, 1 row affected (0.10 sec) -- Change the day, now it works mysql> insert into gs01 (ts) values ('2007-03-10 02:41:40'); Query OK, 1 row affected (0.08 sec) -- Remove time, now it works mysql> insert into gs01 (ts) values ('2007-03-11'); Query OK, 1 row affected (0.07 sec) -- Even changing the hour makes it work mysql> insert into gs01 (ts) values ('2007-03-11 01:41:40'); Query OK, 1 row affected (0.09 sec) -- But... mysql> insert into gs01 (ts) values ('2007-03-11 02:41:40'); ERROR 1292 (22007): Incorrect datetime value: '2007-03-11 02:41:40' for column 'ts' at row 1