Pools, .execute() method and error handling

[fewbulets]

Hi Sidorares,
Great job indeed, thanks!

However... I'm running into an issue trying to handle possible errors that may arise while using the .execute() method.

From the 'prepare.js' example, I can read as follows:
// execute will internally call prepare and query connection.execute( 'SELECT * FROM tableWHEREname= ? ANDage > ?', ['Rick C-137', 53], (err, results, fields) => { console.log(results); // results contains rows returned by server console.log(fields); // fields contains extra meta data about results, if available
So, what about the err argument ?

I've set up a pool (named pool) exported as a promise.

In my code, using:
const myFunction = async () => { const query = SELECT * FROM table1; const [result, _] = await pool.execute(query) ...
works fine, but I may not catch errors even in a try / catch block.

On the other hand, using:
const dbGetAllServices = async () => { const query = SELECT * FROM table1; await pool.execute(query, undefined, (err, res) => { console.log('RES: ', res) console.log('ERR: ', err) ...
will fail, .

I was assuming this to actually log something ('promise pending' or whatsoever) but I can't get any result nor error with this syntax. What's wrong with it ? isn't it working because of the 'pool' connection ?
And btw, what king of errors is the 'err' argument suppose to provide us with ? connection-wide, database-wide, table errors only...?

Looking forward :-)

[wellwelwel]

Hey, @fewbulets.

---

Using callbacks, we can handle errors like:

import mysql from 'mysql2';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
});

pool.execute('SELEC 1 + 1', (err, rows) => {
  if (err instanceof Error) {
    console.log(err.message); // 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 'SELEC 1 + 1' at line 1
    return;
  }

  console.log(rows);
});

• If there are no errors, the err will be null.

The same idea for prepared statements.

---

Using promises, we can handle errors like:

import mysql from 'mysql2/promise';

try {
   const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      database: 'test',
   });

   const [rows] = await pool.execute('SELEC 1 + 1');
   console.log(rows);
} catch (err) {
  err instanceof Error && console.log(err.message); // 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 'SELEC 1 + 1' at line 1
}

The same idea for prepared statements.

---

To catch connection errors, I recommend this discussion:

• I want to catch createConnection() Error #1998

---

And btw, what king of errors is the 'err' argument suppose to provide us with ? connection-wide, database-wide, table errors only...?

I'll bring it here from mysqljs error handling section:

Most errors created by this module are instances of the JavaScript Error
object. Additionally they typically come with two extra properties:

• err.code: String, contains the MySQL server error symbol if the error is
  a MySQL server error (e.g. 'ER_ACCESS_DENIED_ERROR'), a Node.js error
  code if it is a Node.js error (e.g. 'ECONNREFUSED'), or an internal error
  code (e.g. 'PROTOCOL_CONNECTION_LOST').
• err.errno: Number, contains the MySQL server error number. Only populated
  from MySQL server error.
• err.fatal: Boolean, indicating if this error is terminal to the connection
  object. If the error is not from a MySQL protocol operation, this property
  will not be defined.
• err.sql: String, contains the full SQL of the failed query. This can be
  useful when using a higher level interface like an ORM that is generating
  the queries.
• err.sqlState: String, contains the five-character SQLSTATE value. Only populated from MySQL server error.
• err.sqlMessage: String, contains the message string that provides a
  textual description of the error. Only populated from MySQL server error.