CODEWITHSUNDEEP
mysqlnode.jsexpress
Manoj
Manoj

Reputation: 221

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

Am trying to connect to MySQL database using the below code.

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'pass',
    database : 'my_db',
    insecureAuth : true
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results[0].solution);
});

connection.end();

But am getting the below error :

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

I have MySQL-installer-community-8.0.11.0 installed on my local.

Do I need to install any other connectors to connect to the database?

Upvotes: 3

Views: 13686

Answers (6)

Martin Smith
Martin Smith

Reputation: 129

Try using the MySQL2 npm package

import mysql2 from 'mysql2';

Upvotes: 0

DBurca
DBurca

Reputation: 31

I had the same problem and this solution worked for me:

  1. Go to your MySQL workbench and click on your current database.
  2. In the Navigator window (on the left), click Users and Privileges.
  3. Click Add Account at the bottom of the user list.
  4. Give it another login name (e.g. node_test).
  5. Set the Authentication type to Standard.
  6. Set Limit to Host Matching to localhost.
  7. Set a new password (e.g. coding-is-awesome).

  8. Here's what your resulting code should look like:

    var mysql = require("mysql");

var con = mysql.createConnection({
  host: "localhost",
  user: "node_test",
  password: "coding-is-awesome"
});

con.connect(function(err) {
  if (err) {
    console.log("Failed to connect");
    throw err;
  } else {
    console.log("Connected!");
  }
});

Upvotes: 2

Manoj
Manoj

Reputation: 221

MySQL(8.x) connection works fine if we choose legacy password encryption of MySQL(5.x) during MySQL(8.x) installation. If i choose strong password encryption with Mysql(8.x) during installation its not working.

Upvotes: 4

thoomasbro
thoomasbro

Reputation: 63

I'm using https://hub.docker.com/r/mysql/mysql-server/

In the doc, it's said

MYSQL_ONETIME_PASSWORD: When the variable is true (which is its default state, unless MYSQL_ROOT_PASSWORD is set or MYSQL_ALLOW_EMPTY_PASSWORD is set to true), the root user's password is set as expired and must be changed before MySQL can be used normally. This variable is only supported for MySQL 5.6 and later.

Try setting MYSQL_ROOT_PASSWORD for your docker environment. You also may need to change auth mode for your user.

> mysql -u root -p

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

Worked for me.

Edit : To check what auth is set:

SELECT user, plugin FROM mysql.user WHERE user = 'username';

Upvotes: 5

King of Hentai
King of Hentai

Reputation: 41

I had the same problem yesterday with my docker container with mysql + nodejs. I didnt found so far a fix but it has something todo with the new mysql version(8.X). After i reinstalled mysql with version 5 everything worked well for me.

Upvotes: 4

Chad Lomax
Chad Lomax

Reputation: 113

I would try removing the insecureAuth : true line and try again.

Upvotes: 0

Related Questions