MySQL server ER NOT SUPPORTED AUTH MODE

Problem

I had MariaDB installed on a “Node.js Server,” but I chose to switch to a SQL Database. “MariaDB” was uninstalled and entirely removed, following which I installed the “Community Ed.” “MySQL Database.” After completing the *’MySQL Setup Process,’** I attempted to connect to the database using a JavaScript document that implemented the de facto code snippet for a JS DB Connection — my DB-connection document is displayed in the code snippet below. The JS/SQL connection failed at each attempt, which was disappointing.

"ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication
protocol requested by server. Consider upgrading MariaDB client."

    var mysql = require('mysql');

    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'root',
        password : '********',
        database : 'foobarDb'
    }); 

Asked by João Gouveia

Solution #1

Use the following for MySQL v8.0:

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

Answered by Archil Labadze

Solution #2

The Quick Action Settings must be reconfigured by clicking the “Reconfigure Link,” as indicated in the screenshot below. Select “Legacy password” for v5.1 from the drop-down menu.

The latest “MySQL Version,” v8.0, was installed. For authenticating users during login, the current version uses a different encryption plugin. The previous encryption techniques are reverted in versions 5.6 and 5.1. Please be aware that Oracle has identified multiple security vulnerabilities in versions 5.6 and 5.1.

Answered by Vinayak Shedgeri

Solution #3

The most popular responses in this Q/A thread are mostly correct, but they are disorganized to say the least. There is a solution here, however it is made up of bits and parts from three other answers. To provide a single solution that is more beneficial and time saving, I will strive to write a response myself in a clear, succinct, and logical manner. I’ll go over the entire difficulty that Ubuntu users are having, as well as information that isn’t mentioned in any other solution and will help readers grasp the problem that they are having.

The problem you’re having has to do with the fact that the ‘ROOT’ account in Ubuntu doesn’t have a password and can be accessed by anyone with $ sudo capabilities (a fact most software developers/I.T. professionals are probably already aware of). To provide clarification for anyone facing this issue who may be unfamiliar with some of the terminology I’m using, Ubuntu users register as the Root-user using the sudo -i command, whereas every other Linux distribution uses a User-ID with a Password. In actuality, I don’t recall ever needing to be a ROOT user for anything other than Database Management, and then just when I’m first setting up a database on a server, though my experience is probably unique.

Because Ubuntu lacks a ‘ROOT PASSWORD,’ everyone who is encountering the problem is running an Ubuntu OS/SHELL distribution. We can’t give Ubuntu SHELL a “root password” unless we replace the Ubuntu kernel and almost everything else in the operating system.

We may not be able to give the Ubuntu SHELL a root password, but we can, and we will, give MySQL a ‘ROOT PASSWORD’.

and you are running an Ubuntu distro, then you should be getting an error message that probably looks somthing like the one I got when I had to fix this issue.

ERROR: (28000): Access denied for user 'ajc'@'localhost' 
ERROR: ERROR_NOT_SUPPORTED_AUTH_MODE: Client does not support
authentication protocol requested by server; consider upgrading 
MySQL client

'Client does not support authentication protocol requested by server; consider upgrading MySQL client

let mysql = require('mysql');

let connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '********',
    database : 'DB_App_00',
});

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

    console.log('Connected to the MySQL server.');
  });

~$: sudo apt update
~$: sudo apt upgrade

~$: sudo mysql -u root

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ChoosePassword';

mysql> FLUSH PRIVILEGES;

 ~$: mysql -u root -p 

…you should now be able to leave.

mysql>exit

let connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '********',
    database : 'DB_App_00',
});

~$: node testsql.js

If it doesn’t say connected, something went wrong, but if everything went smoothly, you should be able to connect. It took some time for me to figure out how to make it work, but my response should save you time from reading the other half-written replies.

Answered by JΛYÐΞV

Solution #4

You can either modify an existing user or create a new user to utilize mysql native password.

CREATE USER 'new_user'@'%' IDENTIFIED WITH mysql_native_password BY '***';
GRANT USAGE ON *.* TO 'new_user'@'%';
ALTER USER 'new_user'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `new_user`.* TO 'new_user'@'%';
FLUSH PRIVILEGES;

Set your password and change new user with your new user name.

Now you can access mysql from node using the mysql package,

 npm install mysql

For this package, it is advised that you use pool connection.

Answered by pingle60

Solution #5

I believe that the authentication for establishing a connection is a little messed up in some MySQL versions. All I had to do was change the CreateConnection(…) credentials to include the line “insecureAuth”: true.

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

The error has vanished, and the customer is now connected correctly.

Answered by João Gouveia

Post is based on https://stackoverflow.com/questions/44946270/er-not-supported-auth-mode-mysql-server