asked on

MYSQL: ER_NOT_SUPPORTED_AUTH_MODE

Hello Experts,

I am looking for a solution to a ER_NOT_SUPPORTED_AUTH_MODE that is stating that the authentication protocol is not supported requested by the server, consider upgrading.

The latest version of MYSQL out is 8.0.12 and has no updates to install. MySQL and Node.JS are being used in combination for this demonstration. The node.js file runs fine in the win cmd line when referencing the file.

There is a password that I have set to the MySQL server, which will be needed. This is a LocalHost. Running the page set at the port 3000 returns as a "unable to connect error"

I have tried this solution to no avail in the mySQL command line in an attempt to bridge the issue:

use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';

Only the mySQL command line can be used (excluding workbench, etc). This seems to be a CLIENT_AUTH problem with the latest mySQL version?
index.js

David Johnson, CD

node.js has problems with the authentication

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

Open in new window

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

Open in new window

Jason Maxwell

ASKER

Hello Expert,

The node.js file does run when calling it in the cmd line using node index.js command. There is confirmation message displayed in the cmd and that it is listening on the port.

Before using the ALTER USER command:

1)for the BY 'password' part, is that taking the id of password variable already containing the field data of the existing password, or does that have to be filled with the existing password combination from the node js file?

2)Would this also permanently change the authentication issue so that future node .js files ran in combination with MYSQL will run with ERR_AUTHENTICATION problems, or will this command have to be instantiated per node.js file?

Looking for more of a permanent solution for the ERR_AUTHENTICATION error message.

Jason Maxwell

ASKER

Hello

I have used the

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

Open in new window

and now have ER_ACCESS_DENIED_ERROR using password:YES with the node.js still not running when connected to the mySQL.

I need to revert the last changes done and solve original ER_NOT_SUPPORTED_AUTH_MODE

Jason Maxwell

ASKER

I have used the default changed password and have gotten back into the database.

Now, I have accessed the database in mySQL by using use nameofdatabase;

Then I have changed the node.js passwords to include the new password in replacement of the old.

Can anyone tell why the initial passwords were running into an Authentication issue and only after ALTERING the password in the mySQL command line, it stopped? Also is the command a permanent solution or does it have to be re-ran with each problem?

David Favor

Might relate to David's first comment.

Might relate to new MySQL-8.X password handling changes, which may seem subtle + tend to break software, related to authentication.

https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html#pluggable-authentication-available-plugins provides a list of the authentication plugins available.

If the mysql library binding you're using with NodeJS only handles old style passwords, then you'll have to determine if you're going to update your NodeJS binding or trying + fallback to old style passwords at the MySQL server level.

There are many considerations.

I'd suggest this. First run your MySQL server with --skip-grant-tables + verify your application is working.

Then restart MySQL with various plugin combinations.

Or the simple approach use https://insidemysql.com/introducing-connector-node-js-for-mysql-8-0/ info to replace your MySQL binding with full 8.0 support, which will also resolve other subtle problems you'll likely hit down the road.

Jason Maxwell

ASKER

Thank you

I found out that it is indeed a Client_AUTH issue with the latest version of MySQL.

This is good to know as it is not a end-user issue but a AUTH issue with the MySQL v8.

Does

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

Open in new window

is only ran once in the MYSQL cmd line? Since it is a root change, I am assuming that this has to be done only once. It would be useful to be able to use any password set with special letters and combinations instead of the default.

See that the mysqladmin flush-logs also restores privilege checks after --skip-grant-tables

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.