Problem to read data from Mysql

[osancleca]

Hello, I am trying to make a query to a mysql table filtering the data by the date that interests me at the time of the query, the connection and the query are created if errors but the msg has no data, the array appears empty when As seen in the image of the mysql table if there is data on that date,
Any suggestions ?? Since I have tried many things and to no avail, I attach images since the flow is quite simple.

Image the dashboard

Image the flow

Image the function node

mysql table image

Debug

[Colin]

What column type is the time field?

[osancleca]

timestamp

[jbudd]

Assuming a datatype of "Datetime":
You need to wrap the dates in single quotes:
"select * from datos where tiempo between '2021-10-10' and '2021-10-22'"

[osancleca]

when I do it this way it gives me an error.

This is the console cmd error

[Colin]

Connect the sql node only to the debug node (remove any other wires) and see what happens.

[jbudd]

There are several posts on the forum about Node-Red running out of memory, I've not seen the problem myself.

Is the database MySQL or Mariadb? (not sure if they are synonymous)
What hardware and OS are they running on?
Are you using Docker?
What versions of Node-Red (from the hamburger menu), node.js (node -v) and database (mariadb --version) do you have?

[osancleca]

@jbudd

'2021-10-10' and '2021-10-22'

The Data base is MYSQL, my hardware is a pc gaming asus and windows 10 and node red is the last version

[osancleca]

It works,,

now I would like the query of dates not to be fixed, that it could be consulted from the dashboard choosing the range of dates that you select for the query

that would be similar to what I had

msg.topic = "SELECT * FROM data where TIME between" + start + "and" + end + ";";

but working of course is

[Colin]

@osancleca

It works,,

I thought it might. The problem was that you were sending 8 million values to the dashboard table. I am not surprised it gave it indigestion.

[osancleca]

from what I understand with node red working with this volume of data is complicated, because I am trying to create a csv with those 8 thousand arrays and it exploits node red

[Colin]

@osancleca

8 thousand

8 Million (8,000,000)

Creating a csv should be ok, but I think you were sending it to a dashboard table. How could you possibly scroll down through a table that long in the browser?

[jbudd]

@osancleca

now I would like the query of dates not to be fixed, that it could be consulted from the dashboard choosing the range of dates that you select for the query

that would be similar to what I had

msg.topic = "SELECT * FROM data where TIME between" + start + "and" + end + ";";

I think you just need to include the single quotes in your strings:
msg.topic = "SELECT * FROM data where TIME between '" + start + "' and '" + end + "' ;";

[Colin]

@jbudd

msg.topic = "SELECT * FROM data where TIME between '" + start + "' and '" + end + "' ;";

I think examples like this are easier to write/read by using the template literal syntax

msg.topic = `SELECT * FROM data where TIME between '${start}' and '${end}';`

[jbudd]

Thanks. I've never come across that syntax before.

[osancleca]

it works well in both ways,

the problem now is to generate a cvs file with an array of objects which is the load format extracted from mysql, as you can see I have a little sequence below where I simulate an array of objects and it works fine as it only has 3 objects but when it i do with mysql node network payload explodes

this is what happens when i try to pull the data out of mysql

Although the connected mysql node appears in the image, it is not like that, it crashes and I have to restart it

[Colin]

@osancleca

it crashes and I have to restart it

It is no good just stating that. In what way does it crash? What is in the node red log? What is shown by the debug nodes?

[osancleca]

I answer myself hahaha the node csv the only thing it does is separate by commas when I already get that from mysql, it seems that it works friends thanks for the help

[jbudd]

It is possible to get MySQL to write a CSV file directly, which would presumably avoid huge amaounts of data crashing Node-Red.

An example from https://www.databasestar.com/mysql-output-file/

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';