I am expanding/developing a system i have had in place running on a pi-0-w using 6 sensors writting logs to csv files that has been running well for over a year
I intend to employ significantly more sensors and log to a more robust and useful dbs
But I have 2 simple flows that cannot co-exist on the same host without one failing, while if the 2 flows are hosted on distinct hosts all is fine. ????
the flow that fails when coexisting polls/reads bluetooth ble sensors and publishes the data collected as a mqtt topic/payload
the flow that cannot coexist with it is subscribing to the sensor mqtt topic root and logging the data into 3 databases 1 x sqlite db (2 tables one per sensor type) 1 x mariadb ( again 2 x tables) and influxdb using a single time series.
I want to try out each dbs and see which is the best fit for me..
the failure behaviour is that generally the first round of sensor queries execute without issue but the 2nd and subsiquent rouond start to fail with nodata/not found conditions and within 30 minutes successful poll.reads stop entirely.
hosting the write/logging flow on a pi3B and the Poll.read flow on a Pi-0-w things run for days without issue, 1-2% nodata/not found conditions occur with sensors generally lower on battery power..
I became aware of this after adding the influxdb write so that is my highest suspect?
to confirm i have tried not writing to the sqllite db as that node seems to have had issues.. no different
I then cut out the mariadb write and the 2 flows on the same server still could not co-exist writting to the influxdb time series alone.
next i will try isolated sqllite and mariadb inserts.. but????
the result of a systemctl bluetooth status command on a failing host is no different from that of a successful one (with respect to hosting the polling flow)
I would be happy to discover i have made an error in my influxdb messages but it seems ok, the data seems complete and accessable from my first steps with grafana but im kinda focusing on getting the data atm..
I oould just run on 2 x pi puters but i really want to move the system off a single pi-0-w onto the pi3B to expand it and free the pi-0-w for other toy duties..
any advice on how to proceed would be appreciated i am stumped atm.
My write flow is
[{"id":"1cf132c4.f8326d","type":"mqtt in","z":"f0d0c09c.8a075","name":"Sub All","topic":"Readings/#","qos":"2","datatype":"json","broker":"46c274c7.0d85dc","x":70,"y":160,"wires":[["fbcb0c2f.d945b","5a7de77b.34d678"]]},{"id":"fbcb0c2f.d945b","type":"function","z":"f0d0c09c.8a075","name":"Mija influxdb","func":"var temppayload = msg.payload;\nvar intopic= msg.topic;\nvar deviceS = intopic.split(\"/\");\nmsg.payload = [temppayload,\n{\n tag1:deviceS[1] + deviceS[2],\n tag2:deviceS[1]\n}];\nreturn msg;\n","outputs":1,"noerr":0,"x":230,"y":100,"wires":[["2023c304.0acd6c","2727e373.fc5ebc"]]},{"id":"5a7de77b.34d678","type":"function","z":"f0d0c09c.8a075","name":"SQL Insert","func":"var intopic= msg.topic;\nvar deviceS = intopic.split(\"/\");\nvar device = \"'\" + deviceS[1] + deviceS[2] + \"'\";\nvar moisture =msg.payload.moisture ;\nvar battery = msg.payload.battery ;\nvar temperature =msg.payload.temperature ;\nvar conductivity = msg.payload.conductivity ;\nvar curtime = \"time('now')\";\nvar curdate = \"date('now')\";\nvar light = msg.payload.light ;\nvar humidity =msg.payload.humidity ;\nif (!temperature){\n node.error(\"Battery data only!\", msg);\n return null;\n}\nvar sqliteMija = {\n \"topic\": \"INSERT INTO mijareadings(temperature, humidity,battery, currentdate, currenttime, device) values( \" + temperature + \", \" + humidity + \", \" + battery + \",\" + curdate + \", \" + curtime + \", \" + device + \")\"\n}\nvar sqliteMiflora = {\n \"topic\": \"INSERT INTO miflorareadings(battery,temperature,light,moisture,conductivity,currentdate,currenttime,device) VALUES ( \" + battery + \", \" + temperature + \", \" + light + \", \" + moisture + \", \" + conductivity + \", \" + curdate + \", \" + curtime + \", \" + device + \")\"\n}\ncurtime = \"CURRENT_TIME()\";\ncurdate = \"CURRENT_DATE()\";\nsqliteMija.payload =\"test\";\nsqliteMiflora.payload = \"dummy\";\nvar mysqlMija = {\n \"topic\": \"INSERT INTO Mijareadings(temperature, humidity,battery, currentdate, currenttime, device) values( \" + temperature + \", \" + humidity + \", \" + battery + \",\" + curdate + \", \" + curtime + \", \" + device + \");\"\n}\nvar mysqlMiflora = {\n \"topic\": \"INSERT INTO Miflorareadings(battery,temperature,light,moisture,conductivity,currentdate,currenttime,device) VALUES ( \" + battery + \", \" + temperature + \", \" + light + \", \" + moisture + \", \" + conductivity + \", \" + curdate + \", \" + curtime + \", \" + device + \");\"\n}\nmysqlMija.payload =\"idk\";\nmysqlMiflora.payload =\"lmao\";\nif (deviceS[1] === \"Mija\") \n{\n return [sqliteMija,mysqlMija];\n} \nreturn [sqliteMiflora,mysqlMiflora];\n\n\n","outputs":2,"noerr":0,"x":230,"y":220,"wires":[[],[]]},{"id":"2023c304.0acd6c","type":"influxdb out","z":"f0d0c09c.8a075","influxdb":"9d901da8.0160d","name":"Sensors","measurement":"Sensors","precision":"s","retentionPolicy":"","x":400,"y":100,"wires":[]},{"id":"6597a440.2de31c","type":"sqlite","z":"f0d0c09c.8a075","mydb":"a2d1428b.52365","sqlquery":"msg.topic","sql":"","name":"Sqlite3DB","x":400,"y":180,"wires":[["2727e373.fc5ebc"]]},{"id":"fb21cb7d.2ffb38","type":"mysql","z":"f0d0c09c.8a075","mydb":"d7bde3cf.73dd7","name":"MariaDB","x":400,"y":260,"wires":[["2727e373.fc5ebc"]]},{"id":"2727e373.fc5ebc","type":"debug","z":"f0d0c09c.8a075","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":570,"y":220,"wires":[]},{"id":"46c274c7.0d85dc","type":"mqtt-broker","z":"","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":"9d901da8.0160d","type":"influxdb","z":"","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"daisycat","name":"","usetls":false,"tls":""},{"id":"a2d1428b.52365","type":"sqlitedb","z":"","db":"/home/pi/daisycat.db","mode":"RWC"},{"id":"d7bde3cf.73dd7","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"oriondb","tz":""}]
and the poll.read flow is
[{"id":"895ca3d2.68b1","type":"inject","z":"7e4ae01a.d0bc5","name":"trigger injection","topic":"control","payload":"trigger","payloadType":"str","repeat":"30","crontab":"","once":true,"onceDelay":"5","x":120,"y":20,"wires":[["1fef75cb.22e5fa"]]},{"id":"1fef75cb.22e5fa","type":"q-gate","z":"7e4ae01a.d0bc5","name":"Queue Devices","controlTopic":"control","defaultState":"queueing","openCmd":"open","closeCmd":"close","toggleCmd":"toggle","queueCmd":"queue","defaultCmd":"default","triggerCmd":"trigger","flushCmd":"flush","resetCmd":"reset","maxQueueLength":"100","keepNewest":false,"qToggle":false,"persist":false,"x":340,"y":60,"wires":[["c4ec1033.31b78"]]},{"id":"c4ec1033.31b78","type":"change","z":"7e4ae01a.d0bc5","name":"Save Iopic and Prime address","rules":[{"t":"set","p":"SendTopic","pt":"flow","to":"topic","tot":"msg"},{"t":"set","p":"address","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":60,"wires":[["f9637c82.b6224"]]},{"id":"295b3d80.a7e4f2","type":"inject","z":"7e4ae01a.d0bc5","name":"Mija1 Inject","topic":"Readings/Mija/1","payload":"4c:65:a8:d9:9e:64","payloadType":"str","repeat":"300","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":100,"wires":[["1fef75cb.22e5fa"]]},{"id":"b3fee628.3e5218","type":"inject","z":"7e4ae01a.d0bc5","name":"Mija2 inject","topic":"Readings/Mija/2","payload":"4c:65:a8:d0:40:7c","payloadType":"str","repeat":"300","crontab":"","once":true,"onceDelay":"60","x":110,"y":140,"wires":[["1fef75cb.22e5fa"]]},{"id":"66ea4bf9.38d164","type":"inject","z":"7e4ae01a.d0bc5","name":"Mija3 inject","topic":"Readings/Mija/3","payload":"58:2D:34:38:13:CA","payloadType":"str","repeat":"300","crontab":"","once":true,"onceDelay":"660","x":110,"y":180,"wires":[["1fef75cb.22e5fa"]]},{"id":"f528e840.833528","type":"inject","z":"7e4ae01a.d0bc5","name":"Mija4 inject","topic":"Readings/Mija/4","payload":"58:2D:34:38:19:97","payloadType":"str","repeat":"300","crontab":"","once":true,"onceDelay":"720","x":110,"y":220,"wires":[["1fef75cb.22e5fa"]]},{"id":"d796dc88.d0d6f","type":"inject","z":"7e4ae01a.d0bc5","name":"Mija5 inject","topic":"Readings/Mija/5","payload":"58:2D:34:38:10:85","payloadType":"str","repeat":"300","crontab":"","once":true,"onceDelay":"780","x":110,"y":260,"wires":[["1fef75cb.22e5fa"]]},{"id":"8f938f5a.cbc26","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora1 Inj","topic":"Readings/Miflora/1","payload":"C4:7C:8D:64:43:f5","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"120","x":110,"y":340,"wires":[["1fef75cb.22e5fa"]]},{"id":"9e63c3e0.95e2c","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora2 Inj","topic":"Readings/Miflora/2","payload":"C4:7C:8D:61:FF:7F","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"180","x":110,"y":380,"wires":[["1fef75cb.22e5fa"]]},{"id":"6af1c378.3c5bfc","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora3 Inj","topic":"Readings/Miflora/3","payload":"C4:7C:8D:64:43:C8","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"240","x":110,"y":420,"wires":[["1fef75cb.22e5fa"]]},{"id":"f13ab60b.605ed8","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora4 Inj","topic":"Readings/Miflora/4","payload":"C4:7C:8D:64:47:a7","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"300","x":110,"y":460,"wires":[["1fef75cb.22e5fa"]]},{"id":"1bc46d01.02e6d3","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora5 Inj","topic":"Readings/Miflora/5","payload":"C4:7C:8D:64:43:AE","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"360","x":110,"y":500,"wires":[["1fef75cb.22e5fa"]]},{"id":"eb956602.ebfb78","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora6 Inj","topic":"Readings/Miflora/6","payload":"C4:7C:8D:64:43:61","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"420","x":110,"y":540,"wires":[["1fef75cb.22e5fa"]]},{"id":"e0098620.d1e0d8","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora7 Inj","topic":"Readings/Miflora/7","payload":"C4:7C:8D:62:10:AD","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"480","x":110,"y":580,"wires":[["1fef75cb.22e5fa"]]},{"id":"a14fdd56.05212","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora8 Inj","topic":"Readings/Miflora/8","payload":"C4:7C:8D:62:07:01","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"540","x":110,"y":620,"wires":[["1fef75cb.22e5fa"]]},{"id":"7cd8f27b.c03a0c","type":"inject","z":"7e4ae01a.d0bc5","name":"Miflora9 Inj","topic":"Readings/Miflora/9","payload":"C4:7C:8D:64:43:6F","payloadType":"str","repeat":"900","crontab":"","once":true,"onceDelay":"600","x":110,"y":660,"wires":[["1fef75cb.22e5fa"]]},{"id":"f9637c82.b6224","type":"Xiaomi BLE","z":"7e4ae01a.d0bc5","name":"BLE","address":"","scanningTimeout":"15","x":330,"y":160,"wires":[["721fdc9f.af3dc4"]]},{"id":"721fdc9f.af3dc4","type":"change","z":"7e4ae01a.d0bc5","name":"Reset Topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"SendTopic","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":160,"wires":[["6901ddc5.916f14","5f224e7a.1f07d"]]},{"id":"6901ddc5.916f14","type":"mqtt out","z":"7e4ae01a.d0bc5","name":"Publish BLE Data","topic":"","qos":"","retain":"","broker":"46c274c7.0d85dc","x":650,"y":160,"wires":[]},{"id":"5f224e7a.1f07d","type":"debug","z":"7e4ae01a.d0bc5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":260,"wires":[]},{"id":"b4a98584.68ba28","type":"catch","z":"7e4ae01a.d0bc5","name":"","scope":null,"uncaught":false,"x":340,"y":340,"wires":[["5f224e7a.1f07d"]]},{"id":"46c274c7.0d85dc","type":"mqtt-broker","z":"","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":""}]```