NodeJS mysql module transforms localhost to 127.0.0.1 and fails connection

Question

I am using the mysql package to connect to ProxySQL that is set-up to connect to a MySQL database. From command line, the following works:

mysql -u demo1 -P6033 --database=database -p

However, this does not work.

var connection = mysql.createConnection({
        "host" : "localhost",
        "port": 6033,
        "user": "demo1",
        "password" : "password as above",
        "database": "database"
    })
 connection.connect( (err) => { console.log(err.stack) } )

This is the error message.

Error: ER_ACCESS_DENIED_ERROR: ProxySQL Error: Access denied for user 'demo1'@'127.0.0.1' (using password: YES)

The user was created as

CREATE USER 'demo1'@'localhost' IDENTIFIED BY 'password';

I tried adding another user as 'demo'@'127.0.0.1' and granted privileges for the same; didn't work.

How do I retain the definition above and get mysql to return a successful connection?

score 0 · Answer 1 · answered Jul 24 '19 at 06:01

SOLVED! Thanks to this SO question - https://stackoverflow.com/a/50131831/919480

Two things.

MySQL has to be told to use mysql_native_password. I think, this is related to the choice of option 'Strong Password' when installing MySQL.
The host parameter should not be localhost because on Unix, it is treated differently. That is, the connection is via a socket. Therefore, set host parameter to 127.0.0.1. Or, remove host parameter and use socketPath.

Some discussion here too - https://github.com/mysqljs/mysql/issues/2248

NodeJS mysql module transforms localhost to 127.0.0.1 and fails connection

1 Answers1