MySQL Bugs: #4128: Please allow more than one DEFAULT or ON UPDATE of CURRENT

Bug #4128	Please allow more than one DEFAULT or ON UPDATE of CURRENT_TIMESTAMP in a table
Submitted:	14 Jun 2004 16:53	Modified:	25 Apr 2005 19:15
Reporter:	Sophia Poirier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.2 alpha	OS:	MacOS (Mac OS X 10.3.4)
Assigned to:		CPU Architecture:	Any

Description:
When I try to add a new column of type TIMESTAMP like so:

ALTER table MyTable ADD column creationtime TIMESTAMP;

to a table that I already created with another TIMESTAMP column (in MySQL 4.0), I get the following error:

"failed Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause"

The first TIMESTAMP column, having been created with MySQL 4.0, implicitly has DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

So my request is that MySQL 4.1 please allow for more than one TIMESTAMP column with DEFAULT or ON UPDATE values of CURRENT_TIMESTAMP.  This would be very useful, and I am baffled by the enforcement of this limitation which seems pretty arbitrary to me, and not SQL standard.

In my case, I want to have 2 TIMESTAMP columns:  a mod-time column and a creation-time column.  So I want these:

modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
creationtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP

This way the time of INSERT creation of a table entry is stored separately from the time of the latest UPDATE modification.  This seems to me like a valid thing to want to do, and I wish that it were possible with MySQL.

How to repeat:
CREATE table MyTable 
(creationtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
(modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Suggested fix:
Allow the above table to be created.

I'll close it, as it's already in our todo.

The limitation lays in our .frm format (which dates back to 1980-ties) - it's doesn't have a space for new extensions (there are a couple of bits left here and there that we're trying to use, but two bits cannot be used for everything :( ).

We do plan to change frm format in 5.0 (it is a big internal change - so it's not easy), then what you ask for will be possible.

In comments related to the closing of this request, Sergei Golubchik  [15 Jun 2004 12:37] mentioned that this was on the ToDo for MySQL 5. I'm currently testing 5.0.3 (using InnoDB tables, on solaris 8), and this feature still doesn't seem to be implemented. I searched the MySQL site, the to-do list in the documentation & the mysql-internals list and found nothing. This feature would be very useful to automatically capture both the create date & last update dates of a record. Did I miss something on the ToDo list, or has this feature been postponed? Thanks.

It was postponed (if you take a look at frm files in the 5.0.3 you will see that they're still binary files - that is "plain-text frm" feature is not implemented).

But 5.0.3 supports triggers, and you can create a trigger to set as many fields to CURRENT_TIMESTAMP as you want.

Still no word on this?  :o(

It would be extremely handy (as noted above) for creating tables with automatic created_at and updated_at fields.