CODEWITHSUNDEEP
mysqlnode.js
A Wizard Did It
A Wizard Did It

Reputation: 3674

MySQL Can Connect Remotely but not Locally

This is a weird problem and I'm not sure what's going on. I installed MySQL on a linux box I have running Ubuntu 10.04 LTS. I can access mysql via SSH mysql -p and perform all my commands that way. I added a user, and I can use AddedUser to connect remotely from my machine, but not from the local machine. It makes no sense to me...

SELECT host, user FROM mysql.user Yields: 

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | AddedUser        |
| 127.0.0.1 | root             |
| li241-255 | root             |
| localhost | debian-sys-maint |
| localhost | root             |
+-----------+------------------+

Problem is I'm developing on this machine using Node.js, and I can't connect locally from the server using the same username. I've tried FLUSH PRIVILEGES but that seems to have no effect.

I know it's not Node.js because I'm using the same code on another database and it's working in that environment.

Edit

This is the error node is giving me.

node.js:50
    throw e; // process.nextTick error, or 'error' event on first tick
    ^
Error: ECONNREFUSED, Connection refused
    at Stream._onConnect (net.js:687:18)
    at IOWatcher.onWritable [as callback] (net.js:284:12)

Edit 2

I have the right port & server as best I can tell. My /etc/mysql/my.cnf contains this:

port            = 3306
socket          = /var/run/mysqld/mysqld.sock

My MySQL object contains:

{ host: 'localhost',
  port: 3306,
  user: 'removed',
  password: 'removed',
  database: '',
  typeCast: true,
  flags: 260047,
  maxPacketSize: 16777216,
  charsetNumber: 192,
  debug: false,
  ending: false,
  connected: false,
  _greeting: null,
  _queue: [],
  _connection: null,
  _parser: null,
  server: 'ExternalIpAddress' }

Possibly useful?

netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     1016418  /var/run/mysqld/mysqld.sock

Upvotes: 4

Views: 9649

Answers (8)

tqwhite
tqwhite

Reputation: 880

For me, this problem occurred on an Ubuntu box only accessible on our internal net (though I think this solution applies regardless of the network). I was able to access mysql perfectly well with mysql Workshop and with Node.js mysql plugin from my dev system but not from Node running on the server.

In particular, I tried localhost, 127.0.0.1 or even 'serverName', which was defined as 127... in /etc/hosts. I got nothing but ECONNREFUSED.

Turns out that my mysql instance was set to only accept connections addressed to the server's IP address. That is, it would only accept connections from

{host:'172.xxx.xxx.xxx', user:'blah', ....

Good luck!!

Upvotes: 0

Siva
Siva

Reputation: 171

You need to set the socketPath

mysql = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'password',
    socketPath  : '/var/run/mysqld/mysqld.sock',

});

Upvotes: 3

maggie1000
maggie1000

Reputation: 61

In my.cnf, I changed bind-address to 0.0.0.0 and now connections from my node app are correctly accepted.

Upvotes: 0

Nick Hagianis
Nick Hagianis

Reputation: 1468

Manually defining the socket fixed this problem for me.

var client = mysql.createClient({
    user: '(your user here)',
    password: '(your password here)',
    host: '127.0.0.1',
    port: '3306',
    _socket: '/var/run/mysqld/mysqld.sock',
});

Upvotes: 2

flienteen
flienteen

Reputation: 234

same problem. i've just commented the line with skip-networking in mysql config file, and the problem dissapeared

Upvotes: 1

bbaane
bbaane

Reputation: 1

As the first and good answer says, MySQL isn't listening to your local TCP/IP sockets.

Connect your mysql.Client module using:

client.port = '/tmp/mysql.sock';

and fill in wherever your mysql socket file is located. If you can't find this, consider using this heavy but success-guaranteed system-wide search

cd / ; find * | grep mysql.sock

Upvotes: 0

Shripad Krishna
Shripad Krishna

Reputation: 10498

I have had similar issues, but with MongoDB. I think you need to make your host point to 127.0.0.1 rather than localhost.

Check:

$ nslookup localhost

and

$ nslookup localhost.

both should return an address of 127.0.0.1

Also check cat /etc/hosts

if localhost maps to ipv6: ::1 localhost then change to 127.0.0.1 localhost

localhost might be pointing to the ipv6 address rather than the ipv4 address.

Hope this helps :)

Upvotes: 1

Jim Garrison
Jim Garrison

Reputation: 86774

Connection Refused is being signaled at the TCP/IP protocol level, and means that your local connection attempt is using the wrong hostname and/or (more likely) port number.

EDIT: Here's another (admittedly low-probability) possibility:

  1. You have both host and server defined in the JS object, in which case node.js might give precedence to server.
  2. If the externalIPaddress is on the other side of a NAT firewall, the firewall may be (actually, should be) configured to discard incoming traffic to port 3306.
  3. When you try to connect, your packets are being routed to the NAT firewall first and are being discarded there.

Verify that having both server and host set will not cause this problem.

Upvotes: 4

Related Questions