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

[18 Jun 2008 21:09] Ken Zo
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.
[19 Jun 2008 1:35] MySQL Verification Team
Thank you for the bug report.
[23 Jun 2008 21:10] Johannes Taxacher
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.