Hi Guys
i need help this problem, if i download csv get data on mysql between 2 date node red is conection lost and i cant function time get data on mysql,
Thank U All..
Can we see the code which is in the My SQL Database Request (or can you share the nodes shown in the screenshot)?
Btw, your date range in the screenshot does not seem right.
Your flow looks okay to me and works with a test data base. I do not experience a "connection lost". Where is this error coming from? Node-RED or mysql?
I did some small changes:
Adding the time to the query should not be a big problem. Just combine date and time in a string?
[{"id":"fced8960.3302d8","type":"ui_date_picker","z":"db57571125b56dbb","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"payload","topicType":"msg","x":380,"y":140,"wires":[["db743ddf.1cdf2"]]},{"id":"bb241d.419f3be","type":"ui_date_picker","z":"db57571125b56dbb","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"end_date","topicType":"str","x":420,"y":240,"wires":[["29858d7e.14d6c2"]]},{"id":"73943720.123458","type":"function","z":"db57571125b56dbb","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == \"download\"){\n //var port= \"dewa_10\"; //replace with port no\n var startDate = flow.get(\"startDate\");\n var endDate = flow.get(\"endDate\");\n\n // check if date range is correct\n if (new Date(endDate) > new Date(startDate)) {\n msg.topic = \"SELECT * FROM print WHERE datetime BETWEEN '\" + startDate + \"' AND '\" + endDate + \"' order BY datetime\";\n return msg;\n } else {\n node.warn(\"end date is before start date.\");\n }\n\n}\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":220,"y":420,"wires":[["77ca2030.8a273"]]},{"id":"b0a6b2d.9820c5","type":"csv","z":"db57571125b56dbb","name":"","sep":" ","hdrin":true,"hdrout":"all","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":false,"include_null_values":false,"x":910,"y":420,"wires":[["7847ddfd.282c64"]]},{"id":"d7a49664.ea6c88","type":"http response","z":"db57571125b56dbb","name":"","statusCode":"","headers":{},"x":1250,"y":420,"wires":[]},{"id":"f130565b.4c5a88","type":"catch","z":"db57571125b56dbb","name":"","scope":null,"uncaught":false,"x":360,"y":480,"wires":[["2da78617.39032a","119f9d9a.fea782"]]},{"id":"2da78617.39032a","type":"function","z":"db57571125b56dbb","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1080,"y":480,"wires":[["d7a49664.ea6c88"]]},{"id":"119f9d9a.fea782","type":"debug","z":"db57571125b56dbb","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":390,"y":540,"wires":[]},{"id":"c9c865ec.16f888","type":"ui_template","z":"db57571125b56dbb","group":"1435e071.39b76","name":"ui_temlplate - present download link on dashboard","order":8,"width":5,"height":1,"format":"<style>\n.button {\n font: bold 11px Arial;\n text-decoration: none;\n background-color: #EEEEEE;\n color: #333333;\n padding: 2px 6px 2px 6px;\n border-top: 1px solid #CCCCCC;\n border-right: 1px solid #333333;\n border-bottom: 1px solid #333333;\n border-left: 1px solid #CCCCCC;\n}\n</style>\n\n<a href=\"/download/output.csv\" download=\"Download CSV\" class=\"button\">Download CSV</a>\n<!--div>\n <a href=\"/data\">CLICK TO DOWNLOAD</a>\n</div-->","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":710,"y":540,"wires":[[]]},{"id":"4fdfc12f.c0cac","type":"http in","z":"db57571125b56dbb","name":"","url":"/download/output.csv","method":"get","upload":false,"swaggerDoc":"","x":170,"y":620,"wires":[["c9b105a38c98b8da"]]},{"id":"7847ddfd.282c64","type":"function","z":"db57571125b56dbb","name":"Set Headers","func":"msg.headers = {}\nmsg.headers[\"Content-type\"] = \"text/csv\";\nmsg.headers[\"Content-Disposition\"] = \"attachment; filename=data.csv\";\nmsg.headers[\"Pragma\"] = \"no-cache\";\nmsg.headers[\"Expires\"] = \"0\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":420,"wires":[["d7a49664.ea6c88"]]},{"id":"aa9e9b7e.b8c1b8","type":"debug","z":"db57571125b56dbb","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1110,"y":220,"wires":[]},{"id":"3f503323.02092c","type":"mysql","z":"db57571125b56dbb","mydb":"e882f2c3.e1f7","name":"DB","x":590,"y":420,"wires":[["65aaa7e8.442dd8","95c88f701372aad5"]]},{"id":"c3ea697d.895368","type":"ui_text_input","z":"db57571125b56dbb","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":410,"y":180,"wires":[["352040e.0355dc"]]},{"id":"5b167e47.f3e8d","type":"ui_text_input","z":"db57571125b56dbb","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":420,"y":280,"wires":[[]]},{"id":"db743ddf.1cdf2","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":620,"y":140,"wires":[["4c6ed513.afa47c"]]},{"id":"4c6ed513.afa47c","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":140,"wires":[["aa9e9b7e.b8c1b8","73943720.123458"]]},{"id":"29858d7e.14d6c2","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":620,"y":240,"wires":[["97ed9b53.d5ca38"]]},{"id":"97ed9b53.d5ca38","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":240,"wires":[["aa9e9b7e.b8c1b8","73943720.123458"]]},{"id":"352040e.0355dc","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":640,"y":180,"wires":[["82f239d2.5c12d8"]]},{"id":"82f239d2.5c12d8","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":180,"wires":[["aa9e9b7e.b8c1b8"]]},{"id":"53fd7840.d64688","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm:","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":640,"y":280,"wires":[["2dbab6a0.961aaa"]]},{"id":"2dbab6a0.961aaa","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":850,"y":280,"wires":[["aa9e9b7e.b8c1b8"]]},{"id":"65aaa7e8.442dd8","type":"debug","z":"db57571125b56dbb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":730,"y":360,"wires":[]},{"id":"77ca2030.8a273","type":"delay","z":"db57571125b56dbb","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"x":440,"y":420,"wires":[["3f503323.02092c"]]},{"id":"95c88f701372aad5","type":"function","z":"db57571125b56dbb","name":"check result","func":"// only proceed if the result of the query has rows\nif(msg.payload.length > 0){\n return msg;\n}else{\n node.warn(\"there are now rows.\");\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":750,"y":420,"wires":[["b0a6b2d.9820c5"]]},{"id":"c9b105a38c98b8da","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"download","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":150,"y":520,"wires":[["73943720.123458"]]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":18,"collapse":false},{"id":"e882f2c3.e1f7","type":"MySQLdatabase","name":"Lift Monitoring ","host":"127.0.0.1","port":"3306","db":"iot","tz":"","charset":"UTF8"},{"id":"48313a88.497314","type":"ui_tab","name":"Test","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]
Edit: May I ask why there is a 5 s delay before the mysql node?
i try for your flow, if i start - end date 1 days, i have connection lost
this error conection lost on my dashboard
There must be some other problems. What does the log say? What hardware are you running Node-RED on? Is the mysql server running on the same machine?
The log doesn't tell me much to be honest. Maybe somebody can spot something? Could you please provide the version numbers?
In the meantime, can we check what triggers the problem? Can you try to run the flow below?
Set a proper date range which leads to a result and use the query inject? Please also make sure you're only running the relevant flow and nothing else.
This should give a result in the debug (in my case there are two rows in the result) but no download because I cut the wire:
If the connection lost appears again, I think the problem has to do with the DB node (I am using version 0.2.1). If the problem is gone, it must be the file handling.
[{"id":"fced8960.3302d8","type":"ui_date_picker","z":"db57571125b56dbb","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"payload","topicType":"msg","x":380,"y":140,"wires":[["db743ddf.1cdf2"]]},{"id":"bb241d.419f3be","type":"ui_date_picker","z":"db57571125b56dbb","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"end_date","topicType":"str","x":420,"y":240,"wires":[["29858d7e.14d6c2"]]},{"id":"73943720.123458","type":"function","z":"db57571125b56dbb","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\"){\n //var port= \"dewa_10\"; //replace with port no\n var startDate = flow.get(\"startDate\");\n var endDate = flow.get(\"endDate\");\n\n // check if date range is correct\n if (new Date(endDate) > new Date(startDate)) {\n msg.topic = \"SELECT * FROM print WHERE datetime BETWEEN '\" + startDate + \"' AND '\" + endDate + \"' order BY datetime\";\n return msg;\n } else {\n node.warn(\"end date is before start date.\");\n }\n\n}\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":420,"wires":[["3f503323.02092c"]]},{"id":"b0a6b2d.9820c5","type":"csv","z":"db57571125b56dbb","name":"","sep":" ","hdrin":true,"hdrout":"all","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":false,"include_null_values":false,"x":910,"y":420,"wires":[["7847ddfd.282c64"]]},{"id":"d7a49664.ea6c88","type":"http response","z":"db57571125b56dbb","name":"","statusCode":"","headers":{},"x":1250,"y":420,"wires":[]},{"id":"f130565b.4c5a88","type":"catch","z":"db57571125b56dbb","name":"","scope":null,"uncaught":false,"x":360,"y":480,"wires":[["2da78617.39032a","119f9d9a.fea782"]]},{"id":"2da78617.39032a","type":"function","z":"db57571125b56dbb","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1080,"y":480,"wires":[["d7a49664.ea6c88"]]},{"id":"119f9d9a.fea782","type":"debug","z":"db57571125b56dbb","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":390,"y":540,"wires":[]},{"id":"c9c865ec.16f888","type":"ui_template","z":"db57571125b56dbb","group":"1435e071.39b76","name":"ui_temlplate - present download link on dashboard","order":8,"width":5,"height":1,"format":"<style>\n.button {\n font: bold 11px Arial;\n text-decoration: none;\n background-color: #EEEEEE;\n color: #333333;\n padding: 2px 6px 2px 6px;\n border-top: 1px solid #CCCCCC;\n border-right: 1px solid #333333;\n border-bottom: 1px solid #333333;\n border-left: 1px solid #CCCCCC;\n}\n</style>\n\n<a href=\"/download/output.csv\" download=\"Download CSV\" class=\"button\">Download CSV</a>\n<!--div>\n <a href=\"/data\">CLICK TO DOWNLOAD</a>\n</div-->","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":710,"y":540,"wires":[[]]},{"id":"4fdfc12f.c0cac","type":"http in","z":"db57571125b56dbb","name":"","url":"/download/output.csv","method":"get","upload":false,"swaggerDoc":"","x":150,"y":640,"wires":[["c9b105a38c98b8da"]]},{"id":"7847ddfd.282c64","type":"function","z":"db57571125b56dbb","name":"Set Headers","func":"msg.headers = {}\nmsg.headers[\"Content-type\"] = \"text/csv\";\nmsg.headers[\"Content-Disposition\"] = \"attachment; filename=data.csv\";\nmsg.headers[\"Pragma\"] = \"no-cache\";\nmsg.headers[\"Expires\"] = \"0\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":420,"wires":[["d7a49664.ea6c88"]]},{"id":"aa9e9b7e.b8c1b8","type":"debug","z":"db57571125b56dbb","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1110,"y":220,"wires":[]},{"id":"3f503323.02092c","type":"mysql","z":"db57571125b56dbb","mydb":"e882f2c3.e1f7","name":"DB","x":590,"y":420,"wires":[["95c88f701372aad5"]]},{"id":"c3ea697d.895368","type":"ui_text_input","z":"db57571125b56dbb","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":410,"y":180,"wires":[["352040e.0355dc"]]},{"id":"5b167e47.f3e8d","type":"ui_text_input","z":"db57571125b56dbb","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"topic","topicType":"msg","x":420,"y":280,"wires":[[]]},{"id":"db743ddf.1cdf2","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":620,"y":140,"wires":[["4c6ed513.afa47c"]]},{"id":"4c6ed513.afa47c","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":140,"wires":[["aa9e9b7e.b8c1b8","73943720.123458"]]},{"id":"29858d7e.14d6c2","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":620,"y":240,"wires":[["97ed9b53.d5ca38"]]},{"id":"97ed9b53.d5ca38","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":240,"wires":[["aa9e9b7e.b8c1b8","73943720.123458"]]},{"id":"352040e.0355dc","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":640,"y":180,"wires":[["82f239d2.5c12d8"]]},{"id":"82f239d2.5c12d8","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":180,"wires":[["aa9e9b7e.b8c1b8"]]},{"id":"53fd7840.d64688","type":"moment","z":"db57571125b56dbb","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Bangkok","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"HH:mm:","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Asia/Bangkok","x":640,"y":280,"wires":[["2dbab6a0.961aaa"]]},{"id":"2dbab6a0.961aaa","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":850,"y":280,"wires":[["aa9e9b7e.b8c1b8"]]},{"id":"65aaa7e8.442dd8","type":"debug","z":"db57571125b56dbb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":910,"y":360,"wires":[]},{"id":"95c88f701372aad5","type":"function","z":"db57571125b56dbb","name":"check result","func":"// only proceed if the result of the query has rows\nif(msg.payload.length > 0){\n return msg;\n}else{\n node.warn(\"there are now rows.\");\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":750,"y":420,"wires":[["65aaa7e8.442dd8"]]},{"id":"c9b105a38c98b8da","type":"change","z":"db57571125b56dbb","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"download","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":150,"y":520,"wires":[["73943720.123458"]]},{"id":"000f03c958e5108f","type":"inject","z":"db57571125b56dbb","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"query","payloadType":"date","x":110,"y":300,"wires":[["73943720.123458"]]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":18,"collapse":false},{"id":"e882f2c3.e1f7","type":"MySQLdatabase","name":"Lift Monitoring ","host":"127.0.0.1","port":"3306","db":"iot","tz":"","charset":"UTF8"},{"id":"48313a88.497314","type":"ui_tab","name":"Test","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]
What is the size of the array that you get from the DB query?
From my experience, if the data size is big, then you may experience "disconnect" problem.
For the first step, you can limit the number of points (say 500) from the query and try it again.
okay thanks, this solve
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy