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