MySQL Bugs: #82225: MySQL Workbench does not support NOW(6) in triggers

Bug #82225	MySQL Workbench does not support NOW(6) in triggers
Submitted:	14 Jul 2016 6:13	Modified:	17 Aug 2016 10:45
Reporter:	Kuba Gasiorowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	6.3.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MICROSECOND, NOW, timestamp, trigger

Description:
MySQL Workbench  does not support NOW(6) in triggers.
The usage of NOW(6) is marked as error and such trigger is ignored when generating script via 'Forward Engineer SQL CREATE Script' export.
The only workaround is to use NOW() and process later the script to replace NOW() with NOW(6). The same occurs when using milliseconds granularity NOW(3). 

How to repeat:
1. Create new table TABLE1 with column of TIMESTAMP(6) type, for example:
  mod_timestamp TIMESTAMP(6) NULL,

2. Add the trigger AFTER UPDATE:
CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`TABLE1_AFTER_UPDATE` AFTER UPDATE ON `TABLE1` FOR EACH ROW
BEGIN
  SET NEW.mod_timestamp = NOW(6);
END

3. The red mark is now appearing on the line with 'SET NEW...' indicating syntax error.

4. When attempting to 'Export -> Forward Engineer SQL CREATE Script...' the trigger TABLE1_AFTER_UPDATE is ignored and neither counted in 'SQL Object Export Filter'/'Export MySQL Trigger Objects' nor present in generated script.

Hello Kuba,

Thank you for the report.
Verifying the issue for the syntax error seen for NOW(6) which isn't seen in SQL Editor. Btw, the reason trigger is missing from the script is because it it invalid i.e if you try to modify NEW value in after trigger then it would surely complain. On 5.7.13 I see

mysql> delimiter //
mysql> CREATE DEFINER = CURRENT_USER TRIGGER account_update AFTER UPDATE ON account FOR EACH ROW
    -> BEGIN
    ->   SET NEW.mod_timestamp = NOW(6);
    -> END//
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql> delimiter ;

Thanks,
Umesh

Screenshot..

Attachment: 82225_model.png (image/png, text), 34.52 KiB.

Screenshot..

Attachment: 82225_sqleditor.png (image/png, text), 42.15 KiB.

The additional time precision has been added in server 5.6.4. Did you set the version in your model to that value (or higher)?

MySQL Workbench strictly checks language features against the server it connects (or, in the absence of a server, the model version).

Setting the model DB version to 5.6.29 fixed the issue, indeed.
Case may be closed.

Great, thanks for letting us know it works for you now.