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

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?

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: ''
});


                                                                                                                                

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'
  }); 

Try adding a port field:

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

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 :)

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

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

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.

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

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.

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

>1. Open MySQL Workbench > >2. Open Local instance MySQL57 using old password >3. Go to Server > Users and Privileges >4. Change password, and login to MySQL again. OR > Create a newuser and set privileges. (If changing password do not work.)

//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!");
});

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"
});

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,

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';

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

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!");
 });

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'
});

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

> [mysqld] > > skip-grant-tables > > port=3306

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

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. https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

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'

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 .

https://stackoverflow.com/questions/8838777/error-1044-42000-access-denied-for-user-localhost-to-database-db

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"

1. Open "MySQL Installer".

2. Click "Reconfigure" MySQL server under Quick Action.

3. Click next to maintain current configurations under "High Availability".

4. Click next to maintain current configurations under "Type and Networking"

5. Select radio button "Use Legacy Authentication Method" under "Authentication Method" and click next.

6. Enter root account password and click on check. Wait a while for verification of password and click next.

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

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);
	});

});

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.

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.

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

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

2. it will as password e.g. Password: enter your mac user password

3. Now you will be in root e.g. Avi-MBP:~ root#

4. now again go to project directory e.g. Avi-MBP:~ root# cd /Users/avisurya/projectDirectory

5. now start node application e.g. in my case "node server.js"

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

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

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)

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"});

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.

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,
            });

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