In Change Node, setting output of mySQL node to Flow.Context gives "[object Object]"

General
1

I am trying to store the output of a node-red-node-mysql mysql node into a flow Context variable to aid verification and debuging (I keep missing output in debug pane as they only occur every 10 minutes or so).

But all it writes to the flow context is a string "[object Object]".
FlowCOntext result from SQL node

The Change node contains:

The common change Node
The common change Node821×848 27.2 KB

(and I have also removed the msg.topic bit but same result.

I feed the same change node from a inject node where I have set payload, topic and others with the same objects I manage to capture from the mySQL node output, the flow contect variable is set correctly
FlowCOntext result from Inject

So why sending the same data, but from a different node type give different results.

Inject node (yes I even duplicated some irrelevant fields)

inject node
inject node727×842 40 KB

Output of change Node for input from mySQL node

With response from SQL node
With response from SQL node1528×670 78.9 KB

and same as an object

{
  "payload": {
    "fieldCount": 0,
    "affectedRows": 0,
    "insertId": 0,
    "info": "(Rows matched: 0  Changed: 0  Warnings: 0",
    "serverStatus": 2082,
    "warningStatus": 0,
    "changedRows": 0,
    "topic": "UPDATE Parameters  SET Parameter = 'House/hwTank/top/Temp' WHERE Parameter LIKE  'House_Tank';"
  },
  "parts": {
    "id": "aac8ef27eebcad89",
    "type": "array",
    "count": 41,
    "len": 1,
    "index": 13
  },
  "_msgid": "4a9ae7cba3a26ee6",
  "topic": "UPDATE Parameters  SET Parameter = 'House/hwTank/top/Temp' WHERE Parameter LIKE  'House_Tank';",
  "ttl": 0,
  "_queuetimestamp": 1714320000216,
  "_queueCount": 27
}

Output of change Node for input from inject node

With response from inject Node node
With response from inject Node node1498×633 78 KB

Finally a snippet of the flow

[
    {
        "id": "138151cbcdb18b2d",
        "type": "debug",
        "z": "0b6335e33844cb2a",
        "name": "debug 81",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1560,
        "y": 240,
        "wires": []
    },
    {
        "id": "ee2a903078fd0ffa",
        "type": "change",
        "z": "0b6335e33844cb2a",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "LastDBrequest",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1200,
        "y": 180,
        "wires": [
            [
                "839d56c8c874dfcd",
                "138151cbcdb18b2d"
            ]
        ]
    },
    {
        "id": "3f92fb986016d19a",
        "type": "inject",
        "z": "0b6335e33844cb2a",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            },
            {
                "p": "parts",
                "v": "{\"id\":\"aac8ef27eebcad89\",\"type\":\"array\",\"count\":41,\"len\":1,\"index\":13}",
                "vt": "json"
            },
            {
                "p": "ttl",
                "v": "0",
                "vt": "num"
            },
            {
                "p": "_queuetimestamp",
                "v": "1714320000216",
                "vt": "num"
            },
            {
                "p": "_queueCount",
                "v": "27",
                "vt": "num"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "UPDATE Parameters  SET Parameter = 'House/PresPump/Current' WHERE Parameter LIKE  'HousePresPumpCurrent';",
        "payload": "{\"fieldCount\":0,\"affectedRows\":2104477,\"insertId\":0,\"info\":\"4Rows matched: 2104477  Changed: 2104477  Warnings: 0\",\"serverStatus\":2082,\"warningStatus\":0,\"changedRows\":2104477}",
        "payloadType": "json",
        "x": 990,
        "y": 120,
        "wires": [
            [
                "ee2a903078fd0ffa"
            ]
        ]
    },
    {
        "id": "44c09ce4be5151d8",
        "type": "mysql",
        "z": "0b6335e33844cb2a",
        "mydb": "aa025dd27a4e9d87",
        "name": "",
        "x": 960,
        "y": 280,
        "wires": [
            [
                "ca1860475146d8f9",
                "80e44054eedb2ac5"
            ]
        ]
    },
    {
        "id": "ca1860475146d8f9",
        "type": "junction",
        "z": "0b6335e33844cb2a",
        "x": 1100,
        "y": 280,
        "wires": [
            [
                "307afb51c6cc7dca",
                "22397dbe0f9be484",
                "ee2a903078fd0ffa"
            ]
        ]
    },
    {
        "id": "307afb51c6cc7dca",
        "type": "debug",
        "z": "0b6335e33844cb2a",
        "name": "debug 80",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1300,
        "y": 440,
        "wires": []
    },
    {
        "id": "aa025dd27a4e9d87",
        "type": "MySQLdatabase",
        "name": "WombatHollow@ NAS",
        "host": "10.0.0.118",
        "port": "3306",
        "db": "WombatControl",
        "tz": "",
        "charset": "UTF8"
    }
]

Any idea as to why this anomaly

System Config data
NR 3.1.0 on a
Raspi 3B running Bullseye
npm v9.8.1
node v18.18.2

2

I have seen this behaviour before (with another node) - and I believe it has to do with the way it returns the object (not sure what the exact issue was), could you try to enable 'deep copy value' ?

edit found my own topic

3

You could use node-red-contrib-flogger so that you can go back and look at what happened, or just set the debug node to also output to the console, then you can look back through the node-red log to see what happened.

4

Thanks Colin, but I would still like to know why this behaviour is seen

5

I don't see how those comments relate to my post.

6

Thanks for quick response, I have now tried 'deep Copy' and result is unchanged.

I also resorted to a function node with this short code

var DBResponse = {}
DBResponse = msg.payload
DBResponse.topic = msg.topic
flow.set("LastDBRequest1", DBResponse);
msg.payload = DBResponse
return msg;

The output msg is right, but the Flow Context Variable is same as for change node.

7

hit wrong reply button. corrected now

8

Could you try:

DBResponse = JSON.parse(JSON.stringify(msg.payload))
9

Thanks BakMan, that appears to work in the function node. So for the mySQL node developers, they have a bug that impacts the output of the mySQL node very subtly they might want to look at.

10

There is an open issue against it.

This might also work:

DBResponse = {...msg.payload}
11

thanks, good to hear it is a known issue

12

ISTR that the MySQL node does not return a normal object so the debug node doesn’t handle it correctly. We don’t want to change the MySQL output as other things rely on that object type. Likewise we don’t really want to have to encumber the debug node with lots of special types as they occur. Re-parsing it to a proper object is a good work around.