Can't Connect to Mysql Database from Nodejs
MySQL 8.0 — Client does not support authentication protocol requested by server; consider upgrading MySQL client
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
const DataBaseConnection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'node_curd'
});
DataBaseConnection.connect( (err) =>{
if (err)
console.log('this is error: '+ JSON.stringify(err, undefined, 2))
else
console.log('DataBase is connected')
})
below is the error that showed in my terminal
this is error: {
"code": "ER_NOT_SUPPORTED_AUTH_MODE",
"errno": 1251,
"sqlMessage": "Client does not support authentication protocol requested by server; consider upgrading MySQL client",
"sqlState": "08004",
"fatal": true
}
so i went through some stack-overflow threads and medium, fortunately i found this answer from medium and Stack-overflow
https://medium.com/codespace69/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server-consider-8afadc2385e2
Medium
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.)
StackOverflow
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.