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

399

I can't make a simple connection to the server for some reason. I install the newest MySQL Community 8.0 database along with Node.JS with 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!");
    });

Below is the error found in Command Prompt:

C:\Users\mysql-test>node app.js
    C:\Users\mysql-test\node_modules\mysql\lib\protocol\Parse
    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:\Users\mysql-
test\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
    at Handshake.ErrorPacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\sequences\Handshake.js:130:18)
    at Protocol._parsePacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\Protocol.js:279:23)
    at Parser.write (C:\Users\mysql-test\node_modules\mys
ql\lib\protocol\Parser.js:76:12)
    at Protocol.write (C:\Users\mysql-test\node_modules\m
ysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.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:\Users\mysql-test\node_module
s\mysql\lib\protocol\Protocol.js:145:48)
    at Protocol.handshake (C:\Users\mysql-test\node_modul
es\mysql\lib\protocol\Protocol.js:52:23)
    at Connection.connect (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.js:130:18)
    at Object.<anonymous> (C:\Users\mysql-test\server.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've read up on some things such as: https://dev.mysql.com/doc/refman/5.5/en/old-client.html https://github.com/mysqljs/mysql/issues/1507

But I am still not sure how to fix my problem. Any help would be appreciated :D

This question is tagged with mysql node.js

~ Asked on 2018-04-30 02:04:31

The Best Answer is


1035

Execute the following query in MYSQL Workbench

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

Where root as your user localhost as your URL and password as your password

Then run this query to refresh privileges:

flush privileges;

Try connecting using node after you do so.

If that doesn't work, try it without @'localhost' part.

~ Answered on 2018-05-02 09:58:11


214

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.


Your Current Options

Option 1) 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) 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.

Cheers!

~ Answered on 2019-06-08 18:21:12


Most Viewed Questions: