Node-Mysql throwing connection timeout

Question

My node.js app gives 5xx due to connection timeouts at random times. Here's how I connect and query:

var mysql = require('mysql');
var config = {  
            host: '172.10.1.1',
            port: 3306,
            user: 'user',
            password: 'pwd',
            database: 'mydb',
            connectionLimit: 15,
            queueLimit: 30
        }

var poolCluster = mysql.createPool(config);

var queryDB = function(query, cb) {
    poolCluster.getConnection(function(err, connection) {
        if(err) {
            cb(err, null);
        }
        else {
            connection.query(query, function (err, rows) {
                connection.release();
                cb(err, rows);
            });
        }
    });
};

Also, in another alternate version, with connection pooling disabled, the code looks like this:

queryDB = function(query, cb) {
    var connection = mysql.createConnection(config);
    connection.query(query, function(err, rows) {
        connection.end();
        cb(err, rows);
    });
};

But both the setups give Error: connect ETIMEDOUT at Connection._handleConnectTimeout

A similar project to my current setup can be seen here: https://github.com/hay-wire/NodeBootstrap/blob/master/models/UsersUtils.js

It would be great if you could point out what could be going wrong with the connections. Thanks.

UPDATE

Since the node.js service was running in cluster mode, I thought maybe a race condition across the threads to acquire mysql connection resource from the shared connection pool is the reason. So I switched off the cluster mode to single thread mode and connection timeouts stopped.

Still I'm not convinced it was the race condition causing this issue. Any way to verify this?

Hello, as i have a similar issue, is it also related to an increased CPU issue? — gtzinos, Oct 23 '18 at 20:49

score 8 · Answer 1 · answered Jun 05 '17 at 19:36

This has nothing to do with the timeout. I noticed the following (if you are using it in something like AWS Lambda functions, I think this also applies to many situations with callbacks).

You are calling the connection.end(); before it actually sends the COM_QUIT packet to the MySQL server to close the connection. So the next time you just import var mysql = require('mysql'); (in my case at least) it will throw a timeout error as the previous connection still seems open to your machine but has actually been closed by MySQL.

Please see this link from directly from the documentation on terminating connections

So to fix this condition use .destroy() instead of .end().

connection.query(query, function(err, rows) 
{            
    connection.destroy();
    cb(err, rows);                  
});

Other wise use .end() correctly with a callback as in:

connection.query(query, function(err, rows) 
{            
    connection.end(function(errCon) //Don't do anything with end Error
    {
       // The connection is terminated now 
       cb(err, rows);
    });           
});

Thanks, this was exactly my problem working with AWS Lambda. — Mauricio Avendaño, Mar 07 '20 at 15:13
Had same problem with local instance of MariaDB. 'conn.end()' does the trick. — Rares-Mihai Chelariu, Mar 17 '21 at 08:42

score 6 · Answer 2 · answered Oct 04 '15 at 13:25

6

That happens from time to time, the default acquireTimeout is a bit low (10000ms), so you should increase it if you have multiple connections running. You can set it in your connection options with

acquireTimeout: 1000000

var config = {  
        host: '172.10.1.1',
        port: 3306,
        user: 'user',
        password: 'pwd',
        database: 'mydb',
        connectionLimit: 15,
        queueLimit: 30,
        acquireTimeout: 1000000
}

answered Oct 04 '15 at 13:25

baao

62,535
14
113
168

I'm up to know the why it happens time to time! There must be some reason? – Haywire Oct 04 '15 at 16:38
Good question, there was a longer discussion on github about it with no real answer. I'd say it might be due to network latency or whatever. @haywire. I'm using node-mysql with a 5000 connections pool and didn't run into the issue since I increased the timeout. I then stopped trying to find an answer on the why... – baao Oct 04 '15 at 16:41
Lol! Btw are you using cluster mode with node.js or single threaded mode only? PS: I've updated the question in this reference. – Haywire Oct 04 '15 at 16:50
The whole application is clustered, but not using pool clustering @haywire – baao Oct 04 '15 at 16:53

score -1 · Answer 3 · edited Nov 14 '19 at 09:26

-1

I was also getting a connection timeout error while executing the following code.

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  port:"3307",
  user: "root",
  password: "root",
  database:"nodedb"
});


/*
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});
*/
module.exports=con;

I removed con.connect() method to resolve this issue. It's working fine now.

edited Nov 14 '19 at 09:26

Nilanshu Jaiswal

1,265
3
15
31

answered Nov 14 '19 at 08:26

user2486108

1

removing con.connect stops the mysql from attempting to connect to the MYSQL server, so of course it would prevent the issue. – Christopher Allen May 02 '21 at 23:23

Node-Mysql throwing connection timeout

3 Answers3

Linked