That was a brand new table change in 5.7 to make sure our custom tables have primary keys instead of just unique indexes.
Can you check to see what the current state of the table is (on any of the sites), and what indexes it has? Most notably, checking to see if it still has the UNIQUE “id” index AND the new PRIMARY key.
Thread Starter
pepe
(@pputzer)
I’ve checked. The tables where I’ve got an error message only have the primary key, but no index. The other sites have only the unique index, but no primary key.
I’ll check into this further when I get back on the 28th. Do let me know if you discover anything new in the meantime.
Still catching up from vacation, will dig back into this on Monday hopefully!
Please grab the latest code from GitHub and let me know if that resolves things for you: https://docs.ewww.io/article/73-update-from-github
The fix was released in 5.8, please do let me know if you are still having issues!
Thread Starter
pepe
(@pputzer)
Mhm, I just got a similar error again with EWWW 5.8.2:
WordPress database error Incorrect table definition; there can be only one auto column and it must be defined as a key for query ALTER TABLE <prefix>_ewwwio_images DROP INDEX id made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, ewww_image_optimizer_admin_init, ewww_image_optimizer_upgrade, ewww_image_optimizer_install_table
and
WordPress database error Incorrect index name 'id' for query ALTER TABLE <prefix>_ewwwio_images ADD PRIMARY KEY(id) made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, ewww_image_optimizer_admin_init, ewww_image_optimizer_upgrade, ewww_image_optimizer_install_table
Can you find out what version of MySQL (or Percona, or MariaDB) your site is using? Note, we want to know what version the server is running, rather than the client library version in PHP.
I’m starting to wonder if there’s some versions that don’t return what I’m expecting for some of the queries where we check for existence of the primary key.
Thread Starter
pepe
(@pputzer)
Hi @nosilver4u, this server is running MariaDB 10.4.17.
Spent yesterday upgrading my dev box, but it’s still on 10.3 with no errors for multi-site.
But I know MariaDB has repos for 10.4, so I’ll try that out next week sometime just to see if that’s connected. I suspect I’m still just missing something in the logic here, but can’t pinpoint it yet.
Can you also check to see what your sql_mode is set to on the server?
Thread Starter
pepe
(@pputzer)
@nosilver4u: sql_mode is set to the default (STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION).
The error is indeed specific to MariaDB 10.4: https://jira.mariadb.org/browse/MDEV-19598
I’m looking to see if there is a workaround, but upgrading to MariaDB 10.5.2 or better fixes it.
Rather than trying to completely rewrite the logic, and risk complete breakage, we’ll just suppress the PRIMARY KEY update for MariaDB 10.4. It’s not a huge deal, as the folks that need the primary key are (usually) running MySQL 8.x.
I’ve upgraded my own dev site to 10.5 now, and confirmed it works as expected. Fix will be in EWWW IO 5.9 (if not sooner).
