Need assistance with accessing mysql database using node.js

Question

I am trying to run a simple node sample for access a mysql db and am getting the following error-Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'ubuntu.local' (using password: YES)

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

connection.connect();

connection.query('SELECT * from users', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0]);
});

connection.end();

The above is the code for accessing the mysql using node.js.Any suggestions as to what I may be doing wrong, thanks in advance.

can you connect to mysql from console ? mysql -h 192.168.0.12 -u root -p app (enter password after request) — zb'
– zb', Commented Nov 18, 2012 at 3:03
nope I cannot, execute the command you gave me, but if I replace the ip addr with localhost it works from the command line but in the node script I get the following error-connect ECONNREFUSED. — anonymous123
– anonymous123, Commented Nov 18, 2012 at 3:10
login to mysql console and run GRANT ALL ON app.* to 'root'@'ubuntu.local' identified with 'password'; and retry your first approach — zb'
– zb', Commented Nov 18, 2012 at 3:18

irm518 · Accepted Answer · 2013-07-30 20:50:41Z

9

I had a similar problem where I was getting the same error.

The answer for me ended up being that I was using a different port. By default, mysql expects a port of 3306, but I was using MAMP, where the MySQL server port was specified as 8889. As soon as I added a port definition to my database connection, it was fine:

var connection = mysql.createConnection({
    port: 8889,
    user: 'root',
    password : 'password',
    database : 'my_database'
});

NOTE: This is for Mac using MAMP, haven't tried it on ubuntu or Windows.

answered Jul 30, 2013 at 20:50

irm518

991 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NullPointer · Accepted Answer · 2015-02-26 00:44:27Z

3

I was getting the exact same issue. I was not able to connect via the mysql module in nodejs, but was able to connect via the mysql client in the terminal (mysql -u root -p1111 -h localhost mydb)

It turns out, the mysql module converts "localhost" to 127.0.0.1. This would not work via the (mysql -u root -p1111 -h 127.0.0.1 mydb). See Issue 734

So I had to set a password for 127.0.0.1 in MySQL and it solved the problem.

Instructions

Connect to mysql via the console:

mysql -u root -p mysql

And Run

SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('Add-Your_password-here');

Source

answered Feb 26, 2015 at 0:44

NullPointer

5551 gold badge6 silver badges17 bronze badges

1 Comment

jose920405 Over a year ago

when run mysql -u root -p mysql get this error Access denied for user 'root'@'localhost' (using password: YES)

PsyKoPeeR · Accepted Answer · 2016-12-29 00:00:50Z

1

I've been on this problem for a long time now and among all forums I did, one simple solution resolve my issue. My password was with some special character. I change it to a simpler one like "onlyletters" and it works perfectly. Probably a encoding problem in the nodejs mysql module, I can... Hope it would help some of you guys...

answered Dec 29, 2016 at 0:00

PsyKoPeeR

111 bronze badge

1 Comment

Myonara Over a year ago

With me it was the password: [A-Za-z0-9\.]{64} worked for me,

enginocal · Accepted Answer · 2017-03-23 18:02:57Z

0

I had a similar problem,my nodejs project.I was using knex.js for querybuilder and it's configuration.

This problem occurs when host value is 127.0.0.1,when i changed to 127.0.0.1 to localhost,the problem is gone away.

var knex = require('knex')({
    client: 'mysql',
    connection: {
        **host: 'localhost',**
        user: 'userNew',
        password: 'password',
        database: 'appDb'
    }
});

answered Mar 23, 2017 at 18:02

enginocal

3191 gold badge6 silver badges19 bronze badges

Comments

Balibrera · Accepted Answer · 2019-11-03 20:10:19Z

0

The user might not have any administrative role assigned so he is not able to access the database. The user will need at least the SELECT privilege to connect.

More info here: https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-users-and-privileges.html

answered Nov 3, 2019 at 20:10

Balibrera

1852 silver badges7 bronze badges

Comments

user5960462 · Accepted Answer · 2016-02-22 00:15:13Z

-1

Actually the solution is easier than you think. Change the host to 127.0.0.1 for the database connection.

answered Feb 22, 2016 at 0:15

user5960462

1

1 Comment

Dwayne Charrington Over a year ago

Would you please be able to provide a little more detail about why changing the host to "127.0.01" fixes the database connection? Because the OP is using an IP address that appears to be related to a database server.

Collectives™ on Stack Overflow

Need assistance with accessing mysql database using node.js

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related