NodeJS/mySQL - ER_ECCESS_DENIED_ERROR Access denie

问题:

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?

回答1:

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

回答2:

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

回答3:

Try adding a port field:

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

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

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

回答6:

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

回答7:

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

回答8:

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.

回答9:

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

回答10:

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

回答11:

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,

回答12:

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

回答13:

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

回答14:

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

[mysqld]

skip-grant-tables

port=3306

回答15:

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

回答16:

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'

回答17:

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

回答18:

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

回答19:

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'

回答20:

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.

回答21:

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

回答22:

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