PLC machine data sent Via MQTT to Node Red Then to SQL Database

General
1

Hello. I am new to the world of Node-Red and could use some help setting it up for our facility. From what I have read so far it seems like it should work for this application.

I have approx 40 machines I need to collect various points of data from through out the day. Some Data points include:

Data Points
Data Points1034×698 83.4 KB

Data points are listed under name.

We are using the Productivity Series PLCs from Automation Direct, which has a MQTT function to publish and subscribe to a broker. I have Node-Red running on our data collection server and installed the MySQL Node and the Aedes MQTT Broker.

Node Test
Node Test1918×447 100 KB

I uploaded a test I set up to make sure I was connecting to the server and inputting blank data into the SQL database, which did work.

I am looking for some help with setting up the MQTT to the broker and the MQTT in node to the SQL node so it populates the data base.
***Would all the machines communicate to one SQL node or each MQTT node to its own SQL node?
***Do all the flows tabs run simultaneously or just the flow thats pulled up is the one that s running and have to have all machines querying on one flow sheet? Would like to keep things organized for future additions or debugging.

Also will need to be able to pull data from an SQL database and export via MQTT to the machine controllers, but I can look at that after I get the data collection set up unless it makes more sense to do it at the same time. Thanks for the help!!!

1 Like
2

Aedes does work but you probably shouldn't use it on a production system.

The Mosquitto broker might be a better choice (I am not sure if it's available for Windows, but it is excellent on Linux)

1 Like
3
  1. Would all the machines communicate to one SQL node or each MQTT node to its own SQL node?
  • It depends on how you would like to organize your flows. I would say group each machine up with a node, but it doesn't matter. (Take a look at the template node so you can make custom queries and have them all go to the same node)
  1. Do all the flows tabs run simultaneously or just the flow thats pulled up is the one that s running and have to have all machines querying on one flow sheet? Would like to keep things organized for future additions or debugging.
    Node-RED is event-driven, meaning all flows are active at the same time and just wait for an event to take action. Depending on how much work you are doing, I would consider creating a flow for each machine and using the find feature to switch between flows.
4

Ha! I was just about to say route all queries through a single database access node.
It's whatever you prefer :slightly_smiling_face:

The way I do it is to have a tab which contains just the database access and link to it (link-in and link-out nodes) from various flows:

image
image827×226 13.3 KB

ps hivemq is another MQTT broker, cloud based.

5

The server is running on Linux

6

I have Mosquitto running in linux but Im not seeing it show up on the manage pallet so I can add it to Node-red. Does it need to be installed again? Will the MQTT in and out nodes not work without it?

7

All data will be on an internal network so I dont think there is need for a cloud.

Your example is showing that all the MQTT nodes form all the machines are on one flow tab and run to a link node which takes that to another flow tab with just a single SQL node correct?

8

I highly recommend taking a look at Mosquitto then.

If all of your machines submit essentially the same data, including the press ID, and a well chosen MQTT topic, you may only need a single MQTT node, a fairly simple function to construct your SQL query and a single db access node

image
image725×213 14.2 KB

9

If you have a mosquitto broker on the same machine as Node-red you can connect to it using the standard mqtt-in and mqtt-out nodes. If it's on another machine in your network, I think you have to configure remote access in MQTT.

Here is an example of now my mqtt nodes are configured (I'm not using encryption or mqtt v5)

image
image803×579 13.5 KB

image
image890×728 28.1 KB

10

Hi @kwalt , if you're looking to implement Node-RED at scale, FlowFuse may be worth looking into. We were founded by the creators of Node-RED to address needs like security, scalability, reliability, etc. when using Node-RED in production.

I'd be happy to see where we can potentially add value to your project, here is a link to find time to chat: https://meetings-eu1.hubspot.com/flowfuse/zack

11

Thanks for the link. Im going to be running some test back and forth on connectivity at least to see if this even works. Ill reach out if FlowFuse is a route I want to try and take!

1 Like
12

I now have the MQTT form the PLC and the MQTT node communicating with each other. I can send some test data from the PLC to the MQTT in node and see it on the debug, but all the data points need to be sent in 3 separate payloads from the PLC because there are only so many lines available per payload in the PLC software (see attached pic)

payload prop
payload prop732×550 36.7 KB

The payload data has to be converted and packed into a string. The commas are needed to separate the data so the database knows what goes where.

MQTT Test
MQTT Test1734×332 67.4 KB

How to I get the data from the data from the MQTT node to the SQL? I tried just connecting the nodes but obviously that didnt work.
I may also need to combine the three payloads into one before it goes to the SQL, how would I go about that?

13

Here is a pic of the PLC MQTT Client properties page

MQTT prop
MQTT prop659×925 76 KB

14

did you try the join node ?

15

I did but either it didnt work or I didnt set it up properly. But I want to say the join node would be for if I had three separate mqtt nodes each sending a payload. Then the join would be able to combine them, rather than one node sending three payloads. I could be mistaken though

16

Feed the output of the MQTT node into a debug node and show us what the three messages look like.

17

if they had their own unique 'topics' it should work, share the output of MQTT node as @Colin requested. there is a screenshot in your post, but difficult to read. I see that three messages are coming a second apart, (is it possible to attach a unique topic to each message?) do a copy paste contents of debug 32 in your flow. also please set debug node to send complete msg object

18

I just did a small check and indeed is possible to merge three messages from same node.

[{"id":"c4b945af68906cd5","type":"inject","z":"8aeac5104f2cecfe","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":315,"y":3250,"wires":[["5d54fa7ae950d281"]]},{"id":"5d54fa7ae950d281","type":"join","z":"8aeac5104f2cecfe","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":450,"y":3250,"wires":[["5057b0f10bf81264"]]},{"id":"5057b0f10bf81264","type":"debug","z":"8aeac5104f2cecfe","name":"debug 2675","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":3250,"wires":[]}]

I am not sure if there are any caveats though.

check if this works for you..

19

MQTT Test
MQTT Test1734×332 67.4 KB

The debug on the right side shows the three payloads.

In the previous MQTT client properties pic i added, you can see that the I have the MQTT topics with the same name but the payload names are different. I did it this way so I only needed the one MQTT node. That could be easily changed to use multiple MQTT nodes instead if need be/.

20

Change the topics to something like press0/cycle_data/a, press0/cycle_data/b, and press0/cycle_data/c. Then you can configure the MQTT node to subscribe to press0/cycle_data/# and it will receive all of them.

Feed them into a Join node in key/value mode (See this article in the cookbook for an example of how to do that). Then you will have the three strings available in one payload,so you can build the SQL query that you need. You won't be able to send it directly to the SQL node, you will need to build an INSERT query that matches your db schema.

1 Like