| 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: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.0.54, 5.0.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[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.
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?