Reusing Mysql Connection in Lambda on AWS

Question

I have a lot of lambda invocations and I am always querying database so I want my lambda to be able to reusing connection, so I am establishing connection in constructor, like this:

let mysqlConnection = mysql.createConnection({
  host: process.env.HOST,
  user: process.env.DB_USER,
  password: process.env.BDB_PWD,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME
});

dbConn.connect(function(err) {
  if (err) {
    throw new Error('Error during connecting to db');
  } else {
    console.log("Database is connected");
  }
});

And I have callbackWaitsForEmptyEventLoop set to false in my handler:

exports.handler = (event, context) => {
  context.callbackWaitsForEmptyEventLoop = false;
  event.Records.forEach(record => {
   processEvent(JSON.parse(record.body), context);
  });
 };

When mysql connection fails I am destroying mysql connection, my query in provessEvent function looks like this:

  mysqlConnection.query(sql, values, function (err, rows) {
    if (err) {
      mysqlConnection.destroy();
      context.fail();
    } 
  }

But I am getting a lambda errrors from time to time. I guess sometimes lambda wants to reuse a connection which is not available.

 "Error: Error during connecting to db",
    "    at Handshake.<anonymous> (/var/task/lambda.js:34:13)",
    "    at Handshake.<anonymous> (/var/task/node_modules/mysql/lib/Connection.js:525:10)",
    "    at Handshake._callback (/var/task/node_modules/mysql/lib/Connection.js:491:16)",
    "    at Handshake.Sequence.end (/var/task/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)",
    "    at Protocol.handleNetworkError (/var/task/node_modules/mysql/lib/protocol/Protocol.js:369:14)",
    "    at Connection._handleNetworkError (/var/task/node_modules/mysql/lib/Connection.js:421:18)",
    "    at Connection._handleConnectTimeout (/var/task/node_modules/mysql/lib/Connection.js:417:8)",
    "    at Object.onceWrapper (events.js:286:20)",
    "    at Socket.emit (events.js:198:13)",
    "    at Socket.EventEmitter.emit (domain.js:448:20)"

Is there anything could I do to prevent this situation? Or should I destroy connection and connect once again?

lexfaraday · Accepted Answer · 2020-02-03 11:37:21Z

3

I found this useful tool that could help you, https://github.com/jeremydaly/serverless-mysql you need just to install a npm module and configure it. Check also the #connection-backoff section.

answered Feb 3, 2020 at 11:37

lexfaraday

765 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lajtovo Over a year ago

Thanks, I will check that tool!

Tuan Vo · Accepted Answer · 2020-02-03 11:39:06Z

0

I do not use MySQL but Mongodb in some projects. callbackWaitsForEmptyEventLoop was help.

'use strict';

const mysql = require('mysql'); // require mysql

// If 'client' variable doesn't exist
if (typeof client === 'undefined') {
  // Connect to the MySQL database
  var client = mysql.createConnection({
    // your connection info
  });

  client.connect()
}

module.exports.handler = (event, context, callback) => {
  // This will allow us to freeze open connections to a database
  context.callbackWaitsForEmptyEventLoop = false;

  client.query('SELECT * FROM `books`', function (error, results) {
    callback(null, results)
  });
}

Refer to AWS document and this tutorial

edited Feb 3, 2020 at 11:39

answered Feb 3, 2020 at 11:32

Tuan Vo

2,09513 silver badges10 bronze badges

3 Comments

Lajtovo Over a year ago

yeah, I am using it as I wrote in my post and it's working, but from time to time some connections error are happening which I want to get rid of.

Tuan Vo Over a year ago

I see it is network error. do you set timeout or something?

Lajtovo Over a year ago

I suspect that lambda want to reuse connection which has been timeouted (well, database connections are not "forever") and I am looking for a way to avoid it.

Collectives™ on Stack Overflow

Reusing Mysql Connection in Lambda on AWS

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related