errno: 1251, sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client

Question

I'm creating the simple api project in nodejs.

For that I have installed mySql workbench with version 8.0.11. I have created config file, dbConnection.js file and in the dbconnection.js file I have written the following code :

var mysql = require('mysql');
var path = require('path');
var config = require(path.resolve('./', 'config'))

var connection = mysql.createConnection({
    host: config.databaseHost,
    user: config.databaseUser,
    password: config.databasePassword,
    database: config.databaseDatabaseName,
    multipleStatements: true
});

connection.connect(function (err) {
    if (err) {
        console.log('Error connecting to Database',err);
        return;
    }
    console.log('Connection established');
});
module.exports = connection;

And I'm facing following 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

Note : I kept my congif as simple as it can be, user = root , password = root, host = localhost.

Lillia

Answer 1

Open terminal and login:

mysql -u root -p

(then type your password)

After that:

USE mysql;

UPDATE user SET authentication_string=password(''), plugin='mysql_native_password' WHERE user='root';

And the last important thing is to reset mysql service:

sudo service mysql restart

Bruckner

When pasting in the UPDATE command I get the error: 1604 that there is an error with the syntax – Autopilot

Answer 2

once you establish your host, root, password &, etc inside your MySQL connection function.. paste the following inside of your database you are trying to connect within workbench with the following:

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

Go back to the terminal and run node <file name.js> again

Madaih

This solution is the quickest and easiest way to solve the issue – Convulse

Had absolutely no luck with this method. Same error continued to come up. I tried several other methods like creating a totally new user as well but the only thing that worked was using mysql2 as the library. – Autopilot

Change the password value – Webfoot

worked for me in 2024 with workbench thanks! – Thermoluminescent

Answer 3

Install mysql2 instead of mysql

npm install mysql2

Then add this to index.js

const mysql = require('mysql2')

Cnidoblast

Answer 4

You have to reconfigure MySQL Server. for that follow these steps.

1) Open mysql intsaller.

2) Click on Reconfigure (to the left mysql server)

3) Go to Authentication method tab (by clicking on Next twice.)

4) Then select the radio button Use Legacy Authentication Method. Click Next.

5) Enter your password. Click on Check. Go to Apply Configuration (by clicking on Next twice).

6) Click on Execute. Then Finish.

That's it. Enjoy Hacking!!

Disunion

Answer 5

Was getting ERROR 1251 in node v17 with mysql

❌ // didn't worked: errno 1251 ER_NOT_SUPPORTED_AUTH_MODE

import mysql from 'mysql';

Try to remove mysql from node and replace it for mysql2

yarn remove 'mysql'

yarn add 'mysql2'

✅  import mysql2 from 'mysql2';

This fixed the issue for me!

Chablis

Answer 6

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

Urease

Answer 7

I also had the same problem. It did not work for the root user. I created a new user (using mySQL workbench) and gave privileges to the new user. Then I tried with the new user and it worked fine. see workbench settings

Heteroecious

Answer 8

Try to remove mysql

yarn remove mysql
yarn add mysql2
import mysql2 from "mysql2"

This fixed issue for me. code :

"ER_NOT_SUPPORTED_AUTH_MODE" errno : 1251 fatal : true sqlMessage : "Client does not support authentication protocol requested by server; consider upgrading MySQL client" sqlState : "08004"

Helfrich

Please format your code properly using backticks. – Gerhardt

Answer 9

Try this with your password and user name root if your password was not root try with your password.

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

Regular

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Wormhole

Answer 10

I did not want to manipulate my "root" user because I'm using it in my Java applications, so that I decided to create a new User for NodeJS stuff.

I'm using MySQL 8.0.31, and Node's MySQL version "2.18.1" This solution really solved my problem:

first, log in to the MySQL server using root access:

mysql -u <MYSQL_USER> -p <MYSQL_PASSWORD>

once you entered mysql shell, run these commands:

create user '<YOUR_USERNAME>'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '<YOUR_PASSWORD>';

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

FLUSH PRIVILEGES;

Maximilianus

Answer 11

1.Copy the code below

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

2.Paste in the Table you want to connect

3.Run the thunder icon

Distrustful

Answer 12

This is because in your mysql2 version is outdated please update it to current LTS. I was also facing same issue then I just delete my node_modules file and package-lock.json file and change the version of my mysql2 version to current LTS version in my package.json file and again install all my files by running command npm install.

Royalroyalist