Bug #33887 SHOW CREATE TABLE doesn't recreate TIMESTAMP columns correctly
Submitted: 16 Jan 2008 14:24 Modified: 10 Jan 2013 11:10
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0.54, 5.0.40 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[16 Jan 2008 14:24] Baron Schwartz
Description:
SHOW CREATE TABLE may not correctly recreate a table with more than one TIMESTAMP column.  The CREATE TABLE statement will fail to execute.

This means backups can't be restored.  That's why I think it's a serious bug.

How to repeat:
mysql> create table test(t1 timestamp null, t2 timestamp not null default current_timestamp on update current_timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test modify t1 timestamp not null default '2000-01-01 00:00:00';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test alter column t1 drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `t1` timestamp NOT NULL,
  `t2` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `t1` timestamp NOT NULL,
    ->   `t2` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    -> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
mysql> 

Suggested fix:
I think that the behavior of automatically adding the default value (default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) when no default value is specified is a bug, because it doesn't allow you to say "no default value" for the first TIMESTAMP column in a table.

I know this is legacy behavior... hindsight is 20/20, right?
[17 Jan 2008 7:18] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.54.
[17 Jan 2008 22:47] Peter Laursen
I'd like to comment on this:

"I think that the behavior of automatically adding the default value (default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) when no default value is specified is a bug, because it doesn't allow you to say "no default value" for the first TIMESTAMP column in a table."

Maybe you can consider it a bug but it would cause LOTS of problems to change that!  With all sorts of client and applications!  

I also rather think it is a (parser?) bug with the specific syntax that you use!

actually dropping the default for 1st column using this statement:

alter table `test`.`test` change `t1` `t1` timestamp  NOT NULL

returns the expected

Error Code : 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
[20 Nov 2012 8:32] Abhishek Ranjan
This bug is about keeping the first column with timestamp as NOT NULL itself,
rather than promoting it.

Mysql-5.6 ,introduced --explicit-defaults-for-timestamp option, which when enabled, solves the above issue. The current behavior also differs from as shown in the bug report as server don't throw the error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS
anymore, even without --explicit-defaults-for-timestamp option. This was also implemented in 5.6.
[10 Jan 2013 11:10] Erlend Dahl
Fixed in 5.6.6.