NodeJS/mySQL - ER_ACCESS_DENIED_ERROR Access denied for user 'root'@'localhost' (using password: YES)

Question

I am attempting to connect to mySQL through a NodeJS file, but I receive the following error:

{ Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)
    at Handshake.Sequence._packetToError (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)
    at Handshake.ErrorPacket (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/sequences/Handshake.js:67:18)
    at Protocol._parsePacket (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:197:24)
    at Parser.write (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Parser.js:62:12)
    at Protocol.write (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:37:16)
    at Socket.ondata (_stream_readable.js:555:20)
    at emitOne (events.js:101:20)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Protocol._enqueue (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:110:26)
    at Protocol.handshake (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:42:41)
    at Connection.connect (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:81:18)
    at Connection._implyConnect (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:222:10)
    at Connection.query (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:137:8)
    at Object.<anonymous> (/home/matthew/Node/mySqlTest/index.js:11:12)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000',
  fatal: true }

The weird thing is that I can connect fine through the terminal by running mysql -u root -p. I only get this error when running my javascript. I have been all over Google and StackOverflow, but still have not found a solution that works. I am using MySQL 5.7.16 on Ubuntu 16.04.1 on a VIRTUAL MACHINE. Not sure if a VM makes a difference here. My Javascript code is below:

'use strict';                                                                                                                                      

var mysql = require('mysql');

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

connection.query(
    'SELECT "foo" AS first_field, "bar" AS second_field',
    function(err, results, fields) {
        console.log(err);
        console.log(results);
        connection.end();
    }
);

I have tried using 'locahost' as well as '127.0.0.1' in my javascript. I have a 'root' user for both 'localhost' and '127.0.0.1' in mySql.user table and I am able to see this by executing SELECT user, host FROM mysql.user WHERE user='root';

I have added privileges to 'root' user by executing this:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';

I ran the above on 127.0.0.1 as well. I have also tried this:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

I have attempted to reset the root password like this: https://help.ubuntu.com/community/MysqlPasswordReset

I have run FLUSH PRIVILEGES after each attempt. I've stopped and restarted mySQL. I have uninstalled mySQL completely and reinstalled.

All to no avail. I receive the access denied error every time I try to run the javascript, but I have absolutely no issues when I connect to mySQL via the terminal.

Any ideas?

The next thing I'd do is create a different user for your app to talk to. It's something you should do anyway, and there might be restrictions in your mysql instance limiting how one can connect as root - perhaps socket vs. tcp. — Jerry, Nov 08 '16 at 03:52
I have also tried that. Created new user and granted all privileges. I get the same error: "Access denied for 'newUser'@'localhost'. — mwelk11, Nov 09 '16 at 00:04
I liked the way you have described details in this question. Perfect! — user1451111, Mar 17 '17 at 19:57

score 23 · Answer 1 · edited Jul 26 '17 at 04:39

23

I have the same problem, I solved it by changing the password to empty string.

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

edited Jul 26 '17 at 04:39

Anh Pham

2,108
9
18
29

answered Jul 26 '17 at 03:34

zhan mei yang

287
2
5

score 22 · Answer 2 · edited Sep 16 '19 at 08:41

22

Try adding a port field:

var connection = mysql.createConnection({
   host: 'localhost',
   user: 'root',
   password: 'password',
   port: 3307
});

edited Sep 16 '19 at 08:41

marcolz

2,880
2
23
28

answered Oct 23 '17 at 13:02

Rahul Paul

339
2
5

9

I don't think the port is the problem - the server responded by denying the access. – AndreyF Oct 23 '17 at 13:05
4

Worked like a charm. Its necessary if you have change the default port i.e. 3306! – Muhammad Noman Sep 30 '21 at 20:41

score 17 · Answer 3 · answered Jun 20 '19 at 15:11

Create new user (instead of using root) fixed my problem.

mysql> CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Then grant:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_user'@'%' WITH GRANT OPTION;

Then change the credentials:

  var connection = mysql.createConnection({
    host     : 'The mysql IP',
    port     : 'The mysql Port',
    user     : 'new_iser',
    password : 'new_user_pass',
    database : 'database-name'
  });

Good catch! That also solved my issue. I'm using vertrigo server. — Paulo Henrique Queiroz, Feb 03 '21 at 13:57

score 10 · Answer 4 · edited Apr 11 '23 at 11:28

10

The problem is not with the mysql user authentication. It just that you have to grant your node application to access mysql db. I was facing the same issue earlier.I added the port number on which my node application is running.And its working perfectly fine now.

Also user:"root" was written as username:"root" . Be careful with the spellings.

const mysqlConnection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Pass@123",
    database: "employees",
    port:"3000",
    multipleStatements: true
});

I am using mysql version "mysql": "^2.18.1".

edited Apr 11 '23 at 11:28

Bayarjargal Jargalsaikhan

376
1
2
13

answered Mar 11 '20 at 19:07

Pooja Das

101
1
3

Why is it that you put the node port instead of the mysql port? This worked and I am blown away as to why. – Aleks Vidmantas May 13 '21 at 03:43

score 8 · Answer 5 · answered Mar 02 '18 at 11:33

I had a similar problem. I was running mysql in a Docker container and had the same error when trying to connect to it from my node app.

It appeared, that I had run the Docker container without exposing the port, hence it was 3306 inside the container, but would not have been accessible through localhost:3306. Why I got ER_ACCESS_DENIED_ERROR error was because I actually had some other mysql server running on the port 3306, with different username and password.

To see if or what you have running on the specific port type:

ps axu | grep 3306

Since I already had something on port 3306, to make the server accessible to my app I changed a port to 3307 and run my docker mysql container with the command:

docker run --name=<name> -e MYSQL_ROOT_PASSWORD=<password> -p 3307:3306 -d mysql

After starting mysql client inside Docker with command:

docker exec -it <name> mysql -u root -p

And after creating a database to connect to, I was able to connect to my mysql db from my node app with these lines:

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

 connection.connect();

Hopefully helps someone new to docker and mysql :)

I had this problem. For people on a mac, you can do `sudo lsof -iTCP -sTCP:LISTEN -n -P | grep 3306` to list any processes using port 3306 (stop your docker container first). I had to use `brew services stop mysql` to stop the mysqld process on my host machine (killing it just made it restart with a different PID). — potterbm, Mar 05 '19 at 01:35

score 5 · Answer 6 · answered Sep 20 '18 at 10:25

5

For mysql version 2.16.0 (Sept 2018):

just create a new user on mysql.

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

replace username and password.

answered Sep 20 '18 at 10:25

Faris Han

525
3
16

score 5 · Answer 7 · answered Nov 20 '19 at 05:04

5

A bit late to talk about it but I guess I found the problem: special chars in password!!! I had a $ in pass. Solution: use escape Ex: te\$t

answered Nov 20 '19 at 05:04

Vitor Esteves

51
1
3

Actually, if you only care about ASCII that should not be a real problem at all. You only have to properly escape them. A good reference for this is https://mathiasbynens.be/notes/javascript-escapes console.log('\xB2 \xDB') Special chars:https://allwebco-templates.com/support/S_hex.htm – user2080866 Apr 06 '20 at 18:33
this was the issue for me. you're a life saver – Jude Bobinihi May 05 '22 at 07:43

gaurish.salunke · Answer 8 · 2020-01-07T10:36:12.883

4

const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'database_name', password: 'your_pwd' });

make sure you have spelled the the keys and the properly. I was facing similar issue, then realised that I had written username instead of user

edited Jan 07 '20 at 10:36

answered Jan 26 '19 at 15:54

gaurish.salunke

1,059
10
18

It helped me I have written use insted of user – INDRAJITH EKANAYAKE Jan 02 '20 at 15:50

score 2 · Accepted Answer · edited Jun 16 '17 at 13:05

2

Using recent MySQL version in package.json solved the problem.

I was using version 2.0.0. I changed the version to 2.10.2.

edited Jun 16 '17 at 13:05

Sachith Muhandiram

2,819
10
45
94

answered Nov 29 '16 at 00:21

mwelk11

643
1
5
10

6

Strangely enough, I use a newer version 2.13.0, but I have the same problem ..."I receive the access denied error every time I try to run the javascript, but I have absolutely no issues when I connect to mySQL via the terminal." Any ideas? – Yurii Holskyi Jan 29 '17 at 12:01
1

2.10.2 has the same issue. MySQL version is 5.7. – Jason Xu May 29 '18 at 09:51
1

2.16.0 still has the same issues – Alexey Grigorev Nov 08 '18 at 11:05
sometimes there is a conflict with "localhost" and "127.0.0.1". The liberary uses 127.0.0.1 for Connection and not allows "localhost" MYSQL Users – Jan Bludau Dec 27 '19 at 08:39

score 2 · Answer 10 · edited Jun 20 '20 at 09:12

2

I had the same problem and changing password of database user worked for me. Follow these steps :

Open MySQL Workbench

Open Local instance MySQL57 using old password

Go to Server > Users and Privileges

Change password, and login to MySQL again. OR Create a newuser and set privileges. (If changing password do not work.)

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 11 '18 at 18:10

marwari

181
2
13

score 2 · Answer 11 · answered Mar 04 '20 at 12:09

I was getting the same issue, but using require('mariadb'), which is essentially the same. So my answer should apply to both drivers.

The problem was 2 fold:

host: 'localhost', user: 'user' is always resolving as 'user'@'127.0.0.1' on the database. So don't use localhost but 127.0.0.1 instead!
The password encryption scheme was incompatible between client and server, apparently the Node client is using mysql_native_password.

Here's the solution: (from the mysql command-line client)

# If you don't have a 127.0.0.1 equivalent user:
CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';

# If you already have the user, reset its password:         
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

# Redo your grants on the 127.0.0.1 user:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';
FLUSH PRIVILEGES;

Voilà, now connect from Node and everything works:

const mariadb = require('mariadb'); // or require('mysql');

const pool = mariadb.createPool({
    host: 'localhost',  // or host: '127.0.0.1'
    user: 'root',
    password: 'password',
    database: 'mydatabase',  // don't forget the database
    port: 3306,
    connectionLimit: 5
});

References:

Here's where I got the solution from, read for more info

score 1 · Answer 12 · answered Jan 06 '18 at 13:28

1

//surprisingly this works.

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

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

answered Jan 06 '18 at 13:28

micheal nayebare

11
1

score 1 · Answer 13 · answered Aug 09 '19 at 11:08

1

If anyone is still facing problem. Try

var mysql = require("mysql");
var con = mysql.createConnection({
  host: "127.0.0.1",
  user: "your_username",
  password: "password",
  database: "your_db_name"
});

answered Aug 09 '19 at 11:08

azm_shah

198
2
10

score 1 · Answer 14 · answered Dec 27 '19 at 08:37

You had to add the new user with an IP of the allowed host section not of the name of "localhost"

// Do the mySQL Stuff
var con = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'mypwd',
  database: 'database',
  port: 3306,
  debug: true
});

//MYSQL Statement

RENAME USER 'myuser'@'localhost' TO 'myuser'@'127.0.0.1';

Gianni Fontanot · Answer 15 · 2021-08-21T22:36:05.247

In my case, the connection was successful when seeding the database, but not when starting the server. Strange!

The problem was that the .env file was not found by the server because the parent directory was different than the parent directory of the seed process.

require('dotenv').config({path: "../../.env"});

I solved my problem by using a library called 'app-root-path'

const appRoot = require('app-root-path');
require('dotenv').config({path: appRoot + path.sep + ".env"});

score 1 · Answer 16 · answered Mar 04 '22 at 16:52

1

In my case, the password I was using had "#" in it and this prevented dotenv package to read the whole password from .env file. Surrounding the password with double quotes ("") solved the problem.

answered Mar 04 '22 at 16:52

SametSahin

571
3
7
24

Vishnuvardhan · Answer 17 · 2018-01-24T08:29:42.740

0

I have faced this issue by giving the full user name in the user section when I changed the 'root'@'localhost' to 'root' It is working fine now.

var mysql = require('mysql');

 var con = mysql.createConnection({
  host: hostname,
  user: "root",
  password: "rootPassword"
 });

 con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
 });

edited Jan 24 '18 at 08:29

answered Jan 02 '18 at 11:38

Vishnuvardhan

5,081
1
17
33

its root and its not working. Kindly delete the answer – manish kumar Nov 20 '18 at 17:48

score 0 · Answer 18 · answered May 26 '18 at 12:44

0

I had the same error from nodejs script, so i removed password parameter and now it magically works fine

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

answered May 26 '18 at 12:44

hacktar

1
1

score 0 · Answer 19 · edited Mar 12 '19 at 07:47

0

Add skip-grant-tables in my.ini (this file is available in default installation path of mysql)

[mysqld]

skip-grant-tables

port=3306

edited Mar 12 '19 at 07:47

Partho63

3,117
2
21
39

answered Mar 12 '19 at 06:26

Girish

107
3
10

score 0 · Answer 20 · answered May 08 '19 at 15:44

0

For me the problem was having inside the SQL connection object pass: 'mypassword' instead of password: 'mypassword'.

answered May 08 '19 at 15:44

Valchy

165
3
14

score 0 · Answer 21 · answered Aug 10 '19 at 03:27

Mostly the issue will be with the way the password entered is interpreted by MySQL server. Please follow the below link. It should resolve the issue. MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

score 0 · Answer 22 · answered Aug 24 '19 at 15:01

If you are connecting to an external server from localhost, you may need to add your gateway router address to MySQL's "Allowable Hosts" box.

This address can be found after the @ sign in the error message:

Access denied for user 'your_username'@'blah.blah.blah.yourisp.com'

score 0 · Answer 23 · answered Nov 12 '19 at 10:41

Try n make sure that you use the credentials that you use to login your database are correct

const Sequelize = require('sequelize')
const db = {}
const sequelize = new Sequelize('ochiengsDatabase', 'ochienguser', ' 
mydbpassword', {
host: 'localhost',
dialect: 'mysql',
operatorsAliases: false,

score 0 · Answer 24 · answered Dec 29 '19 at 17:43

0

Most of the time this things happen due to the misconfigurations in mySQL on your device . I had this problem myself . I have solved the problem using the link below .

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db'

answered Dec 29 '19 at 17:43

MILLION GASHAW

97
9

score 0 · Answer 25 · answered Jan 07 '20 at 15:11

Best Solution to resolve this problem:

You got this Error : ER_NOT_SUPPORTED_AUTH_MODE this is error is mentioning when you install sql server you selected"strong authentication", but you set a weak password. You need to reset strong password or need to choose legacy authentication method.

Follow these steps to choose legacy authentication method...

You installed mysql server using "mysql installer"

Open "MySQL Installer".
Click "Reconfigure" MySQL server under Quick Action.
Click next to maintain current configurations under "High Availability".
Click next to maintain current configurations under "Type and Networking"
Select radio button "Use Legacy Authentication Method" under "Authentication Method" and click next.
Enter root account password and click on check. Wait a while for verification of password and click next.
Click next to apply configurations and restart the database server.

Now run code:

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

)};

Password field should be replaced with root password.

score 0 · Answer 26 · answered Feb 18 '20 at 14:36

Login into your mysql using mysql -u root -p password

Create new user z (MySQL console)

CREATE USER 'z'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON * . * TO 'z'@'localhost';

Node.js script

var mysql = require('mysql');

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

con.connect(function(err) {

    if (err) throw err;
    console.log("Connected!");

    con.query("use mysql;", function(err, result) {
        if (err) throw err;
        console.log(result);
    });

    con.query("select * from user limit 1;", function(err, result) {
        if (err) throw err;
        console.log(result);
    });

});

score 0 · Answer 27 · answered Feb 28 '20 at 07:41

0

You need to make sure that the username you are using is matched against the ip address you are using under host name for connection.

answered Feb 28 '20 at 07:41

Englon Kavhuru

1
1

Avi · Answer 28 · 2020-09-20T05:50:40.167

I was facing same error on mac, however whenever I run same code on windows machine it was working absolutely good, without any error. After spending 2 days I found solution.

Below are the steps I followed.

to your project folder in terminal and run "sudo su -" command

e.g. Avi-MBP:projectDirectory avisurya$ sudo su -

it will as password e.g. Password: enter your mac user password
Now you will be in root e.g. Avi-MBP:~ root#
now again go to project directory e.g. Avi-MBP:~ root# cd /Users/avisurya/projectDirectory
now start node application e.g. in my case "node server.js"

score 0 · Answer 29 · answered Oct 13 '20 at 09:25

0

I have solved this by adding socket path to connection configuration. For more details you can see here

answered Oct 13 '20 at 09:25

Sandeep

9
4

score 0 · Answer 30 · edited Feb 10 '21 at 00:36

I had the same problem today, that's what I did. You might not need to choose a different password, just make sure you have the right password in your js file

go to your mysql bash by:

$ sudo mysql

change the password of the root user by:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-chosen-password';

exit the mysql by Ctrl+D
make sure you have the same password in your js code
in the bash:

$ mysql -u root -p

enter the password you chose you should be able to see the welcome message of mysql
exit and in the bash run the node connection code again:

$ node ./database/actions/db-connect.js

make sure you use the right password

score 0 · Answer 31 · edited Apr 17 '21 at 02:51

make sure that there is no error in your configuration files, I struggled to get this sorted as well only to find out that all along I had been attempting to connect with wrong connection parameters.. the bug code was:

require('dotenv').config()
    let  config= {
        client: 'mysql2',
        connection: {
            host:process.env.MYSQL_HOST,
            user:process.env.MYSQL_USER,
            database:process.env.MYSQL_PASS, // here
            password:process.env.MYSQL_DB,   //here
            multipleStatements: true
        }
    }

module.exports= require('knex')(config);

the correction:

require('dotenv').config()
    let  config= {
        client: 'mysql2',
        connection: {
            host:process.env.MYSQL_HOST,
            user:process.env.MYSQL_USER,
            password:process.env.MYSQL_PASS, // here
            database:process.env.MYSQL_DB,   //here
            multipleStatements: true
        }
    }

module.exports= require('knex')(config)

score 0 · Answer 32 · answered Jul 06 '22 at 10:24

var mysql = require('mysql')    
    var con = mysql.createConnection({
        host: '127.0.0.1',
        user: 'root',
        password: '',
        database: 'hospital_manager',
        });

    con.connect(function (err) {
        if (err) throw err;
        console.log("Connected!");
    });

score 0 · Answer 33 · answered Oct 06 '22 at 15:43

I have the same issue, try to check if you can log in, open your cmd terminal, and type cd C:\Program Files\MySQL\MySQL Server 8.0\bin then type: MySQL -u root -p, then enter your password, On my own, I find that MySQL is still using my old password, and I tried login in with a new password that is not recognized

score -2 · Answer 34 · answered Aug 27 '20 at 10:45

The code to connect to mysql using dotenv in nodejs

          .env file

          NODE_ENV=DEVELOPMENT
          DB_HOST=localhost
          DB_USER=root
          DB_PASSWORD=password
          DB_NAME=test

              
           db.js file


           const util = require("util");
           const mysql = require("mysql2");

           const pool = mysql.createPool({
           host: process.env.DB_HOST,
           user: process.env.DB_USER,
           password: process.env.DB_PASSWORD,
           database : process.env.DB_NAME,
           uri: process.env.DB,
           waitForConnections: true,
           connectionLimit: 10,
           queueLimit: 2,
            });

score -5 · Answer 35 · answered Jun 16 '17 at 06:30

-5

uninstall mysql,and uninstall mysql related service(eg. mysqld.exe xampp).then,reinstall mysql.

answered Jun 16 '17 at 06:30

popzhou

1

7

And what is the guarantee that it's going to fix the problem? Could you elaborate your answer? – Harsh Wardhan Jun 16 '17 at 06:47

NodeJS/mySQL - ER_ACCESS_DENIED_ERROR Access denied for user 'root'@'localhost' (using password: YES)

35 Answers35

Linked