query shouldn't require mysql/mariadb cli tools

[dd32]

When mysql and mariadb CLI tools are unavailable, wp db query should fallback to using WPDB.

This is particularly important when running with drop-in database engines, and multi-server/partitioned database drivers such as HyperDB.

[swissspidy]

Most of the db commands, including wp db query, also work when WordPress is not installed yet but the database is around, hence the current requirement of the binaries being installed.

We could try falling back to loading WordPress, either implicitly or via an explicit flag such as --load-wordpress that would call Runner::load_wordpress(), so wpdb would be available. Or some custom loading such as we currently do for SQLite in maybe_load_sqlite_dropin(), which loads just a few files.

For HyperDB, see also:

• WordPress database error Unknown column 'Tables_in_' during search-replace search-replace-command#207
• Add support for database sharding ideas#181

[chubes4]

Studio 1.14.0-dev22 reproduces this on a WordPress site using the SQLite database integration:

$ studio wp db query "SELECT 1;"
Fatal error: Uncaught Error: Undefined constant "DB_HOST"
.../vendor/wp-cli/db-command/src/DB_Command.php:1798

wp db query runs at after_wp_config_load, calls get_sql_mode_query(), then assumes DB_HOST while constructing a MySQL command. The SQLite-backed site works normally; only this command fails. This is the same command-ownership gap described in closed #234, and supports the load-WordPress / SQLite-mode direction discussed here.

No data was changed during reproduction.

[apermo]

I just ran into this on a live project, so here is a real-world data point.

Our server admins keep the webserver image minimal and simply do not install the mysql/mariadb client binaries. WordPress itself does not care, it talks to the database through mysqli anyway. But every wp db command that shells out is dead, and wp db query was the one that hurt.

The part that cost me the most time was the error message. Utils\get_mysql_binary_path() returns an empty string when it finds nothing, and nobody checks that, so the command ends up as /usr/bin/env --no-defaults --no-auto-rehash and you get this:

/usr/bin/env: illegal option -- n

That is not a great hint for "the mysql client is missing". Even without any fallback, a clear error here would already help a lot.

What we did instead was route everything through $wpdb. This is from a multisite domain migration script, anonymised:

# `wp db query` needs the mysql client binary, which the image does not ship,
# so the statements go through WordPress' own $wpdb instead.
SQL="UPDATE \`wp_2_options\` SET option_value='https://project.tld/blog' WHERE option_name='home';" \
  wp eval --skip-plugins --skip-themes '
    global $wpdb;
    $statements = array_filter( array_map( "trim", explode( "\n", (string) getenv( "SQL" ) ) ) );
    foreach ( $statements as $statement ) {
      if ( false === $wpdb->query( $statement ) ) {
        WP_CLI::error( "failed: {$statement}: {$wpdb->last_error}" );
      }
    }
  '

It works, but passing SQL through an env var into an inline PHP snippet to survive two layers of quoting is not something I want in a deploy script.

On the design question from your first comment: both options work for us. An automatic fallback when the binary is missing, or an explicit flag like --use-php-for-db. I have no preference, the flag is arguably more predictable for CI, the automatic version means nobody has to change their scripts.

One thing I would add to the scope: db check, db optimize and db repair hit the same wall through mysqlcheck, and those three map onto plain CHECK TABLE, OPTIMIZE TABLE and REPAIR TABLE SQL. Same images, same problem, and no dump format to reimplement. db export is the hard one, I would leave that alone.

@swissspidy I saw #320 and left a review there. Happy to help with this, whether that means testing on our setup, writing the missing Behat scenarios, or picking up the branch in a fork and pushing the fixes. Just say what is most useful.

[swissspidy]

Thanks for sharing.

That error message is definitely not ideal, something we should fix either way.

I'll take another look at #320, though I have to say in your case I'd probably simply recommend installing the missing binary.

db-command was never intended to go through wpdb originally, so #320 creates a lot of complexity and discrepancy. I'm still on the fence about it.