I updated my MM1 to 3.6 and data stopped populating in a table. I dug a bit farther and tried the same on an MM2…it also failed. The last MM1 working version is 3.5.0-b.46 . When I update to 3.6.0 the code function up until the join module which combines data before sending to a table. All the data is there…it just never passes out of the join.
When you paste code into the forums please be sure and use the < / > icon or wrap the code in three back ticks `
I had to delete your code in your post as it was broken.
Please edit your original post and we can take a look.
[{"id":"8b9a1993739b6995","type":"split","z":"4bfe5e028f0a64b7","name":"Split array","splt":"","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":980,"y":420,"wires":[["789a8624b3a3d18c"]]},{"id":"79dde4c9664017e0","type":"ping","z":"4bfe5e028f0a64b7","mode":"triggered","name":"Ping IP","host":"","timer":"20","inputs":1,"x":250,"y":520,"wires":[["21d76c0559716722"]]},{"id":"789a8624b3a3d18c","type":"change","z":"4bfe5e028f0a64b7","name":"","rules":[{"t":"set","p":"mac","pt":"msg","to":"payload.addrMAC","tot":"msg"},{"t":"set","p":"name","pt":"msg","to":"payload.description","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payload.addrIP","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1160,"y":420,"wires":[["79dde4c9664017e0"]]},{"id":"a1963de586b19610","type":"change","z":"4bfe5e028f0a64b7","name":"PingOutFinal","rules":[{"t":"set","p":"delay","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"ip","pt":"msg","to":"ip","tot":"msg"},{"t":"set","p":"name","pt":"msg","to":"name","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":520,"wires":[["c034ce78c423e0de"]]},{"id":"d619fce5bb5949f0","type":"change","z":"4bfe5e028f0a64b7","name":"Extract required properties","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t \"name\": msg.name,\t \"topic\": msg.topic,\t \"mac\": msg.mac,\t \"delay\": msg.delay\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":620,"wires":[["75305fb994dec31c"]]},{"id":"75305fb994dec31c","type":"function","z":"4bfe5e028f0a64b7","name":"Color Name","func":"if (msg.delay > 250) {\n msg.payload.name = '<span style=\"color: red;\">' + msg.payload.name + '</span>';\n }else{\n if (msg.delay >50) {\n msg.payload.name = '<span style=\"color: orange;\">' + msg.payload.name + '</span>';\n }\n if (msg.delay > 25) {\n msg.payload.name = '<span style=\"color: yellow;\">' + msg.payload.name + '</span>';\n }\n} \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":620,"wires":[["c3ee3717cb540a90"]]},{"id":"c3ee3717cb540a90","type":"join","z":"4bfe5e028f0a64b7","name":"Join into array","mode":"auto","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"[ ]","joinerType":"json","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":690,"y":620,"wires":[["aa58ac129176e06b","37aac33cb4c544f7"]]},{"id":"c034ce78c423e0de","type":"function","z":"4bfe5e028f0a64b7","name":"function 24","func":"msg.topic = \"<a href=http:\\\\\" + msg.topic + \" target=_blank rel=noopener noreferrer>\" + msg.topic + \"</a>\"\nif (msg.payload.delay = 99999) {\n msg.offlineIndex = msg.offlineIndex+1\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":790,"y":520,"wires":[["d619fce5bb5949f0"]]},{"id":"21d76c0559716722","type":"change","z":"4bfe5e028f0a64b7","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"false","fromt":"bool","to":"99999","tot":"num"},{"t":"set","p":"arrayLength","pt":"flow","to":"length","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":520,"wires":[["a1963de586b19610"]]},{"id":"bdae06b3a5baa2a3","type":"ui_table","z":"4bfe5e028f0a64b7","group":"bf8b56c718798e2a","name":"V2","order":2,"width":11,"height":16,"columns":[{"field":"name","title":"Name","width":"40%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"topic","title":"Topic","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"mac","title":"MAC","width":"24%","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"delay","title":"Delay","width":"16%","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":1110,"y":640,"wires":[]},{"id":"882721aa09be1e9d","type":"function","z":"4bfe5e028f0a64b7","name":"sql1","func":"msg.sql1 = \"SELECT [addrIP],[addrMAC],[description] FROM OpTechAttendance order by addrIP desc;\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":420,"wires":[["cc54ac40d6a7fff7"]]},{"id":"cc54ac40d6a7fff7","type":"MSSQL","z":"4bfe5e028f0a64b7","mssqlCN":"c785e70192feda74","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"{{{sql1}}}","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":620,"y":420,"wires":[["d91537cb49c6b001"]]},{"id":"993bb529d379d282","type":"inject","z":"4bfe5e028f0a64b7","name":"Start","props":[],"repeat":"600","crontab":"","once":true,"onceDelay":"2","topic":"","x":90,"y":420,"wires":[["303b70b3213530f8"]]},{"id":"d91537cb49c6b001","type":"function","z":"4bfe5e028f0a64b7","name":"function 25","func":"// Assuming msg.payload contains the result of the SQL query\n// msg.payload is an array of objects where each object represents a row\n\n// Convert the output to the desired array format\nlet outputArray = msg.payload.map(row => ({\n addrIP: row.addrIP,\n addrMAC: row.addrMAC,\n description: row.description\n}));\n\n// Set the new array to msg.payload\nmsg.payload = outputArray;\n\nmsg.length = outputArray.length\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":810,"y":420,"wires":[["8b9a1993739b6995","55afd039055b89cc"]]},{"id":"303b70b3213530f8","type":"ui_button","z":"4bfe5e028f0a64b7","name":"","group":"5eaca1a3.15e28","order":4,"width":10,"height":2,"passthru":true,"label":"Table Refresh","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"date","topic":"topic","topicType":"msg","x":260,"y":420,"wires":[["882721aa09be1e9d"]]},{"id":"55afd039055b89cc","type":"debug","z":"4bfe5e028f0a64b7","name":"debug 21","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":980,"y":380,"wires":[]},{"id":"b16b51d54b5f2087","type":"ui_text","z":"4bfe5e028f0a64b7","group":"bf8b56c718798e2a","order":1,"width":"12","height":"1","name":"","label":"<b>Devices Scanned/Offline: </b> ","format":"{{msg.length}} / {{msg.count}}","layout":"row-left","className":"","x":1200,"y":600,"wires":[]},{"id":"aa58ac129176e06b","type":"function","z":"4bfe5e028f0a64b7","name":"Count Offline Nodes","func":"// Assume the array of objects is in msg.payload\nlet array = msg.payload;\n\n// Initialize a counter\nlet count = 0;\n\n// Loop through the array and count the entries where delay equals 99999\nfor (let i = 0; i < array.length; i++) {\n if (array[i].delay === 99999) {\n count++;\n }\n}\n\n// Set the count in msg.count\nmsg.count = count;\n\n// Return the message object\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":900,"y":620,"wires":[["bdae06b3a5baa2a3","b16b51d54b5f2087"]]},{"id":"37aac33cb4c544f7","type":"debug","z":"4bfe5e028f0a64b7","name":"debug 22","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":860,"y":660,"wires":[]},{"id":"bf8b56c718798e2a","type":"ui_group","name":"Device Statistics","tab":"2806402b.f11e7","order":1,"disp":false,"width":"12","collapse":false,"className":""},{"id":"c785e70192feda74","type":"MSSQL-CN","tdsVersion":"7_4","name":"OTA","server":"database.windows.net","port":"1433","encyption":true,"trustServerCertificate":true,"database":"ProductionEnvironmentData - DEV","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true},{"id":"5eaca1a3.15e28","type":"ui_group","name":"Availability / Time","tab":"2806402b.f11e7","order":1,"disp":false,"width":"12","collapse":false,"className":""},{"id":"2806402b.f11e7","type":"ui_tab","name":"Networking","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
I don’t have an MSSQL system, so cant test your flow.
I don’t see anything in the new version of Node-RED that might change anything.
I see that your join node set for ‘automatic’, I’ve never had a lot of luck that way, try setting it to the type you need.
Also put a debug on the output of the node ahead of the join. One of the BIG Node-RED debug tips that @torchard has taught me is to always always always look at whats going INTO the node that is causing the error, NOT just the output.
The debug was there…I just moved it. data feeding the join was good. I can literally just change the firmware and it crashed.
I will try and bypass your MSSQL and see if I can debug your flow for you.
The nodeJS version did not change and the dashboard also did not change.
In other words, there is something going on with the backup restore process you are doing with the flow and something is changing.
If what you have dropped here is the current flow, I can try and work with it … give me some time…
Go for it. I was planning to pull a result array out of a plc running 3.5 and push that into the 3.6.0 plc to see if the table accepts it.
What error are you getting in your debug tab?
I’m running EPIC firmware 3.6.0-b.32 on my EPIC and put some dummy data into your flow and the join is working as expected on my system. Here’s my output table:
Here’s the dummy data I’m injecting:
msg.payload = [{
"addrIP": "192.168.1.75",
"addrMAC": "aabbzzss",
"description": "wifi"
},{
"addrIP": "192.168.1.182",
"addrMAC": "aaffeeccdd",
"description": "llama"
},{
"addrIP": "192.168.1.1",
"addrMAC": "qqwwqqww",
"description": "router"
},{
"addrIP": "192.168.1.250",
"addrMAC": "ggffrr",
"description": "opto22EPIC"
}];
msg.length = msg.payload.length
return msg;
Are you getting an array that looks like that from “debug 21” on the output of your “function 25”?
I will have to dig in a bit. I have run the code on an MM1 and MM2 and both failed. Are you set to Auto or manual on the join after the oing test?
Thanks
We set it up to be as exact as possible to your flow.
So the only change that was made was to inject that manual array after the MSSQL node.
Nothing else was changed.
Auto, just as you have it in your posted flow.
I suspect that something in your network settings is changing in the firmware update and it’s impacting your connection to the MSSQL?
If you do as @torchard suggested and move your debug upstream, you should find why it’s failing.
tl;dr Terry showed me that the Split Array Node (After your function 25 node) is the key to allowing the Join node to work in auto mode later in the flow.
In other words, I did not understand that bad array data into the Split Array equals bad Join data (ie none).
I dislike anomalies when not looking for a challenge. Could not get it to work on 3.6. So I reverted to 3.5 and all was good. Waited a day then upgraded again…magic. Not sure how the smoke got out or in but it is working on 3.6.0 with no code change.
Taking this as a win even though I don’t know how or why.