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,

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

Can anyone guide me how to do this properly?

Chris Williams · Accepted Answer · 2020-06-27 08:52:16Z

2

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.

edited Jun 27, 2020 at 8:52

answered Jun 27, 2020 at 7:38

Chris Williams

35.7k4 gold badges46 silver badges79 bronze badges

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

23 Comments

Chris Williams Over a year ago

Ooh derp, accidently had two sets of connection release, updated answer :)

Chris Williams Over a year ago

Ah right I see its 3 queries, not 2. This might cause syntax issues too.

RickyBelmont Over a year ago

Done! It was supposed to be 3 queries. I separated the create view query and now it works!

Chris Williams Over a year ago

YEs I just updated in the answer too :). Glad I could help :)

RickyBelmont Over a year ago

Just right it time! hahaha... we got it! Thank you so much! I really appreciate your help! See you in some of my queries! I'll mark this as answered already. Thank you!

|

Collectives™ on Stack Overflow

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

1 Answer 1

23 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

23 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related