The `wp db query` command fails with a permissions error under some configurations

[bgturner]

Bug Report

• Yes, I reviewed the contribution guidelines.
• Yes, more specifically, I reviewed the guidelines on how to write clear bug reports.

Describe the current, buggy behavior

While trying to run wp db query within a WordPress site in Local, I get the following error:

Error: Failed to get current SQL modes. Reason: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The connection issue seems odd because I am able to successfully run wp db cli and interact with the WP database.

Describe how other contributors can replicate this bug

This might be true under other environments, but using Local is probably the easiest place to reproduce:

1. Create a new site
2. Click the "Site Shell" button to open a terminal and set the various env vars
3. Run a wp db query command, for example wp db query 'show tables;'
4. Note the error
5. Try using another DB command like wp db cli and note you are able to connect to the DB

Describe what you would expect as the correct outcome

I would expect the wp db query command to work when other wp db commands work

Let us know what environment you are running this on

✗ 1   app/public % wp cli info
OS:	Darwin 23.6.0 Darwin Kernel Version 23.6.0: Fri Jul  5 17:56:41 PDT 2024; root:xnu-10063.141.1~2/RELEASE_ARM64_T6000 arm64
Shell:	/bin/zsh
PHP binary:	/Users/ben.turner/Library/Application Support/Local/lightning-services/php-8.1.29+0/bin/darwin-arm64/bin/php
PHP version:	8.1.29
php.ini used:	/Users/ben.turner/Library/Application Support/Local/run/snDl_yMHU/conf/php/php.ini
MySQL binary:	/Users/ben.turner/Library/Application Support/Local/lightning-services/mysql-8.0.16+6/bin/darwin/bin/mysql
MySQL version:	mysql  Ver 8.0.16 for macos10.14 on x86_64 (MySQL Community Server - GPL)
SQL modes:	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
WP-CLI root dir:	phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir:	phar://wp-cli.phar/vendor
WP_CLI phar path:	/Users/ben.turner/Local Sites/wpdbquery-sandbox/app/public
WP-CLI packages dir:
WP-CLI cache dir:	/Users/ben.turner/.wp-cli/cache
WP-CLI global config:	/Applications/Local.app/Contents/Resources/extraResources/bin/wp-cli/config.yaml
WP-CLI project config:
WP-CLI version:	2.10.0
★   app/public % which -a php
/Users/ben.turner/Library/Application Support/Local/lightning-services/php-8.1.29+0/bin/darwin-arm64/bin/php
★   app/public % stat $(which wp)
16777229 140821378 lrwxr-xr-x 1 ben.turner staff 0 14 "Jul 16 09:06:31 2024" "Jul 16 09:06:31 2024" "Jul 16 09:06:37 2024" "Jul 16 09:06:31 2024" 4096 0 0 /Applications/Local.app/Contents/Resources/extraResources/bin/wp-cli/posix/wp
★   app/public % wp package list
+------+---------+---------+--------+----------------+
| name | authors | version | update | update_version |
+------+---------+---------+--------+----------------+
+------+---------+---------+--------+----------------+

Provide a possible solution

I think the issue might have to do with the inclusion of the --no-defaults flag when trying to determine the supported SQL modes:

db-command/src/DB_Command.php

Lines 2122 to 2129 in 4cce2d5

	list( $stdout, $stderr, $exit_code ) = self::run(
	sprintf(
	'/usr/bin/env mysql%s --no-auto-rehash --batch --skip-column-names',
	$this->get_defaults_flag_string( $assoc_args )
	),
	array_merge( $args, [ 'execute' => 'SELECT @@SESSION.sql_mode' ] ),
	false
	);

I came to that conclusion by using --debug to find the actual mysql command and then iterating on the various args. Notably, when I run the command for determining the SQL modes, it appears that it runs fine in my terminal without adjustments. However, if I try a different --execute=... flag using show databases as the query, I get different results. It seems like removing the --no-defaults flag gets the command to actually see the WP site (ie, I can see the local database in the output).

See this gist with the full terminal output:

https://gist.github.com/bgturner/f4a8edf762d5b2e4f1740298d96b7434

I'm not quite sure what next steps to take or what additional debugging would help clarify the issue. Let me know what else I can do to help!