Bug #55131 cannot define timestamp column without an implicit DEFAULT
Submitted: 9 Jul 2010 16:34 Modified: 9 Aug 2012 16:54
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0,5.1,5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: timestamp
Triage: Triaged: D2 (Serious)

[9 Jul 2010 16:34] Matthew Lord
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).
[9 Jul 2010 16:38] Matthew Lord
I forgot to include this as the first line in the "how to repeat" section:

set session sql_mode=traditional;
[18 Sep 2010 15:40] Kat Lim Ruiz
This also happens when I try to do a Forward Engineering from Workbench CE 5.2, but I suppose is a database bug rather than a WCE bug.
[9 Aug 2012 16:54] Paul Dubois
This is possible in 5.6.6 with the introduction of explicit_defaults_for_timestamp.