Input multiple sensor outputs into mysql

General
1

First I must say what a great platform Node Red is and congratulations to the builders.

As a retired old man and with only 12 months programming experience I have just ventured into MQTT, MYSQL and now Node Red, I have successfully got several sensors in my home coupled to a local server running on an Rpi4b and saving the data on an mySql database.

The data from the sensors come in at different intervals, I'm currently using a Join node to combine the data as an array and then use this array to put the corresponding data into the correct db columns. This works okay, BUT sometimes (I'm not sure why) the value position in the array changes so the data is not inserted correctly.

I think I should be using the message topics somehow but having read endless posts and instructions I just cannot work out how to do it.

Any advice would be greatly appreciated.

[{"id":"b81988ca.7f1d68","type":"join","z":"b06d38d5.2a2e48","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"5","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":350,"y":560,"wires":[["f6b37364.99938","d340b818.f387c8"]]},{"id":"f6b37364.99938","type":"debug","z":"b06d38d5.2a2e48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":530,"y":520,"wires":[]},{"id":"49355e6a.850b","type":"mqtt in","z":"b06d38d5.2a2e48","name":"","topic":"IAQ/0999/temperature","qos":"1","datatype":"auto","broker":"8db3fac0.99dd48","x":160,"y":480,"wires":[["b81988ca.7f1d68"]]},{"id":"76c46ba1.e2c7d4","type":"mqtt in","z":"b06d38d5.2a2e48","name":"","topic":"IAQ/0999/humidity","qos":"1","datatype":"auto","broker":"8db3fac0.99dd48","x":150,"y":520,"wires":[["b81988ca.7f1d68"]]},{"id":"2ecf8a23.4e7086","type":"mqtt in","z":"b06d38d5.2a2e48","name":"","topic":"IAQ/0999/pm25","qos":"1","datatype":"auto","broker":"8db3fac0.99dd48","x":140,"y":560,"wires":[["b81988ca.7f1d68"]]},{"id":"2b367ee4.126c92","type":"mqtt in","z":"b06d38d5.2a2e48","name":"","topic":"IAQ/0999/count","qos":"1","datatype":"auto","broker":"8db3fac0.99dd48","x":140,"y":640,"wires":[["b81988ca.7f1d68"]]},{"id":"b2f7d3d4.57db5","type":"mqtt in","z":"b06d38d5.2a2e48","name":"0999 CO2","topic":"IAQ/0999/co2","qos":"1","datatype":"auto","broker":"8db3fac0.99dd48","x":120,"y":600,"wires":[["b81988ca.7f1d68"]]},{"id":"20042d51.e67242","type":"mysql","z":"b06d38d5.2a2e48","mydb":"3d3884d0.e0a06c","name":"","x":700,"y":580,"wires":[[]]},{"id":"d340b818.f387c8","type":"function","z":"b06d38d5.2a2e48","name":"INSERT Data","func":"temp = msg.payload[0];\nhum = msg.payload[1];\npm25 = msg.payload[2];\nco2 = msg.payload[4];\nif(msg.payload[3] === \"6\") {\nmsg.topic=\"INSERT INTO AP20999(value1, value2, value3,value4) VALUES(\"+pm25+\",\"+temp+\",\"+hum+\",\"+co2+\")\";\n\nreturn msg;\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":520,"y":580,"wires":[["20042d51.e67242"]]},{"id":"8db3fac0.99dd48","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"3d3884d0.e0a06c","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"IAQ","tz":"","charset":"UTF8"}]

Richard

2

Hi and welcome to the forum.

It seems you have found a new hobby - that if unchecked can turn into an obsession :wink:

Node-red is as you say, a great platform.

This is because values do not arrive at the same time every time.

the solution is to use key/value and the topic to generate the joined object.

3

This version does not have the out of order problem...

image
image971Ă—312 32.3 KB

[{"id":"a78eaae2.ed1fe8","type":"inject","z":"c9ca6ac7.25b568","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"IAQ/0999/temperature","payload":"26.5","payloadType":"num","x":650,"y":60,"wires":[["fe93ea10.7aec78"]]},{"id":"fe93ea10.7aec78","type":"join","z":"c9ca6ac7.25b568","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"5","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":870,"y":140,"wires":[["186dd683.c907a9","7510f528.68649c"]]},{"id":"186dd683.c907a9","type":"debug","z":"c9ca6ac7.25b568","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1030,"y":140,"wires":[]},{"id":"7510f528.68649c","type":"function","z":"c9ca6ac7.25b568","name":"INSERT Data","func":"temp = msg.payload[\"IAQ/0999/temperature\"];\nhum = msg.payload[\"IAQ/0999/humidity\"];\npm25 = msg.payload[\"IAQ/0999/pm25\"];\nco2 = msg.payload[\"IAQ/0999/co2\"];\nif (msg.payload[\"IAQ/0999/count\"] == \"6\") {\n    msg.topic = `INSERT INTO AP20999(value1, value2, value3,value4) VALUES(${pm25},${temp},${hum},${co2})`;\n    return msg;\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1040,"y":200,"wires":[["f1c8a200.0841a"]]},{"id":"649d8a14.5ca094","type":"inject","z":"c9ca6ac7.25b568","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"IAQ/0999/count","payload":"6","payloadType":"num","x":620,"y":220,"wires":[["fe93ea10.7aec78"]]},{"id":"d9cfd990.ab8d18","type":"inject","z":"c9ca6ac7.25b568","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"IAQ/0999/co2","payload":"0.0123","payloadType":"num","x":640,"y":180,"wires":[["fe93ea10.7aec78"]]},{"id":"9b7a804c.b5a02","type":"inject","z":"c9ca6ac7.25b568","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"IAQ/0999/pm25","payload":"12345","payloadType":"num","x":640,"y":140,"wires":[["fe93ea10.7aec78"]]},{"id":"954f23b0.5f66f","type":"inject","z":"c9ca6ac7.25b568","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"IAQ/0999/humidity","payload":"100.56","payloadType":"num","x":650,"y":100,"wires":[["fe93ea10.7aec78"]]},{"id":"f1c8a200.0841a","type":"debug","z":"c9ca6ac7.25b568","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":1040,"y":240,"wires":[]}]
4

Hi Steve, it certainly is an obsession now. I've gone from simple Arduino Uno's to using both cores on ESP32's, LAMP server on a Rpi and now into MQTT all in 12 months.

I've tried setting the Join to use key/value but then I cannot fathom out the function to split the message into single values again to insert. I'm struggling with the payload/topic concept and must read more!

Richard

5

This will help you accessing values when using the topic...
https://nodered.org/docs/user-guide/messages

The key to it using the node-red features like the debug panel and "copy path" button.

LaVNedXJsm

6

Ah, okay many thanks for that you have made my day!!

It was how to define the topic in correct syntax that was baffling me,
temp = msg.payload["IAQ/0999/temperature"];
the topic must be in [ " " ] format.

Richard

7

trips up many folk who are new to JS programming - thats why the "copy path" feature is an excellent helper.

Its because the topic has invalid characters (must be a valid JavaScript identifier) you therefore have to use bracket notation instead of dot notation.

What I always recommend to node-red newbies is watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

8

You might also want to look at using a timeseries database engine rather than SQL for your data.

InfluxDB is a free, stable, performant timeseries db engine that is very popular with Node-RED enthusiasts. It will happily manage very large amounts of sensor data even on a Pi. You can also us Grafana to produce dashboards direct from InfluxDB data.

9

What is cure? Is there one? The only relief seems to be do more... not less? Even my dachshunds head straight for their bed under my desk, the second they see me open the NR editor. NR tinkering is cannibalizing my XBOX gaming time now as well. I got it bad, real bad.

On a serious note, I would always suggest you consolidate your DB transactions as much as possible or reasonable... basic DB health and welfare qualification, right? Former DBA here, maybe stating the obvious.

10

Work! Lots of it! Try getting yourself a job that requires 16 hour days during COVID, I can tell you that is a fairly effective - but only temporary - cure. :skull_and_crossbones:

11

Only 16 hours a day? Slacker.

12

Compared to many of my colleagues I did indeed feel like a slacker. But then I'm 60 and they are a lot younger :slight_smile: It was exhausting enough. I'm head of architecture so I wouldn't expect to need to put in the same number of hours as someone doing ops. But we all put in a lot of time to help the NHS adapt to the pandemic. Starting with moving 10,000 people from offices to home working in a couple of weeks.

Of course, I lied anyway, Node-RED was a great distraction when I needed to switch my mind from work. There is no cure :rofl:

1 Like
13

Over my 30+ years working as IT for fortune 50 firms, and more as a consultant, I don't think I ever had a less than 60 hour week. But that is not a gripe, I enjoyed the work, and almost all the time, worked with great people, true friends, so it was not the time in that was a big deal, but foolishness of others at times that was notable. I have seen some of the dumbest decisions for the worst reasons. Fortunately, for me, I was rarely impacted by same. If not for the advent of virtualization, might have left the industry about 15 years ago. But virtualization, and my deep involvement with it, working with the various expert teams working on same, including VMware, Hyper-V, KVM, etc. as it changed the industry of IT forever. Now retired, the most difficult decision I have... is do I spend time with NR, XBOX, or family. LOL.

1 Like
14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.