mysql: NodeJS mysql module transforms localhost to 127.0.0.1 and fails connection

I am trying to connect to ProxySQL that is set-up to connect to a MySQL database. From command line, the following works. The port number 6033 is for ProxySQL.

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

However, the connection to ProxySQL with this package does not work.

var connection = mysql.createConnection({
        "host" : "localhost",
        "port": 6033,
        "user": "demo1",
        "password" : "password",
        "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?

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 19 (10 by maintainers)

Most upvoted comments

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

Two things.

  1. 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.
  2. The host parameter should not be localhost because on Unix, it is treated differently. That is, the connection is via a socket (as you mentioned earlier). Therefore, set host parameter to 127.0.0.1. Or, remove host parameter and use socketPath.

Basically, let me know what the result of running the following command is on your setup when you have a chance:

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