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

[8 Oct 2007 14:59] Dmitry Tkach
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
[8 Oct 2007 15:47] Miguel Solorzano
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>
[8 Oct 2007 22:49] Dmitry Tkach
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? :-)
[9 Oct 2007 17:52] Sergei Golubchik
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:"
[5 Jan 2009 20:13] Matt Kantor
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.
[17 Feb 2010 6:22] Jonathan Harker
Sorry, but how is ERROR 1293 not a bug?
[17 Jan 2011 7:11] Parvesh Garg
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