Mysql ER_PARSE_ERROR until flow is redeployed or restarted

node-red-node-mysql

[{"id":"c96f4085.cf626","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"ad2984eb.e8368","type":"http request","z":"c96f4085.cf626","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://api.coindesk.com/v1/bpi/currentprice.json","tls":"","persist":false,"proxy":"","authType":"","x":430,"y":140,"wires":[["b50bc723.faa318"]]},{"id":"2a6d8e84.498ae2","type":"inject","z":"c96f4085.cf626","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":140,"wires":[["ad2984eb.e8368"]]},{"id":"564dec65.1c2f0c","type":"debug","z":"c96f4085.cf626","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1090,"y":140,"wires":[]},{"id":"1a181218.cb02f6","type":"function","z":"c96f4085.cf626","name":"","func":"var dataIn = msg.payload;\n//var ts=dataIn.time.updatedISO;\nvar price = dataIn.bpi.USD.rate_float;\nif (price != null){\n flow.set('price',price);\n var dataForInsertQuery = [];\n var ts = dataIn.time.updatedISO.slice(0, 19).replace('T', ' ');\n dataForInsertQuery.push(ts);\n dataForInsertQuery.push(price.toFixed(1));\n var SQLcmd = \"INSERT INTO crypto.btc_tbl (ts,price) VALUES (?,?);\";\n var SQLval = [dataForInsertQuery[0],parseFloat(dataForInsertQuery[1])];\n msg.topic = SQLcmd;\n msg.payload = SQLval;\n return msg;\n}\nreturn null;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":770,"y":140,"wires":[["dc4c15c0.93cfe8","cebf78e4.33715"]]},{"id":"b50bc723.faa318","type":"json","z":"c96f4085.cf626","name":"","property":"payload","action":"","pretty":false,"x":600,"y":140,"wires":[["1a181218.cb02f6"]]},{"id":"dc4c15c0.93cfe8","type":"mysql","z":"c96f4085.cf626","mydb":"bed6f58d.9485b8","name":"Bitcoin","x":930,"y":140,"wires":[["564dec65.1c2f0c"]]},{"id":"62ef5a99.bed454","type":"ui_chart","z":"c96f4085.cf626","name":"","group":"a1e2659e.2dc9","order":0,"width":0,"height":0,"label":"LIVE DATA","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":510,"y":200,"wires":[[]]},{"id":"d92be7c6.66d318","type":"inject","z":"c96f4085.cf626","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"\"BTC Price\"","payload":"price","payloadType":"flow","x":210,"y":200,"wires":[["62ef5a99.bed454"]]},{"id":"410aad75.232f8c","type":"ui_form","z":"c96f4085.cf626","name":"Form","label":"","group":"a1e2659e.2dc9","order":1,"width":0,"height":0,"options":[{"label":"Start Date","value":"startDate","type":"date","required":true,"rows":null},{"label":"End Date","value":"endDate","type":"date","required":true,"rows":null},{"label":"Enable MA","value":"enableMA","type":"switch","required":false,"rows":null},{"label":"MA time (min)","value":"timeMA","type":"number","required":false,"rows":null}],"formValue":{"startDate":"","endDate":"","enableMA":false,"timeMA":""},"payload":"","submit":"submit","cancel":"cancel","topic":"Form","topicType":"str","splitLayout":true,"x":130,"y":380,"wires":[["a467a699.21b66"]]},{"id":"a467a699.21b66","type":"function","z":"c96f4085.cf626","name":"","func":"\nvar formData=msg.payload;\nvar startDate = formData.startDate.slice(0, 19).replace('T', ' ');\nvar endDate = formData.endDate.slice(0, 19).replace('T', ' ');\nvar dataForRetrieveQuery = [];\nvar SQLcmd = \"\";\nvar timeMA = formData.timeMA;\nif (formData.enableMA == true) {\n if (timeMA<2){\n timeMA=2;\n }\n SQLcmd = \"SELECT ts,price, AVG(price) OVER (ORDER BY id ROWS BETWEEN \" + timeMA + \" PRECEDING AND CURRENT ROW) AS average FROM crypto.btc_tbl WHERE ts between \" + \"'\" + startDate + \"'\" + \" AND \" + \"'\" + endDate + \"'\" + \" LIMIT 1000;\";\n} else {\n SQLcmd = \"SELECT ts, price FROM crypto.btc_tbl WHERE ts between \" + \"'\" + startDate + \"'\" + \" AND \" + \"'\" + endDate + \"'\" + \" LIMIT 1000;\";\n}\nmsg.topic=SQLcmd; \n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":380,"wires":[["6c57145a.4101ac","19cfb6d1.4d8d41"]]},{"id":"6c57145a.4101ac","type":"mysql","z":"c96f4085.cf626","mydb":"bed6f58d.9485b8","name":"Bitcoin","x":470,"y":380,"wires":[["162f343c.61acfc","7dbad9bb.e6e36"]]},{"id":"dd196901.bf4e58","type":"ui_chart","z":"c96f4085.cf626","name":"","group":"a1e2659e.2dc9","order":2,"width":"12","height":"6","label":"HISTORICAL DATA","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"1000","removeOlderUnit":"604800","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#ff8080","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1050,"y":380,"wires":[["9cdace0f.9477e"]]},{"id":"8096fc5a.81ad5","type":"change","z":"c96f4085.cf626","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t { \"field\": \"price\", \"label\": \"SPOT\" },\t { \"field\": \"average\", \"label\": \"MA\" }\t ];\t $xaxis := \"ts\";\t [\t {\t \"series\": [$series.label],\t \"data\":$series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis),\t \"y\": $lookup($, $yaxis)\t }\t )\t ]\t }\t ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":380,"wires":[["dd196901.bf4e58"]]},{"id":"9cdace0f.9477e","type":"debug","z":"c96f4085.cf626","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1250,"y":380,"wires":[]},{"id":"162f343c.61acfc","type":"function","z":"c96f4085.cf626","name":"","func":"//convert SQL timestamp format to UNIX style\nvar dbData=msg.payload;\nvar i;\nvar temp;\nvar date = new Date(\"11/21/1987 16:00:00\"); // some mock date\nvar milliseconds = date.getTime();\nfor (i=0;i<dbData.length;i++){\n date = new Date(dbData[i].ts);\n var ms = date.getTime();\n dbData[i].ts=ms;\n\n}\nmsg.payload = dbData;\nmsg.topic = \"Historical Data\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":640,"y":380,"wires":[["8096fc5a.81ad5"]]},{"id":"cebf78e4.33715","type":"debug","z":"c96f4085.cf626","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":970,"y":60,"wires":[]},{"id":"7dbad9bb.e6e36","type":"debug","z":"c96f4085.cf626","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":780,"y":520,"wires":[]},{"id":"19cfb6d1.4d8d41","type":"debug","z":"c96f4085.cf626","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":520,"y":540,"wires":[]},{"id":"bed6f58d.9485b8","type":"MySQLdatabase","name":"","host":"localhost","port":"3306","db":"crypto","tz":"","charset":"UTF8"},{"id":"a1e2659e.2dc9","type":"ui_group","name":"Default","tab":"b2b8ac42.67007","order":1,"disp":true,"width":"12","collapse":false},{"id":"b2b8ac42.67007","type":"ui_tab","name":"BTC Price [USD]","icon":"dashboard","disabled":false,"hidden":false}]

The node-red-node-mysql 0.1.9 node starts throwing ER_PARSE_ERROR on a query after 15-20minutes of working. Redeploying/restarting flow makes it working again.

To not give error on INSERT query

Please tell us about your environment:

• Node-RED version: v1.3.4
• node.js version: v10.24
• npm version: 7.15.1
• Platform/OS: Linux raspberrypi 5.10.17-V7
• Browser: Firefox (windows 10)

Start by reproducing the failure with node-red-node-mysql 0.1.9 and the supplied INSERT query in the listed Node-RED, Node.js, Raspberry Pi, and MySQL environment. Observe what changes after 15–20 minutes and compare behavior before and after redeploying or restarting; done means the INSERT no longer produces ER_PARSE_ERROR during sustained operation.