How to pass multiple MySQL Queries in AWS Lambda Function at once

Question

I have a multiple MySQL queries created in Workbench. This query runs by one trigger only in Workbench. I would like this query to be embedded to my AWS Lambda Function but to no avail. I suspect that it is because it multiple queries at the same time.

Here's the code that I created but failed:

var mysql = require('mysql');
var config = require('./config.json');
var pool  = mysql.createPool({
       host     : config.dbhost,
       user     : config.dbuser,
       password : config.dbpassword,
       database : config.dbname
    });

exports.handler = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {
      // Use the connection
      connection.query('DROP VIEW IF EXISTS view_table; \
                        CREATE VIEW view_table AS \
                            SELECT table3.field1, table1.field2 \
                            FROM table1 \
                            JOIN table2 ON table1.table2_id = table2.id \
                            JOIN table3 ON table1.table3_id = table3.id \
                            JOIN table4 ON item_var.table4_id = table4.id \
                            WHERE item_var.list_id = ? \
                            ORDER BY table1 ASC LIMIT 3 \
                        SELECT table4.field1, table2.field2, table2.field3, \
                            (SELECT field1 FROM view_table LIMIT 0, 1) AS field4, \
                            (SELECT field1 FROM view_table LIMIT 1, 1) AS field5, \
                            (SELECT field1 FROM view_table LIMIT 2, 1) AS field6 \
                        FROM table2 \
                        JOIN table4 ON table2.id = table4.id \
                        WHERE table2.id = ?', event['param1'], function (error, results, fields) {
      // And done with the connection.
      connection.release();
      // Handle error after the release.
      if (error) callback(error) ;
      else callback(null, results);
    });
  });
};

And here's the error message I got:

Response:
{
  "errorType": "Error",
  "errorMessage": "ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE VIEW view_table SELECT table3.field1,' at line 1",

I suspect the reason for this is Lambda does not accept multiple MySQL queries at the same time.

There are options I got here like,

1. To use AWS SNS Notifications; or
2. Create multiple lambda and trigger them one over the other.

Can anyone guide me how to do this properly?

Answer 1

This is entirely down to MySQL not supporting multi query, from what I can see you have 2 queries.

You should not try running in parallel based on what you've provided as you cannot guarantee the order, why not instead just perform as two seperate queries such as below.

var mysql = require('mysql');
var config = require('./config.json');
var pool  = mysql.createPool({
       host     : config.dbhost,
       user     : config.dbuser,
       password : config.dbpassword,
       database : config.dbname
    });

exports.handler = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {
      // Use the connection
      connection.query('DROP VIEW IF EXISTS view_table;', function (error, results, fields) {
      // Handle error after the release.
      if (error) callback(error) ;
    });
      connection.query('CREATE VIEW view_table AS \
                            SELECT table3.field1, table1.field2 \
                            FROM table1 \
                            JOIN table2 ON table1.table2_id = table2.id \
                            JOIN table3 ON table1.table3_id = table3.id \
                            JOIN table4 ON item_var.table4_id = table4.id \
                            WHERE item_var.list_id = ? \
                            ORDER BY table1 ASC LIMIT 3;', function (error, results, fields) {
      // Handle error after the release.
      if (error) callback(error) ;
    });
    
      connection.query('SELECT table4.field1, table2.field2, table2.field3, \
                            (SELECT field1 FROM view_table LIMIT 0, 1) AS field4, \
                            (SELECT field1 FROM view_table LIMIT 1, 1) AS field5, \
                            (SELECT field1 FROM view_table LIMIT 2, 1) AS field6 \
                        FROM table2 \
                        JOIN table4 ON table2.id = table4.id \
                        WHERE table2.id = ?', event['param1'], function (error, results, fields) {
      // And done with the connection.
      connection.release();
      // Handle error after the release.
      if (error) callback(error) ;
      else callback(null, results);
    });
  });
};

The above would have you drop the view, and then perform the create view afterwards.