Hi folks,

Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so I need to make this as performant as possible.

I originally used a query like this: ALTER TABLE wp_ewwwio_images ALTER updated SET DEFAULT CURRENT_TIMESTAMP;

This was speedy, and worked a treat, but now I'm finding it doesn't work on all MySQL servers. Notably, we've run into trouble with sites running MariaDB 10.1 and MySQL 5.7, where it says something like this: 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

Per https://dev.mysql.com/doc/refman/5.7/en/alter-table.html the syntax looks fine. At least, the syntax in the manual is identical to that for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

And ditto for MariaDB of course: https://mariadb.com/kb/en/alter-table/

The alternative is to use this: ALTER TABLE wp_ewwwio_images MODIFY updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

This works fine for the affected server versions, but is much slower; ALTER is near instant, MODIFY takes 3-4 seconds on a table with 260k records on SSDs. Who knows what it looks like if some poor sap is on spinning disks still...

So I know how to work around said "issue", but what I really want to know is, what IS the actual problem here? Why don't older MySQL (and MariaDB) versions accept the ALTER syntax when the docs are identical?

Notably, 10.5 works fine, and I think 10.4 was okay also, don't have any sites to test older versions myself.

Thanks!

Shane Bishop