mysql: [Error: connect ETIMEDOUT]

Hi @cashFLO960 the error Error: connect ETIMEDOUT is from the Node.js networking code. It means that a TCP connection could not be established to your MySQL server. Usually this is a networking or firewall issue.

Hi there,

When creating an RDS instance via the AWS web console and when following “default” settings, AWS puts your instance into a security group with inbound rules set to your machine IP. Also, everything will work when running on your local machine but will timeout when doing the same thing in Lambda.

Solution: modify the default security group inbound rules associated with your db instance.

I fought this same error for about 2 days when using Nodejs mysql module in AWS Lambda when trying to connect to a database in a VPC. It is a very tough problem to debug because Lambda’s logging is so flaky (depending on the error you may not get log data at all).

@dougwilson had the right thought in that it is most likely is a networking issue.

@hagen is certainly right that all those factors have to be worked out first before it will even consider working. Also, if you are accessing a service that is not in the same VPC as your Lambda function you will need a route to it, probably using a VPC NAT Gateway, NOTE: a VPC Internet Gateway will not work as you might think because Lambda does not allocate a public IP address to its ENI, only a private IP address from one of the VPC subnets you select in Lambda’s “Configuration”.

If you can work all that out, which is not easy! Chances are it still will not work if you are using traditional Nodejs code logic. This is due primarily, because Lambda’s have a high start-up delay and automatically passivate and reactive depending on when and how often they are accessed. This causes mysql’s data connection pools to work sometimes and fail other times. Often what you will see is that the mysql connection works fine the first invocation then fails most of the time afterwards. If you see this behavior you are half way to solving the problem as it means you have solved all of the AWS VPC networking issues and are now facing Lambda delay, passivation, reactivation issues. These issues can be fixed by modifying your Nodejs Lambda’s source code logic.

I have tried to provide a simplified example of what you need to do below:

` const VERSION = “1”; console.log(“asclepiusrod.net Lambda Tutorial " + VERSION + " starting up…”); process.env[‘PATH’] = process.env[‘PATH’] + ‘:’ + process.env[‘LAMBDA_TASK_ROOT’];

//CMT You must install below packages first i.e. npm install fs, npm install await, npm install aws-sdk, npm install mysql <or> npm install mysql2

const fs = require(‘fs’); const Await = require(‘await’); const AWS = require(‘aws-sdk’); const mysql = require(‘mysql’); //const mysql = require(‘mysql2’);

const DATABASE_HOST = process.env[‘DATABASE_HOST’]; const DATABASE_USERNAME = process.env[‘DATABASE_USERNAME’]; const DATABASE_PASSWORD_ENCRYPTED = process.env[‘DATABASE_PASSWORD’]; let DATABASE_PASSWORD; const DATABASE_SCHEMA = process.env[‘DATABASE_SCHEMA’]; const DATABASE_POOL_LIMIT = process.env[‘DATABASE_POOL_LIMIT’]; const DATABASE_CONNECT_TIMEOUT_MILLI = process.env[‘DATABASE_CONNECT_TIMEOUT_MILLI’]; const DATABASE_TEST_TABLE = process.env[‘DATABASE_TEST_TABLE’]; const KMS_REGION = new String(process.env[‘KMS_REGION’]);

var mySqlPool; var AWSEvent; var AWSContext; var AWSCallback; var promise1; var promise2; var promise3; AWS.config.update(KMS_REGION); //AWS.config.update({region: ‘us-east-1’});

makePromise3(); makePromise2(); makePromise1();

decryptENV();

function makePromise1() { promise1 = Await(‘DATABASE_PASSWORD’); promise1.onkeep(function (got) { console.info(‘[OK] Promise1 kept, creating database connection pool…’); DATABASE_PASSWORD = got.DATABASE_PASSWORD; createDBConnectionPool(); testDBConnection(); }) .onfail(function (err) { console.error(‘[FATAL!] [ERROR!] Promise1 not kept!’ + err, err.stack); promise2.fail(err); }) .onresolve(function () { console.info(‘[INFO] Promise1 resolved.’); }); } function makePromise2() { promise2 = Await(‘isDBTestSuccessful’, ‘isAWSReady’); promise2.onkeep(function (got) { console.log(‘[OK] Promise2 kept, database test successful.’); AWSContext.callbackWaitsForEmptyEventLoop = false; get(AWSEvent, AWSContext); }) .onfail(function (err) { console.error(‘[FATAL!] [ERROR!] Promise2 not kept!’ + err, err.stack); promise3.fail(err); }) .onresolve(function () { console.info(‘[INFO] Promise2 resolved.’); }); }

function makePromise3() { promise3 = Await(‘response’); promise3.onkeep(function (got) { console.info(‘[OK] Promise3 kept, database test successful.’); //CMT - export.handler final success return AWSCallback(null, JSON.stringify(got.response)); console.info(‘[OK] Lambda function completed successfully, ok to to end process once threads finish wrapping up. ADMIN_CODE: 75’); }) .onfail(function (err) { console.error(‘[FATAL!] [ERROR!] Promise3 not kept!’ + err, err.stack); //CMT - export.handler final failure return AWSCallback(err); console.error(‘[FATAL!] Lambda function completed unsuccessfully, ok to to end process. ADMIN_CODE: 82’); }) .onresolve(function () { console.info(‘[INFO] Promise3 resolved.’); //CMT - Not sure it is efficent to execute end() and may cause intermittent timesouts to requests - mySqlPool.end(); AWSContext.done(); //CMT - index.js final return return; }); }

function decryptENV() { console.log(‘Decrypting enviroment variables…’); //Put logic here to keep promise1 }

function createDBConnectionPool() {
try { if (!mySqlPool) { mySqlPool = mysql.createPool({ connectTimeout: DATABASE_CONNECT_TIMEOUT_MILLI, connectionLimit: DATABASE_POOL_LIMIT, host: DATABASE_HOST, user: DATABASE_USERNAME, password: DATABASE_PASSWORD, database: DATABASE_SCHEMA, authSwitchHandler: ‘mysql_native_password’ });

        mySqlPool.on('connection', function (connection) {
            console.log('mySqlPool established a database connection.');
        });

        mySqlPool.on('error', function (err) {
            console.warn('mySqlPool database connection pool failed!' + err, err.stack);
            promise2.fail(err);
        });
    }
    return mySqlPool;
} catch (err)
{
    console.error("FATAL! ERROR! Cannot create connection pool!  Verify that your credentials in ENV are correct. ADMIN_CODE: 122 ", err);
    promise2.fail(err);
}

}

function testDBConnection() { console.log(“Connecting to database…”); try { if (!mySqlPool) { createDBConnectionPool(); console.log(“mySqlPool not ready, requesting pool initialization…”); } mySqlPool.getConnection(function (err, con) { if (err) { console.warn("WARN! Unable to create database connection pool! Will try again later. ADMIN_CODE: 138 ; “, err); //CMT Do not fail promise here as you might think - promise2.fail(err); return false; } else { console.log(“SUCCESS. MySql database pool online.”); promise2.keep(‘isDBTestSuccessful’, true); con.release(); return true; } }); } catch (err) { console.error(”[FATAL!] [ERROR!] Cannot connect to database! Verify that your credentials in ENV are correct and that database is online and you have a route. ADMIN_CODE: 151 ", err); promise2.fail(err); return false; } }

/*

• //CNT Uncomment and comment exports.handler line to test local

function exitNow(err) { process.exit(1); return err; }

function t1(event, context, callback) { */

exports.handler = function (event, context, callback) { AWSEvent = event; AWSContext = context; AWSCallback = callback;

makePromise3();
if (!testDBConnection())
{        
    makePromise2();
}
promise2.keep('isAWSReady', true);

}

function get(myAWSEvent, myAWSContext) { var myResponse = null; var header = “<html><body>”; var footer = “</body></html>”; var welcome = “

Welcome to Lambda Tutorial vR” + VERSION + “.”; welcome += "

App Server is online.

Database server is "; try { mySqlPool.getConnection(function (err, con) { if (err) { console.error("FATAL! ERROR! Could not create connection pool or port not open! ADMIN_CODE: 192 ; ", err); console.warn(“Database is OFFLINE.”);

            myResponse = header + welcome + footer;
            promise3.keep('response', myResponse);
            return;
        } else
        {
            console.log("Accessing database...");
            var sql = "SELECT * FROM " + DATABASE_TEST_TABLE + " LIMIT 1 ;";
            con.query(sql, function (err, rows, fields) {
                if (err)
                {
                    console.warn("Database is OFFLINE.");
                    console.error("[FATAL!] [ERROR!] Executing SELECT query on database. ADMIN_CODE: 207 ; ", err);
                    welcome += "offline! Please try again later. ";                        
                } else {
                    console.info("Database is ONLINE.");
                    welcome += "online. <p>Congratulation! You have fixed the problem. Lambda is working!";
                }
                myResponse = header + welcome + footer;
                promise3.keep('response', myResponse);
                con.release();
                return;
            });
        }
    });
} catch (err) {
    console.error("[FATAL!] [ERROR!] Could not create connection pool or port not open! ADMIN_CODE: 221 ; ", err);
    console.warn("Database is OFFLINE.");
    welcome += "offline! <p>Please try again later. ";
    
    myResponse = header + welcome + footer;
    promise3.keep('response', myResponse);
    return;
}

}

`

Good Luck! Sam

It might not be related, but I got Error: connect ETIMEDOUT because the default value for the createConnection option connectTimeout (10000 or 10sec) was not enough which obviously has an easy fix.

My theory for my case is something like this:

• …some code
• make a query: connection.query(..) (async)
• …do some intensive coding that “takes up” the whole node thread, not giving room for the query callback function to trigger before the timeout is past

For me, I had some code that was working synchronously and did some heavy lifting that lasted for almost a minutte. Setting the connectTimeout option to 60000 did the trick for me.

I spend a couple of days debugging this problem. Even though I found a fix, I’m still not really satisfied with my “theory”, but I don’t know enough about how node works to fully understand this.

Also, @cashFLO960 , I tried to copy and paste the both pieces of of code into files and when I run them, I get the following error:

ReferenceError: mysql is not defined

What am I missing?

Hi @nilasissen the error Error: connect ETIMEDOUT is from the Node.js networking code. It means that a TCP connection could not be established to your MySQL server. Usually this is a networking or firewall issue.

1. Node Version = 4.2.3
2. Using mysql": “*” in package.json 3.

var connection = mysql.createConnection({ host: ‘xxx’, user: ‘xxx’, password: ‘xxx’, database: ‘xxx’,

});

app.get(‘/test1’, //require(‘connect-ensure-login’).ensureLoggedIn(), function (req, res) { var testQuery = ‘select * from table1’ connection.query(testQuery, function (error, results, fields) { console.log(results); res.render(‘test1’, { user: req.user, results: results}); }); });

var pool = mysql.createPool({ connectionLimit: 10, acquireTimeout: 30000, //30 secs host: ‘xxx’, user: ‘xxx’, password: ‘xxx’, database: ‘xxx’, minConnections: 1,

});

app.get(‘/test’, //require(‘connect-ensure-login’).ensureLoggedIn(), function (req, res) {

     pool.getConnection(function (err, connection) {
         console.log(connection);



            var testQuery = 'select * from table1'
            connection.query(testQuery, function (error, results,

fields) { console.log(results); res.render(‘test’, { user: req.user, results: results });

            });


     });
});

Thank you so much for helping out with this.

I got the same error,but when I changed my connection config ‘host’:‘localhost’ to ‘host’:‘127.0.0.1’ it works fine. It is to be observed that I can connect mysql server with mysql-client command mysql -hlocalhost -uroot -proot.

Hi Douglas,

I saw that in previous responses. I am able to connect and query just fine using var connection = mysql.createConnection()… why would that work but not pool?

Hi there, I am having same issue with ETIMEDOUT as well. This code runs fine on localhost but not on production. Also, I’ve tested regular connections and pooled connection(without running a query) and those work too. Here is code:

var pool = mysql.createPool({
    connectionLimit: 10,
    acquireTimeout: 30000, //30 secs
    host: 'xxx',
    user: 'dev',
    password: 'xxxx',
    database: 'xxxx'

});

app.get('/thisPage',
    //require('connect-ensure-login').ensureLoggedIn(),
    function (req, res) {

        pool.getConnection(function (err, connection) {

            if (err) {
                console.log(err);
            } else {

                var query1 = 'select * from table'
                connection.query(query1, function (error, results, fields) {
                    res.render('thispage', { user: req.user, results: results });

                });
                connection.release();
            };

        });

    });

Returns error:

{ [Error: connect ETIMEDOUT]
  errorno: 'ETIMEDOUT',
  code: 'ETIMEDOUT',
  syscall: 'connect',
  fatal: true }

I can connect just fine using connection.connect() or pool.getConnection() without a query. Any ideas on how to resolve?

Same issue, but this video hepled me out: https://www.youtube.com/watch?v=I9Fzm1obG7U copy ip from ssh: [ec2-user@ip-172-31-14-241 app]$ <–that one and add to RDS security group as mysql/aurora.

This same issue is cropping up in different forms, but is essentially: “My Nodejs Lambda using mysql gets TIMEDOUT” The fix is to ensure that your security groups are correctly configured - RDS instance must be in one, Lambda must be in one. Lambda must also be in the same VPC as RDS, and have the policy AWSLambdaVPCAccessExecutionRole (note, it is a policy NOT a role, as the name suggests). Once you have your Lambda and RDS in different SGs, you must allow TCP traffic into your RDS SG from the Lambda’s SG. As @dougwilson keeps saying, this is a TCP/networking issue. The explanation above is the ‘exact’ networking issue.