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

[chainhead]

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?

[dougwilson]

Hi! And thanks for the report. I'm not familiar with any code here that specifically modifies the host at all. Would you be willing to make a pull request with a fix or perhaps some step by step instructions for how to set everything up, the versions of software involved, etc. so I can reproduce and see what is going on?

[chainhead]

Hello @dougwilson
Please find instructions to reproduce. However, let me set-up some context.

The aim is to use this package to connect to ProxySQL which is set-up to connect to MySQL. The issue here is, while I can connect to ProxySQL from mysql command prompt, the same connection parameters do not work with this package.

To be fair, the issue could be with either of the products - mysql package, ProxySQL or MySQL.

Steps to reproduce

These steps were executed on Ubuntu 18.04 LTS on AWS.

Product installation

MySQL installation

cd /tmp
curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb
#  2. Install the .deb file
#     - Scroll down to the last option (ok) and hit Enter
sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
#  3. Refresh apt repository
sudo apt update
#  4. Install MySQL server
#     - Set root password and save it somewhere secure.
#     - Use strong authentication option.
sudo apt install -y mysql-server

ProxySQL installation

# 1. Download latest .deb from here https://github.com/sysown/proxysql/releases/tag/v2.0.5
cd /tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v2.0.5/proxysql_2.0.5-clickhouse-ubuntu18_amd64.deb
# 2. Install the package
sudo dpkg -i proxysql_2.0.5-clickhouse-ubuntu18_amd64.deb
# 3. Start service
sudo service proxysql start

Package installation

npm i mysql --save

Configuration

MySQL

The following DDL were saved in a file install.sql and run as

mysql -u root -p < install.sql

-- Create schema
CREATE SCHEMA MYDB DEFAULT CHARACTER SET utf8 ;

-- *******************
-- The following were the original CREATE and GRANT statements. 
-- I tried adding another pair of CREATE and GRANT for 'demo1'@'127.0.0.1'. 
-- That didn't work.
-- *******************

-- Create users for our demo
CREATE USER 'demo1'@'localhost' IDENTIFIED BY 'password';
-- Grant privileges
GRANT ALL PRIVILEGES ON MYDB.* TO 'demo1'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

ProxySQL

The following configuration was copy-replaced into /etc/proxysql.conf.

admin_interfaces = {
    admin_ifaces="0.0.0.0:7031"
}

mysql_servers =
(
    {
        address="localhost"
        port=3306
        hostgroup=0
        max_connections=100
    }
)

mysql_users:
(
    {
        username = "demo1"
        password = "password"
        default_hostgroup = 0
        max_connections=500
        default_schema="MYDB"
        active = 1
    }
)

The ProxySQL service was reloaded thus:

sudo proxysql service restart

NodeJS

The options object passed to connect() is as shown below.

{
    "host" : "localhost",
    "port" : 6033,
    "user" : "demo1",
    "password" : "password",
    "database" : "BROKERS"
}

And, this is how I am using mysql package.

const mysql = require('mysql')
//
function connect(options, callback) {
    var connection = mysql.createConnection(options)
    connection.connect( (err) => {
        if (err) {
            return callback(err, null)
        }
        return callback(null, connection)
    })
}
//
module.exports = {
    connect
}

The err object is populated with sqlMessage as Access denied for user 'demo1'@'127.0.0.1' (using password: YES).

Alternative

Based on discussion below, we see that mysql is connecting _not_ via TCP. Therefore, I tried connecting via socket as shown below.

//
var connection = mysql.createConnection({
"socketPath" : "/var/run/mysqld/mysqld.sock",
"user" : "demo1",
"password": "password",
"database":"MYDB"
})
//
connection.connect( (err) => {
        if (err) {
            console.log('ERROR ' + err.sqlMessage)
        } else {
        console.log('INFO ' + err.sqlMessage)
}
    })

It returns the following error.

ERROR Client does not support authentication protocol requested by server; consider upgrading MySQL client

[dougwilson]

Thank you so much! I will find some time to get it all set up. And you are 100% sure the connection with the command line that worked actually went though proxysql server and did not just connect over unix socket to the mysql server instance?

I'm asking because the standard MySQL command line program thinks the host of localhost means to connect over unix socket and not over tcp as a convenience, and the port you pass in -P is ignored.

https://dev.mysql.com/doc/refman/8.0/en/connecting.html

The default host name is localhost. On Unix, this has a special meaning, as described later.

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs: the client connects using a Unix socket file. The --socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

Only connection options that are relevant to the selected protocol are used or checked. Other connection options are ignored. For example, with --host=localhost on Unix, the client attempts to connect to the local server using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number.

To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

You can add --protocol=TCP to your command line to ensure that is is using a TCP connection and will then connect to the proxysql port.

[dougwilson]

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

[chainhead]

Finished my tests; and, you are right! It wasn't using a TCP connection.

ubuntu@ip-172-31-23-36:/tmp$ mysql -u demo1 --protocol=TCP -p -P6033 --database=MYDB
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'demo1'@'127.0.0.1' (using password: YES)

Can you please help me understand, how to enable TCP connection in ProxySQL configuration? Alternatively, can socketPath be used without changing the current ProxySQL configuration?

[dougwilson]

Before you mentioned it, I hadn't heard of ProxySQL before, so really don't know how to really assist with ProxySQL configuration. I'm happy to help with anything relayed to this Node.js module, though. You can use a UNIX socket with this module if that is what you're asking?

[dougwilson]

Just pass in the path to your UNIX socket to the socketPath option of this module, if so, and it will use that UNIX socket instead of TCP to connect to the server.

[chainhead]

From mysql> show variables like 'socket';, I got the path as /var/run/mysqld/mysqld.sock. I used it as so:

//
var connection = mysql.createConnection({
"socketPath" : "/var/run/mysqld/mysqld.sock",
"user" : "demo1",
"password": "password",
"database":"MYDB"
})
//
    connection.connect( (err) => {
        if (err) {
            console.log('ERROR ' + err.sqlMessage)
        } else {
        console.log('INFO ' + err.sqlMessage)
}
    })

I get this error message.

ERROR Client does not support authentication protocol requested by server; consider upgrading MySQL client

Thoughts?

[dougwilson]

Interesting. What are the grants on that user? The mysql server usually gives that error if it is not set to enable password auth over the unix socket.

[dougwilson]

You can validate if the password is being used for auth with the command line client by removing the -p and seeing if the auth still works. This would indicate that user is jot set up to use password auth over the unix socket (which is the only auth method this module currently supports).

[chainhead]

You can validate if the password is being used for auth with the command line client by removing the -p and seeing if the auth still works.

I tried both options as shown below - is this what you meant?

Via ProxySQL

ubuntu@ip-172-31-23-36:~$ mysql -u demo1 -P6033
ERROR 1045 (28000): Access denied for user 'demo1'@'localhost' (using password: NO)

Direct connection

ubuntu@ip-172-31-23-36:~$ mysql -u demo1 
ERROR 1045 (28000): Access denied for user 'demo1'@'localhost' (using password: NO)

[chainhead]

What are the grants on that user?

GRANT ALL PRIVILEGES ON MYDB.* TO 'demo1'@'localhost' WITH GRANT OPTION;

[dougwilson]

Hm, then I'm not sure why the server is rejecting it. Would you mind updating your original instructions to get to reproduce this issue so I can take a look? Also I see they are based on AWS, is that a free service?

[chainhead]

I have updated the steps to reproduce this error with instructions on connecting via Unix socket.

I am using AWS only to set-up the Ubuntu server. It uses the opens source versions of MySQL and ProxySQL.

[dougwilson]

Ah, sorry, I missed the Ubuntu note. I should be able to run that locally, as Ubuntu is available in the Windows store 👍 . I will try to get everything set up this weekend and look into the issue for you.

[chainhead]

👍 Thanks for your support so far!

[chainhead]

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.

[dougwilson]

Hi @chainhead , great to know! Do you still need me to look into this, or is it OK to close the issue?

[chainhead]

@dougwilson Yes, ok to close; closing now. 👍