How to connect to local mariadb server with node.js

Question

I'm trying to connect to a local mariadb server using node.js. When I try connecting I get the error:

"Error: (conn=8, no: 1045, SQLState: 28000) Access denied for user 'root'@'localhost' (using password: NO)"

I am not sure why this is, as I have the password set when I make the connection, and I can log into the mysql server using the root password with

 mysql -u root -p

This is what my code looks like:

var mysql = require('mariadb');

var con = mysql.createConnection({
        host: "localhost",
        user: "root",
        password: "8pJcpe/h0h_%",
        port: 3306
        })
        .then(conn => {
                console.log("connected" + conn.threadId);
        })
        .catch(err => {
                console.log("got error " + err);
        });

I have also tried it with port 3307, and get this error: "Error: connect ECONNREFUSED 127.0.0.1:3307"

When I try to use the port I am using to ssh into the server, I get this different error: " Error: (conn=-1, no: 45009, SQLState: 08S01) socket has unexpectedly been closed"

I am new to this and I could use some help. Thanks.

What version of the MariaDB Node.js connector has this problem not providing the `password` ? — spencer7593, Apr 11 '19 at 15:15

score 0 · Answer 1 · answered Apr 03 '19 at 20:11

0

Some key ideas:

MySQL user is identified by user and host.
In MySQL localhost means local socket connection, not TCP/IP loopback.

This:

mysql -u root -p

is going to use local socket connection; this will match mysql.user table row user='root' and host='localhost'

To force a TCP/IP connection:

mysql -h 127.0.0.1 -u root -p

This will match a row in mysql.user such as user='root' and host='127.0.0.1', or user='root' and host='%'

Each row in mysql.user is a separate user; there can be multiple rows with user='root' and different values of host. Each of these rows represents separate users, each with its own privileges and password (or authentication method).

For the mariadb node.js driver

using host option, we are going to attempt a TCP/IP connection.

To get a connection via the local socket file (i.e. equivalent to mysql -u root -p, we need to use socketPath rather than host option.

https://mariadb.com/kb/en/library/nodejs-connection-options/

If we want to get a TCP/IP connection working in node.js, then we should first make sure that the mysql -h 127.0.0.1 -u root -p connection is working.

answered Apr 03 '19 at 20:11

spencer7593

106,611
15
112
140

Thank you for this. It definitely clears some thing up but I am still not getting a result. First thing I tried was making sure I could log in with the TCP/IP connecting "mysql -h 127.0.0.1 -u root -p" and I could. So then I changed my code, so that the host was "127.0.0.1", and when I ran that I got the same error message as before. I also tried to use 'socketPath' instead of 'host', and used the path that I found in the my.cnf file but that also did not work and I got the same error as before also. – John Paul Apr 03 '19 at 20:24
The body of the error message "`Access denied for user 'root'@'localhost' (using password: NO)`" seems to indicate that a password was not provided. And the user `@'localhost'` makes it appear that it was a local socket connection (rather than TCP/IP) that was attempted. So I'm confused. Can we get the mysql command line client to issue the same error? omitting a password issuing just `mysql -u root` ? (we should be able to get an error `using password: YES` if we provide a wrong password.) – spencer7593 Apr 03 '19 at 20:48
One other thing we might try is following the coding pattern demonstrated in the MariaDB knowledge base, using `createPool` and `getConnection` https://mariadb.com/kb/en/library/getting-started-with-the-nodejs-connector/ – spencer7593 Apr 03 '19 at 20:53
So by just doing `mysql -u root` I get the ` Access denied for user 'root'@'localhost' (using password: NO)` response. But if I try to login and provide a wrong password I do get the `(using password: YES)` response. I just tried it again with the TCP/IP connection to make sure: `var con = mysql.createConnection({ host: "127.0.0.1", user: "root", password: "8pJcpe/h0h_%", port: 3306` and this spits out the error `Access denied for user 'root'@'localhost' (using password: NO)` – John Paul Apr 03 '19 at 20:56
And I just tried using the pool method they showed but got the same errors unfortunatley – John Paul Apr 03 '19 at 21:08
So as I suggested earlier, the error message is the one we expect when a password isn't provided. Based on the code shown in the question, we'd expect a password to be passed. I would check the matched row in mysql.user and verify that `plugin='mysql_native_password', verify the authentication string matches the return from `SELECT PASSWORD('mypassword')`, and also check the `password_expired`, `account_locked` columns. If it's possible DML changes were applied to the mysql.user table, we can issue a `FLUSH PRIVILEGES` statement to force MySQL to re-read the tables. I'm stumped. – spencer7593 Apr 04 '19 at 14:54

score 0 · Answer 2 · answered Apr 04 '19 at 15:32

0

I was able to fix this. For some reason when using mariadb it would not recognize that I was providing it a password. So I had to switch to to mysql and use that instead, and then I was able to connect and query the database.

answered Apr 04 '19 at 15:32

John Paul

21
1
2

How to connect to local mariadb server with node.js

2 Answers2