23

Problem in mysql used in nodejs

const mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '123456789',
  database : 'userdata'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err);
    return;
  }

 console.log('connected as id ' + connection.threadId);
});

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Kumar
  • 277
  • 1
  • 3
  • 14
  • 5
    Possible duplicate of [MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client](https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server) – Prashant Gupta Oct 15 '18 at 11:46
  • Right, if you are using MySQL 8.0 series, check [this](https://stackoverflow.com/questions/50373427/node-js-cant-authenticate-to-mysql-8-0/50377944#50377944) as well. – ruiquelhas Oct 15 '18 at 14:39
  • Prashant Gupta -- for me it working thank you so much man.. – Kumar Oct 17 '18 at 05:57

3 Answers3

44

Had the same problem.

Install MySQL Workbench

Execute this query: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456789'; in MySQL Workbench

Head back over to Node.js and try running the file again. It should work, at least it did for me.

Margaret Smith
  • 480
  • 6
  • 4
  • This query bars me to access DB even using WorkBench – Waseem Ahmad Naeem Apr 11 '19 at 09:35
  • I just ran this query and now I cannot even log into workbench – Neil Girardi Sep 18 '22 at 01:29
  • Don't use it on the root user then. Mostly it's never good to use the root user as an application user. Especially because creating a new user and granting it all privileges on one DB can be accomplished in two commands: `CREATE USER 'other_user'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456789'; GRANT ALL PRIVILEGES ON userdata.* TO 'other_user'@'localhost';` – Michael Plautz Jan 22 '23 at 03:47
9

Best Solution try this it will resolve:

You installed mysql server using "mysql installer"

1)open -> "mysql intsaller"

2)press reconfig mysql server

3)select left side "authentication method tab"

4)select radio button -->use legacy authentication method

5)now stop and restart the db

Thanks!

tamilselvan s
  • 1,045
  • 2
  • 11
  • 13
7

In any database software (mysql workbench,datagrip, dbeaver...):

First run this query:

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

Second, if your problem still persists, run this query:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Combining and running these two queries separately worked for me with dockorize mysql.

buddemat
  • 4,552
  • 14
  • 29
  • 49
joah
  • 71
  • 1
  • 2