Jonathan Desrosiers

Recently, I created an issue on the WP-CLI’s entity command repository to introduce a wp site generate command to allow for multisite installs to be easily populated for testing purposes.

I decided to set up a local WP-CLI sandbox on my machine (not within a virtual machine) so I can more easily and consistently contribute to the project. There is great documentation detailing how to contribute code to the project in the WP-CLI handbook. I ended up using the wp-cli-dev repo instead, which sets up every package for development.

After I had everything set up, I experienced database connection issues that took me quite a while to figure out. I hope that this will help save someone else time and frustration!

tl;dr (but you totally should read): MySQL 8.0 changed the preferred authentication plugin from mysql_native_password to caching_sha2_password, which PHP does not yet support. Running this command will fix the issue for the wp_cli_test user expected when running the WP-CLI Behat tests.

ALTER USER 'wp_cli_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password1';

The Problem: More Details

Being on MacOS, I use Homebrew to install various packages on my machine. After installing mysql with Homebrew, creating the required wp_cli_test database and user, and granting that user the needed privileges for that database, I couldn’t get WP-CLI to connect (connecting via command line worked fine).

$ wp core install
Error: Error establishing a database connection. This either means that the username and password information in your `wp-config.php` file is incorrect or we can’t contact the database server at `localhost`. This could mean your host’s database server is down.

What made the situation even more weird was that certain database tasks appeared to complete successfully.

$ wp db check
Success: Database checked.
$ wp db optimize
Success: Database optimized.

I worked through the issue with a coworker and they were able to reproduce the issue. We dug deeper and discovered the following error:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

After researching, I found that there are a few other variations of this notice.

Authentication plugin 'caching_sha2_password' is not supported

Warning: mysqli_connect(): The server requested authentication
method unknown to the client [caching_sha2_password]

What’s Happening

In MySQL 8.0, the default authentication plugin was changed from mysql_native_password to caching_sha2_password (on the MySQL server). This is a problem when the client (PHP in this case) is not aware of this change or does not support the new plugin.

When wp core install is called, the WordPress Database API is used. This means that the call is initiated by PHP as the MySQL client. When wp db check is called, the result is a mysqlcheck call directly to the MySQL server, which is aware of and supports the new authentication plugin.

PHP does not seem to have support for the new caching_sha2_password plugin yet. That explains why PHP initiated requests to the database are failing.

The Solution

After some searching and trial and error, I found three solutions that worked.

First Solution

You can change the authentication plugin used on a per-user basis by running this MySQL command:

ALTER USER 'wp_cli_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password1';

I chose this route because I was only looking to configure one user for running WP-CLI tests and I want to start using the new authentication plugin as soon as it’s supported by PHP. Future connection failures for new users will remind me to check up on this issue.

Second Solution

If you want to fix the issue for all new users, you can edit the server configuration file to use the previous default authentication plugin (mysql_native_password) by default. To do this, you need to add a line to the my.cnf located in /etc/. When testing this solution, I did not have a my.cnf file present. So, I had to create one.

cd /etc
sudo touch my.cnf

Add the following to that file:

default_authentication_plugin=mysql_native_password

Save and exit, and then restart the server.

mysql.server restart

Note: This change will only apply to new users. The authentication plugin for users created after installing or upgrading to MySQL >= 8.0 will remain unchanged. You will need to apply the first solution to each of these users.

Alternative Solution

There is a third option you could take that I do not really recommend. You could uninstall MySQL 8.x and install 5.7.x. Personally, I always try to work using the latest versions of packages and tools. If you want to take this approach, this is the process I found worked best (this is a combination of steps found in a few different locations).

Note: This will remove all MySQL related data. Make sure to back up any databases you need.

ps -ax | grep mysql
mysql.server stop
brew remove mysql
brew cleanup
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm /etc/my.cnf

Check your .bash_profile file for any MySQL related code and restart your computer.

brew doctor
brew update
brew install [email protected]
echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.bash_profile

Open a new terminal window.

mysql.server start

Notes

It’s worth mentioning that all of these should only be a temporary fix and not a long-term or permanent solution. caching_sha2_password was introduced to provide improved security and performance and it should be utilized when possible.

If you want to read more, a complete breakdown of this change can be found in the MySQL 8.0 Reference Guide, and some details about the issue in PHP can be found on this page. I have also opened a PR to add a notice about this on the Pull Requests page in the WP-CLI handbook.