clean, create, drop, reset subcommands not working with `--defaults` flag

[bgturner]

Bug Report

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

Describe the current, buggy behavior

When using wpcli in an environment that needs the --defaults flag (like Local ), the clean, create, drop, and reset subcommands are not working.

When running these commands, I get this error within the terminal:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

The problem is that the above socket (/tmp/mysql.sock) isn't the correct one for the environment. Here are some details from my site's terminal session:

★  app/public % which mysql
/Applications/Local.app/Contents/Resources/extraResources/lightning-services/mysql-8.0.16+5/bin/darwin/bin/mysql
★  app/public % ps aux | grep mysql
benjamin         73741   0.2  0.6  4487024  94948   ??  S     2:01PM   0:08.94 /Applications/Local.app/Contents/Resources/extraResources/lightning-services/mysql-8.0.16+5/bin/darwin/bin/mysqld --defaults-file=/Users/benjamin/Library/Application Support/Local/run/Q1Jz6TdXS/conf/mysql/my.cnf
benjamin         75797   0.0  0.0  4258892    216 s002  U+    2:28PM   0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox mysq
★  app/public % cat ~//Library/Application\ Support/Local/run/Q1Jz6TdXS/conf/mysql/my.cnf
[mysqld]
skip-name-resolve

mysqlx = 0

datadir = /Users/benjamin/Library/Application Support/Local/run/Q1Jz6TdXS/mysql/data
port = 10024
bind-address = 127.0.0.1,::1
socket = /Users/benjamin/Library/Application Support/Local/run/Q1Jz6TdXS/mysql/mysqld.sock

# Older PHP/client compatibility
character-set-server = utf8mb3
default_authentication_plugin = mysql_native_password

# Fine Tuning
performance_schema = off
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

# InnoDB
innodb_buffer_pool_size = 32M
innodb_log_file_size = 96M

[client]
socket = /Users/benjamin/Library/Application Support/Local/run/Q1Jz6TdXS/mysql/mysqld.sock
user = root
password = root

Describe how other contributors can replicate this bug

You might be able to replicate by having a custom MySQL config and using the --defaults flag, but the easiest way I know is to use Local:

• Create a new site within Local
• Right-click on the site in Local and select "Open Site Shell." This will open a terminal and configure the environment variables
• Run any of the subcommands that are having issue: clean, create, drop, or reset
• You should see an error related to being unable to connect to MySQL:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Describe what you would expect as the correct outcome

I would expect that all db subcommands should work by being able to use the same socket connection.

Let us know what environment you are running this on

★  app/public % wp cli info
OS: Darwin 19.6.0 Darwin Kernel Version 19.6.0: Tue Nov 10 00:10:30 PST 2020; root:xnu-6153.141.10~1/RELEASE_X86_64 x86_64
Shell:  /bin/zsh
PHP binary: /Applications/Local.app/Contents/Resources/extraResources/lightning-services/php-7.3.5+10/bin/darwin/bin/php
PHP version:    7.3.5
php.ini used:   /Users/benjamin/Library/Application Support/Local/run/Q1Jz6TdXS/conf/php/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:   /Users/benjamin/Local Sites/site-reset/app/public
WP-CLI packages dir:    /Users/benjamin/.wp-cli/packages/
WP-CLI global config:   /Applications/Local.app/Contents/Resources/extraResources/bin/wp-cli/config.yaml
WP-CLI project config:  
WP-CLI version: 2.5.0-alpha

Provide a possible solution

Taking a look at those functions that produce errors, it seems like maybe they need to be refactored to not use $this->run_query(...

I'm new to the wpcli project, so I might see if I can get something working, but if anyone has pointers, I'd love to learn more!

Provide additional context/Screenshots

It looks like this is related to the with the work done in #157 however, a few subcommands weren't covered.

[kraftner]

I also just experienced this with https://all-inkl.com/ hosting. --defaults does not help.

Problem also seems to be that the socket as defined in /etc/mysql/my.cnf is not being picked up.

As a quick workaround adding a --socket=/path/to/mysqld.sock to each command works, but doesn't feel like a long term stable solution.

If I can provide any further information to help let me know.

[schlessera]

@bgturner, @kraftner Can you test with the latest nightly (wp cli update --nightly) to see if this issue is resolved?

[kraftner]

Sorry, it seems as if the hoster fixed the issue on their side in the meantime. Since I can't reproduce the original issue I also can't check if your fix solved it, sorry! :disappointed:

[bgturner]

Thanks for taking a crack at this @schlessera ! It looks like the clean command works -- the tables were nuked!

drop create and reset are still having issues:

Here's a recorded screencast of me doing the QA in case it helps give more context:

https://i.getf.ly/d5uAGDg8

[SagnikSRHUSE]

It seems that the problem happens when running wp config create too.

[PerryRylance]

Seeing this with wp config create, it won't accept --defaults