wp db query gives "access denied" when using .mylogin.cnf created with mysql_config_editor

[uselessjargon]

With an obfuscated mysql credentials file in place all wp db query commands return with 'access denied'.

Steps to reproduce:

1. Create an obfuscated file for root mysql user (enter root password when prompted)

$ mysql_config_editor set --login-path=client --user=root --password --host=localhost

2. View the config

$ mysql_config_editor print --all
[client]
user = root
password = *****
host = localhost

3. Switch to a wordpress directory (or set its --path=) and run a wp db query -- get access denied

$ wp --path=/psirt db query 'show databases'
ERROR 1045 (28000): Access denied for user 'psirtAAA'@'10.X.X.X' (using password: YES)

4. Remove the obfuscated .mylogin.cnf

$ rm ~/.mylogin.cnf

5. Run the same wp db query command -- works fine

$ wp --path=/psirt db query 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| psirt              |
+--------------------+

Other info

Red Hat Enterprise Linux Server release 7.8 (Maipo)

wp --info

OS: Linux 3.10.0-1127.13.1.el7.x86_64 #1 SMP Fri Jun 12 14:34:17 EDT 2020 x86_64 Shell: /bin/bash PHP binary: /usr/bin/php PHP version: 7.3.20 php.ini used: /etc/php.ini 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: /root WP-CLI packages dir: WP-CLI global config: WP-CLI project config: WP-CLI version: 2.4.0

mysqld --version

mysqld Ver 5.7.30-33-57 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel33, Revision 5dd6d59, WSREP version 31.43, wsrep_

[uselessjargon]

So I did some quick searching and it looks like there's no (good) way to override the mysql binary from using the .mylogin.cnf.

       ·   --defaults-file=file_name

           Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs.  file_name is interpreted
           relative to the current directory if given as a relative path name rather than a full path name.

           Exception: Even with --defaults-file, client programs read .mylogin.cnf.

In this bug link it does say you can get around it by the issue by either changing your HOME var to something else or setting a dummy path for MYSQL_TEST_LOGIN_FILE. May need to implement one of these work arounds in the query code until an override option is written into the mysql code.

[schlessera]

@uselessjargon: By default, WP-CLI uses --no-defaults to avoid having a config file break the setup.

If you update to the latest nightly, you should be able to provide the --defaults flag to override this default behavior. Can you try this with the latest nightly to see whether it fixes your issue?

To update to the latest nightly: wp cli update --nightly

[uselessjargon]

Wow, quick response! Thanks, dude! The --no-defaults makes the most sense since you'd want WP-CLI to use the user and password from the wp-config.php. Unfortunately the issue still exists with the nightly build and supplying --defaults

# wp --allow-root --path=/psirt --defaults db query 'show databases'
Error: Failed to get current SQL modes. Reason: ERROR 1045 (28000): Access denied for user 'psirt970'@'10.111.8.107' (using password: YES)

# wp --allow-root cli version
WP-CLI 2.5.0-alpha-75cb7e3

The .mylogin.cnf seems to be little/not well documented. I did, however, try one of the workarounds found in that bug link above by setting the HOME var to something other than the users actual home. It worked (see below) but might impact other things if added to WP-CLI code.

# HOME=/tmp wp --allow-root --path=/psirt db query 'show databases'
Database
information_schema
psirt

[schlessera]

Sounds like this is more of a bug/problem in the mysql binary than a problem with WP-CLI. However, adapting the HOME environment variable will indeed mess with WP-CLI as well, as it uses this for example to find the global configuration, the installed packages and the internal caches.

So it might be worthwhile to add a work-around for this in some sort. I wonder whether it would make sense to internally override HOME by default for just the mysql process that is launched by WP-CLI...