MyISAM Tables with WP-CLI

The high-performance keys rely on InnoDB features. So, it’s not possible for you to enable the high-performance keys until you upgrade the tables where the plugin puts those keys.

With the fix I just pushed out you can do this in wp-cli to upgrade all your tables to InnoDB, then enable the high-perf keys on the tables we handle. This will do all your tables. If they are already done, it will still work, but do nothing.

sudo -u www-data wp index-mysql upgrade --all
sudo -u www-data wp index-mysql enable --all

The fix is to avoid returning an error status from wpcli when it finds no tables need upgrading or enabling.

If that doesn’t address your question, I guess I don’t understand what you need. Can you explain a little more fully?

Okay, I read more and created a hybrid script that’s working. It may help someone else. I was not able to do user switching to “www-data” because my setup has each account user name running rather than “www-data” and those individual users can’t run WP-CLI. It’s easier for me to just run root.

#!/bin/bash

declare -a arr=(
"account1"
"account2"
                )

for i in "${arr[@]}"
do
   echo _______________________________________________________________________$
   echo "**** $i ****"
   cd /home/"$i"/www
   echo "SET sql_mode ='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';" > db_optimize.sql #stops default date error
   wp db query "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME, ' ENGINE=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM'" --skip-column-names >> db_optimize.sql
   wp db query < db_optimize.sql --allow-root
   rm db_optimize.sql

   wp plugin install index-wp-mysql-for-speed --allow-root --activate
   wp index-mysql enable --all --allow-root
   echo _______________________________________________________________________$
   echo ""
   echo ""
done

This converts any tables with the MyISAM engine over to InnoDB, while ignoring the zero date errors. I received expected output:

Index WP MySQL For Speed 1.4.16
Versions  Plugin:1.4.16 MySQL:5.7.44 WordPress:5.8.9 WordPress database:49752 php:7.4.33
enable wp_comments complete. (2 MySQL statements, 0.06s)
enable wp_options complete. (2 MySQL statements, 0.06s)
enable wp_postmeta complete. (2 MySQL statements, 0.16s)
enable wp_posts complete. (2 MySQL statements, 0.04s)
enable wp_termmeta complete. (2 MySQL statements, 0.03s)
enable wp_usermeta complete. (2 MySQL statements, 0.03s)
enable wp_users complete. (2 MySQL statements, 0.01s)

I apologize for not refreshing the page before posting my reply. Your “upgrade” command is exactly what I had hoped for. You’re an enormous help!

Happy to help.