CODEWITHSUNDEEP
mysqlnode.jsauthenticationmysql-8.0
Hey
Hey

Reputation: 1841

Node.js can't authenticate to MySQL 8.0

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?

Upvotes: 34

Views: 37695

Answers (6)

Akshay Vijay Jain
Akshay Vijay Jain

Reputation: 15935

execute following in mysql terminal

alter user root@localhost identified with 'mysql_native_password' by 'hello123';
flush privileges;

then you can connect to db with mysql, by using username = 'root' and password = 'hello123'

Upvotes: 0

Lorraine Ram-El
Lorraine Ram-El

Reputation: 2745

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

const mysql = require('mysql2');

Upvotes: 6

Ravi Pasupuleti
Ravi Pasupuleti

Reputation: 1

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.

Upvotes: 0

evan g
evan g

Reputation: 94

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.

MySQL 8.0.31 Installation Configuration

Upvotes: 0

Prince Billy Graham
Prince Billy Graham

Reputation: 3578

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';

Upvotes: 0

ruiquelhas
ruiquelhas

Reputation: 2005

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.

Upvotes: 69

Related Questions