CODEWITHSUNDEEP
mysqlnode.js
jsh6303
jsh6303

Reputation: 2040

Error from ending connection from 'query inside query'

I am using mysql node module and having an error of Error: Cannot enqueue Query after invoking quit. from my source code which has a block that does 'update if exists, otherwise insert' (similar logic as in another question I asked).

The reason I include it in node is that the definition of 'duplicates' is customized.

I have tested my source code and it was able to successfully perform 'update if exists, otherwise insert' to my table, but I am not able to close my connection, as for my insert/update query reside in the search query.

const insertQueue = (results) => {
  _.forEach(results, (values, key) => {
    _.map(values, (value) => {
      let query = 'SELECT index FROM table WHERE (document=? AND ((document NOT like \'PERSONAL:%\' AND username=?) OR (document like \'PERSONAL:%\' AND serial=?)))';
      connection.query(query, [value.document, key, value.serial], function(error, rows){
        if (error) {
          console.log(error);
        }
        else {
          //Ideally there should be only one entry, but still return an array to make the function error-safe
          let indices = _.map(rows,'index');
          if (_.isEmpty(indices)) {
            //sqlInsert and sqlUpdate are defined elsewhere and has sql query defined with 'connection.query(..)' similar to this
            sqlInsert(key, value.serial, value.document, value.lastRun);
          }
          else {
            _.each(indices, (index) => {
              sqlUpdate(index,value.lastRun);
            });
          }
        }
      });
    });
  });
  connection.end();
}

sqlInsert and sqlUpdate are defined elsewhere and has sql query defined with connection.query(..) similar to the function above.

I understand that connection.query(..) is asynchronous, and I put my connection.end() in the very end of the function. But I don't understand why I am still getting the error of Cannot enqueue Query after invoking quit every time I call my insertQueue.

Also if I replace my sqlInsert(...); and sqlUpdate(...) with some testing command (with no db sql query execution), the error will be gone.

Is there any reasons for this?

Upvotes: 1

Views: 34

Answers (1)

shivshankar
shivshankar

Reputation: 2135

first thing you should not call async function inside loop (map, forEach, for, while or anyone). second it's worst idea to fire query in loop.

mysql.js provide method for bulk insert from official doc https://github.com/mysqljs/mysql

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

take an example

connection.query('INSERT INTO posts SET ?', [['a', 'b'], ['c', 'd']], function (error, results, fields) {
  if (error) throw error;
  // ...
});

third i think lastRun is datetime. you can choose mysql type current timestamp and now no need to worry this in query,

Upvotes: 1

Related Questions