Mysql Error - 1251 'ER_NOT_SUPPORTED_AUTH_MODE'

Question

I know that this question was already answered in SO, but the suggested fixxes dont work for me!

Error:

      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Handshake.ErrorPacket (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
    at Protocol._parsePacket (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (events.js:223:5)
    at addChunk (_stream_readable.js:309:12)
    --------------------
    at Protocol._enqueue (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Protocol.handshake (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\protocol\Protocol.js:51:23)
    at Connection.connect (C:\Dev\Programmierung\FitnessAppExpo\node_modules\mysql\lib\Connection.js:116:18)
    at Object.<anonymous> (C:\Dev\Programmierung\FitnessAppExpo\server\server.js:29:5)
    at Module._compile (internal/modules/cjs/loader.js:955:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:991:10)    
    at Module.load (internal/modules/cjs/loader.js:811:32)
    at Function.Module._load (internal/modules/cjs/loader.js:723:14)
    at Function.Module.runMain (internal/modules/cjs/loader.js:1043:10)
    at internal/main/run_main_module.js:17:11 {
  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
}

I have a Xampp Server running with myphpadmin on localhost. I tried to insert this code in myphpadmin:

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

But if I try to use this it gives me an error inside phpmyadmin:

Missing ',' before start of a new ALTER operation!

and a second one stating:

Unknown ALTER operation (near by)

Is there something special I need to do when I want to use or execute this command inside phpmyadmin? Because the way that the ALTER Query is written doesnt let me execute it..

Node (Express) Code:

var mysql = require("mysql");
var express = require("express");
var session = require("express-session");
var app = express();
var cors = require("cors");

var bodyParser = require("body-parser");
app.use(bodyParser.json({ limit: "50mb" }));
app.use(bodyParser.urlencoded({ limit: "50mb", extended: true }));

app.use(
  session({
    secret: "ssshhhhh",
    saveUninitialized: true, // (default: true)
    resave: true
  })
);

app.use(cors({ origin: ["http://localhost:4200"], credentials: true }));

var con = mysql.createConnection({
  host: "localhost",
  // host: "127.0.0.1:3307",
  user: "root",
  password: "",
  database: "fitness_app"
});

con.connect(err => {
  if (err) throw err;
  console.log("Database connected -> ");
});

var server = app.listen(8090, () => {
  var host = "localhost";
  var port = server.address().port;
  console.log("App listening at http://%s:%s", host, port);
});

~Faded

node-mysql version? MySQL server version? SHOW CREATE USER root@localhost? — danblack
– danblack, Commented Jun 9, 2020 at 13:07
mysql version is 2.18.1 express version: 4.17.1 // localhost users I dont create them but they exist in the db there are 3 root users — yesIamFaded
– yesIamFaded, Commented Jun 9, 2020 at 13:13
SELECT VERSION() to show the server version. SHOW CREATE USER to show the plugin defined by the user. Do any of these issues look familiar? — danblack
– danblack, Commented Jun 10, 2020 at 0:11
This is my log after select version: 10.4.11-MariaDB and this is the create user: CREATE USER 'root'@'localhost' — yesIamFaded
– yesIamFaded, Commented Jun 10, 2020 at 6:38
If the full info was included it would probably show an auth_socket plugin. This seems to one of the existing nodejs bugs. Recommend creating a new user for application use. Package updates depend on the root user so best leave it along. MariaDB and mysql support effectively an unlimited number of database users. — danblack
– danblack, Commented Jun 10, 2020 at 6:54

ouflak · Accepted Answer · 2022-02-01 12:19:51Z

1

Execute the following query in MYSQL Workbench

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

Where root as your user localhost as your URL and password as your password

Then run this query to refresh privileges:

flush privileges;

Try connecting using node after you do so.

If that doesn't work, try it without @'localhost' part.

edited Feb 1, 2022 at 12:19

ouflak

2,56310 gold badges46 silver badges53 bronze badges

answered Feb 1, 2022 at 10:21

Sukriti Verma

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Dum · Accepted Answer · 2024-04-13 06:46:34Z

0

Use the package mysql2 instead of mysql.To install you can run

npm install mysql2

Simply changing const mysql = require('mysql'); to const mysql = require('mysql2'); worked for me.

Reason for the error: MySQL 8 uses a new authentication protocol. mysql2 supports the new protocol but mysqljs/mysql does not

Original answer: https://stackoverflow.com/a/66561311/12731030

answered Apr 13, 2024 at 6:46

Dum

1,5112 gold badges13 silver badges25 bronze badges

Collectives™ on Stack Overflow

Mysql Error - 1251 'ER_NOT_SUPPORTED_AUTH_MODE'

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related