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.
P粉1619397522023-10-11 09:08:13
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' Then Refresh permissionsmysql2 (instead of mysql) and use it - npm mysql2 and mysql = require('mysql2');. 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 As of July 2019, still open three years later.
Update June 2019: There is a new PR in mysqljs 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 < a href="https://github.com/sidorares/node-mysql2" rel="noreferrer">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.
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).
MySQL It works like a charm that supports caching_sha2_password authentication. (Just make sure you use port 33060
forX 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.
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
, 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) isNPM package link) is not. So the following should probably be the easy answer!
npm un mysql && npm i mysql2
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!
P粉1987499292023-10-11 00:55:10
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.