[Maria-discuss] ALTER vs MODIFY to set default on a column
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
I don't know if it's a problem to mariadb/mysql forum, but, thinking as a product at wordpress, I would check if a know error occurred and retry with a different query just to be sure it is a real error and not a version problem It's like make a software compatible with postgresql+sql+oracle+mysql+mssql, you should check what query should be used to be sure it's right
I'm not sure I follow? If I run the first ALTER query directly on the server, with WordPress completely out of the mix, it throws that same error. Is that what you were getting at? I've found similar questions on stack overflow and the like, but the answer is always just "you're doing it wrong, use MODIFY/CHANGE instead" with no explanation of why it's wrong. I can't find any documentation or bug reports that say WHY the behavior changed, or why it was ever different, and that's what I'm trying to figure out.
On Thu, Aug 12, 2021 at 4:59 PM Roberto Spadim roberto@spadim.com.br wrote:
I don't know if it's a problem to mariadb/mysql forum, but, thinking as a product at wordpress, I would check if a know error occurred and retry with a different query just to be sure it is a real error and not a version problem It's like make a software compatible with postgresql+sql+oracle+mysql+mssql, you should check what query should be used to be sure it's right
I see that it can be a bug, as a workaround execute the first query, if you got a error, try the second/third that should do the trick, while reporting it as a bug to mariadb jira tracking / mysql error tracking Reading docs i didn't found error too v5.7 | ALTER [COLUMN] *col_name* { SET DEFAULT {*literal* | (*expr*)} | DROP DEFAULT } v8.0 | ALTER [COLUMN] *col_name* { SET DEFAULT {*literal* | (*expr*)} | SET { VISIBLE | INVISIBLE} | DROP DEFAULT } v10.5 | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
Hi, Shane!
On Aug 12, Shane Bishop wrote:
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
Note, the error is "near CURRENT_TIMESTAMP", that is "ALTER updated SET DEFAULT" was fine.
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).
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Thanks for the additional info Sergei, I might file a bug just to see what happens for MySQL 5.7, but since MariaDB 10.1 is EOL, I'll leave that lie :) In the meantime, I've had some ideas on how to work around this a bit better, thanks Roberto also for your suggestions. I think WP is already catching the error to prevent PHP from falling over completely, so that helps.
Best, Shane
On Fri, Aug 13, 2021 at 1:59 AM Sergei Golubchik serg@mariadb.org wrote:
Hi, Shane!
On Aug 12, Shane Bishop wrote:
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
Note, the error is "near CURRENT_TIMESTAMP", that is "ALTER updated SET DEFAULT" was fine.
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).
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (3)
-
Roberto Spadim -
Sergei Golubchik -
Shane Bishop