Node.js MySQL error: ECONNREFUSED only sometimes

Question

I have read through many similar questions such as node.js mysql error: ECONNREFUSED, but none of their solutions have solved my problem. For example, I ran mysql -hlocalhost -P 3306 -p*** on the command line and it properly connected to my database. It seems that the problem is this specific script. I have another script with the exact same dbconnection.js file and it is connecting just fine, like the command line is.

dbconnection.js

var mysql = require('mysql');
var connection = mysql.createPool ({
  host: '127.0.0.1',
  user: 'root',
  password: ****,
  database: 'test_db',
  dateStrings: true,
  port: 3306
});
module.exports = connection;

And this works in one program, but not another. The problem area of the problem script uses the following code:

Notifications.js

var db = require('../dbconnection');
var express = require('express');
var router = express.Router();

router.post('/', function(req,res,next) {
    handleNotification(req.body.value[0].resource);
    res.status(202).end();
});

async function handleNotification(resource) {

    db.query('SELECT of.name, po.PurchaseOrderID FROM order_files AS of \
    INNER JOIN purchase_orders AS po ON po.PurchaseOrderID = of.purchaseOrderID \
    INNER JOIN quotes ON po.PurchaseOrderID = quotes.PurchaseOrderID \
    WHERE quotes.QuoteID=10000', function(err, rows) {
      if (err) console.log(err);
      else console.log(rows);
    });
}

Yet I can run from bash, mysql -hlocalhost -P 3306 -p**** and then run the exact query shown and get the results. I can also run this query from another script.

The error received is as follows: Error: connect ECONNREFUSED 127.0.0.1:3306

Is there a reason why this specific script would not be able to connect to the database?

Answer 1

The one that is failing is a TCP/IP connection attempt, the one that is succeeding is using a local unix socketfile.

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

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. For connections to localhost, MySQL programs attempt to connect to the local server by 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.

---

That is to say, we can get the same behavior from the MySQL command line client (connection refused), attempting a TCP/IP connection with either

mysql -h 127.0.0.1 -P 3306 -p****

-or-

mysql -h localhost -P 3306 -p**** --protocol=TCP

---

MySQL identifies a user by both user and host.

That is, 'root'@'localhost' is a different user than 'root'@'%'. These users have separate passwords, separate permissions, etc.

To get a list of users defined in MySQL Server, we can execute a query like this:

SELECT u.user
     , u.host
     , u.password
     , u.plugin
     , u.password_expired 
  FROM mysql.user u
 ORDER BY u.user, u.host

Note that if MySQL Server is started without --skip-name-resolve option, MySQL will do a reverse lookup of the IP address into a hostname. That means users will need to be defined as 'user'@'db01.mydomain' and not 'user'@'127.0.0.1'

Reference: https://dev.mysql.com/doc/refman/5.7/en/host-cache.html

---

If that's not the problem, then it's likely a firewall rule preventing the connection, or the SELinux / AppArmor configuration.

Looking at the reported error message again,

Error: connect ECONNREFUSED 127.0.0.1:3306

That doesn't look like the error message we would expect from MySQL Server. That looks more like the network connection was refused, before we even got to MySQL Server.

If MySQL server was not allowing access, we'd expect to be getting an error like this from MySQL Server:

ERROR 1045 (28000): Access denied for user 'root'@'hostname' (using password: YES)