Bug #37071 Workbench and "on update" clause on timestamp columns
Submitted: 29 May 2008 14:53 Modified: 8 Feb 2011 15:42
Reporter: Bruno Baketaric Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench Severity:S1 (Critical)
Version:5.2.31a OS:Windows
Assigned to: CPU Architecture:Any
Tags: ON UPDATE, timestamp

[29 May 2008 14:53] Bruno Baketaric
Description:
Workbench doesn't seem to handle "on update CURRENT_TIMESTAMP" clause at all. 
Even if you insert "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for a Timestamp Column - that's how mysqldump exports it - a "Forward engineer MySQL ALTER Script" always want's to change the timestamp column.   

How to repeat:
Do this:

CREATE TABLE IF NOT EXISTS `p` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `_lastmod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
PRIMARY KEY (`id`) )

Export it using mysqldump, Reverse engineer and simply do a "copy sql to clipboard".

The "ON UPDATE" Stuff will be gone, and there's no way to get it back.

Suggested fix:
I think, the timestamp datatype needs an additional flag for the "ON UPDATE CURRENT_TIMESTAMP".

And the import/export/sync routines need to honor it.
[29 May 2008 16:29] MySQL Verification Team
Thank you for the bug report. I can't repeat below how it was created the script:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `_lastmod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
[30 May 2008 7:11] Bruno Baketaric
Ok, more details:
- take your CREATE Statement
- Save it as foo.sql
- Import it into MySQL Workbench
- first Problem: take a look at the "_lastmod" column - there's nothing regarding the "ON UPDATE CURRENT_TIMESTAMP" clause. There's just the "CURRENT_TIMESTAMP" as default. So, where do I activate/deactivate it?
- Export the Schema using "Forward Engineer SQL CREATE Script"
- In the resulting file, the "ON UPDATE CURRENT TIMESTAMP" is gone. This is my resulting file:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `_lastmod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

So, Workbench doesn't handle the "ON UPDATE CURRENT_TIMESTAMP" at all, right?
[30 May 2008 7:18] Bruno Baketaric
Screenshot reg. ON UPDATE CURRENT_TIMESTAMP

Attachment: OUCT.png (image/png, text), 12.66 KiB.

[5 Jun 2008 18:56] Frank Wong
I am using Workbench 5.0.22 OSS and I was able to recreate this problem.

1) Create a schema in WB a timestamp column, lmod, default value "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP".  Reason for adding the ON UPDATE statement into the default value is that there is no other place to put it.

2) Export -> Forward Engineer SQL CREATE Script

3) Script reads as follows for that table
-- -----------------------------------------------------
-- Table `mydb`.`transaction`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`transaction` (
  `idtransaction` INT(11) NOT NULL AUTO_INCREMENT ,
  `amount` DOUBLE(5,1) NULL ,
  `sit_idsit` INT(11) NOT NULL ,
  `start_time` TIME NULL ,
  `end_time` TIME NULL ,
  `weekend_bonus` INT(1) NULL ,
  `feeding_bonus` INT(1) NULL ,
  `transaction_date` DATE NULL ,
  `lmod` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`idtransaction`) ,
  INDEX fk_transaction_sit (`sit_idsit` ASC) ,
  CONSTRAINT `fk_transaction_sit`
    FOREIGN KEY (`sit_idsit` )
    REFERENCES `mydb`.`sit` (`idsit` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

4) Create new file in WB

5) Import -> Reverse Engineer MYSQL Create Script using the above output

6) Export -> Forward Engineer SQL CREATE Script

7) Script reads as follows for that table
-- -----------------------------------------------------
-- Table `mydb`.`transaction`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`transaction` (
  `idtransaction` INT(11) NOT NULL AUTO_INCREMENT ,
  `amount` DOUBLE NULL DEFAULT NULL ,
  `sit_idsit` INT(11) NOT NULL ,
  `start_time` TIME NULL DEFAULT NULL ,
  `end_time` TIME NULL DEFAULT NULL ,
  `weekend_bonus` INT(1) NULL DEFAULT NULL ,
  `feeding_bonus` INT(1) NULL DEFAULT NULL ,
  `transaction_date` DATE NULL DEFAULT NULL ,
  `lmod` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`idtransaction`) ,
  INDEX fk_transaction_sit (`sit_idsit` ASC) ,
  CONSTRAINT `fk_transaction_sit`
    FOREIGN KEY (`sit_idsit` )
    REFERENCES `mydb`.`sit` (`idsit` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

NOTE: The "ON UPDATE CURRENT_TIMESTAMP" is gone.

SIDE NOTE: Notice the precision of the "amount" column DOUBLE type is gone also.  But that belongs on another bug report.
[18 Jun 2008 20:39] Ken Zo
I confirm this bug as well.
1) There is no way in the table editor to specify "on update current_timestamp" for a column.
2) If you reverse engineer an existing database that does have "on update current_timestamp" for a column, that information is silently discarded by Workbench.  It neither appears in the model (because the table editor has nowhere for it to go), nor does it appear when you forward engineer the database.  It just discards it.

This is a very serious bug.
[28 Jun 2008 11:35] Bruno Baketaric
Could somebody please work on this! It's preventing us from using the workbench.
[5 Aug 2008 11:44] Johannes Taxacher
i'm sorry that this one got lost in the can't repeat status. actually its a problem already described in this report http://bugs.mysql.com/bug.php?id=36489 and we are fixing this for the upcoming release (5.0.24)
[5 Aug 2008 11:50] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=38565 has been marked as duplicate of this one.
[8 Sep 2008 19:47] Ken Zo
This bug is still present as of 5.0.24.  This bug is marked as "duplicate" of #38565, which is marked as duplicate of this one.  Could somebody unmark #38565 so it can be worked on?
[18 Aug 2009 16:11] Bruno Baketaric
This Bug is still present as of 5.1.17.

Still no possibility to set/define a "ON UPDATE CURRENT_TIMESTAMP" for TIMESTAMP Fields.
[20 Aug 2009 10:24] Johannes Taxacher
The "ON UPDATE CURRENT_TIMESTAMP" property of TIMESTAMP columns is currently handled by entering the string into the "Default" field in the column editor. For columns which are reverse engineerd from scripts/databases you'll also find that string in the default field. You can specify it with or without providing a default value.
I have tested several import/exports (like the examples mentioned in the previous comments of this report) and it was all working as expected. Could you post a step by step example including sql-code and mwb file that still shows this behaviour for you?
[20 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 Feb 2011 14:18] Louis-Sébastien Gac-Artigas
So what's happen with this real PROBLEM ???
[6 Feb 2011 12:17] Valeriy Kravchuk
I wonder if there is a problem like this with recent version, 5.2.31. See http://bugs.mysql.com/bug.php?id=52350.
[7 Feb 2011 8:44] Bruno Baketaric
The Bug is still present as of 5.2.31a:

This is also a duplocate of #52350, which was closed, but isn't fixed.

Here's the query WB generates:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `_auth_devel`.`realms` ADD COLUMN `lmod` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRRENT_TIMESTAMP  AFTER `realm` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

And this is the error Message:

Executing SQL script in server

ERROR: Error 1064: 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 'CURRRENT_TIMESTAMP  AFTER `realm`' at line 1

ALTER TABLE `_auth_devel`.`realms` ADD COLUMN `lmod` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRRENT_TIMESTAMP  AFTER `realm` 

SQL script execution finished: statements: 3 succeeded, 1 failed
[8 Feb 2011 7:52] Valeriy Kravchuk
That triple R in the following clause in the last example:

CURRRENT_TIMESTAMP

is it generated by Workbench or just a user misprint?
[8 Feb 2011 9:04] Bruno Baketaric
Ups! I'm deeply sorry. Without the Typo, everything works as expected.
[8 Feb 2011 15:42] Valeriy Kravchuk
Not repeatable with a recent version, 5.2.31a.