Node.js can't authenticate to MySQL 8.0

Question

I have a Node.js program that connects to a local MySQL database with the root account (this is not a production setup). This is the code that creates the connection:

const mysql = require('mysql');

const dbConn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'myRootPassword',
    database: 'decldb'
});

dbConn.connect(err => {
    if (err) throw err;
    console.log('Connected!');
});

It worked with MySQL 5.7, but since installing MySQL 8.0 I get this error when starting the Node.js app:

> node .\api-server\main.js
[2018-05-16T13:53:53.153Z] Server launched on port 3000!
C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Parser.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\me\project\node_server\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
    at Handshake.ErrorPacket (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\sequences\Handshake.js:130:18)
    at Protocol._parsePacket (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Protocol.js:279:23)
    at Parser.write (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Parser.js:76:12)
    at Protocol.write (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\me\project\node_server\node_modules\mysql\lib\Connection.js:103:28)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    --------------------
    at Protocol._enqueue (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Protocol.js:145:48)
    at Protocol.handshake (C:\Users\me\project\node_server\node_modules\mysql\lib\protocol\Protocol.js:52:23)
    at Connection.connect (C:\Users\me\project\node_server\node_modules\mysql\lib\Connection.js:130:18)
    at Object.<anonymous> (C:\Users\me\project\node_server\main.js:27:8)
    at Module._compile (module.js:652:30)
    at Object.Module._extensions..js (module.js:663:10)
    at Module.load (module.js:565:32)
    at tryModuleLoad (module.js:505:12)
    at Function.Module._load (module.js:497:3)
    at Function.Module.runMain (module.js:693:10)

It seems that the root account uses a new password hashing method:

> select User,Host,plugin from user where User="root";
+------+-----------+-----------------------+
| User | Host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | caching_sha2_password |
+------+-----------+-----------------------+

...but I don't know why Node.js is unable to connect to it. I have updated all the npm packages and it's still an issue.

I would like to keep the new password hashing method. Can I still make this connection work? Do I have to wait for an update of the MySQL Node.js package, or change a setting on my side?

This affects PHP's PDO as well. Accepted answer fixes it too. — Nigel Atkinson, Nov 30 '18 at 01:45
You can look for more answers on this channel: https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server/56752560#56752560 — Campalo, Jun 25 '19 at 11:03

score 65 · Accepted Answer · answered May 16 '18 at 18:40

65

MySQL 8.0 uses a new default authentication plugin - caching_sha2_password - whereas MySQL 5.7 used a different one - mysql_native_password. Currently, the community Node.js drivers for MySQL don't support compatible client-side authentication mechanisms for the new server plugin.

A possible workaround is to alter the type of user account to use the old authentication plugin:

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

Or create a different one that uses that same plugin:

CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';

There's a pull request in pipeline to properly address the issue.

Another option is to use the official MySQL Node.js connector (full disclosure: I'm the lead dev), which is based on the X Protocol and already supports the new authentication mode.

answered May 16 '18 at 18:40

ruiquelhas

1,905
1
17
17

thanks for your answer, second way worked for me. I want know to know that why did the first way i.e ' alter user ' showed me error, "Operation ALTER USER failed for 'root'@'localhost' " – Pranjal Gupta Oct 02 '18 at 08:46
Maybe some client option is preventing you from overriding `root` permissions. I'm not entirely sure without better context. Maybe you can find the answer [here](https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html). – ruiquelhas Oct 02 '18 at 10:04
1

Is there documentation for using the new connector with standard queries. I have been working thru this and I am not interested in document stores. I really just want to build a query and execute it like the standard libraries do. I am finding the session object to be very confusing. – sixstring Oct 18 '18 at 17:35
X DevAPI connectors focus mostly on CRUD and Document Store. SQL is, sort of, a 2nd-class citizen as of today and we believe the community Node.js drivers do a great job already and we want to support them as much as we can. There's still some stuff in the [user guide](https://dev.mysql.com/doc/x-devapi-userguide/en/using-sql.html) and the [Node.js](https://dev.mysql.com/doc/dev/connector-nodejs/8.0/tutorial-Working_with_Tables.html) [Connector](https://dev.mysql.com/doc/dev/connector-nodejs/8.0/Session.html) [docs](https://dev.mysql.com/doc/dev/connector-nodejs/8.0/module-SqlExecute.html). – ruiquelhas Oct 19 '18 at 09:22
Regarding your other point, the [`Session`](https://dev.mysql.com/doc/dev/connector-nodejs/8.0/Session.html) object is mostly an abstraction over a database connection, not yet bound to any schema, table or collection, which means it's the right place to run your queries without limitations using the `Session.sql()` [API](https://dev.mysql.com/doc/dev/connector-nodejs/8.0/module-SqlExecute.html). – ruiquelhas Oct 19 '18 at 09:23
Thank you, this worked for me but only after three hours of trying with a user other than root first! There must be another issue with my other user, but it returns the same error as this. Any suggestions? – Adam Davies Feb 02 '19 at 07:48
1

I just responded to the same question, with more details, in https://stackoverflow.com/a/56509065/2321594, evangelizing for X DevAPI! Great job providing it @ruiquelhas :) – Aidin Jun 08 '19 at 18:22

score 4 · Answer 2 · answered Nov 16 '22 at 10:22

4

Instead of changing the password encryption as suggested in other answers, you can just update your npm package to mysql2:

const mysql = require('mysql2');

which does support MySQL 8.0 new default authentication plugin - caching_sha2_password.

answered Nov 16 '22 at 10:22

Lorraine R.

1,545
1
14
39

princebillyGK · Answer 3 · 2020-01-23T12:39:15.867

0

A workaround solution: create a new user with mysql_native_password protocol:

CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'userpassword';

and then grant permissions on the database for the user:

 GRANT ALL PRIVILEGES ON userdb.* TO 'dgtong'@'localhost';

edited Jan 23 '20 at 12:39

answered Jan 23 '20 at 10:57

princebillyGK

2,917
1
26
20

score 0 · Answer 4 · answered Jan 18 '23 at 05:48

0

Since it's not a production program, and if there's some reason the other (better) answers don't suit you, installing MySQL using Legacy Password Encryption might do the trick as well.

answered Jan 18 '23 at 05:48

evan g

44
6

score 0 · Answer 5 · answered Apr 28 '23 at 19:43

0

Do not mess with 'root'. Create a new user with 'Standard' Authentication type. Give rights to the user as is suitable for what the user can do. Use that user and the user's password in the connection configuration.

answered Apr 28 '23 at 19:43

Ravi Pasupuleti

1

Hi, this is a very outdated question, and it's already resolved. Anyway, updated libraries can do de job without troubles – pierpy May 05 '23 at 18:08

Node.js can't authenticate to MySQL 8.0

5 Answers5

Linked