MySQL (Mariadb) INSERT returns msg.payload = "[object Object]"

[jbudd]

Using node-red-node-mysql v1.0.3, a simple INSERT - msg.topic = "insert into test (temperature) values (24.3)"
The table has an auto-increment primary key - id.
I need to know the id of the inserted record.

If I feed the MySQL output into a debug node it just shows ResultSetHeader "[object Object]"

According to https://www.npmjs.com/package/mysql#getting-the-id-of-an-inserted-row the id should be available as results.insertId

What am I doing wrong, or is there a bug in the MySQL node?

[yOyOeK1]

I'm adding stuff then asking for last id.
Adding I'm doing it by

entryDate = Math.floor(Date.now() / 1000);
tr = {};
tr['topic'] = 'insert into nrNotifications \
    (status, sentence,entryDate) VALUES \
    (:status,:sentence,:entryDate)';
    
tr['payload'] = {
    'status':   'info',
    'sentence': msg.payload,
    'entryDate':    entryDate
};

return tr;

this is going to mysql node. then to ask for last id you will make it?

tr = {
    topic: 'select id from nrNotifications where id desc limit 1;'
};

return tr;

[jbudd]

Thanks @yOyOeK1 Yes I can get the most recent record inserted with a seperate query, I currently use select max(id) from test

But this is not ideal. Apart from needing an extra query, let's say my INSERT failed for some reason, select max(id) from text will give me the previous record and subsequent processing will be based on the wrong data. So I need to catch errors too.

I think the mysql node could/ought to return some useful info after an insert.

[yOyOeK1]

@jbudd you know that getting a id after insert is not a normal behaviors of any mysql driver. In this case it's the same. But I checked and mysql node is returning after insert a payload['insertId'] you don't have it ?

Check your debug node is it set to output "complete msg object"?

[jbudd]

Well that's bizarre!

If you set debug to show msg.payload, payload contains nothing useful.
If you set it to show the complete message, payload contains useful stuff including insertID.

Thank you for the suggestions.

[dceejay]

I think the reason is that the underlying MySQL library returns a "Result Object" type rather than a plain object so the debug node doesn't know how to handle it exactly. eg see Node.js MySQL Result Object - Examples.
So the data is all there - just the debug doesn't show it directly.