NodeJS/mySQL - ER_ECCESS_DENIED_ERROR Access denie

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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