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

uselessjargon commented 4 years ago

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

Steps to reproduce:

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

View the config

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

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)

Remove the obfuscated .mylogin.cnf
```
$ rm ~/.mylogin.cnf
```

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 x8664 (Percona XtraDB Cluster (GPL), Release rel33, Revision 5dd6d59, WSREP version 31.43, wsrep

uselessjargon commented 4 years ago

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 commented 4 years ago

@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 commented 4 years ago

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 commented 4 years ago

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...

wp-cli / db-command