Trouble entering data to mysql

[Thewaver]

Hi,
Been struggling for a while to get this to work (saving multiple sensor readers to MySQL) and finally succeeded but now it saves 2 identical lines each time to the database and I get a sporadic error message from the debug node of the Mysql node;

5/14/2020, 9:37:05 AMnode: monitor DBmsg : error
"Error: ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'field list'"

Any help or guidance would be appreciated...

[{"id":"992108be.ca8be8","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"24eff4.946ed00c","type":"mqtt in","z":"992108be.ca8be8","name":"pool","topic":"esp8266/temppool","qos":"0","datatype":"auto","broker":"a414d62.a841f28","x":90,"y":240,"wires":[["be869ffb.c85e8"]]},{"id":"9125ef0d.73a4e","type":"function","z":"992108be.ca8be8","name":"insert data","func":"var Sensor_Val= parseFloat(msg.pool);\nvar Temp_Val= parseFloat(msg.ldr2);\n\nmsg.topic = \"INSERT INTO nodered (data1, data2) VALUES (?,?)\";\nmsg.payload = [Sensor_Val, Temp_Val];\nreturn msg;\n\n//// replace myTable with your db table name \n//msg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\n//msg.payload = [sensorVal, timeStamp, sensorName];\n//return msg;","outputs":1,"noerr":0,"x":610,"y":300,"wires":[["4321defc.dae73"]]},{"id":"ac0b2bc1.0ea6a8","type":"debug","z":"992108be.ca8be8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":970,"y":300,"wires":[]},{"id":"4321defc.dae73","type":"mysql","z":"992108be.ca8be8","mydb":"a7f567b9.54f4b8","name":"monitor DB","x":790,"y":300,"wires":[["ac0b2bc1.0ea6a8"]]},{"id":"4993bafa.cc5ab4","type":"mqtt in","z":"992108be.ca8be8","name":"ldr2","topic":"esp8266/ldr2","qos":"0","datatype":"auto","broker":"a414d62.a841f28","x":90,"y":360,"wires":[["2b6551ab.1a330e"]]},{"id":"2874b5d1.e599da","type":"delay","z":"992108be.ca8be8","name":"","pauseType":"timed","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":270,"y":300,"wires":[["bcfed16e.72836"]]},{"id":"be869ffb.c85e8","type":"change","z":"992108be.ca8be8","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"pool","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":240,"wires":[["2874b5d1.e599da"]]},{"id":"2b6551ab.1a330e","type":"change","z":"992108be.ca8be8","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"ldr2","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":360,"wires":[["2874b5d1.e599da"]]},{"id":"bcfed16e.72836","type":"join","z":"992108be.ca8be8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":", ","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":450,"y":300,"wires":[["9125ef0d.73a4e"]]},{"id":"a414d62.a841f28","type":"mqtt-broker","z":"","name":"192.168.0.75","broker":"192.168.0.75","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"a7f567b9.54f4b8","type":"MySQLdatabase","z":"","name":"monitor db","host":"192.168.0.77","port":"3306","db":"monitor","tz":""}]

[Steve-Mcl]

Please read How to share code or flow json and edit your post - your code cannot be imported as is.

Edit

Never mind, i edited your post for you.

Try adding extra debugs - what do you see in debug panel from before the MYSQL node?

[Thewaver]

Hi, I have attached the debug results for both the join and function nodes. Even after looking at these I cannot see why it is doing double entry into the database. I'm obviously doing something wrong but cant fathom it out

[Steve-Mcl]

So I suspect your values are coming together or this happens after you deploy (I noticed you have full deploy set).

When the 2 MQTT values arrive, there is nothing in your flow to stop multiple same values passing.

You could try with an RBE node after the insert data function node. Though you might have to do a bit tweaking as I'm not certain it will check a payload of array type.

Edit. Look at your delay node - there are messages sitting in there waiting to pass.

[Thewaver]

Hi, thank you for your reply and your valued help. I've just looked at RBE and that will not help me as I want is to log ALL temp reading against light levels. I know for a fact that the signals arrive at the same time. checking all debugs, its the join node that sends out 2 lots of the same data each time. i tried putting the sensors direct to the function but get error message on the mysql node. there must be a simple way of putting multiple data readings into a db.

[Steve-Mcl]

@Thewaver

I know for a fact that the signals arrive at the same time

First- this is impossible. Nothing happens at the same time (at least in this environment anyway).

What you need to realise is, when you say they happen at the same time is 2 messages arrive in your flow almost instantaneously.

The significant part being there are 2 messages.

What the join node does is it puts these together.

Consider this.

Your sensors do this 3 set of updates...
Change 1. 57,33 @ 07:00:00
Change 2. 57,34 @ 07:00:02
Change 3. 57,31 @ 07:00:04
(Note Sensor 1 is same / unchanged)

Change No.	Sensor No.	Value	join state	Written to DB	No.of DB writes
1	1	57	1=57, 2 is null, no send	nothing	0
	2	33	1=57, 2=33, send 57,33	57,33	1
2	1	57	1=57, 2=33, send 57,33	57,33	2
	2	34	1=57, 2=34, send 57,34	57,34	3
3	1	57	1=57, 2=33, send 57,33	57,34	4
	2	34	1=31, 2=34, send 57,34	57,31	5

So 3 sets of values ([57,33], [57,34], [57,31]) but 5 DB writes - 2 of them duplicates.

So yes this...

@Thewaver

its the join node that sends out 2 lots of the same data each time

... is true - but its only because your sensors are sending same, unchanged values.

You can use RBE after sensor input to prevent same value sending a message down the wires to the Join (nip it in the bud so to speak)


IMHO, you'd be much better off storing data in a serial fashion (similar to how you might store data in Influx for example)...

RecID	sensor name	sensor value	timestamp
0	Sensor 1	57	07:00:00
0	Sensor 2	33	07:00:00
0	Sensor 2	34	07:00:02
0	Sensor 2	31	07:00:04

Benefits being

• No join would be needed
• Only new changes are logged
• Data table is extensible (you never need to modify it when you add new sensors).

[Colin]

@Thewaver

the join node that sends out 2 lots of the same data each time

The join node can only send a message when it receives one. There cannot be more messages coming out than go in. Put a debug node showing what is going in and you will see that.

[Thewaver]

Hi Colin, thank you for your reply. I was hoping that the two sensor readings went into the join node and then only 1 message came out. Im probably misunderstanding the join node, I thought many went in and only 1 came out??

[zenofmud]

Your problem is caused because you specify to send a message from the join after 1 mesage comes in

that is why you see a NaN.

change After a number of message parts to 2 and uncheck after every subsequent message.

[Thewaver]

You da man!!!. Followed your instructions and its working as planned. Many thanks for your help.

[Thewaver]

I'd like to thank everyone who helped me in trying to solve my project. Please stay safe....