Styjun

I am trying to do testing on my AWS lambda function which makes a query to RDS MySQL (t2.medium).
However, if I request the API a large number of times, although I can get query successfully with correct data, it sometimes results "Error: connect ETIMEDOUT".

Is there anything wrong in my code or setting?

I read some suggestions to set some parameters:

MySQL:

wait_timeout 1

max_connections 16000

interactive_timeout 6000

max_allowed_packet 1073741824

Lamdba:

Timeout 60 sec place the Lambda function in the same VPC as your RDS

Added VPC execution policy AWSLambdaVPCAccessExecutionRole

assign a security group to the lambda function

In the security attached to the RDS instance, added an inbound rule
for mysql

Confirm that Lambda function has access to the same VPC RDS database

Lambda Error Log

2019-03-28T18:51:47.353Z ab4fbbaf-1ea2-458b-a5b5-781cdfdd80df Error:
connect ETIMEDOUT

at Connection._handleConnectTimeout

(/var/task/node_modules/mysql/lib/Connection.js:411:13)

at Object.onceWrapper (events.js:313:30)

at emitNone (events.js:106:13)

at Socket.emit (events.js:208:7)

at Socket._onTimeout (net.js:420:8)

at ontimeout (timers.js:482:11)

at tryOnTimeout (timers.js:317:5)

at Timer.listOnTimeout (timers.js:277:5)

---

at Protocol._enqueue
(/var/task/node_modules/mysql/lib/protocol/Protocol.js:144:48)

at Protocol.handshake
(/var/task/node_modules/mysql/lib/protocol/Protocol.js:51:23)

at Connection.connect
(/var/task/node_modules/mysql/lib/Connection.js:118:18)

at Connection._implyConnect
(/var/task/node_modules/mysql/lib/Connection.js:453:10)

at Connection.query
(/var/task/node_modules/mysql/lib/Connection.js:198:8)

at Promise (/var/task/db.js:62:9)

at new Promise ()

at Object.retrieve (/var/task/db.js:55:10)

at exports.getAlerts (/var/task/index.js:59:24)

errorno: 'ETIMEDOUT',

code: 'ETIMEDOUT',

syscall: 'connect',

fatal: true

RDS Error Log

2019-03-28T18:18:49.378320Z 9500 [Note] Aborted connection 9500 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.392514Z 9498 [Note] Aborted connection 9498 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.470617Z 9499 [Note] Aborted connection 9499 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.636775Z 9501 [Note] Aborted connection 9501 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.694669Z 9502 [Note] Aborted connection 9502 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.803457Z 9503 [Note] Aborted connection 9503 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

2019-03-28T18:18:49.824250Z 9504 [Note] Aborted connection 9504 to db:
'db' user: 'user' host: '123.123.123.123' (Got timeout reading
communication packets)

My db.js query on lambda

const mysql = require('mysql')
let retrieve = (sql, objectArr, entityName) => 
 return new Promise((resolve, reject) => 
 let con = mysql.createConnection(dbParams)
 con.query(sql, objectArr, (err2, results) => 
 con.end()
 if (err2) 
 console.log(err2)
 return reject(new apiError.DatabaseError('An error occurred in retrieve'))
 
 console.log('Data retrieve successfully')
 return resolve(results)
 )
 )
 

My test.js script

const request = require('request')
let errorCount = 0
let success = 0

for (let i = 0; i < 4000; i++) 
 console.log('Send')
 request.get('https://myapi/users', 
 headers: 
 'client_id': 'app',
 'Content-Type': 'application/json'
 
 , (error, response, body) => 
 if (error) 
 console.log('some error')
 errorCount++
 else 
 let jsonBody = JSON.parse(body)
 if (jsonBody.code === 0) 
 success++
 else 
 errorCount++
 
 

 console.log('Success: ', success)
 console.log('Error: ', errorCount)
 )

Edit:
I also tried to change the i < 1 in test script, then it always gives me "Error: connect ETIMEDOUT. But with i < 900, it works successfully sometime

index.js

const db = require('./db.js')
exports.getUsers = async (event, context) => 
 context.callbackWaitsForEmptyEventLoop = false
 try 
 let sql = 'SELECT * FROM User'
 let abc = await db.retrieve(sql, [], 'user')

 let response = 
 statusCode: 200,
 abc: abc,
 code: 0
 
 return response
 catch (err) 
 console.log(err)
 errorHandler(err)
 

db.js with Pool

const mysql = require('mysql')
const constants = require('./constants.js')

let dbParams = 
 host: constants.SQL_CONNECTION_HOST,
 user: constants.SQL_CONNECTION_USER,
 password: constants.SQL_CONNECTION_PASSWORD,
 database: constants.SQL_CONNECTION_DATABASE,
 multipleStatements: true,
 maxConnections: 4


const pool = mysql.createPool(dbParams)
let retrieve = (sql, objectArr, entityName) => 
 return new Promise((resolve, reject) => 
 pool.getConnection((err1, con) => 
 if (err1) 
 console.log(err1)
 return reject(new apiError.DatabaseError('An error occurred in retrieve pool'))
 
 console.log('Pool connect successfully')
 con.query(sql, objectArr, (err2, results) => 
 con.end()
 if (err2) 
 console.log(err2)
 return reject(new apiError.DatabaseError('An error occurred in retrieve'))
 
 console.log('Data retrieve successfully')
 return resolve(results)
 )
 )
 )

Error Log after used pool

2019-03-28T23:35:24.144Z 91b0fc78-e4d1-4fd9-bdf7-923715b165c0 Error:
Handshake inactivity timeout

at Handshake.
(/var/task/node_modules/mysql/lib/protocol/Protocol.js:163:17)

at emitNone (events.js:106:13)

at Handshake.emit (events.js:208:7)

at Handshake._onTimeout
(/var/task/node_modules/mysql/lib/protocol/sequences/Sequence.js:124:8)

at Timer._onTimeout
(/var/task/node_modules/mysql/lib/protocol/Timer.js:32:23)

at ontimeout (timers.js:482:11)

at tryOnTimeout (timers.js:317:5)

at Timer.listOnTimeout (timers.js:277:5)

---

at Protocol._enqueue
(/var/task/node_modules/mysql/lib/protocol/Protocol.js:144:48)

at Protocol.handshake
(/var/task/node_modules/mysql/lib/protocol/Protocol.js:51:23)

at PoolConnection.connect
(/var/task/node_modules/mysql/lib/Connection.js:118:18)

at Pool.getConnection (/var/task/node_modules/mysql/lib/Pool.js:48:16)

at Promise (/var/task/db.js:72:10)

at new Promise ()

at Object.retrieve (/var/task/db.js:67:10)

at exports.getAlerts (/var/task/index.js:59:24)

code: 'PROTOCOL_SEQUENCE_TIMEOUT',

fatal: true,

timeout: 10000

Set with Pool now and test it with request loop:

i < 100 result => Success: 866 and Error: 134 requests.

i < 10 result => Success: 8 and Error: 2 requests.

Error with Handshake inactivity timeout

db.js with con.createConnection outside

const mysql = require('mysql')
// initialize dbParams
let con = mysql.createConnection(dbParams)

let retrieve = (sql, objectArr, entityName) => 
 return new Promise((resolve, reject) => 
 con.query(sql, objectArr, (err2, results) => 
 //con.end() commet out connection end here 
 if (err2) 
 console.log(err2)
 return reject(new apiError.DatabaseError('An error occurred in retrieve'))
 
 console.log('Data retrieve successfully')
 return resolve(results)
 )
 )

It is not explained in the question how does the retrieve function is being called inside your lambda function.

Even though, it strongly seems that it will be executed for each iteration inside your test, which creates a very large number of connections and may explain the behavior you see.

I suggest creating the connection at lambda initialization time (aka 'coldstart'), by moving the code line let con = mysql.createConnection(dbParams) outside of any function (so that only a single connection will take place).

Another good option is to use connection pooling.