maria-discuss team mailing list archive

[Shane Bishop <shanebishop@xxxxxxxxx>]

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