5

I have a method which I want to bulk insert into mysql. I am using NodeJS and mysql2.

My method:

createWorklog = async ({ sqlArray }) => {
        const sql = `INSERT INTO ${this.tableName}
        (project_id, user_id, date, duration, task, description) VALUES ?`

        const result = await query(sql, [sqlArray])
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
}

Where sqlArray is an array of arrays where all the children arrays are the same length.

And the query method that is called in this method is the next one:

query = async (sql, values) => {
    return new Promise((resolve, reject) => {
      const callback = (error, result) => {
        if (error) {
          reject(error);
          return;
        }
        resolve(result);
      }
      // execute will internally call prepare and query
      this.db.execute(sql, values, callback);
    }).catch(err => {
      const mysqlErrorList = Object.keys(HttpStatusCodes);
      // convert mysql errors which in the mysqlErrorList list to http status code
      err.status = mysqlErrorList.includes(err.code) ? HttpStatusCodes[err.code] : err.status;

      throw err;
    });
  }
}

My problem is that the body parameters are ok (as I said, array of arrays) but the method throws 500.

Can this be possible because of execute command that is present in mysql2? Or is another mistake?

Thank you for your time!

EDIT

I changed my method from using 'execute' to 'query' Based on @Gaurav’s answer and it's working well.

poPaTheGuru
  • 1,009
  • 1
  • 13
  • 35
  • 500 is an http error. It's probably generated when you do `throw err` in your nodejs code. I suggest you use `console.err(err)` to display the mysql error. Also, [npm mysql2](https://www.npmjs.com/package/mysql2) handles `await db.execute()` natively; you don't need to promisify it yourself. You can simplify your code a lot. That will help with troubleshooting. – O. Jones May 24 '21 at 13:12
  • 1
    found helpful resources here --> https://github.com/sidorares/node-mysql2/issues/830 – Gaurav Jun 25 '21 at 19:20

1 Answers1

6

This is a known issue with execute and query method in mysql2

I've found a working alternative.

createWorklog = async ({ sqlArray }) => {
        const sql = `INSERT INTO ${this.tableName}
        (project_id, user_id, date, duration, task, description) VALUES ?`

        const result = await query(sql, [sqlArray], true) // adding true for multiple insert
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
}

Then query can be written as below

    return new Promise((resolve, reject) => {
      const callback = ...

      if (multiple) this.db.query(sql, values, callback);
            else this.db.execute(sql, values, callback);

    }).catch(err => {
      ...
...
    });
  }
}

More info regarding this issue can be found here https://github.com/sidorares/node-mysql2/issues/830

Gaurav
  • 1,668
  • 1
  • 13
  • 30
  • 2
    I like your idea, i changed my code from using 'execute' to 'query' and is working well, but your 'if else' is better, but i still need to research to find the performance differences between 'query' and 'execute' and maybe other details. Thank you for your time. – poPaTheGuru Jun 28 '21 at 05:14