MySQL Bugs: #52350: cannot set ON UPDATE CURRENT

Bug #52350	cannot set ON UPDATE CURRENT_TIMESTAMP as default value
Submitted:	25 Mar 2010 0:19	Modified:	27 May 2010 9:49
Reporter:	Ari Awan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.2.16	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	current_timestamp, default value, timestamp

Description:
Cannot set ON UPDATE CURRENT_TIMESTAMP in model as default value. 
Also tried to update the model from the db, by doing synch model > update model, even though the db has ON UPDATE CURRENT_TIMESTAMP set, but it failed to update the model.  

for reference see Bug #37071, it is said that :
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.

But the above is not working. Here is the error log that I got:

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 'ON UPDATE CURRENT_TIMESTAMP' at line 1

ALTER TABLE `fsdb`.`request` CHANGE COLUMN `update_time` `update_time` TIMESTAMP NULL DEFAULT ON UPDATE CURRENT_TIMESTAMP

How to repeat:
Create a table and a field in model with Default column = ON UPDATE CURRENT_TIMESTAMP, then do Database > Synchronize Model > Update Source. The db will not have ON UPDATE CURRENT_TIMESTAMP property set.

Create a table and a field in db, set the ON UPDATE CURRENT_TIMESTAMP, test that its working, then do Database > Synchronize Model > Update Model. The model will not have ON UPDATE CURRENT_TIMESTAMP in "Default" field in the column editor.

problem is that parser inserts the keyword DEFAULT even if you only enter "ON UPDATE CURRENT_TIMESTAMP"
as a workaround, you could specify the default value of NULL (or a zero timestamp in case of a not-null field)

but i could not repeat the "reverse engineering doesn't pick it up from DB" problem. this part is woring fine here

I am sorry I dont understand how to implement the work around, if i specify the default property to NULL , how do I specify "ON UPDATE CURRENT_TIMESTAMP" in the model?

by entering following test into the default-field in column editor:
NULL ON UPDATE CURRENT_TIMESTAMP

or, if you event want to have the CURRENT-TIMESTAMP in the field when the data is first inserted you could write:
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

because the "on update ..." clause only gets activated on an actual update of the data, not on initial insert)

The first timestamp defined TIMESTAMP NOT NULL is automatically becoming a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRRENT_TIMESTAMP.

That works like that with any client that works directly with the MySQL server.  Problem is that WB does not work directly with the MySQL server - only when sync'ing or forward engineering the WB internal model is known by the server. 

But it could be a workaround the TIMESTAMP NOT NULL, forward engineer and reverse engineer.

Besides I disagree with the premises of this discussion: In my understanding  there is no DEFAULT-clause 'ON UPDATE CURRRENT_TIMESTAMP' and also no DEFAULT-clause 'CURRENT_TIMESTAMP ON UPDATE CURRRENT_TIMESTAMP'.  There is a DEFAULT-clause 'CURRRENT_TIMESTAMP' and a ON UPDATE-clause 'CURRRENT_TIMESTAMP'.  Two *different* spec's for that column and not one. 

The ON UPDATE-clause is special in the sense that it can only be used for TIMESTAMP types/columns and only the value 'CURRRENT_TIMESTAMP' can be specified for it.

Peter
(not a MySQL person)

fix confirmed in repository

An entry has been added to the 5.2.22 changelog:

It was not possible to set ON UPDATE CURRENT_TIMESTAMP as the default value for a column.

Just had the same problem with "ON UPDATE CURRENT_TIMESTAMP" getting an invalid SQL syntax error. Putting "NULL ON UPDATE CURRENT_TIMESTAMP" gave me an error that said invalid default value. Then I tried "0 ON UPDATE CURRENT_TIMESTAMP" and this worked.