Fix --defaults flag issue in SQL mode discovery for db query commands by Copilot · Pull Request #2 · lkraav/db-command

Copilot · 2025-06-26T15:33:40Z

The --defaults flag was being incorrectly ignored during SQL mode discovery, causing commands like wp db query "SELECT 1;" --defaults to fail with MySQL connection errors.

Problem

When using the --defaults flag with wp db query, the command would fail during SQL mode discovery with an error like:

Error: Failed to get current SQL modes. Reason: '"mysql --no-defaults --no-auto-rehash --batch --skip-column-names --execute="SELECT' is not recognized as an internal or external command

Root Cause

The issue occurred because get_defaults_flag_string() modifies the $assoc_args array by reference, removing the 'defaults' key after processing it. This caused a sequence problem:

Main query command calls get_defaults_flag_string($assoc_args) → returns empty string (correct), removes 'defaults' key
SQL mode discovery calls get_sql_mode_query($assoc_args) → internally calls get_defaults_flag_string($assoc_args) again
Second call can't find 'defaults' key → incorrectly returns --no-defaults flag
SQL mode discovery query fails because it uses --no-defaults when user specified --defaults

Solution

Preserve the original $assoc_args before the first call to get_defaults_flag_string() and pass the original arguments to get_sql_mode_query():

// Preserve the original defaults flag value before get_defaults_flag_string modifies $assoc_args
$original_assoc_args_for_sql_mode = $assoc_args;

$command = sprintf(
    '/usr/bin/env %s%s --no-auto-rehash',
    $this->get_mysql_command(),
    $this->get_defaults_flag_string( $assoc_args )  // Modifies $assoc_args
);

// Use original args for SQL mode discovery
$assoc_args['execute'] = $this->get_sql_mode_query( $original_assoc_args_for_sql_mode ) . $assoc_args['execute'];

Changes Made

Fixed the issue in three methods that had the same pattern: query(), import(), and run_query()
Added test case to verify SQL mode discovery respects the --defaults flag
Minimal change: only 22 insertions, 3 deletions across 2 files

Testing

Added a new test scenario in features/db-query.feature:

Scenario: SQL mode discovery respects --defaults flag
  Given a WP install

  When I try `wp db query "SELECT 1;" --defaults --debug`
  Then STDERR should match #Running shell command: /usr/bin/env (mysql|mariadb) --no-auto-rehash#
  And STDERR should not match #Running shell command: /usr/bin/env (mysql|mariadb) --no-defaults#

Fixes #1.

Warning

Firewall rules blocked me from connecting to one or more addresses

I tried to connect to the following addresses, but was blocked by firewall rules:

https://api.github.com/repos/Behat/Gherkin/zipball/34c9b59c59355a7b4c53b9f041c8dbd1c8acc3b4
- Triggering command: /usr/bin/php8.3 -n -c /tmp/RCdY1Y /usr/bin/composer install (http block)
https://api.github.com/repos/php-fig/log/zipball/f16e1d5863e37f8d8c2a01719f5b34baa2b714d3
- Triggering command: /usr/bin/php8.3 -n -c /tmp/RCdY1Y /usr/bin/composer install (http block)

If you need me to access, download, or install something from one of these locations, you can either:

Configure Actions setup steps to set up my environment, which run before the firewall is enabled
Add the appropriate URLs or hosts to my firewall allow list

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.

Co-authored-by: lkraav <[email protected]>

lkraav

This issue is really not related specifically to SQL mode discovery, but rather how wp db query loses any general arguments given to it, such as --defaults, or as another use case example, --ssl-verify-server-cert.

All these args work correctly for wp db cli, so maybe we could explore how wp db query is different and how wp db cli succeeds in managing retaining cmdline args better.

Copilot · 2025-06-26T15:59:51Z

This issue is really not related specifically to SQL mode discovery, but rather how wp db query loses any general arguments given to it, such as --defaults, or as another use case example, `--ssl-...

You're right that this is a broader architectural issue. The problem is that get_defaults_flag_string() destructively modifies $assoc_args (removes the 'defaults' key), which works fine for simple commands like wp db cli but breaks when commands need multiple operations that each require the same WP-CLI flags.

Looking at the difference:

wp db cli: Single operation, calls get_defaults_flag_string() once
wp db query: Multiple operations (main command + SQL mode discovery), both need defaults flag

Would you prefer a solution that makes the flag handling non-destructive across all commands, or should we explore having SQL mode discovery take a different approach that doesn't rely on re-parsing the args? I'm curious what specific architectural change you'd like to see explored.

lkraav · 2025-06-26T16:07:38Z

Would you prefer a solution that makes the flag handling non-destructive across all commands, or should we explore having SQL mode discovery take a different approach that doesn't rely on re-parsing the args? I'm curious what specific architectural change you'd like to see explored.

Let's try to fix wp db query alone first @copilot

Here's debug output of failure

Debug (bootstrap): Processing bootstrap step: WP_CLI\Bootstrap\RegisterDeferredCommands (0.212s)
Debug (bootstrap): Processing bootstrap step: WP_CLI\Bootstrap\InitializeContexts (0.212s)
Debug (bootstrap): Processing bootstrap step: WP_CLI\Bootstrap\LaunchRunner (0.213s)
Debug (bootstrap): Using default global config: /var/www/cxl.com/.wp-cli/config.yml (0.213s)
Debug (bootstrap): No project config found (0.213s)
Debug (bootstrap): argv: /var/www/cxl.com/.local/bin/wp db query SELECT CURRENT_USER(); --ssl-verify-server-cert --debug (0.213s)
Debug (bootstrap): ABSPATH defined: /var/www/cxl.com/html/institute/ (0.213s)
Debug (hooks): Executing hook: before_wp_load (0.213s)
Debug (context): Using context 'cli' (0.213s)
Debug (bootstrap): Begin WordPress load (0.213s)
Debug (bootstrap): wp-config.php path: /var/www/cxl.com/html/institute/wp-config.php (0.213s)
Debug (bootstrap): Looking for UTF-8 BOM (0.213s)
Debug (bootstrap): Looking for UTF-16 (BE) BOM (0.213s)
Debug (bootstrap): Looking for UTF-16 (LE) BOM (0.213s)
Debug (hooks): Executing hook: after_wp_config_load (0.214s)
Debug (hooks): Processing hook "before_run_command" with 1 callbacks (0.214s)
Debug (hooks): On hook "before_run_command": WP_CLI\Bootstrap\RegisterDeferredCommands->add_deferred_commands() (0.214s)
Debug (bootstrap): Running command: db query (0.214s)
Debug (db): Running shell command: /usr/bin/env mariadb --no-auto-rehash (0.238s)
Debug (db): Final MySQL command: /usr/bin/env mariadb --no-defaults --no-auto-rehash --batch --skip-column-names --host='dbpgf00000000.sysp0000.db2.skysql.com' --user='dbpgf00000000' --default-character-set='utf8' --execute='SELECT @@SESSION.sql_mode' (0.238s)
Error: Failed to get current SQL modes. Reason: ERROR 1045 (28000): Access denied for user 'dbpgf00000000'@'1.2.3.4' (using password: YES)

Copilot · 2025-06-26T16:13:31Z

Would you prefer a solution that makes the flag handling non-destructive across all commands, or should we explore having SQL mode discovery take a different approach that doesn't rely on re-parsing...

You're right, the issue is broader than just the --defaults flag. The real problem is in get_current_sql_modes() method where it has an empty foreach loop that's supposed to preserve MySQL arguments but doesn't preserve any. This causes SQL mode discovery to lose connection arguments like --ssl-verify-server-cert. I'll fix this to properly preserve all valid MySQL arguments.

Warning

Firewall rules blocked me from connecting to one or more addresses

I tried to connect to the following addresses, but was blocked by firewall rules:

https://api.github.com/repos/wp-cli/mustache.php/zipball/ca23b97ac35fbe01c160549eb634396183d04a59
- Triggering command: /usr/bin/php8.3 -n -c /tmp/9r4PIF /usr/bin/composer install --no-dev (http block)
https://api.github.com/repos/wp-cli/spyc/zipball/6aa0b4da69ce9e9a2c8402dab8d43cf32c581cc7
- Triggering command: /usr/bin/php8.3 -n -c /tmp/9r4PIF /usr/bin/composer install --no-dev (http block)

If you need me to access, download, or install something from one of these locations, you can either:

Configure Actions setup steps to set up my environment, which run before the firewall is enabled
Add the appropriate URLs or hosts to my firewall allow list

- Replace empty foreach loop in get_current_sql_modes() with proper MySQL argument preservation using get_mysql_args() - Add ssl-verify-server-cert to allowed MySQL options list - Add test case to verify connection arguments are preserved during SQL mode discovery - Fixes issue where connection flags like --ssl-verify-server-cert were lost during SQL mode discovery Co-authored-by: lkraav <[email protected]>

Co-authored-by: Alain Schlesser <[email protected]>

In wp-cli#277 this code was changed in order to support a count number for certain types of queries. That change had a side effect of removing the default table that MySQL/MariaDB prints for regular queries because that relies on TTY detection from MySQL/MariaDB itself. To revert to preivious behavior, only run the new code if we know it is a row modifying query that will just print the count. Otherwise, use the previous code path. Fixes wp-cli#290

Co-authored-by: Copilot <[email protected]>

…repair Add automatic --silent flag to mysqlcheck commands when --quiet is set

Use negative lookahead REPLACE(?!\s*\() instead of bare REPLACE to distinguish the MySQL REPLACE() string function from the REPLACE INTO (or bare REPLACE) DML write statement. This avoids a false positive where SELECT queries containing REPLACE() are routed through the write-query code path, causing their results to be silently swallowed. Fixes wp-cli#313

…-positive

See wp-cli/wp-cli#5070

Co-authored-by: swissspidy <[email protected]> Co-authored-by: Copilot <[email protected]> Co-authored-by: copilot-swe-agent[bot] <[email protected]> Co-authored-by: Pascal Birchler <[email protected]>

rolf-yoast and others added 2 commits April 19, 2024 08:16

Add alignments to table

925732a

Initial plan

9289ce7

Copilot AI assigned Copilot and lkraav Jun 26, 2025

Copilot started work on behalf of lkraav June 26, 2025 15:33 View session

Fix --defaults flag issue in SQL mode discovery

5389fcc

Co-authored-by: lkraav <[email protected]>

Copilot AI changed the title ~~[WIP] query with "--default"-flag fails when discovering the SQL mode~~ Fix --defaults flag issue in SQL mode discovery for db query commands Jun 26, 2025

Copilot AI requested a review from lkraav June 26, 2025 15:48

Copilot finished work on behalf of lkraav June 26, 2025 15:48

lkraav reviewed Jun 26, 2025

View reviewed changes

Copilot started work on behalf of lkraav June 26, 2025 15:54 View session

Copilot AI requested a review from lkraav June 26, 2025 16:01

Copilot finished work on behalf of lkraav June 26, 2025 16:01

Copilot started work on behalf of lkraav June 26, 2025 16:07 View session

Copilot finished work on behalf of lkraav June 26, 2025 16:22

lkraav marked this pull request as ready for review June 26, 2025 18:22

lkraav mentioned this pull request Jun 26, 2025

Try refactor wp db query --defaults handling wp-cli/db-command#285

Open

swissspidy and others added 9 commits July 1, 2025 14:09

PHPStan level 9

b5df0f3

Update phpstan.neon.dist

8f60a27

Update dependencies

e847909

Remaining type fixes

fac34b3

Update PHPCS config

14895b3

Update src/DB_Command.php

d09bb14

Co-authored-by: Alain Schlesser <[email protected]>

Update src/DB_Command.php

16e7c12

Co-authored-by: Alain Schlesser <[email protected]>

Merge pull request wp-cli#286 from wp-cli/try/phpstan

cf0dba1

swissspidy and others added 30 commits January 13, 2026 12:36

Update test

1a36bab

Remove tests

9780f67

Update file(s) from wp-cli/.github

aee6570

Merge branch 'main' into add-alignments

e67dc98

Merge branch 'main' into add-alignments

54c54e4

spellcheck fixes

e0d482c

Revert accidental change

fa7f6d1

Add test

51732e8

Update test

06ef2e4

Revert test

e83917e

Merge branch 'main' into copilot/fix-1

b98ce0d

Update features/db-query.feature

c020721

Co-authored-by: Copilot <[email protected]>

Update src/DB_Command.php

d654e9f

Co-authored-by: Copilot <[email protected]>

Update file(s) from wp-cli/.github

5077d19

Regenerate README file - 2026-01-22

e4cb95f

Merge pull request wp-cli#304 from wp-cli/regenerate-readme

471a110

Merge pull request wp-cli#252 from rolf-yoast/add-alignments

db5498e

Merge pull request wp-cli#302 from wp-cli/copilot/fix-wp-db-optimize-…

c397ba4

…repair Add automatic --silent flag to mysqlcheck commands when --quiet is set

Merge branch 'main' into copilot/fix-1

0565779

Add schedule for code quality workflow

9b69699

Fix newly reported PHPStan errors

a959fd3

Merge pull request wp-cli#314 from chubes4/fix/replace-function-false…

768a128

…-positive

Update file(s) from wp-cli/.github

9f32ef9

Update file(s) from wp-cli/.github

67f02b8

Update file(s) from wp-cli/.github

dbfe4c6

Update file(s) from wp-cli/.github

ab1a39a

Add .gitattributes file

089afe8

See wp-cli/wp-cli#5070

Add SQLite compatibility to wp db commands (wp-cli#299)

358e2a0

Co-authored-by: swissspidy <[email protected]> Co-authored-by: Copilot <[email protected]> Co-authored-by: copilot-swe-agent[bot] <[email protected]> Co-authored-by: Pascal Birchler <[email protected]>

Merge branch 'main' into copilot/fix-1

fcee709

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix --defaults flag issue in SQL mode discovery for db query commands#2

Fix --defaults flag issue in SQL mode discovery for db query commands#2
Copilot wants to merge 62 commits intomainfrom
copilot/fix-1

Copilot AI commented Jun 26, 2025 •

edited

Loading

Uh oh!

lkraav left a comment

Uh oh!

Copilot AI commented Jun 26, 2025

Uh oh!

lkraav commented Jun 26, 2025

Uh oh!

Copilot AI commented Jun 26, 2025 •

edited

Loading

I tried to connect to the following addresses, but was blocked by firewall rules:

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

Conversation

Copilot AI commented Jun 26, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Problem

Root Cause

Solution

Changes Made

Testing

I tried to connect to the following addresses, but was blocked by firewall rules:

Uh oh!

lkraav left a comment

Choose a reason for hiding this comment

Uh oh!

Copilot AI commented Jun 26, 2025

Uh oh!

lkraav commented Jun 26, 2025

Uh oh!

Copilot AI commented Jun 26, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

I tried to connect to the following addresses, but was blocked by firewall rules:

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

Copilot AI commented Jun 26, 2025 •

edited

Loading

Copilot AI commented Jun 26, 2025 •

edited

Loading