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?

Solution 1

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);
    });           
});

Solution 2

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
}