Can connect from mysql cli, but not from node-mysql2

[nphyx]

I've been getting a "host not allowed to connect error" that completely baffles me, as connecting from the mysql command line interface using the same parameters works fine. I boiled it down to the simplest possible setup for testing:

var mysql = require("mysql2");

var db = mysql.createConnection({
    "database": "database",
    "user": "user",
    "password": "xxxxxxx",
    "host": "yyy.yyy.yyy.yyy",
    "insecureAuth":true,
    "debug":true,
    "ssl": {
        "rejectUnauthorized":false
    }
});

db.connect();
db.query("SELECT * FROM table LIMIT 1", function(err, res) {});
db.end();

The insecureAuth and ssl options were meant to help eliminate server-side issues. The same error occurs with or without them.

Running this with node test.js results in:

0 undefined ==> Connection#handshakeInit(0,Error,70)
 raw: ff6a04486f737420273137332e33302e38332e32343927206973206e6f7420616c6c6f77656420746f20636f6e6e65637420746f2074686973204d7953514c20736572766572
events.js:85
      throw er; // Unhandled 'error' event
            ^
Error: Host 'zzz.zzz.zzz.zzz' is not allowed to connect to this MySQL server
    at Packet.asError (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/packets/packet.js:493:13)
    at ClientHandshake.Command.execute (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/commands/command.js:25:22)
    at Connection.handlePacket (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:310:28)
    at PacketParser.onPacket (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:83:65)
    at PacketParser.executeStart (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/packet_parser.js:39:12)
    at Socket.<anonymous> (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:95:31)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)

However if I do:

$ mysql -u user -p -h yyy.yyy.yyy.yyy --database database
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 260318
Server version: 5.5.41-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

I connect successfully and am able to run queries per the permissions configured on the mysql server.

The server is not configured to require/use SSL. It is running on the standard (3306) port and listening on the external interface, as the cli results indicate.

Local host info:

$ uname -a
Linux jormungandr 3.19.3-3-ARCH #1 SMP PREEMPT Wed Apr 8 14:10:00 CEST 2015 x86_64 GNU/Linux
$ node --version
v0.12.2

Any thoughts? I'm on a tight deadline here so please let me know if you need anything else.

[sidorares]

Can you double check that cli mysql client is not configured to use any proxy? node-mysql2 is able to get initial handshake which means there is no connectivity issues, but Host 'zzz.zzz.zzz.zzz' is not allowed usually means that ip is not in allowed list

[sidorares]

Also - are you running mysql cli and node-mysql2 client from the same box? ( and check what interfaces you have - is it single ip? multiple ethernet ports? running VPN tap/tun interface? )

[sidorares]

check results of select host from mysql.user where User='user'

[nphyx]

I actually haven't granted that user global permissions, but the IP address definitely matches my local address. Both commands are run from the same box (my dev laptop). I have both wireless and ethernet, but wireless is disabled. My VPN is also off. MySQL client is using stock configuration packaged with Arch Linux/mariadb.

The "zzz.zzz.zzz.zzz" IP matches the external IP given by my modem and by http://whatsmyip.org, as well as the one configured in user/database permissions on the remote host.

Between posts I set up a local mysql database with similar setup to the remote host and tried to connect to that, which results in:

Error: Server does not support secure connnection
(...)

Is it possible that the problem results from the remote host not supporting SSL?

[sidorares]

try to remove ssl part from connection config altogether

[nphyx]

Same result :(

[sidorares]

same "Server does not support secure connnection"?

with client like

var mysql = require("mysql2");

var db = mysql.createConnection({
    "database": "database",
    "user": "user",
    "password": "xxxxxxx",
    "host": "yyy.yyy.yyy.yyy",
    "debug":true
});
db.query("SELECT 1", console.log);

?

[sidorares]

can you post full debug output from ^ example?

[nphyx]

I've got two separate results. For the local client:

test-local.js:

var mysql = require("mysql2");

var db = mysql.createConnection({
    "database": "database",
    "user": "user",
    "password": "xxxxxxx",
    "host": "localhost",
    "debug":true
});

db.connect();
db.query("SELECT * FROM table LIMIT 1", function(err, res) {});
db.end();

I get a successful connection now with those options removed. For the remote host:

test.js:

var mysql = require("mysql2");

var db = mysql.createConnection({
    "database": "database",
    "user": "user",
    "password": "xxxxxxx",
    "host": "yyy.yyy.yyy.yyy",
    "debug":true
});

db.connect();
db.query("SELECT * FROM table LIMIT 1", function(err, res) {});
db.end();

I get:

$ node test.js
Add command: Connection
Add command: query
Add command: 
0 undefined ==> Connection#handshakeInit(0,Error,70)
 raw: ff6a04486f737420273137332e33302e38332e32343927206973206e6f7420616c6c6f77656420746f20636f6e6e65637420746f2074686973204d7953514c20736572766572
events.js:85
      throw er; // Unhandled 'error' event
            ^
Error: Host 'zzz.zzz.zzz.zzz' is not allowed to connect to this MySQL server
    at Packet.asError (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/packets/packet.js:493:13)
    at ClientHandshake.Command.execute (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/commands/command.js:25:22)
    at Connection.handlePacket (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:310:28)
    at PacketParser.onPacket (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:83:65)
    at PacketParser.executeStart (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/packet_parser.js:39:12)
    at Socket.<anonymous> (/home/nphyx/Dropbox/dev/fbradius/node_modules/mysql2/lib/connection.js:95:31)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)

(edit: fixed to include mysql2 debug output)

[sidorares]

do you have same results if using node-mysql instead node-mysql2?

[nphyx]

Not identical, but similar (using felixge/node-mysql):

var mysql = require("mysql");

var db = mysql.createConnection({
    "database": "database",
    "user": "user",
    "password": "xxxxxxxx",
    "host": "yyy.yyy.yyy.yyy",
    "debug":true
});

db.connect();
db.query("SELECT * FROM table LIMIT 1", function(err, res) {});
db.end();

I get:

$ node test.js
<-- ErrorPacket
{ fieldCount: 255,
  errno: 1130,
  sqlStateMarker: undefined,
  sqlState: undefined,
  message: 'Host \'zzz.zzz.zzz.zzz\' is not allowed to connect to this MySQL server' }

Is there something different about how mysql/mysql2 implements handshake & auth vs. the mysql cli?

[sidorares]

no difference as far as I know ( mysql2 actually does not support insecure handshake protocol but this is only relevant for server version 3.2 and older ). It's definitely some configuration issue. Do you have access to a server box? Can you try to detect client IP address by running this script on the server:

require('net').createServer(function(s) {
   console.log(" %s %s %s ", s.remoteAddress, s.remotePort, s.remoteFamily );
}).listen(3307);

and try to connect using node and cli mysql ( to a port 3307 ) ?

[nphyx]

Well, that led me down a rabbit trail that seems to have fixed the problem. For expediency's sake I turned off selinux and flushed the firewall rules on the remote host so I could run that script without changing their configurations.

I was able to set up nodejs on the host, run that script, and then connected remotely via mysql-cli, the above test script, and via telnet.

I then ran the test script again, and got a result. So I brought selinux & firewall up, ran it again, got another successful result!

I have no idea how selinux or iptables could have created the situation where mysql-cli works and nodejs/msyql2 does not, or how it went away simply by reloading them, but at least that solved it :/

Thanks a ton for your help, and sorry for the trouble.

[sidorares]

no problems, good luck with the rest!

[jiechliu]

good