Download csv between 2 dates - conection lost

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,


maybe can correct me if i wrong this flow
this debug after get data on mysql
image

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.

Function on mysql


my dashboard
image

Can you paste your flow in between the
image image

signs?

this my flow
flows.json (7.6 KB)

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:

  • removed HH:MM in endDate formatter
  • check if start date is before end date (this check could also be done after the user has entered a date)
  • only execute query if download button has been clicked
  • only create file if there are rows in the result

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
image

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?

i use my server cloud 4 core processor and 8 gb ram

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.

image

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:
image

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.