Why can't I use ALTER to set the default on a column?

Question

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 performance is important here.

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 version 8: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

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?

Answer 1

I was able to get a bit more insight from Sergei at MariaDB. Specifically, he noted that the error is with "CURRENT_TIMESTAMP", which is what I suspected also:

In MySQL before 8.0.13 and in MariaDB before 10.2.1 one can only use a signed number in ALTER ... SET DEFAULT.

This is arguably a bug. But it's unlikely that you'll get it fixed in MySQL 5.7 (and MariaDB 10.1 is beyond EOL already).

In other words, one could use the ALTER column SET DEFAULT syntax just fine if you were setting it to a specific value, like I did originally in setting the default to "1971-01-01 00:00:00". But if you want to set the default to CURRENT_TIMESTAMP on those older versions, you'd need to use the MODIFY/CHANGE syntax instead.

Based on the docs, that does indeed seem a bug, but if anyone has additional insight/references, feel free to share.