Handling errors in mysql

General
1

I have a MySQL database with a column email that is unique. I have an endpoint to insert data into the database. When I try to create a user with a duplicate email, I don't know how to get the ER_DUP_ENTRY error code that MySQL returns. I have a catch where I capture the error, but when I display the error with the debug node, it shows the entire message, and I want to extract only the ER_DUP_ENTRY error code.

2

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path/value for any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

1 Like
3

The problem is that the object msg.error does not include an error code.

4

image
image284Ă—521 8.67 KB

So I can't access the error code ER_DUP_ENTRY
If I were using Node, I could access the error code:

connection.on('error', (err) => {
  console.error('Database error:', err.message);

  // Handle connection lost error
  if (err.code === 'ER_DUP_ENTRY') {
    console.error('duplicated email.');    
  } else {
    throw err;
  }
});
5

You could always send the output of the catch node to a switch node and
have the 'Property' set to 'msg.error.message'
select the contains option and enter "Error: Duplicate entry
and have another option using otherwise to send the valid data onward.

6

The actual error code is 1062.
Node-red helpfully (?) translates this for you to ER_DUP_ENTRY using a lookup table at ~/.node-red/.node_modules/mysql2/lib/constants/errors.js:exports.
Of course it fails to include either the original code or the shortened string in the message properties (which you might consider a bug worth reporting).

You can obtain the error code from the database with this flow. It only shows the error from the most recent query executed so it relies on nothing else happening in the db between the two queries.

image
image1373Ă—287 29.1 KB

[{"id":"cca57a9f7b5c6572","type":"group","z":"4b8cd70fedd40617","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["529b71b83bd3e665","ad2c96e4ac6b8c3e","fa714713423592b3","17ec365e3d6b338a","f4b371be87419697","c31973f08b83ba7c"],"x":234,"y":79,"w":652,"h":142},{"id":"529b71b83bd3e665","type":"inject","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","name":"Make an error","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"insert into users(id, username) values (1, \"X Billups\")","x":350,"y":120,"wires":[["ad2c96e4ac6b8c3e"]]},{"id":"ad2c96e4ac6b8c3e","type":"mysql","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","mydb":"2381cb15.b563bc","name":"","x":520,"y":120,"wires":[[]]},{"id":"fa714713423592b3","type":"catch","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","name":"","scope":["ad2c96e4ac6b8c3e"],"uncaught":false,"x":310,"y":180,"wires":[["17ec365e3d6b338a"]]},{"id":"17ec365e3d6b338a","type":"change","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","name":"topic = show warnings","rules":[{"t":"set","p":"topic","pt":"msg","to":"show warnings","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":180,"wires":[["f4b371be87419697"]]},{"id":"f4b371be87419697","type":"mysql","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","mydb":"2381cb15.b563bc","name":"","x":660,"y":180,"wires":[["c31973f08b83ba7c"]]},{"id":"c31973f08b83ba7c","type":"debug","z":"4b8cd70fedd40617","g":"cca57a9f7b5c6572","name":"Show","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":180,"wires":[]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"espresso","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"}]
1 Like
7

Thanks everyone for contributing! I've created an issue describing the problem in the Node-red repository Error object doesn't include code attribute · Issue #5080 · node-red/node-red · GitHub

8

Ah. I see that issue was raised an hour ago and is now fixed.

I don't know how to test it but my feeling is it won't help with your duplicate key error since apparently the error object created by the mysql node does not include error.code.