MySQL Bugs: #31452: problems with setting column default to current

Bug #31452	problems with setting column default to current_timestamp
Submitted:	8 Oct 2007 14:59	Modified:	8 Oct 2007 22:49
Reporter:	Dmitry Tkach	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> create table xyz(a timestamp, b timestamp default current_timestamp);

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

????????

First, I DO only have one. Second, why in the world can I not have two????
Furthermore:

mysql> create table xyz (a timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table xyz alter a set default current_timestamp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_timestamp' at line 1

?????

How to repeat:
see description

Thank you for the bug report.

mysql> alter table xyz change a a timestamp default current_timestamp;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql>

I don't understand how this is "not a bug".
\h alter table says
ALTER [IGNORE] TABLE tbl_name
    alter_specification

alter_specification:
...
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

I do exactly that and it does not work. You must have some definition of "bug", that's very different from mine :-)

In any event, what about the first part:
mysql> create table a (a timestamp, b timestamp default current_timestamp);
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Is this somehow intended behavior too? :-)

Yes. See http://dev.mysql.com/doc/refman/5.0/en/timestamp.html starting from "In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:"

What is the reasoning behind this "feature"?  I haven't looked at the source code, but allowing two columns to be CURRENT_TIMESTAMP by default/on update seems like it would be trivial to implement.  It'd definitely be very useful; I often want created_at and modified_at columns in the same table.

Sorry, but how is ERROR 1293 not a bug?

I second all reasons for this being a bug (if somebody wants, can call it feature request or feature removal request). The mere reason that this is documented does not mean its valid.

created_on, modified_on are the most used columns in well designed databases. please to be taking community seriously