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 gt 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  lt anonymous gt   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  lt anonymous gt   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  Any help would be appreciated  D

User · Answer

For existing mysql 8 0 installs on Windows 10 mysql   launch installer   click  quot Reconfigure quot  under QuickAction  to the left of MySQL Server   then  click next to advance through the next 2 screens until arriving  at  quot Authentication Method quot   select  quot Use Legacy Authentication Method  Retain MySQL 5 x compatibility quot   Keep clicking until install is complete

User · Answer

If you encountered this error but still wanted to use MySQL version 8  You can achieve this by telling MySQL Server to use the legacy authentication plugin when you create the database using Docker   So  your compose file will look like this     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

User · Answer

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 gt  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  your new password   mysql gt  FLUSH PRIVILEGES  mysql gt  quit  Then try connecting using node

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Make sure the user you are using to access your database has the privileges to do so   GRANT ALL PRIVILEGES ON       TO  user   localhost    Where the first star can be replaced by database name  second star specifies table  Make sure to flush privileges after

User · Answer

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   gt              console log session inspect                session close         catch err   gt        console error err stack       process exit 1

User · Answer

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

User · Answer

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   Your Current Options Option 1  Downgrade  quot MySQL quot  to authenticate using good old  quot mysql native password quot  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  Replace  quot Node quot  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  Cheers

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In Mysql Latest docker container   ALTER USER root IDENTIFIED WITH mysql native password BY  password

User · Answer

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

User · Answer

Downgrading might not be a good option as   Its upgraded for a reason  To provide better authentication   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 Hope it helps

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

Examples related to mysql

Examples related to node.js