node-mysql2 icon indicating copy to clipboard operation
node-mysql2 copied to clipboard

Published 20 hours ago • verified publisher icon sidorares

Error Handling?

Open kingram6865 opened this issue 1 year ago • 5 comments

I have a REST API and for an INSERT handler I have a custom (45000) error in MySQL. Node throws the stack in the console but I cannot capture that info in my try catch for proper handling.

There's no documentation on Error handling for mysql2. Any thoughts/suggestions?

Error: DUPLICATE: Already have that data
    at PromisePool.query (/home/kingram/PROJECTS/omnibusapi/node_modules/mysql2/promise.js:356:22)
    at call (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:8:36)
    at tryCatch (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at Generator._invoke (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at Generator.next (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at asyncGeneratorStep (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at _next (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at /home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1
    at new Promise (<anonymous>)
    at apply (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:2:1)
    at apply (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:14:2)
    at executeSQL (/home/kingram/PROJECTS/omnibusapi/controllers/data.js:5:26)
    at call (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:26:36)
    at tryCatch (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at Generator._invoke (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at Generator.next (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at asyncGeneratorStep (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at _next (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at /home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1
    at new Promise (<anonymous>)
    at apply (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at handler (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:37:4)
    at Layer.handle [as handle_request] (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/layer.js:95:5)
    at next (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/route.js:149:13)
    at Route.dispatch (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/route.js:119:3)
    at Layer.handle [as handle_request] (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/layer.js:95:5)
    at /home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:284:15
    at Function.process_params (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:346:12)
    at next (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:280:10)
    at serveStatic (/home/kingram/PROJECTS/omnibusapi/node_modules/serve-static/index.js:75:16)
    at Layer.handle [as handle_request] (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:328:13)
    at /home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:286:9
    at Function.process_params (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:346:12)
    at next (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:280:10)
        at serveStatic (/home/kingram/PROJECTS/omnibusapi/node_modules/serve-static/index.js:75:16)
    at Layer.handle [as handle_request] (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:328:13)
    at /home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:286:9
    at Function.process_params (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:346:12)
    at next (/home/kingram/PROJECTS/omnibusapi/node_modules/express/lib/router/index.js:280:10)
    at /home/kingram/PROJECTS/omnibusapi/node_modules/body-parser/lib/read.js:137:5
    at AsyncResource.runInAsyncScope (node:async_hooks:206:9)
    at invokeCallback (/home/kingram/PROJECTS/omnibusapi/node_modules/raw-body/index.js:238:16)
    at done (/home/kingram/PROJECTS/omnibusapi/node_modules/raw-body/index.js:227:7)
    at IncomingMessage.onEnd (/home/kingram/PROJECTS/omnibusapi/node_modules/raw-body/index.js:287:7)
    at IncomingMessage.emit (node:events:518:28)
    at IncomingMessage.emit (node:domain:488:12)
    at endReadableNT (node:internal/streams/readable:1696:12)
    at processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ER_SIGNAL_EXCEPTION',
  errno: 1644,
  sql: "INSERT INTO a (data) VALUES (data)",
  sqlState: '45000',
  sqlMessage: 'DUPLICATE: Already have that data'
}

console.log(err) produces:

TypeError: Invalid attempt to destructure non-iterable instance.
In order to be iterable, non-array objects must have a [Symbol.iterator]() method.
    at _nonIterableRest (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at _slicedToArray (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at call (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:26:51)
    at tryCatch (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at Generator._invoke (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at Generator.next (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at asyncGeneratorStep (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at _next (/home/kingram/PROJECTS/omnibusapi/routes/data/addNewData.js:2:1)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)

kingram6865 avatar Aug 27 '24 22:08 kingram6865

Hi @kingram6865, could you provide some code context?

There's no documentation on Error handling for mysql2.

See the How to handle errors? FAQ section 🙋🏻‍♂️

wellwelwel avatar Aug 27 '24 22:08 wellwelwel

Hi @kingram6865, could you provide some code context?

There's no documentation on Error handling for mysql2.

See the How to handle errors? FAQ section 🙋🏻‍♂️

Yeah, the FAQ does not add any new or detailed information.

I included the output of error handling, which is the same as that offered in the FAQ. The node error handler throws the stack, but I cannot capture the output from MySQL. How do I capture that error info? It is not provided by console.log(err)

What kind of code context do you want? It's a simple try-catch block.

try {
      const [rows, fields] = await executeSQL(sql)
      res.json(rows)
    } catch(err) {
      // console.log(Object.keys(err))
      // console.log(err.name, err.message)
      // throw err;
      // res.json(err)
      // res.send(err.message)
      // for (let k in err) { console.log(`Line 34: ${k}: ${error[k]}`) }
      if (err instanceof Error) {
        console.log('execute error:', err);
      }
    }

I'm preventing duplicate entries in the table at the MySQL server:

IF (v_hashdupe = 0 && NEW.data IS NOT NULL) THEN
  SET NEW.hash = v_hash;
ELSEIF (v_hashdupe = 0 && NEW.data IS NULL) THEN
  signal sqlstate '45000' set message_text = "MISSING: No hash data";
ELSEIF (v_hashdupe > 0) THEN
  signal sqlstate '45000' set message_text = "DUPLICATE: Already have that data";
END IF;

kingram6865 avatar Aug 27 '24 22:08 kingram6865

I provided details two weeks ago. Is anyone going to respond?

kingram6865 avatar Sep 07 '24 15:09 kingram6865

I provided details two weeks ago. Is anyone going to respond?

I know I don't need to explain myself, but I really like how the open-source community works.

I usually create a priority queue based on how many users are affected by a bug/issue. For example a single PR (#2988) that fixes 8 issues and resolves a large discussion all at once, with practically zero code changes, also opened 2 weeks ago and no feedbacks and it's totally fine. It needs investigation, make sure nothing gets broken, etc (these things demand time and effort of the community and maintainers).

At a point where the JavaScript error stack is not sufficient for your case or doesn't return a specific behavior, I don't know what should be returned. That's why we keep unanswered issues open, so any user in the community who has experienced and solved it can comment 🤝

wellwelwel avatar Sep 07 '24 16:09 wellwelwel

I provided details two weeks ago. Is anyone going to respond?

I know I don't need to explain myself, but I really like how the open-source community works.

I usually create a priority queue based on how many users are affected by a bug/issue. For example a single PR (#2988) that fixes 8 issues and resolves a large discussion all at once, with practically zero code changes, also opened 2 weeks ago and no feedbacks and it's totally fine. It needs investigation, make sure nothing gets broken, etc (these things demand time and effort of the community and maintainers).

At a point where the JavaScript error stack is not sufficient for your case or doesn't return a specific behavior, I don't know what should be returned. That's why we keep unanswered issues open, so any user in the community who has experienced and solved it can comment 🤝

Ok. Would be nice to find an answer at some point. If there is one.

kingram6865 avatar Sep 08 '24 04:09 kingram6865

@kingram6865 the upper trace should show you all the information you need. You should get all this from the error element:

code: 'ER_SIGNAL_EXCEPTION', errno: 1644, sql: "INSERT INTO a (data) VALUES (data)", sqlState: '45000', sqlMessage: 'DUPLICATE: Already have that data'

Code to identify, some error number, the sql you executed, the state of that sql, and lastly the error message generated by your sql server

cd-FileX avatar Mar 29 '25 10:03 cd-FileX

@kingram6865 the upper trace should show you all the information you need. You should get all this from the error element:

code: 'ER_SIGNAL_EXCEPTION', errno: 1644, sql: "INSERT INTO a (data) VALUES (data)", sqlState: '45000', sqlMessage: 'DUPLICATE: Already have that data'

Code to identify, some error number, the sql you executed, the state of that sql, and lastly the error message generated by your sql server

Thanks for the reply. Somewhere in December I was able to find an example that made me realize I had the scope wrong. The answer to my problem was further up the chain of logic.

Basically I needed to use async chaining instead of async/await syntactic sugar to get the info on any SQL Error. I didn't see that before because I was making a lot of assumptions about the operations. Async is still a bit of a mystery for me.

This code simplified the notification on duplicate records so that I could catch it during the SQL call rather than only seeing it only in the node script error.

Maybe my solution will help someone.

import * as colors from '../utilities/consoleColorsES6.js'
import mysql from 'mysql2';
import 'dotenv/config'

const DBCONFIG = {
  host: process.env.DBHOST,
  port: process.env.DBPORT,
  user: process.env.DBUSER,
  password: process.env.DBPW,
}

export const pool = (db) => mysql.createPool({...DBCONFIG, database: db, waitForConnections: true})
export const formatSQL = (SQL, parameters) => mysql.format(SQL, parameters)

export async function executeSQL(conn, sql) {
  let results

  await conn.promise().query(sql)
    .then(response => {
      results = response[0]
    })
    .catch(err => {
      // Query Error [ 'message', 'code', 'errno', 'sql', 'sqlState', 'sqlMessage' ]
      if (err.sqlState === '45000') {
        console.log(`${colors.brightMagenta}Message${colors.Reset}: ${colors.brightRed}${err.sqlMessage}${colors.Reset}`)
        results = {insertId: 0, message: err.sqlMessage}
      } else {
        console.log(`MySQL Error: ${Object.keys(err)}\n${colors.brightCyan}code: ${err.code}${colors.Reset}\n${err.errno}\n${err.sqlState}\n${err.sqlMessage}\n${err.sql}\n${colors.brightRed}${err.message}${colors.Reset}`)
        results = {insertId: 0, message: `${err.message}`}
      }
    })

  return results;
}```

kingram6865 avatar Mar 29 '25 17:03 kingram6865