Bulk insert with mysql2 and NodeJs throws 500

Question

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.

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 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
– O. Jones, Commented May 24, 2021 at 13:12
found helpful resources here --> github.com/sidorares/node-mysql2/issues/830 — Gaurav
– Gaurav, Commented Jun 25, 2021 at 19:20

Gaurav · Accepted Answer · 2021-06-25 19:28:18Z

8

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

answered Jun 25, 2021 at 19:28

Gaurav

2,0802 gold badges18 silver badges31 bronze badges

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

1 Comment

poPaTheGuru Over a year ago

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.

Collectives™ on Stack Overflow

Bulk insert with mysql2 and NodeJs throws 500

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related