Save MQTT data to MySQL

[eduardoluiztodero]

Hello guys, I'm doing a project to monitor temperature, humidity and air quality in chicken farms. One need is to save the data in a database to generate indicators. I am sending the data to an MQTT broker and need to save it in a database. I am not able to send the data in the format I receive it. This is my flow, in the first image is the data I get from MQTT (there are several devices, this is just an example of one), in the second photo it is what appears when it passes through the mysql node. I need your help to solve this problem. Thanks.

[{"id":"d15a8d98.892a7","type":"tab","label":"Teste MySQL","disabled":false,"info":""},{"id":"8e15704a.5251f","type":"function","z":"d15a8d98.892a7","name":"SQL query teste","func":"msg.topic = \"SELECT valores FROM dadosteste ORDER BY id DESC LIMIT 1\";\nreturn msg;","outputs":1,"noerr":0,"x":349.0174102783203,"y":165.00003814697266,"wires":[["4ee4064.61fcaf8"]]},{"id":"4ee4064.61fcaf8","type":"mysql","z":"d15a8d98.892a7","mydb":"e9f025e8.70ddf8","name":"teste","x":556.0173225402832,"y":165.00002765655518,"wires":[["12c4989a.f34b37"]]},{"id":"12c4989a.f34b37","type":"debug","z":"d15a8d98.892a7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":748.0172080993652,"y":166.00006866455078,"wires":[]},{"id":"628f2721.50b168","type":"mqtt in","z":"d15a8d98.892a7","name":"TCC","topic":"device3Av1","qos":"0","datatype":"json","broker":"c7266bc3.062ef8","x":135.01736450195312,"y":166.01040840148926,"wires":[["8e15704a.5251f","5519b318.8e355c"]]},{"id":"5519b318.8e355c","type":"debug","z":"d15a8d98.892a7","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":327.576416015625,"y":243.5972499847412,"wires":[]},{"id":"e9f025e8.70ddf8","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"teste_nodered","tz":""},{"id":"c7266bc3.062ef8","type":"mqtt-broker","z":"","name":"TCC ","broker":"m24.cloudmqtt.com","port":"14910","clientid":"","usetls":false,"compatmode":true,"keepalive":"1","cleansession":true,"birthTopic":"esp/test","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

[Colin]

What is the query that you are passing to the database node? Feed it to a debug node set to Show Complete Message.

[Edit] I mean the one writing the data of course, not the one reading it.

[eduardoluiztodero]

what I'm sending to the mysql node is what I'm getting from MQTT

[zenofmud]

Your flow is only doing a select where are you tryin to insert the data?

[eduardoluiztodero]

Zenofmud, I took this query function from an example .. I never did this kind of thing, I'm new using node red .. could you give me a light at the end of the tunnel?

[Colin]

You need to find a primer on how to write data to the database using SQL statements. Also you will first need to design and create the database. This isn't really anything to do with node-red.

[zenofmud]

Along with @Colin, my first suggestion would be to go take an SQL tutorial. Creating SQL statements really isn't NR.

Here is a sample of an create, insert, select and delete using SQLite that you can take a look of.

[{"id":"a06bd52c.105218","type":"sqlite","z":"7acc20b7.b27b9","mydb":"3594a0ec.6329a8","sqlquery":"msg.topic","sql":"","name":"testdb","x":690,"y":320,"wires":[["9289f4ef.b44a9"]]},{"id":"6b5a0680.fcd06","type":"inject","z":"7acc20b7.b27b9","name":"create table","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":150,"y":260,"wires":[["1a35bc39.05bdd4"]]},{"id":"51107643.82d748","type":"inject","z":"7acc20b7.b27b9","name":"insert","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":130,"y":320,"wires":[["b65516ba.aeb3e"]]},{"id":"a4ece8df.0e597","type":"inject","z":"7acc20b7.b27b9","name":"select","topic":"select * from greenhouse","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":230,"y":140,"wires":[["a06bd52c.105218"]]},{"id":"1a35bc39.05bdd4","type":"template","z":"7acc20b7.b27b9","name":"Create","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE greenhouse (\n  id          INTEGER PRIMARY KEY AUTOINCREMENT, \n  temperature NUMERIC, \n  humidity    NUMERIC, \n  datetime    DATE, \n  device TEXT\n)","output":"str","x":370,"y":260,"wires":[["a06bd52c.105218"]]},{"id":"b65516ba.aeb3e","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n  temperature, \n  humidity, \n  datetime, \n  device\n  ) \nVALUES (\n  22.4, \n  48, \n  date('now'),\n  \"room3\"\n  )","output":"str","x":400,"y":320,"wires":[["a06bd52c.105218"]]},{"id":"c1f2f118.befeb8","type":"inject","z":"7acc20b7.b27b9","name":"insert","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":130,"y":380,"wires":[["27490a6f.de7636"]]},{"id":"67e31919.5f33c8","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n  temperature, \n  humidity, \n  datetime, \n  device\n  ) \nVALUES (\n  {{payload.temperature}}, \n  {{payload.humidity}},\n  {{payload.datetime}},\n  \"{{payload.device}}\"\n  )","output":"str","x":540,"y":380,"wires":[["a06bd52c.105218","dbbade4d.e358d8"]]},{"id":"27490a6f.de7636","type":"change","z":"7acc20b7.b27b9","name":"","rules":[{"t":"delete","p":"payload","pt":"msg"},{"t":"set","p":"date","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.temperature","pt":"msg","to":"72.3","tot":"num"},{"t":"set","p":"payload.humidity","pt":"msg","to":" 64","tot":"num"},{"t":"set","p":"payload.datetime","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.device","pt":"msg","to":"room1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":380,"wires":[["67e31919.5f33c8"]]},{"id":"9289f4ef.b44a9","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":850,"y":320,"wires":[]},{"id":"dbbade4d.e358d8","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":420,"wires":[]},{"id":"3594a0ec.6329a8","type":"sqlitedb","z":"","db":"/Users/Paul/databases/testdb"}]

[lizzardguki]

If you still need help, i have free time and i am able to help you

[TotallyInformation]

You might also want to consider whether you really do want to use a SQL db for this as they are not always ideal for time series type data.

If you later need to analyse by time, something like InfluxDB might be a better fit.