Open crabman1337 opened 3 years ago
crabman1337
commented
3 years ago mysql database table created using: CREATE TABLE btc_tbl(id INT NOT NULL AUTO_INCREMENT, ts TIMESTAMP NOT NULL, price DECIMAL(10,2) NOT NULL, comment VARCHAR(45) NULL, PRIMARY KEY(id));
node-red-node-mysql v0.1.9
hardillb
commented
3 years ago What is the actual query that is being run when the error is output and what are the inputs? e.g. is it an insert and what is being inserted. Have you checked you are feeding the right types to the right columns?
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: