Lambda Function - problems with connection to external MySQL

The issue you're experiencing is related to how Lambda handles asynchronous operations. Your Lambda function is returning before the database query completes because connection.query() is asynchronous, but your handler isn't waiting for it to finish.

When you call the function from your computer via API Gateway, Lambda may terminate the execution context before the database operation completes, which explains why sometimes entries are created and sometimes they aren't. The "Internal server error" message suggests the function is timing out or returning undefined.

To fix this, you need to make your handler properly handle the asynchronous database operation. Here are the key changes needed:

1. Use callbacks or promises: Lambda needs to know when your asynchronous work is complete. You should either use the callback parameter that Lambda provides, or return a Promise.

2. Don't return inside the callback: Your return statement inside the connection.query() callback doesn't actually return from the Lambda handler - it just returns from that inner function.

Here's how to fix your code:

export function handler(event, context, callback) {
connection.query(
'INSERT INTO table_t (source,sourceIP,uid,date) VALUES (\'testing\', ?, UUID(), NOW())',  
[event.requestContext.identity.sourceIp],
function (error, results, fields) {
if (error) {
connection.destroy();
callback(error);
} else {
console.log(results);
connection.end();
callback(null, { 
statusCode: 200,
body: "Ok!"
});
}
}
);
}

Alternatively, use promises with async/await for cleaner code:

export async function handler(event, context) {
return new Promise((resolve, reject) => {
connection.query(
'INSERT INTO table_t (source,sourceIP,uid,date) VALUES (\'testing\', ?, UUID(), NOW())',  
[event.requestContext.identity.sourceIp],
function (error, results, fields) {
if (error) {
connection.destroy();
reject(error);
} else {
console.log(results);
connection.end();
resolve({ 
statusCode: 200,
body: "Ok!"
});
}
}
);
});
}

The key is ensuring Lambda waits for your database operation to complete before the function exits.

Can you paste the whole code? You may be declaring the connection object outside of handler and destroying it inside. That would cause this issue. Here is how lambda handles the variables declared outside the handler:

When you declare variables, SDK clients, or database connections outside the handler function, Lambda treats them as part of the initialization phase (also called static initialization). Here's what happens:

First Invocation (Cold Start):

Lambda downloads your code and sets up the execution environment
Runs initialization code outside the handler (imports, global variables, SDK clients)
Executes the handler function
Freezes (not terminates) the execution environment after completion

Subsequent Invocations (Warm Start):

Lambda thaws the frozen execution environment
Reuses objects and connections declared outside the handler
Executes only the handler function
Freezes again after completion

You have two options: 1. Either create database connection inside the handler and end and destroy it inside. (this will cause some delay for each lambda execution. Also, if there are thousands of lambda invocations, it can affect the database performance)

2. The second option is to leave the connection creating outside and do not end/destroy it inside the lambda handler. This would resolve your issue of subsequent runs without affecting the database performance.

Remember, even when the connection is created outside of the handler, it does not persist forever. It will get destroyed at sometime and recycled. Next Lambda run will not find the connection and reconnect.

I have changed my code, putting the connection creation inside the code. However, the same kind of errors continue to appear - see the error below. My code is:

import mysql from 'mysql';

var connection = mysql.createConnection({
host: ...,
user: ...,
password: ...,
database: "mysqldb"
});

export function handler(event, context, callback) {
    
    connection.connect(function (error) {
      if (error) {
        connection.destroy();
        callback(error);
      } else {

      connection.query(
        'INSERT INTO table_t (source,sourceIP,uid,date) VALUES (\'lambda\', ?, UUID(), NOW())',  
        [event.requestContext.identity.sourceIp],
       function (error, results, fields) {
           if (error) {
               connection.destroy();
               callback(error);
           } else {
               console.log(results);
               connection.end();
               callback(null, { 
               statusCode: 200,
               body: "Ok!"
               });
           }
       });
      }
    });
}

The error is: "errorType": "Error", "errorMessage": "Cannot enqueue Handshake after invoking quit.",