Ошибка client does not support authentication protocol

Summary

1. If you just want to get rid of the error, at the cost of risking the security of the project (e.g. it’s just a personal project or dev environment), go with @Pras’s answer — ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' and then flush privileges
2. If you want to have a fix for it, without knowing why, just install and use mysql2 (instead of mysql) and use it — npm i mysql2, and mysql = require('mysql2');.
3. If you are a curious developer who is always eager to learn, keep reading …

What’s going on?

Let’s first make it clear what’s going on.

MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_password is used rather than our good old mysql_native_password (source). It should be obvious that using a crypto algorithm with several handshakes is more secure than plain password passing that has been there for 24 years!

Now, the problem is mysqljs in Node (the package you install with npm i mysql and use it in your Node code) doesn’t support this new default authentication method of MySQL 8, yet. The issue is in here: https://github.com/mysqljs/mysql/issues/1507 and is still open, after 3 years, as of July 2019.

UPDATE June 2019: There is a new PR in mysqljs now to fix this!

UPDATE Feb 2020: Apparently it’s scheduled to come in version 3 of mysqljs.

UPDATE July 2020: Apparently it’s still not in yet (as of April 2020 at least), but it’s claimed that node-mysql2 is supporting Authentication switch request. Please comment below if node-mysql2 is working fine for this issue — I will test it later myself.

UPDATE April 2021: It seems like the issue is still there and just 3 days ago, someone created a fork and made it there — yet not official in the mysql.js package. Also, as per the comments below, it seems like mysql2 package is working fine and supporting Authentication-switch properly.

Your Current Options

Option 1) [NOT RECOMMENDED] Downgrade «MySQL» to authenticate using good old «mysql_native_password»

That’s what everybody suggests here (e.g. top answer above). You just get into mysql and run a query saying root is fine using old mysql_native_password method for authentication:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...

The good thing is, life is going to be simple and you can still use good old tools like Sequel Pro without any issue. But the problem is, you are not taking advantage of a more secure (and cool, read below) stuffs available to you.

Option 2) [Meh…] Replace «Node» package with MySQL Connecter X DevAPI

MySQL X DevAPI for Node is a replacement to Node’s Mysqljs package, provided by http://dev.mysql.com official guys.

It works like a charm supporting caching_sha2_password authentication. (Just make sure you use port 33060 for X Protocol communications.)

The bad thing is, you have left our old mysql package that everyone is so used to and relies on.

The good thing is, your app is more secure now and you can take advantage of a ton of new things that our good old friends didn’t provide! Just check out the tutorial of X DevAPI and you’ll see it has a ton of new sexy features that can come in handy. You just need to pay the price of a learning curve, which expectedly comes with any technology upgrade.

PS. Unfortunately, this XDevAPI Package doesn’t have types definition (understandable by TypeScript) yet, so if you are on typescript, you will have problems. I tried to generate .d.ts using dts-gen and dtsmake, but no success. So keep that in mind.

Option 3) [RECOMMENDED] Replace «mysql.js» with «mysql2.js» package

As mentioned above, mysql package (NPM package link) is still having this issue (as of April 2021). But mysql2 package (NPM package link) is not. So probably the following should be the one-liner answer!

npm un mysql && npm i mysql2

Please note that mysql2 is a forked work off of the popular mysql, but its popularity (620K downloads per week for mysql2 in April 2020) has got close to the original package (720K download per week for mysql in April 2021) that making the switch seems reasonable!

What you are doing?

Mysql authentication error shows up when i run db:migrate command.

Sequelize CLI [Node: 11.11.0, CLI: 5.4.0, ORM: 4.42.0]

Loaded configuration file "src/database/config/index.js".
Using environment "development".
sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators ../../node_modules/sequelize/lib/sequelize.js:242:13

ERROR: Client does not support authentication protocol requested by server; consider upgrading MySQL client

error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

What do you expect to happen?

it should connect to mysql database without any error. because my code can get connected with the same configuration

What is actually happening?

it gives ERROR: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Output, either JSON or SQL

Dialect: mysql
__Database version: 8.0.15
__Sequelize CLI version:5.4.0
__Sequelize version:4.42.0

Recently I was working on Node.js application. In my application, I faced connection issue with MySQL server. Everything was good still I was getting following error.

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

In this article, I will show you why this error occured and how to solve this error.

While going through Googling the issue, I came to know that MySQL 8.0 is using caching_sha2_password authentication plugin rather than mysql_native_password and Node.js Mysql package uses mysql_native_password authentication plugin.

Solution:

In this situation, either you can change current user password according to mysql_native_password authentication plugin or you need to create new user with mysql_native_password authentication plugin.

Change current user password to authentication plugin

To change current user password to mysql_native_password authentication plugin, you need to login to MySQL command line and run the following command. Note that root is username and password is the password for that user.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Don’t forget to flush privileges:

FLUSH PRIVILEGES;

And that’s it, logout to MySQL command line:

quit;

Add new user with mysql_native_password authentication plugin:

To create a new user, login to MySQL command line and run the following command with username and password.

CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

You may need to grant privileges to new user. If you want to give all privileges, run the following query.

GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@'localhost';

Or you may grant specific privileges with following query:

GRANT CREATE, SELECT, INSERT ON *.* TO ‘newuser’@'localhost';

And reload the privileges:

FLUSH PRIVILEGES;

And close the MySQL connection:

quit;

After that, you can connect Node.js application with MySQL.

I hope this article will help you on your development.

Вы не вошли. Пожалуйста, войдите или зарегистрируйтесь.

Активные темы Темы без ответов

 
"#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client"

Сообщения 7

If you upgrade your MySql server to >= 4.1 you might get the following error:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

This happens because the latest versions of MySql uses a new format for the password (it’s a longer hash). In order for old clients to continue to use the newer server, you have to set the passwords on the server to their old format or upgrade your client. Because upgrading the client can sometimes be a pain, it’s often easier to just update the passwords to the old format on the server.Run mysql and login as root:

mysql -u root -p

Then, paste the following command, editing as necessary, to change the password of the user to the old format.

UPDATE mysql.USER
SET password=OLD_PASSWORD('somepassword')
WHERE USER='someuser'
AND host='somehost';

After you have set the passwords to the old format, flush the tables.

FLUSH privileges;

Then exit the mysql client with «quit» and you are set.

See http://dev.mysql.com/doc/mysql/en/Password_hashing.html for more technical information on this issue.