mysql-client 9.0 does not include mysql_native_password plugin

[zhangyangyu]

brew config

HOMEBREW_VERSION: 4.3.12
ORIGIN: https://github.com/Homebrew/brew
HEAD: 874d2da45344d3b27aa740e555b0210d8c474220
Last commit: 9 days ago
Core tap JSON: 08 Aug 13:26 UTC
Core cask tap JSON: 08 Aug 13:27 UTC
HOMEBREW_PREFIX: /opt/homebrew
HOMEBREW_CASK_OPTS: []
HOMEBREW_MAKE_JOBS: 8
Homebrew Ruby: 3.3.4 => /opt/homebrew/Library/Homebrew/vendor/portable-ruby/3.3.4/bin/ruby
CPU: octa-core 64-bit arm_firestorm_icestorm
Clang: 15.0.0 build 1500
Git: 2.39.3 => /Library/Developer/CommandLineTools/usr/bin/git
Curl: 8.6.0 => /usr/bin/curl
macOS: 14.5-arm64
CLT: 15.3.0.0.1.1708646388
Xcode: N/A
Rosetta 2: false

Verification

• My brew doctor output says Your system is ready to brew. and am still able to reproduce my issue.
• I ran brew update and am still able to reproduce my issue.
• I have resolved all warnings from brew doctor and that did not fix my problem.
• I searched for recent similar issues at https://github.com/Homebrew/homebrew-core/issues?q=is%3Aissue and found no duplicates.

What were you trying to do (and why)?

Use MySQL command line client to connect old version MySQL servers using mysql_native_password auth method.

What happened (include all command output)?

ERROR 2059 (HY000): Authentication plugin 'mysql_native_password' cannot be loaded: dlopen(/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so, 0x0002): tried: '/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file), '/opt/homebrew/Cellar/mysql-client/9.0.1/lib/plugin/mysql_native_password.so' (no such file)

What did you expect to happen?

9.0 version client should connect successfully with mysql_native_password auth method. From MySQL 9.0, mysql_native_password is removed from built-in auth methods. But for backward compatibility, client should still include it but now it's not built-in but a dynamically loadable plugin. https://dev.mysql.com/doc/relnotes/mysql/9.0/en/news-9-0-0.html

The mysql_native_password authentication plugin, deprecated in MySQL 8.0, has been removed, and the server now rejects mysql_native authentication requests from older client programs which do not have CLIENT_PLUGIN_AUTH capability. For backward compatibility, mysql_native_password remains available on the client; the client-side built-in authentication plugin has been converted into a dynamically loadable plugin.

I think mysql-client should still include it. Otherwise, the new version client might not connect to old version servers.

Step-by-step reproduction instructions (by running brew commands)

brew install mysql-client
/opt/homebrew/Cellar/mysql-client/9.0.1/bin/mysql --comments -u '<user>' -h <host> -P <port> -D '<database>' --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem -p'<password>'

[edismooth]

My temporary solution was downgrading to version 8.4:

brew install mysql-client@8.4

[deivid11]

Hello,

MySQL 9 no longer supports the mysql_native_password authentication method, which may affect users upgrading from MySQL 8.x to MySQL 9. To resolve this issue, you need to update the MySQL user tables to use the new authentication method. Follow these steps:

1.- Disable the Grant Tables:

Edit the MySQL configuration file, typically located at /opt/homebrew/etc/my.cnf.
Add the following line under the [mysqld] section to disable the grant tables:

[mysqld]
skip-grant-tables

2.- Restart MySQL:
Use Homebrew to restart MySQL:

brew services restart mysql

3.- Connect to MySQL as the root user:

mysql -uroot

4.- Update User Authentication Method:

Refresh the privileges:

FLUSH PRIVILEGES;

5.- Check for users using the mysql_native_password plugin:

SELECT User, Host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';

6.- Update the root user to use the caching_sha2_password plugin:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password';

7.- Re-enable Grant Tables:

After updating, remove or comment out the skip-grant-tables line from the MySQL configuration file.

8.- Restart MySQL to apply the changes:

brew services restart mysql

By following these steps, you should resolve the authentication method issue after upgrading to MySQL 9.

[elreydetodo]

Well that's an interesting detail for why it doesn't work, but I think this still needs to be fixed somehow. I'm not actually running MySQL locally, but I'm using the mysql-client package to connect to a remove service. In fact it's not even MySQL I'm connecting to, it's actually MariaDB.

So a couple things here:

1. We need mysql-client to keep supporting the old password login module, because this is the client, and there's no guarantee what server version you're trying to talk to.
2. We probably could also use a mariadb-client package similar to this one.

[Bo98]

For backward compatibility, mysql_native_password remains available on the client

Despite upstream's release notes, they didn't actually flip the default value for WITH_AUTHENTICATION_CLIENT_PLUGINS. It is actually disabled by default in the client.

This could be argued to be an upstream bug (or at least: confusing documentation). Though we could also override that default in the formula. I'd be OK with a PR that does that, though we should compare the install directory before and after to make sure it has the intended effect.

[zhangyangyu]

Well that's an interesting detail for why it doesn't work, but I think this still needs to be fixed somehow. I'm not actually running MySQL locally, but I'm using the mysql-client package to connect to a remove service. In fact it's not even MySQL I'm connecting to, it's actually MariaDB.

So a couple things here:

1. We need mysql-client to keep supporting the old password login module, because this is the client, and there's no guarantee what server version you're trying to talk to.
2. We probably could also use a mariadb-client package similar to this one.

Exactly our case. We are connecting to remote MySQL-compatible servers.

[john8329]

As mentioned already, MySQL 9 is not fully backwards compatible without this plugin. Remember to link after installing the older, more stable and potentially less secure version.

brew install mysql-client@8.4
brew unlink mysql
brew link mysql-client@8.4

[goalkes]

This is how I solved this problem, on MySQL 9 and it doesn't use a password for the root user.

1. brew services stop mysql
2. mysqld_safe --skip-grant-tables &
3. mysql -u root
4. USE mysql;
5. UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
6. EXIT;
7. killall mysqld
8. brew services start mysql

and now you can access as before.

[arudp]

As mentioned already, MySQL 9 is not fully backwards compatible without this plugin. Remember to link after installing the older, more stable and potentially less secure version.

brew install mysql-client@8.4
brew unlink mysql
brew link mysql-client@8.4

In case it helps anyone, I did this but also remember to refresh the install of whatever tool might be employing it. In my case, since I'm using Python, I was only missing to reinstall the client with pip after the brew reinstall (it's a dumb thing, but quite forgettable)

pip uninstall mysqlclient
pip install mysqlclient --no-cache-dir

[cswingler]

Linking the old version of mysqlclient doesn't fix all the Homebrew packages that depend on it, however (see the output of brew uses --eval-all mysql-client). This is a blocker for those of us relying on things like innotop and percona-toolkit.

Is there some reason that mysql-client >=9.0 can't be built with the -DWITH_AUTHENTICATION_CLIENT_PLUGINS=yes flag? Extracting that formula into my own tap and rebuilding gets all of the things that depend on it working again as well.

[victormongi]

Linking the old version of mysqlclient doesn't fix all the Homebrew packages that depend on it, however (see the output of brew uses --eval-all mysql-client). This is a blocker for those of us relying on things like innotop and percona-toolkit.

Is there some reason that mysql-client >=9.0 can't be built with the -DWITH_AUTHENTICATION_CLIENT_PLUGINS=yes flag? Extracting that formula into my own tap and rebuilding gets all of the things that depend on it working again as well.

do you have any step for this?

[Zylsjsp]

Linking the old version of mysqlclient doesn't fix all the Homebrew packages that depend on it, however (see the output of brew uses --eval-all mysql-client). This is a blocker for those of us relying on things like innotop and percona-toolkit.
Is there some reason that mysql-client >=9.0 can't be built with the -DWITH_AUTHENTICATION_CLIENT_PLUGINS=yes flag? Extracting that formula into my own tap and rebuilding gets all of the things that depend on it working again as well.

do you have any step for this?

See https://docs.brew.sh/FAQ#can-i-edit-formulae-myself

EDIT: Additional arguments are listed at StackOverflow

[klausbreyer]

Uninstall, deleting everything and reinstall everything fresh with MySQL 9 is also an option.

rm -rf /opt/homebrew/var/mysql/*

[mattnorris]

If you're using SQLAlchemy and Python, use PyMySQL.

# NOTE: Do not use native MySQL to connect. Use PyMySQL instead.
# CONNECTION_URI = f"mysql+mysqldb://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}"

CONNECTION_URI = f"mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}"
db = SQLDatabase.from_uri(CONNECTION_URI)