MySQL Bugs: #106383: ALTER column SET DEFAULT syntax does not work with CURRENT

Bug #106383	ALTER column SET DEFAULT syntax does not work with CURRENT_TIMESTAMP
Submitted:	4 Feb 2022 18:46	Modified:	11 Jan 2023 15:17
Reporter:	Shane Bishop	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In my software I have an upgrade routine that needs to change the column default for a timestamp column to be CURRENT_TIMESTAMP.

I originally used this statement:
ALTER TABLE xy_ewwwio_images ALTER updated SET DEFAULT CURRENT_TIMESTAMP

And because I was using MariaDB for development, it just worked and I didn't think any more of it.
But once the software was deployed on servers with MySQL 5.7 or MariaDB 10.1, folks were getting errors like so:
"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’ at line 1"

The MariaDB folks confirmed that it had been fixed in 10.2, and at the time we thought it only affected MySQL 5.7 and 8.0.12 or older.
A recent update highlighted that this is not the case, and confirmed the bug persists on MySQL 8.0.27, 8.0.26, and at least one other (unidentified) 8.0.x.

The workaround is to run this query, but it is much slower than the ALTER syntax for some reason:
ALTER TABLE xy_ewwwio_images MODIFY updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

How to repeat:
Setup a table that has a timestamp column defaulting to any "static" value, like "1971-01-01 00:00:00".
Here was our original table creation syntax for reference:
CREATE TABLE xy_ewwwio_images (
                id int unsigned NOT NULL AUTO_INCREMENT,
                attachment_id bigint unsigned,
                gallery varchar(10),
                resize varchar(75),
                path text NOT NULL,
                converted text NOT NULL,
                results varchar(75) NOT NULL,
                image_size int unsigned,
                orig_size int unsigned,
                backup varchar(100),
                level int unsigned,
                pending tinyint NOT NULL DEFAULT 0,
                updates int unsigned,
                updated timestamp DEFAULT '1971-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
                trace blob,
                PRIMARY KEY  (id),
                KEY path (path(191)),
                KEY attachment_info (gallery(3),attachment_id)
        );

Then run this query, and any version of MySQL up through 8.0.27 should report an error:
ALTER TABLE xy_ewwwio_images ALTER updated SET DEFAULT CURRENT_TIMESTAMP

Suggested fix:
From my reading of the docs at https://dev.mysql.com/doc/refman/8.0/en/alter-table.html the above (ALTER) query should not throw an error, and should additionally be faster than the MODIFY query.

Hello Shane,

Thank you for the report and feedback.

regards,
Umesh

actually the correct 8.0 syntax is:

ALTER TABLE xy_ewwwio_images ALTER updated SET DEFAULT(CURRENT_TIMESTAMP);

Doesn't seem to work on 5.7 though,  so this is probably a documentation bug.

Take a look at the 5.7 sql grammar:

https://github.com/mysql/mysql-server/blob/mysql-5.7.39/sql/sql_yacc.yy#L8304

it can only do: ALTER opt_column field_ident SET DEFAULT signed_literal

Whereas the 8.0 grammar:
https://github.com/mysql/mysql-server/blob/mysql-8.0.30/sql/sql_yacc.yy#L7440

can do:

DEFAULT_SYM now_or_signed_literal
DEFAULT_SYM '(' expr ')'

Thus converting this to a docs bug.

Posted by developer:
 
Fixed in the MySQL 5.7.41 Reference Manual (not yet released).

Thank you for the report.