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?

Iota

How long does each query take, on average? – Bottali

@schtever: The time is very less.. about a few milliseconds – Iota

Hello, as i have a similar issue, is it also related to an increased CPU issue? – Finegan

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

Retard

Thanks, this was exactly my problem working with AWS Lambda. – Bellini

Had same problem with local instance of MariaDB. 'conn.end()' does the trick. – Laoighis

@MauricioAvendaño I have a problem with Lambda as well and still can't resolve the issue. How did you do it exactly? You can also post an answer under my SO question if you want: #71514349. Thank you – Breastsummer

Answer 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
}

Pothook

I'm up to know the why it happens time to time! There must be some reason? – Iota

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... – Pothook

Lol! Btw are you using cluster mode with node.js or single threaded mode only? PS: I've updated the question in this reference. – Iota

The whole application is clustered, but not using pool clustering @haywire – Pothook

Answer 3

I'm using Node.js 18 with the MySQL 2.18.1 module. I also have a connection pool set up.

In my case, when I boot up my development environment, it always fails to execute the database connection module (myDb.js) at the start with the error "Error: connect ETIMEDOUT." However after a request to "localhost:3000", MySQL executes without error. It has been bothering me a lot. On the Linux server, it was starting twice.I tried to increase "connectTimeout" but did not help. I believe it is the same problem that @baao mentioned. There were 36 connection queries in "myDb.js", and the connection limit was set to 10. Once I increased the limit to 30+, the glitch went away.

You can try increasing the connectTimeout, connectionLimit, queueLimit, and acquireTimeout in options separately or all of them together. This can solve random connection errors to the database.

var mysql = require("mysql");     
var connPool = mysql.createPool({
      connectionLimit: 36,
      host: process.env.MYSQL_HOST,
      port: process.env.MYSQL_PORT,
      user: process.env.MYSQL_USER,
      password: process.env.MYSQL_PASS,
      database: process.env.MYSQL_DB_NAME,
      connectTimeout: 30000,
    });
connPool.getConnection(function (err, con) {
  if (err) return console.error("Mysql Connection: " + err.message); 
  con.query("CREATE TABLE IF NOT EXISTS personal_info(id INT ,name VARCHAR(50))",(error,result)=>{
    if (result.warningCount === 0 && !error) {console.log("Table Created")}
    if(error) throw error;
  });
  /* 
   * multiple con.queries
   */
  console.log("Mysql Tables are ready.");
  con.release();
});
module.exports = connPool;

Side note : always use connectionPool! If you use "createConnection", Node.js server will crash in database connection problems. This could result in multiple restarts in production server if database connection interrupted or overloaded.

Pieplant

Answer 4

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.

Hylozoism

removing con.connect stops the mysql from attempting to connect to the MYSQL server, so of course it would prevent the issue. – Alpaca