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