Do you know what version of MariaDB you are using? I think it shows that on the WP Site Health report if you don’t know it offhand.
Drat, so that’s probably the MariaDB bug resurfacing. I’ll dig into it again and let you now what I find out.
So, I finally had a workaround in version 6.0 that bypassed the bug in 10.4. But then realized the PRIMARY KEY bit of our table creation syntax was incorrect (for the dbDelta() function). This caused issues for MySQL 8.0, so I corrected that in 6.0.1, but the behavior of the core dbDelta() function then re-triggers the MariaDB 10.4 bug.
A potential fix is to give the PRIMARY KEY a name, which I’ve added to the code on GitHub if you could please test it and see how that works for you: https://docs.ewww.io/article/73-update-from-github
Thread Starter
pepe
(@pputzer)
Unfortunately, 6.0.1.1 has not helped. It looks like dbDelta() ignores the primary key name:
WordPress database error Incorrect index name 'id' for query ALTER TABLE <prefix>_ewwwio_images ADD PRIMARY KEY ('id')
made by
...
ewww_image_optimizer_admin_init,
ewww_image_optimizer_upgrade,
ewww_image_optimizer_install_table,
dbDelta
Edit: So I looked it up, it’s on purpose 😩:
// Escape the index name with backticks. An index for a primary key has no name.
$index_name = ( 'PRIMARY KEY' === $index_type ) ? '' : '"' . strtolower( $index_matches['index_name'] ) . '"';
-
This reply was modified 5 years, 3 months ago by
pepe. Reason: Add dbDelta excerpt
Yeah, I just got done testing it on a fresh install and noticed the same… drat!
I’ll see if I can come up with anything else to get around the MariaDB bug.
Alright, I’ve got a different approach this time, similar to the one that was working in 6.0, but without the breakage for MySQL 8. Grab a fresh copy and let me know if that works for you: https://docs.ewww.io/article/73-update-from-github
I tested it on a Cloudways server with MariaDB 10.4.17 and didn’t get any errors in the logs, and the workaround appears to have functioned correctly. Still would be great to get confirmation on your end though.
Thread Starter
pepe
(@pputzer)
Looks good. There were a lot of ALTER TABLE queries on each site after the update (for each column), but I presume that was because they didn’t run earlier due to the error?
If it’s the same ALTER statements as I get on my dev site, those are because MariaDB, and pretty much every version of MySQL prior to 8.0.19, display column widths for a “SHOW TABLES” query, even if none are defined.
So WordPress tries to alter the columns to have no “display widths”, but doesn’t end up actually doing anything.
Thanks for confirming the fix!
