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

Question

I can't make a simple connection to the server for some reason. I install the newest MySQL Community 8.0 database along with Node.JS with default settings.

This is my node.js code

    var mysql = require('mysql');
    
    var con = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "password",
      insecureAuth : true
    });
    
    con.connect(function(err) {
      if (err) throw err;
      console.log("Connected!");
    });

Below is the error found in Command Prompt:

C:\Users\mysql-test>node app.js
    C:\Users\mysql-test\node_modules\mysql\lib\protocol\Parse
    r.js:80
            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:\Users\mysql-
test\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
    at Handshake.ErrorPacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\sequences\Handshake.js:130:18)
    at Protocol._parsePacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\Protocol.js:279:23)
    at Parser.write (C:\Users\mysql-test\node_modules\mys
ql\lib\protocol\Parser.js:76:12)
    at Protocol.write (C:\Users\mysql-test\node_modules\m
ysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.js:103:28)
    at Socket.emit (events.js:159:13)
    at addChunk (_stream_readable.js:265:12)
    at readableAddChunk (_stream_readable.js:252:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    --------------------
    at Protocol._enqueue (C:\Users\mysql-test\node_module
s\mysql\lib\protocol\Protocol.js:145:48)
    at Protocol.handshake (C:\Users\mysql-test\node_modul
es\mysql\lib\protocol\Protocol.js:52:23)
    at Connection.connect (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.js:130:18)
    at Object.<anonymous> (C:\Users\mysql-test\server.js:
11:5)
at Module._compile (module.js:660:30)
at Object.Module._extensions..js (module.js:671:10)
at Module.load (module.js:573:32)
at tryModuleLoad (module.js:513:12)
at Function.Module._load (module.js:505:3)
at Function.Module.runMain (module.js:701:10)

I've read up on some things such as: https://dev.mysql.com/doc/refman/5.5/en/old-client.html https://github.com/mysqljs/mysql/issues/1507

But I am still not sure how to fix my problem.

Painterly

All VS Code users who use SQLTools extension should refer to this post in case they are having trouble with this, especially if you just upgraded a mysql instance on your development computer – Superficies

I had the same issue because I used port 3306 when connecting but when I've installed MySQL I set the default port on 3307. So be careful, it could also be a protocol mismatch. – Pedalfer

You should consider changing the accepted answer to Aidin's. – Stator

MySQL version 8 is not yet supported by Strapi. There is however an official blog post to help you configure MySQL version 8 with Strapi. – Underdrawers

Just update your npm package to mysql2 link. More details in this SO answer. – Londrina

https://mcmap.net/q/55392/-error-client-does-not-support-authentication-protocol-requested-by-server-consider-upgrading-mysql-client – Alejandro

Answer 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.

Gain

It worked for me without the @localhost (i guess it could be because it was not for the root user) – Tactical

can not login with root after this. The password is not accepted anymore.. – Bilabial

replace 'password' with your root password if you know it – Derinna

It works...... but why it was not working before and why it worked after this query is still a question. – Gorgon

Hey people! Can someone go in depth of what is going on here? Why changing the native user would make such error go away? – Boric

Hi @GuilhermeMatuella This is because caching_sha2_password is introduced in MySQL 8.0, but the Node.js version is not implemented yet. – Estus

It works for me without the @'localhost' for me, anyone minds to explain on this? – Styria

@GuilhermeMatuella Please see my answer below for a detailed explanation https://mcmap.net/q/53951/-mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server-consider-upgrading-mysql-client – Ogham

Works for me only if I omit the @hostname part. If I include the hostname it doesn't work. – Cephalic

don't forget to restart node or pm2 process after u do this – Ire

The script doesn't execute if I remove the @'localhost' part. When I try to connect, the error I get says 'Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: NO)' – Satiety

@Satiety this guide worked for me like a charm: dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html. I use Windows, so I follow this section: B.4.3.2.1 Resetting the Root Password: Windows Systems – Illimitable

If I remove the @'localhost' portion, i get the following error: "Error Code: 1396. Operation ALTER USER failed for 'root'@'%'" – Satiety

THIS does not work MySql tells me that there is an Error around BY so the command is typed wrong I guess – Fjord

don't skip on this answer below – Cuspidation

i have to try it without @'localhost' part, otherwiise it wont work – Tips

I ran this in terminal, dont have to remove localhost part. – Mukund

@GuilhermeMatuella for the details on how this solution works and why is this NOT recommended in a production environment, see my answer below: stackoverflow.com/a/56509065 – Ogham

Can you tell me how this is works? – Sp

worked without @localhost part at docker mysql:latest images – Allhallowtide

How to solve the same problem but with SQL Server Instead? – Copeck

Worked for me by only triggering the command 'flush privileges;' – Falbala

you just locked me out of everything with these commands. thanks – Titoism

ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY ''; (in my case -> user: root, pass: ' ') – Hydrolysis

It worked for me without the '@localhost' part! Thanks for helping! – Inhaler

this leads to another error internal assertion for me – Locution

Answer 2

Summary

1. If you just want to get rid of the error, at the cost of risking the security of the project (e.g. it's just a personal project or dev environment), go with @Pras's answer -- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' and then flush privileges
2. If you want to have a fix for it, without knowing why, just install and use mysql2 (instead of mysql) and use it -- npm i mysql2, and mysql = require('mysql2');.
3. If you are a curious developer who is always eager to learn, keep reading ... :)

---

What's going on?

Let's first make it clear what's going on.

MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_password is used rather than our good old mysql_native_password (source). It should be obvious that using a crypto algorithm with several handshakes is more secure than plain password passing that has been there for 24 years!

Now, the problem is mysqljs in Node (the package you install with npm i mysql and use it in your Node code) doesn't support this new default authentication method of MySQL 8, yet. The issue is in here: https://github.com/mysqljs/mysql/issues/1507 and is still open, after 3 years, as of July 2019.

UPDATE June 2019: There is a new PR in mysqljs now to fix this!

UPDATE Feb 2020: Apparently it's scheduled to come in version 3 of mysqljs.

UPDATE July 2020: Apparently it's still not in yet (as of April 2020 at least), but it's claimed that node-mysql2 is supporting Authentication switch request. Please comment below if node-mysql2 is working fine for this issue -- I will test it later myself.

UPDATE April 2021: It seems like the issue is still there and just 3 days ago, someone created a fork and made it there -- yet not official in the mysql.js package. Also, as per the comments below, it seems like mysql2 package is working fine and supporting Authentication-switch properly.

---

Your Current Options

Option 1) [NOT RECOMMENDED] Downgrade "MySQL" to authenticate using good old "mysql_native_password"

That's what everybody suggests here (e.g. top answer above). You just get into mysql and run a query saying root is fine using old mysql_native_password method for authentication:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...

The good thing is, life is going to be simple and you can still use good old tools like Sequel Pro without any issue. But the problem is, you are not taking advantage of a more secure (and cool, read below) stuffs available to you.

Option 2) [Meh...] Replace "Node" package with MySQL Connecter X DevAPI

MySQL X DevAPI for Node is a replacement to Node's Mysqljs package, provided by http://dev.mysql.com official guys.

It works like a charm supporting caching_sha2_password authentication. (Just make sure you use port 33060 for X Protocol communications.)

The bad thing is, you have left our old mysql package that everyone is so used to and relies on.

The good thing is, your app is more secure now and you can take advantage of a ton of new things that our good old friends didn't provide! Just check out the tutorial of X DevAPI and you'll see it has a ton of new sexy features that can come in handy. You just need to pay the price of a learning curve, which expectedly comes with any technology upgrade. :)

PS. Unfortunately, this XDevAPI Package doesn't have types definition (understandable by TypeScript) yet, so if you are on typescript, you will have problems. I tried to generate .d.ts using dts-gen and dtsmake, but no success. So keep that in mind.

Option 3) [RECOMMENDED] Replace "mysql.js" with "mysql2.js" package

As mentioned above, mysql package (NPM package link) is still having this issue (as of April 2021). But mysql2 package (NPM package link) is not. So probably the following should be the one-liner answer!

npm un mysql && npm i mysql2

Please note that mysql2 is a forked work off of the popular mysql, but its popularity (620K downloads per week for mysql2 in April 2020) has got close to the original package (720K download per week for mysql in April 2021) that making the switch seems reasonable!

Ogham

nice, an answer that actually explains mysql_native_password instead of just telling you to do it – Wreck

Yes, a proper explanation for once. Good on you Aidin. How I wish connecting apps did not automatically configure port 3306 to MySQL. We have enough trouble prising MySQL and MariaDB away from 3306. – Johnathon

Be careful of Oracle plugins, you might get a big penalty: theregister.co.uk/2019/10/04/oracle_virtualbox_merula – Ethelynethene

Does not work for me :( User is identified with mysql_native_password, php connector works ok, nodejs - fails with ER_NOT_SUPPORTED_AUTH_MODE :( – Moneymaker

Thanks for the explanation. Would you recommend option 2 to be used for new node projects? – Politesse

@emen, If you are not comfortable with reading the library code (in lieu of proper documentation), or you need to bring in Typescript, or security is NOT a life-threatening issue for your project, then No, don't go with option #2. :) – Ogham

MySQL X DevAPI for Node is not a supported driver with knex.js fwiw – Somite

node-mysql2 still doesn't support it but they are working on it as of 11-28-20... - github.com/sidorares/node-mysql2/issues/1248 – Beneficence

Incorrect. It's ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' – Anitaanitra

@HenriquedeSousa, are you sure you read the answer? I have written, in detail, why the ALTER USER ... solutions are "bad". I guess you have just attempted to copy/paste the SQL line (ending with ...) and because it doesn't work, obviously, you are saying this answer is "incorrect"? :) – Ogham

My phrasing was not correct and the message did not went through, sorry @Ogham :) I said the command was incomplete because I used it on a small PoC I imported from somewhere else, and it wasn't working because there were missing characters (namely theBY 'password') that you replaced by the ellipsis :) – Anitaanitra

Very nice explanation. Helped a lot in solving problem in connecting to MySql from NodeJs. – Furfuran

After doing this I can't log in from the terminal anymore: https://mcmap.net/q/55394/-cannot-log-into-any-account-on-mysql-from-terminal/3310334 – Transpire

@Transpire You are having a problem with the solution that I have said "is Bad!". I don't think you should have commented under this solution, unless, again, you take Stackoverflow answers like pics.me.me/… ;) – Ogham

@Ogham your answer is great and I've upvoted it now, unfortunately it's buried by the lazy answer above and lazy (now regretful) me just copy/pasted from top answer. I wish I had seen yours first. Anyway I post my question to you because you seem to have a far better understanding of this area of mysql, so I just have my fingers crossed – Transpire

mysql2 solved the problem gracefully. I think this is the most proper solution. – Gallenz

@Gallenz thanks for verifying it. I added it as the third solution (recommended). – Ogham

still getting the same error with mysql2 – Zulema

Great answer! Still, don't forget to check the credentials... – Infirmary

Let me add a few rumblings here. What an amazing story with the patch. May people not be so lazy. May not their only wait for an author to take in action, but do something themselves. The authors may get distracted with their lives and other stuff. It's opensource after all. – Inextinguishable

I was battling with this problem, and I actually changed the entrypoint on my docker container so that I could manually troubleshoot the connection. I wrote a little node app using knex and mysql2 and it connected from the same container without a hassle. So I knew it was something to do with the app I'm trying to run. Thanks to you I now know exactly what the problem is. – Infare

I tried installing mysql2 in my project directory, but npm gave me following error: $ npm i mysql2 npm ERR! code UNKNOWN npm ERR! syscall symlink npm ERR! path X:\<path-to-directory>\knex.js npm ERR! dest X:\<path-to-directory>\node_modules\knexfile npm ERR! errno -4094 npm ERR! UNKNOWN: unknown error, symlink 'X:\<path-to-directory>\knex.js' -> 'X:\<path-to-directory>\node_modules\knexfile' --> <path-to-directory> is some path to my project ;) Anyone an idea how to solve? The directory has no symlink knex.js – Canuck

This should be the correct answer, as it explains root of the problem and gives solutions with explanations. Thank you very much Aidin – Salpa

I think I solved the problem with this, the error is gone, but now I cannot connect to db because of some username issue. Everything is imported just fine from the .env file to the .config file, except the username: mysql2 will try to connect with the username of the PC's (at least it seems like that's the one) and not what I've defined in .env. Does somebody have an idea why? – Ferminafermion

later edit: I didn't know, and had to find the hard way: process.env.USER translates to username of machine. I've changed to DB_USER and now it's working fine. – Ferminafermion

FYI, the MySQL X DevAPI Connector for Node.js contains the proper TypeScript type definitions for the whole API since v8.0.30. – Holiness

Issue still persists as of 6 July 2023. – Janejanean

Answer 3

Using the old mysql_native_password works:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword';
-- or
CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar';
-- then
FLUSH PRIVILEGES;

This is because caching_sha2_password is introduced in MySQL 8.0, but the Node.js version is not implemented yet. You can see this pull request and this issue for more information. Probably a fix will come soon!

Rayshell

whats the difference between adding @localhost and just 'root' – Balalaika

Query works. But still node couldn't connect to the MySQL server. Any idea? Here's the error, { "code": "ER_ACCESS_DENIED_ERROR", "errno": 1045, "sqlMessage": "Access denied for user 'root'@'localhost' (using password: YES)", "sqlState": "28000", "fatal": true } – Wakeful

Answer 4

Full Steps For MySQL 8

Connect to MySQL

$ mysql -u root -p
Enter password: (enter your root password)

Reset your password

(Replace your_new_password with the password you want to use)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
mysql> FLUSH PRIVILEGES;
mysql> quit

Then try connecting using node

Chops

@sky see my answer above for the details. – Ogham

I prefer this direct method as opposed to swimming around the confusing Mysql Workbench wondering where/if commands are actually running. – Potence

This solution worked for me except without the @'localhost'. – Phonoscope

Answer 5

Although the accepted answer is correct, I'd prefer creating a new user and then using that user to access the database.

create user nodeuser@localhost identified by 'nodeuser@1234';
grant all privileges on node.* to nodeuser@localhost;
ALTER USER 'nodeuser'@localhost IDENTIFIED WITH mysql_native_password BY 'nodeuser@1234';

Preraphaelite

If someone wants to go this route, here's an article with some explanation: digitalocean.com/community/tutorials/… – Polloch

Siddhant would "prefer", but I was "required" to do this. IMO, this makes this answer required reading! – Bryannabryansk

for me the given priveleges were not enough to create a DB. i gave grant all privileges on *.* to nodeuser@localhost; – Referent

I think the use of grant privileges here worked well with me. I can advise someone to use this answer. – Generation

What exactly is that 'nodeuser@1234' supposed to be? A new password? Why do we need that? – Kudos

@Kudos I just cleaned the code to make it easier to understand (if it will be approved): ``` CREATE USER 'nodeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'somePassword'; GRANT ALL PRIVILEGES ON node.* TO 'nodeuser'@'localhost'; ``` – Peisch

Answer 6

If you ran into this issue but continued to wish to utilise version 8 of MySQL, you can. When creating the database using Docker, you can accomplish this by instructing MySQL Server to implement the legacy authentication plugin.

Thus, your compose file will appear as follows:

# Use root/example as user/password credentials

version: '3.1'

services:

  db:
    image: mysql:8.0.15
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
       MYSQL_ROOT_PASSWORD: 'pass'
       MYSQL_DATABASE: 'db'
       MYSQL_USER: 'user'
       MYSQL_PASSWORD: 'pass'
    ports:
      - 3318:3306
    # Change this to your local path
    volumes:
      - ~/Database/ORM_Test:/var/lib/mysql

Stridulate

I'm using this command, but still getting the unsupported authentication error message when using mysql in Node.js – Ethelynethene

The full stacktrace is here: pastebin.com/SzayQzdh and the docker-compose.yml: pastebin.com/7pUrWYDs The error itself is: Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client – Ethelynethene

@JuhaUntinen make sure that you removed a Docker container (the one created by docker-compose) and volume (~/Database/ORM_Test) and then run 'docker-compose up' again. Otherwise the changes from 'command' won't be applied. It helped in my case. – Boult

The command has to be set in docker-compose BEFORE the user is made. If you are using MYSQL_USER and MYSQL_PASSWORD, you'll have to wipe out /var/lib/mysql and start from scratch so command is set before creating the user, or fallback to one of the other answers and ALTER the already created user – Hybridize

@VitaliiIvanov Exactly. This can be accomplished by docker-compose down --volumes && docker-compose up. – Molluscoid

Answer 7

Simplest answer is :-

1. Use mysql2 instead of mysql in node

install mysql2 in node

2. npm install mysql2
   

Don't downgrade your mysql db.

You are good to go. Happy Coding!

Forsyth

dont forget to change to mysql2 in require – Dermatology

I get the result as [object Object],[object Object][object Object].... What could cause this? – Feme

Found the solution. It retrieves the object, so that I have to read it by line and column. – Feme

Answer 8

If the ALTER USER ... command line doesn't work for you AND if you are using Windows 10 then try to follow those steps:

1) Type MySQL in the windows search bar

2) Open the MySQL Windows Installer - Community

3) Look for "MySQL server" and click on Reconfigure

4) Click on "Next" until you reach the "Authentification Method" phase

5) On the "Authentification Method" phase check the second option "Use Legacy Authentication Method"

6) Then follow the steps given by the Windows installer until the end

7) When it's done, go into "Services" from the Windows search bar, click on "start" MySql81".

Now, try again, the connection between MySQL and Node.js should work!

Anabolism

This solution solved my problem. My code was fine, just the authentication on my Windows 10 machine was the issue. – Dynamiter

Answer 9

The cleanest solution is to do the below command, and this issue will go away:

npm uninstall mysql
npm install mysql2

And then use it normally in your code:

var mysql = require('mysql2');
    
var con = mysql.createConnection({
 host: "localhost",
 user: "root",
 password: "password"
});
    
con.connect(function(err) {
 if (err) throw err;
 console.log("Connected!");
});

I did not have any MySQL client in my system, but my docker container had an image, which was giving me this issue. Simply installing the mysql2 resolved the issue.

You can find the solution here as well, mentioned in the official mysql npm packages issues' answers. All the best.

Fridell

Great answer. Thanks! I'm still stuggling to make everything work with Docker, but that's another problem. – Outshout

Answer 10

In Mysql Latest docker container

ALTER USER root IDENTIFIED WITH mysql_native_password BY 'password';

Unwilling

WARNING: This will make your database less secure – Lanfri

Answer 11

Downgrading might not be a good option as:

1. Its upgraded for a reason (To provide better authentication).
2. You might not have enough permissions to make such changes.

You can use mysql2 package in place of mysql. Its mostly API compatible with mysqljs. Also, it has promises support.

Use it like: const mysql = require('mysql2/promise') (for promise based methods)

You can read more about mysql2 here: https://www.npmjs.com/package/mysql2

Courante

I just use mysql2! const mysql = require('mysql2'). It works! Thanks a lot👍 – Hydropathy

Dang, I might've created prettyquery for promise support for nothing :o – Erving

Answer 12

For MySql 8 instead of changing the authentication for the root user create a new user with all privileges and change the authentication method from caching_sha2_password to mysql_native_password. Please check the documentation by Ochuko Ekrresa for detailed steps.

Summary of Steps:

1. Login as root mysql -u root -p
2. Create new user CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
3. Grand all permission GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

Check the above-mentioned document link to get details on giving specific privileges.

4. Reload the privileges FLUSH PRIVILEGES;
5. Quit MySql quit; and login again with mysql -u [newuser] -p;
6. Last step change the authentication ALTER USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password by 'password';

Additional Info: For me after changing authentication for root, I was faced with Authentication issues and was unable to login. So I reset my password(Reset password doc).

Tinea

Answer 13

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. ...

Most of the answers in this question result in a downgrade to the authentication mechanism from caching_sha2_password to mysql_native_password. From a security perspective, this is quite disappointing.

This document extensively discusses caching_sha2_password and of course why it should NOT be a first choice to downgrade the authentication method.

With that, I believe Aidin's answer should be the accepted answer. Instead of downgrading the authentication method, use a connector which matches the server's version instead.

Lapotin

Answer 14

If you are using docker, it worked for me!

in the docker-compose.yml add the following lines:

mysql:
   ...    
   command: --default-authentication-plugin=mysql_native_password
   restart: always

after that, down the container and up again.

Lair

Answer 15

For existing mysql 8.0 installs on Windows 10 mysql,

1. launch installer,

2. click "Reconfigure" under QuickAction (to the left of MySQL Server), then

3. click next to advance through the next 2 screens until arriving

4. at "Authentication Method", select "Use Legacy Authentication Method (Retain MySQL 5.x compatibility"

5. Keep clicking until install is complete

Chaim

Aren't we trying to have the most recent, since it is more secure? – Papilla

Of course we should, but sometimes it requires re-writing parts of the code that the client/boss/specs insist should be untouched, or we're just lazy and don't want to patch the authentication code when it's tangential to the project or in someone else's scope... – Chaim

Answer 16

simple i uninstall mysql and install mysql2 for this issues and problem solved.

npm uninstall mysql && npm i mysql2

Fabrienne

Answer 17

Original documentation you can find here : https://dev.mysql.com/doc/dev/connector-nodejs/8.0/

'use strict';

const mysqlx = require('@mysql/xdevapi');

const options = {
  host: 'localhost',
  port: 33060,
  password: '******',
  user: 'root',
  schema: 'yourconference'
};

mysqlx.getSession(options)
  .then(session => {
          console.log(session.inspect());
           session.close();
  }).catch(err => {
    console.error(err.stack);
    process.exit(1);
  });

Cimabue

It looks more complicated, but I think we should follow the official guide! b – Ambiguity

Answer 18

I have MYSQL on server and nodejs application on another server

Execute the following query in MYSQL Workbench

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

Handful

Answer 19

With MySQL 8+ the new default authentication is caching_sha2_password instead of mysql_native_password. The new and more secure authentication method is not supported by the native mysql package yet, but you should consider using the package @mysql/xdevapi instead, which is officially supported and maintained by Oracle.

To install the new package, run:

npm install @mysql/xdevapi --save --save-exact

To connect to the database and INSERT some VALUES:

const mysqlx = require('@mysql/xdevapi');
var myTable;

mysqlx
    .getSession({
        user: 'root',
        password: '*****',
        host: 'localhost',
        port: 33060
    })
    .then(function (session) {

    // Accessing an existing table
    myTable = session.getSchema('Database_Name').getTable('Table_Name');

    // Insert SQL Table data
    return myTable
        .insert(['first_name', 'last_name'])
        .values(['John', 'Doe'])
        .execute()
    });

The official package documentation can be found here: https://dev.mysql.com/doc/dev/connector-nodejs/8.0/

Palinode

Answer 20

In addition to the above answers ; After executing the below command

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

If you get an error as :

[ERROR] Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted

Then try in the cmd as admin; set the path to MySQL server bin folder in the cmd

set path=%PATH%;D:\xampp\mysql\bin;

and then run the command :

mysql_upgrade --force -uroot -p

This should update the server and the system tables.

Then you should be able to successfully run the below commands in a Query in the Workbench :

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

then remember to execute the following command:

flush privileges;

After all these steps should be able to successfully connect to your MySQL database. Hope this helps...

Glaydsglaze

Answer 21

I just run into this problem too, with all the MySQL re-config mentioned above the error still appears. It turns out that I misspelled the database name.

So be sure you're connecting with the right database name especially the case.

Absinthe

Answer 22

I would recommend to use Knexjs with MySQL2.

And you have good to go with caching_sha2_password auth method.

Query with Knex:

const response = await knex.raw("SELECT * FROM USERS");

OR

If you don't have a remote user then use CREATE keyword instead of ALTER and just put the below command on the terminal.

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpass';
GRANT ALL ON *.* TO 'root'@'%';
Flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpass';
GRANT ALL ON *.* TO 'root'@'localhost';
Flush privileges;

All done :)

Alternate

Answer 23

Check privileges and username/password for your MySQL user.

For catching errors it is always useful to use overrided _delegateError method. In your case this has to look like:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  insecureAuth : true
});

var _delegateError = con._protocol._delegateError;

con._protocol._delegateError = function(err, sequence) {
    if (err.fatal)
        console.trace('MySQL fatal error: ' + err.message);

    return _delegateError.call(this, err, sequence);
};

con.connect(function(err) {
  if (err) throw err;

  console.log("Connected!");
});

This construction will help you to trace fatal errors.

Laterality

Answer 24

Just figured this out after trying numerous things. What finally did it for me was adding require('dotenv').config() to my .sequelizerc file. Apparently sequelize-cli doesn't read env variables.

Riffe

Answer 25

You can skip the ORM, builders, etc. and simplify your DB/SQL management using sqler and sqler-mdb.

-- create this file at: db/mdb/read.table.rows.sql
SELECT TST.ID AS "id", TST.NAME AS "name", NULL AS "report",
TST.CREATED_AT AS "created", TST.UPDATED_AT AS "updated"
FROM TEST TST
WHERE UPPER(TST.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%') 

const conf = {
  "univ": {
    "db": {
      "mdb": {
        "host": "localhost",
        "username":"admin",
        "password": "mysqlpassword"
      }
    }
  },
  "db": {
    "dialects": {
      "mdb": "sqler-mdb"
    },
    "connections": [
      {
        "id": "mdb",
        "name": "mdb",
        "dir": "db/mdb",
        "service": "MySQL",
        "dialect": "mdb",
        "pool": {},
        "driverOptions": {
          "connection": {
            "multipleStatements": true
          }
        }
      }
    ]
  }
};

// create/initialize manager
const manager = new Manager(conf);
await manager.init();

// .sql file path is path to db function
const result = await manager.db.mdb.read.table.rows({
  binds: {
    name: 'Some Name'
  }
});

console.log('Result:', result);

// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
  await manager.close();
  console.log('Manager has been closed');
});

Sacken

Answer 26

I had this error for several hours an just got to the bottom of it, finally. As Zchary says, check very carefully you're passing in the right database name.

Actually, in my case, it was even worse: I was passing in all my createConnection() parameters as undefined because I was picking them up from process.env. Or so I thought! Then I realised my debug and test npm scripts worked but things failed for a normal run. Hmm...

So the point is - MySQL seems to throw this error even when the username, password, database and host fields are all undefined, which is slightly misleading..

Anyway, morale of the story - check the silly and seemingly-unlikely things first!

Monachism

Answer 27

If you have access to create a new user privilege then do so to connect normally with node.js, that is worked for me

Stenophyllous

Answer 28

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

This worked for me.

Gothurd

Answer 29

If you're on Mac OS, and would like to use the legacy password encryption without using terminal.

Go to System Settings -> Find "My SQL" -> Click "Initialize Database" -> Select "Use legacy password encryption" and enter your database user password in the textfield and click "Ok"

Revivalism

Answer 30

in case you come accross this question and your stuff is at Digitalocean:

You can add a legacy user using MySQL 5+ encryption

1. Select your Database / Cluster
2. Click "Users & Databases"
3. Enter a username and select the proper encryption

Voilá

Talanta

Answer 31

For my case just changing the driver class name in configuration

from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver

works for me .

Mcclintock

Answer 32

Create a user with permissions DBA

Unicuspid

Answer 33

Just Run MySQL Server Installer and Reconfigure the My SQL Server...This worked for me.

Berga

Answer 34

you should use whatever schema you use for your mysql connection for your session

(async () => {
    const connection = await db.connection();
    sessionStore = new MySQLStore({

    }, connection); //just pass your connection here 
})();

I just copy paste this here but your probably have implemented something similar to this to deal with your queries

const mysql = require('mysql')

if (!process.env.NODE_ENV || process.env.NODE_ENV === 'development') {
    require('dotenv').config();
}

const dbConfig = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    connectionLimit: process.env.DB_CONNECTION_LIMITS
}

const pool = mysql.createPool(dbConfig);

const connection = () => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            if (err) {
                reject(err);
            }

            const query = (sql, binding) => {
                return new Promise((resolve, reject) => {
                    connection.query(sql, binding, (err, result) => {
                        if (err) {
                            reject(err);
                        }
                        resolve(result);
                    });
                });
            };

            const release = () => {
                return new Promise((resolve, reject) => {
                    if (err) {
                        reject(err);
                    }
                    resolve(connection.release());
                });
            };
            resolve({ query, release });
        });
    });
};

const query = (sql, binding) => {
    return new Promise((resolve, reject) => {
        pool.query(sql, binding, (err, result, fields) => {
            if (err) {
                reject(err);
            }
            resolve(result);
        });
    });
};

module.exports = { pool, connection, query };

Zulema

Answer 35

I have the same problem with MySQL and I solve by using XAMPP to connect with MySQL and stop the services in windows for MySQL (control panel - Administrative Tools - Services), and in the folder db.js (that responsible for the database ) I make the password empty (here you can see:)

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

Guipure

please don't add a issue that you are facing as a answer to the problem faced – Carpo

And also do not use root to connect to your databases. Create another user, grant the permissions you need it to have, than use it! – Zoltai