MySQL Can Connect Remotely but not Locally

Question

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

Answer 1

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.

Answer 2

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',

});