Description:
When using sql_mode=TRADITIONAL you expect that all 0 dates will not be possible since
it includes NO_ZERO_DATES. In practice, however, this is not the case because you cannot
simply specify a timestamp as NOT NULL because a DEFAULT "0000..." gets added and
cannot be removed. This means that if a value is not specified the timestamp has a value
of all 0's even when NO_ZERO_DATES is set.
mysql> CREATE TABLE `testing` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> `created` timestamp NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.24 sec)
mysql> show create table testing;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| simon_testing | CREATE TABLE `testing` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into testing (id) values (1);
Query OK, 1 row affected (0.08 sec)
mysql> select * from testing;
+----+---------------------+---------------------+
| id | last_change | created |
+----+---------------------+---------------------+
| 1 | 2010-07-09 09:32:00 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE `testing` (
`id` int(10) unsigned NOT NULL auto_increment,
`last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB;
insert into testing (id) values (1);
select * from testing;
Suggested fix:
Allow a timestamp to be defined as NOT NULL w/o adding the DEFAULT '0000-00-00 00:00:00' clause (at least when NO_ZERO_DATES is set).