Handle empty $wpdb->dbname in wp_get_table_names for drop-ins like HyperDB and LudicrousDB - #6432

Patch Testing Report

Patch tested: #6432 at 5cfd391, compared with main at a1735b0

Environment

• WordPress: 7.1.3, single site, then the same site converted to a subdirectory multisite with a second site (/two/)
• PHP: 8.5.9 CLI
• Server: n/a (WP-CLI only)
• Database: MariaDB 11.8.9
• Browser: n/a (command line test)
• OS: Alpine Linux 3.24.1 (Docker)
• Theme: Twenty Twenty-Five 1.5 (default)
• MU Plugins: None
• Plugins (drop-ins, one at a time; both sample configs point to the same database, one writer and one reader):
  • HyperDB 1.9 from wordpress.org: unmodified db.php and the sample db-config.php
  • LudicrousDB 5.3.1 from GitHub: unmodified drop-ins/db.php and drop-ins/db-config.php, plugin folder in wp-content/plugins/ludicrousdb
• WP-CLI: search-replace-command main at 5a62210, db-command v3.0.1. To switch the framework I ran composer install in a search-replace-command clone, mounted a wp-cli clone over vendor/wp-cli/wp-cli, and checked out main or this PR in it.
• WP_DEBUG: false for HyperDB, true for LudicrousDB. HyperDB 1.9 still loads wp-includes/wp-db.php. With WP_DEBUG true on WordPress 7.1.3 that is a fatal error before any command runs (Call to undefined function wp_kses() from _deprecated_file()).

Steps taken

1. On main, both drop-ins leave $wpdb->dbname empty, and SELECT DATABASE() returns the right name:

$ wp eval 'global $wpdb; echo "class=" . get_class($wpdb) . " dbname=" . var_export($wpdb->dbname, true) . " DATABASE()=" . var_export($wpdb->get_var("SELECT DATABASE()"), true) . "\n";'
class=hyperdb dbname=NULL DATABASE()='wp_cli_test_pr6432'

class=LudicrousDB dbname=NULL DATABASE()='wp_cli_test_pr6432'

2. On main, wp search-replace finds no tables and still exits 0. The error is printed twice (table list, then the views lookup):

$ wp search-replace foozle barzle --dry-run
WordPress database error Unknown column 'Tables_in_' in 'WHERE' for query SHOW TABLES WHERE `Tables_in_` IN ('wp_users', 'wp_usermeta', 'wp_posts', 'wp_comments', 'wp_links', 'wp_options', 'wp_postmeta', 'wp_terms', 'wp_term_taxonomy', 'wp_term_relationships', 'wp_termmeta', 'wp_commentmeta') made by WP_CLI\Utils\wp_get_table_names
WordPress database error Unknown column 'Tables_in_' in 'WHERE' for query SHOW TABLES WHERE `Tables_in_` IN ('wp_users', 'wp_usermeta', 'wp_posts', 'wp_comments', 'wp_links', 'wp_options', 'wp_postmeta', 'wp_terms', 'wp_term_taxonomy', 'wp_term_relationships', 'wp_termmeta', 'wp_commentmeta') made by WP_CLI\Utils\wp_get_table_names
Success: 0 replacements to be made.

3. Ran the commands below on main without a drop-in, on main with each drop-in, and on this PR with each drop-in. foozle is in one post (and its revision) and in blogdescription. The database also has a custom table wp_custom, a table without the prefix other_table, and a view wp_view_posts. "DB error" means the Unknown column 'Tables_in_' error above.

Single site:

Multisite:

HyperDB and LudicrousDB gave the same results in every row. With this PR, the full output of every command above, with either drop-in, is identical to main without a drop-in. I compared the saved outputs with diff, and only the class name in the first wp eval line differs.

4. On a normal site without a drop-in (WP_DEBUG true), the output of this PR is identical to main for all the single site commands, with no PHP warnings.
5. ✅ Patch is solving the problem: it fixes wp-cli/search-replace-command#207 with HyperDB and LudicrousDB, for every caller of wp_get_table_names() I tried, on single site and multisite.
6. ❌ Patch is failing: on a normal site with lower_case_table_names=1 and an uppercase prefix, --all-tables-with-prefix returns no tables (see Additional Notes).

Expected result

• With a drop-in that leaves $wpdb->dbname empty, the commands find the same tables as without a drop-in, and nothing changes for normal sites.

Additional Notes

• Regression: --all-tables-with-prefix returns nothing with lower_case_table_names=1 and an uppercase prefix. This is on a normal site, no drop-in.

  In the --all-tables-with-prefix branch, $dbname is only set in the else path. On the plain SHOW TABLES LIKE path it is never set, and $tables_sql is never empty at that point. So ! empty( $tables_sql ) && empty( $dbname ) is always true there, and the new PHP filter runs on every --all-tables-with-prefix call, not only when the database name is missing.

  Usually that only repeats what LIKE already did. But SHOW TABLES LIKE is case-insensitive when lower_case_table_names is 1 (the default on Windows), and strpos() is not. With $table_prefix = 'WP_' the server stores the tables as wp_posts and so on, and the filter removes all of them.

  Steps: start MariaDB with --lower-case-table-names=1, run wp config create ... --dbprefix=WP_, then wp core install ..., then:

  $ wp db tables --all-tables-with-prefix --format=csv
  

  main:

  wp_commentmeta,wp_comments,wp_links,wp_options,wp_postmeta,wp_posts,wp_term_relationships,wp_term_taxonomy,wp_termmeta,wp_terms,wp_usermeta,wp_users
  

  This PR: an empty line.

  $ wp search-replace http://lctn.test http://lctn.example --all-tables-with-prefix --dry-run --format=count
  

  main: 8. This PR: 0.

  wp db clean uses all-tables-with-prefix too. On this PR, wp db clean --yes prints Success: Tables dropped. and all 12 tables are still there.

  The default scope is not affected: wp db tables lists 12 tables and wp search-replace without flags finds 8 replacements on both.

  About the first Copilot comment: no notice is raised, because empty() does not warn on an undefined variable (I ran with WP_DEBUG true, and PHPStan passes). But that missing $dbname is the cause of this problem.

• Minor: order of the last fallback. The SHOW TABLES plus array_intersect() fallback only runs when $wpdb->dbname is empty and SELECT DATABASE() returns nothing. With both real drop-ins SELECT DATABASE() returned the name, so they never reach it. On a connection with no database selected, SHOW TABLES fails as well:

  $ mariadb -h wpcli-db -u wp_cli_test -ppassword1 --skip-ssl -e "SELECT DATABASE(); SHOW TABLES;"
  DATABASE()
  NULL
  --------------
  SHOW TABLES
  --------------
  
  ERROR 1046 (3D000) at line 1: No database selected
  

  To reach it anyway, I used a test-only db.php that extends wpdb, sets dbname to '' and returns null for SELECT DATABASE(). The tables were right, but in $wp_tables order instead of sorted:

  $ wp db tables --format=csv
  wp_users,wp_usermeta,wp_posts,wp_comments,wp_links,wp_options,wp_postmeta,wp_terms,wp_term_taxonomy,wp_term_relationships,wp_termmeta,wp_commentmeta,wp_blogs,wp_blogmeta,wp_signups,wp_site,wp_sitemeta,wp_registration_log
  

  The comment in the other branch says the sorted order is a BC change since 1.5.0, and the legacy SQLite filter below uses array_intersect( $tables, $wp_tables ). Swapping the arguments keeps the sorted order. Since this fallback can only help a drop-in that answers those two queries from different connections, removing it would also be fine.

• Other bot comments:

  • Copilot, narrowing the fallback to SHOW TABLES LIKE '<prefix>%': I would not do that. $wpdb->tables() returns CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE when they are defined, and those do not have to use the prefix.
  • Copilot, str_starts_with(): WP-CLI supports PHP 7.2.24+, and CI tests WordPress 4.9, which has no polyfill for it. strpos() is right here.
  • CodeRabbit: no actionable comments. It notes the drop-in behaviour was not executed, which is what the runs above cover.

Support Content

• Suggested change for both points above:

@@ -504,19 +504,23 @@ function wp_get_table_names( $args, $assoc_args = [] ) {
 		$tables = $wpdb->get_col( $tables_sql, 0 );
 
 	} elseif ( get_flag_value( $assoc_args, 'all-tables-with-prefix' ) ) {
+		$filter_by_prefix = false;
 		if ( empty( $tables_sql ) ) {
 			$tables_sql = $wpdb->prepare( 'SHOW TABLES LIKE %s', esc_like( $wpdb->get_blog_prefix() ) . '%' );
 		} else {
 			$dbname = ! empty( $wpdb->dbname ) ? $wpdb->dbname : (string) $wpdb->get_var( 'SELECT DATABASE()' );
 			if ( ! empty( $dbname ) ) {
 				$tables_sql .= sprintf( " AND %s LIKE '%s'", esc_sql_ident( 'Tables_in_' . $dbname ), esc_like( $wpdb->get_blog_prefix() ) . '%' );
+			} else {
+				$filter_by_prefix = true;
 			}
 		}
 
 		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared, see above.
 		$tables = $wpdb->get_col( $tables_sql, 0 );
 
-		if ( ! empty( $tables_sql ) && empty( $dbname ) ) {
+		// Only filter in PHP when the prefix condition could not be added to the query.
+		if ( $filter_by_prefix ) {
 			$blog_prefix = $wpdb->get_blog_prefix();
 			$tables      = array_values(
 				array_filter(
@@ -558,7 +562,7 @@ function wp_get_table_names( $args, $assoc_args = [] ) {
 			// Fallback when database name cannot be determined (e.g. drop-ins like HyperDB or LudicrousDB).
 			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is safe.
 			$all_tables = (array) $wpdb->get_col( 'SHOW TABLES', 0 );
-			$tables     = array_values( array_intersect( $wp_tables, $all_tables ) );
+			$tables     = array_values( array_intersect( $all_tables, $wp_tables ) );
 		} else {
 			// Note: BC change 1.5.0, tables are sorted (via TABLES view).
 			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- uses esc_sql_ident() and $wpdb->_escape().

• Suggested test. The PR has no test, and Codecov reports 2 lines not covered. This scenario for features/utils-wp.feature adds a db.php drop-in that clears $wpdb->dbname, like HyperDB and LudicrousDB do. It needs @require-mysql because SQLite already uses wp-content/db.php.

  # `wp db query` does not yet work on SQLite, and SQLite already uses wp-content/db.php.
  @require-mysql
  Scenario: Get WP table names when a db.php drop-in leaves $wpdb->dbname empty
    Given a WP installation
    And I run `wp db query "CREATE TABLE wp_xx_posts ( id int );"`
    And I run `wp db query "CREATE VIEW wp_posts_view AS ( SELECT ID from wp_posts );"`
    And a wp-content/db.php file:
      """
      <?php
      // Like HyperDB and LudicrousDB, this drop-in does not set $wpdb->dbname.
      class Empty_Dbname_DB extends wpdb {
          public function __construct( $dbuser, $dbpassword, $dbname, $dbhost ) {
              parent::__construct( $dbuser, $dbpassword, $dbname, $dbhost );
              $this->dbname = '';
          }
      }
      $wpdb = new Empty_Dbname_DB( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );
      """
    And a table_names.php file:
      """
      <?php
      WP_CLI::add_command( 'get_table_names', function ( $args, $assoc_args ) {
          echo implode( PHP_EOL, WP_CLI\Utils\wp_get_table_names( $args, $assoc_args ) ) . PHP_EOL;
      } );
      """

    When I run `wp --require=table_names.php get_table_names`
    Then STDOUT should be:
      """
      wp_commentmeta
      wp_comments
      wp_links
      wp_options
      wp_postmeta
      wp_posts
      wp_term_relationships
      wp_term_taxonomy
      wp_termmeta
      wp_terms
      wp_usermeta
      wp_users
      """

    When I run `wp --require=table_names.php get_table_names 'wp_post*'`
    Then STDOUT should be:
      """
      wp_postmeta
      wp_posts
      """

    When I run `wp --require=table_names.php get_table_names --all-tables-with-prefix --base-tables-only 'wp_*posts'`
    Then STDOUT should be:
      """
      wp_posts
      wp_xx_posts
      """

    When I run `wp --require=table_names.php get_table_names --all-tables-with-prefix --views-only`
    Then STDOUT should be:
      """
      wp_posts_view
      """

• Test results on MariaDB 11.8.9. I ran these scenarios with vendor/bin/behat directly, because the tags skip @require-mysql on MariaDB:

PHPUnit gives the same result on all three: OK, but there were issues! with 1 warning and 5 skipped tests, so nothing changes there.

vendor/bin/run-behat-tests features/utils-wp.feature (9 scenarios after the tag filter) passes on main and this PR on MariaDB, and with the suggested change on SQLite. vendor/bin/phpcs php/utils-wp.php and composer phpstan pass with the suggested change.

With the suggested change, the lower_case_table_names=1 site lists the 12 tables and finds 8 replacements again, wp db clean --yes drops the tables again, the fallback returns sorted tables, and the HyperDB and LudicrousDB multisite runs are still identical to main without a drop-in.