`mysql-client` 9.0 does not include `mysql_native_password` plugin

zhangyangyu commented 1 month ago

`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

[X] My brew doctor output says Your system is ready to brew. and am still able to reproduce my issue.
[X] I ran brew update and am still able to reproduce my issue.
[X] I have resolved all warnings from brew doctor and that did not fix my problem.
[X] 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 commented 1 month ago

My temporary solution was downgrading to version 8.4:

brew install mysql-client@8.4

deivid11 commented 1 month ago

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 commented 1 month ago

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:

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.
We probably could also use a mariadb-client package similar to this one.

Bo98 commented 1 month ago

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 commented 1 month ago

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:

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.

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

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 commented 3 weeks ago

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

brew services stop mysql
mysqld_safe --skip-grant-tables &
mysql -u root
USE mysql;
UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
EXIT;
killall mysqld
brew services start mysql

and now you can access as before.

arudp commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 5 days ago

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

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

mattnorris commented 4 days ago

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)

Homebrew / homebrew-core