Please upgrade the MySQL client: MySQL 8.0 - The client does not support the authentication protocol requested by the server

Question

For some reason I cannot establish a simple connection to the server. I installed the latest MySQL Community 8.0 database along with Node.JS using default settings.

This is my node.js code

var mysql = require('mysql');
    
    var con = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "password",
      insecureAuth : true
    });
    
    con.connect(function(err) {
      if (err) throw err;
      console.log("Connected!");
    });

The following are the errors found in the command prompt:

C:Usersmysql-test>node app.js
    C:Usersmysql-testnode_modulesmysqllibprotocolParse
    r.js:80
            throw err; // Rethrow non-MySQL errors
            ^
    
Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (C:Usersmysql-
testnode_modulesmysqllibprotocolsequencesSequence.js:52:14)
    at Handshake.ErrorPacket (C:Usersmysql-testnode_mo
dulesmysqllibprotocolsequencesHandshake.js:130:18)
    at Protocol._parsePacket (C:Usersmysql-testnode_mo
dulesmysqllibprotocolProtocol.js:279:23)
    at Parser.write (C:Usersmysql-testnode_modulesmys
qllibprotocolParser.js:76:12)
    at Protocol.write (C:Usersmysql-testnode_modulesm
ysqllibprotocolProtocol.js:39:16)
    at Socket.<anonymous> (C:Usersmysql-testnode_modul
esmysqllibConnection.js:103:28)
    at Socket.emit (events.js:159:13)
    at addChunk (_stream_readable.js:265:12)
    at readableAddChunk (_stream_readable.js:252:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    --------------------
    at Protocol._enqueue (C:Usersmysql-testnode_module
smysqllibprotocolProtocol.js:145:48)
    at Protocol.handshake (C:Usersmysql-testnode_modul
esmysqllibprotocolProtocol.js:52:23)
    at Connection.connect (C:Usersmysql-testnode_modul
esmysqllibConnection.js:130:18)
    at Object.<anonymous> (C:Usersmysql-testserver.js:
11:5)
at Module._compile (module.js:660:30)
at Object.Module._extensions..js (module.js:671:10)
at Module.load (module.js:573:32)
at tryModuleLoad (module.js:513:12)
at Function.Module._load (module.js:505:3)
at Function.Module.runMain (module.js:701:10)

I have read something like: https://dev.mysql.com/doc/refman/5.5/en/old-client.html https://github.com/mysqljs/mysql/issues/1507

But I'm still not sure how to solve my problem.

Answer 1

Summary

1. If you just want to eliminate bugs, at the expense of project security risk (e.g. this is just a personal project or development environment), use @Pras' answer -- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' Then Refresh permissions
2. If you want to fix it and don't know why, just install and use mysql2 (instead of mysql) and use it - npm mysql2 and mysql = require('mysql2');.
3. If you are a developer who is curious and always eager to learn, read on... :)

---

What happened?

We first need to figure out what happened.

MySQL 8 supports pluggable authentication methods. By default, one of the passwords named caching_sha2_password is used instead of our old mysql_native_password (source). Apparently, an encryption algorithm using multiple handshakes is more secure than the plain password passing used in 24. Year!

Now, the problem is that mysqljs in Node (the package you install using npm i mysql and use it in Node code) does not support this new default authentication for MySQL 8 methods are not yet available. The issue is here: https://github.com/mysqljs/mysql/issues/1507 a> As of July 2019, still open three years later.

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

February 2020 Update: Apparently it is planned to come in mysqljs version 3.

Update July 2020: Apparently it's not in yet (as of at least April 2020) , but claims node- mysql2 supports authentication switch requests. Please leave a comment below if node-mysql2 resolves this issue properly - - I will test it myself later.

Update April 2021: The problem seems to still exist, just 3 days ago someone created a fork and implemented it - but in the mysql.js package It's not official yet. Also, based on the comments below, it seems that the mysql2 package works fine and properly supports authentication switching.

---

Your current options

Option 1) [Not Recommended] Downgrade 'MySQL' to use the old 'mysql_native_password' for authentication

This is what everyone here suggests (like the best answer above). You can simply go into mysql and run a query stating that root can be authenticated using the old mysql_native_password method:

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

The good news is, life gets easier and you can still use good old tools like Sequel Pro without any questions. But the problem is, you're not taking advantage of something safer (and cool, read below).

Option 2) [Meh...] Replace "Node" package with MySQL Connecter X DevAPI

MySQL It works like a charm that supports caching_sha2_password authentication. (Just make sure you use port 33060

for

X protocol communication.) What's bad is that you've moved away from the old mysql package that every one of us was used to and relied on.

The benefit is that your applications are now more secure and you can take advantage of tons of new features that our good old friend doesn't offer! Just check out the

X DevAPI Tutorial and you'll see it has tons of new sexy features that you can come in handy with. You just have to pay the price of the learning curve, which is expected to come with any technology upgrade. :) PS. Unfortunately, this XDevAPI package doesn't yet have type definitions (that TypeScript understands), so if you use

typescript

, you'll have problems. I tried using dts-gen and dtsmake to generate .d.ts without success. So please keep this in mind. Option 3) [Recommended] Replace "mysql.js" with the "mysql2.js" package

As mentioned above, the mysql package (NPM package link) is

still having this issue

(as of April 2021). But the mysql2 package (

NPM package link) is not. So the following should probably be the easy answer!

npm un mysql && npm i mysql2

Please note that mysql2 is a fork of the popular mysql, but its popularity (

mysql2 was 620,000 downloads per week in April times) 2020) is close to the original package (720K downloads per week in April 2021 mysql), so it seems reasonable to make the switch!

Answer 2

Execute the following query in MYSQL Workbench

Change user 'root'@'localhost' to be identified by mysql_native_password BY 'password';

where root is your user localhost as your URL and password as your password

Then run this query to refresh permissions:

Refresh permissions;

After doing this try to connect using node.

If this doesn't work, try not using the @'localhost' part.