MySQL Bugs: #37506: on update current_timestamp not supported by table editor or reverse engineer

Bug #37506	on update current_timestamp not supported by table editor or reverse engineer
Submitted:	18 Jun 2008 21:09	Modified:	23 Jun 2008 21:10
Reporter:	Ken Zo	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.22 OSS rev 3118	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	discards, missing feature, ON UPDATE, reverse engineer, table editor, timestamp

Description:
1) There is no way in the table editor to specify "on update current_timestamp" for a column.
This MySQL feature is documented here:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

2) If you reverse engineer an existing database that does have "on update
current_timestamp" for a column, Workbench silently discards that information.  It neither appears in the model (the table editor has nowhere for it to go), nor does it appear when you forward engineer the database.  Workbench just discards it.

(This is also described in bug #37071, but that bug's status is "can't repeat".  I will list a simpler how-to-repeat below.)

How to repeat:
1) Create new table with a timestamp column, and try to indicate "on update current_timestamp".  There is no way to do it with the table editor interface.

2) Without using Workbench, create a table with a timestamp column that has "on update current_timestamp":
mysql> CREATE TABLE `timestamp_bug` (`timestamp_field` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL) ENGINE = MYISAM ;

Dump table:
> mysqldump
CREATE TABLE `timestamp_bug` (
  `timestamp_field` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

In Workbench, File->Import->Reverse Engineer MySQL Create Script, and load that script.  Note that there is nowhere in the table editor that indicates "on update current_timestamp" for that column.  Use Workbench to Forward Engineer SQL CREATE Script (or, right click on table and choose "Copy SQL To Clipboard").  The result:

CREATE  TABLE IF NOT EXISTS `timestamp_bug`.`timestamp_bug` (
  `timestamp_field` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1

Note that "on update current_timestamp" does not appear in the script, even though it should.

Thank you for the bug report.

this is actually a duplicate of http://bugs.mysql.com/bug.php?id=36489.
we are working on this issue. While it will be generally supported in next release (5.0.23) to enter this  clause into the default field of a timestamp column, we're still working on one special case for this situation (using "ON UPDATE CURRENT_TIMESTAMP" without specifying a default value). The fix for the special case will go into 5.0.24.