node-red-node-mysql throw uncaught exception

[mac5562]

Hi !

Randomly i got a unhandled exception form mysql node. Here is what i got from console:

27 Feb 16:31:45 - [red] Uncaught Exception: 27 Feb 16:31:45 - Error: Connection lost: The server closed the connection. at Protocol.end (C:\Users\tamas.node-red\node modules\mysql\lib\protocol\Protocol.js:112:13) at Socket.tanonymoust (C:\Users\tamas.node-red\node modules\mysql\lib\Connection.js:94:20 at Socket. (C:\Users\tamas.node-red\node modules\mysql\lib\Connection.js:526:10) at Socket.emit (events.js:326:22) at endReadableNT (_stream_readable.js:1223:12) at processTicksAndRejections (internal/process/task queues.js:84:21)

Can you help me?

• [ ] Node-RED version: 1.2.7
• [ ] node.js version: v12.19.0
• [ ] npm version: 6.14.8
• [ ] Platform/OS: Windows_NT 10.0.19042 x64 LE
• [ ] Browser: chrome 88.0.4324.190 64 bit

[dceejay]

do you have an example flow that demonstrates the problem ? so we can recreate it ? If so how can we make it fail. How often does it happen ? what is happening when it fails ?

The error message looks like it is coming from the underlying library so unless we can isolate which call is causing it to fail then we may be stuck.

[mac5562]

Here is a insert example [{"id":"4ceb3916.d48878","type":"mqtt in","z":"a36ded7b.70f52","name":"","topic":"dht11/data","qos":"0","datatype":"auto","broker":"aa7adaed.786a78","x":140,"y":300,"wires":[["11694fdd.17d87","e75064a5.8617e8"]]},{"id":"11694fdd.17d87","type":"change","z":"a36ded7b.70f52","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"Temperature_out","fromt":"str","to":"\"Temperature_out\"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"Humidity_out","fromt":"str","to":"\"Humidity_out\"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"Battery_out","fromt":"str","to":"\"Battery_out\"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"Voltage_out","fromt":"str","to":"\"Voltage_out\"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"ID_out","fromt":"str","to":"\"ID_out\"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"Name_out","fromt":"str","to":"\"Name_out\"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":380,"wires":[["c831d44c.3bcbd8"]]},{"id":"c831d44c.3bcbd8","type":"json","z":"a36ded7b.70f52","name":"","property":"payload","action":"","pretty":false,"x":570,"y":380,"wires":[["8349505.887c7b","fb3611b6.82ca1","8998e4f5.aa9b08","283d94a8.79090c","7c12bcfe.7be524","6d79671f.8f4688","3c77ab5d.4a1154","d9979939.2b9688","ea9d5804.c45b48","2625f800.a29ce8","e29ed7b.674b328","824098de.044768"]]},{"id":"ea9d5804.c45b48","type":"delay","z":"a36ded7b.70f52","name":"","pauseType":"rate","timeout":"4","timeoutUnits":"seconds","rate":"1","nbRateUnits":"2","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":780,"y":780,"wires":[["ace1cc7f.dd8ca"]]},{"id":"ace1cc7f.dd8ca","type":"function","z":"a36ded7b.70f52","name":"SQL","func":"var temperature=msg.payload.Temperature_out;\nvar humidity=msg.payload.Humidity_out;\nvar id=msg.payload.ID_out;\nvar name=msg.payload.Name_out;\nvar battery=msg.payload.Battery_out;\n//var gt=Date.now();\nvar sql=[];\n\nsql.push({topic: \"INSERT INTO devices (device_id,device_name,device_battery,last_connection) VALUES ('\"+id+\"','\"+name+\"','\"+battery+\"',UNIX_TIMESTAMP()) ON DUPLICATE KEY UPDATE last_connection=UNIX_TIMESTAMP(),device_battery='\"+battery+\"'\"});\nsql.push({topic:\"INSERT INTO tempandhum (temperature,humidity,device_id,log_date) VALUES ('\" + temperature+ \"', '\" + humidity+ \"', '\" + id + \"',UNIX_TIMESTAMP())\"});\n\n\n\nvar now = new Date();\nvar yyyy = now.getFullYear();\nvar mm = now.getMonth() < 9 ? \"0\" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based\nvar dd = now.getDate() < 10 ? \"0\" + now.getDate() : now.getDate();\nvar hh = now.getHours() < 10 ? \"0\" + now.getHours() : now.getHours();\nvar mmm = now.getMinutes() < 10 ? \"0\" + now.getMinutes() : now.getMinutes();\nvar ss = now.getSeconds() < 10 ? \"0\" + now.getSeconds() : now.getSeconds();\nnode.status({fill:\"blue\",shape:\"ring\",text:\"Last update: \"+dd + \".\" + mm + \".\" + yyyy + \" \" + hh + \":\" + mmm + \":\" + ss}); \n\n\nreturn [sql];","outputs":1,"noerr":0,"initialize":"","finalize":"","x":950,"y":780,"wires":[["b6c14faa.f73a4"]]},{"id":"b6c14faa.f73a4","type":"mysql","z":"a36ded7b.70f52","mydb":"d0b63b56.52c6c8","name":"","x":1150,"y":780,"wires":[[]]},{"id":"aa7adaed.786a78","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"status","willQos":"0","willPayload":"offline"},{"id":"d0b63b56.52c6c8","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"smh","tz":"","charset":"UTF8"}]

It's happening once a day and when it fails the hole node-red shutting down for that reason.

[dceejay]

I don't have your database or sensor so I have set up a simple table being injected with data once per second... I shall sit and wait