Error when try to set timestamp field to CURRENT_TIMESTAMP() on update

bansheekitty's profile image bansheekitty posted 7 months ago in General Permalink

I'm trying to add an 'updated_at' field to a table using the UI. The default should be either NULL (or CURRENT_TIMESTAMP() would be okay as well), with CURRENT_TIMESTAMP() on update. I'm getting the error:

/ SQL 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 '(CURRENT_TIMESTAMP()) AFTER created_by' at line 2 /

The ALTER code generated by HeidiSQL is: ALTER TABLE groups_members_new CHANGE COLUMN updated_at updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE (CURRENT_TIMESTAMP()) AFTER created_by;

bansheekitty's profile image bansheekitty posted 7 months ago Permalink

I'm using HeidiSQL version 12.6.0.6677 on Windows 11, with MySQL 8.0.31

ansgar's profile image ansgar posted 7 months ago Permalink

Please update to the latest build of HeidiSQL (Help > Check for updates). That syntax error on MySQL 8 should be fixed there.

bansheekitty's profile image bansheekitty posted 7 months ago Permalink

Thanks, but it's still giving me the same error after updating. This is the ALTER code generated:

ALTER TABLE `groups_members_new`
    CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE (CURRENT_TIMESTAMP()) AFTER `created_by`;

which is the same as before

bansheekitty's profile image bansheekitty posted 7 months ago Permalink

(I'm now on 12.5.0.6760)

ansgar's profile image ansgar posted 7 months ago Permalink

While MySQL v8.0.13+ expect parentheses around the expression, like so:

DEFAULT (CURRENT_TIMESTAMP())...

the ON UPDATE value must not be wrapped in parentheses, like so:

DEFAULT (CURRENT_TIMESTAMP()) ON UPDATE CURRENT_TIMESTAMP()

This is all (half) documented on https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

By the way, this was also reported in issue #1826

ansgar's profile image ansgar posted 7 months ago Permalink

Note I just pushed a modification to HeidiSQL, so the next build should fix the issue.

bansheekitty's profile image bansheekitty posted 7 months ago Permalink

That fixed it! Thank you so much!

croshad's profile image croshad posted 6 months ago Permalink

I'm still getting error on table copy, "SQL Error (1067): Invalid default value for 'updated'".

The line from the CREATE TABLE is "updated TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP", the apostrophe's are extra.

MySQL 8.0.15.

Please login to leave a reply, or register at first.