MySQL Bugs: #37071: Workbench and "on update" clause on timestamp columns

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

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.

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;

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?

Screenshot reg. ON UPDATE CURRENT_TIMESTAMP

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

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.

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.

Could somebody please work on this! It's preventing us from using the workbench.

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)

Bug: http://bugs.mysql.com/bug.php?id=38565 has been marked as duplicate of this one.

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?

This Bug is still present as of 5.1.17.

Still no possibility to set/define a "ON UPDATE CURRENT_TIMESTAMP" for TIMESTAMP Fields.

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?

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".

So what's happen with this real PROBLEM ???

I wonder if there is a problem like this with recent version, 5.2.31. See http://bugs.mysql.com/bug.php?id=52350.

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

That triple R in the following clause in the last example:

CURRRENT_TIMESTAMP

is it generated by Workbench or just a user misprint?

Ups! I'm deeply sorry. Without the Typo, everything works as expected.

Not repeatable with a recent version, 5.2.31a.