Node-Red MySQL 1.0.1 crashing MySQL

General
1

HI,

Running into odd MySQL issue where high use of ram then crashes.

I have node red and mysql running in a kubernetes cluster, each in their own container. I have given MySQL 16gb of ram. Nothing else is running, I'm running a maintenance routine this weekend on a third party DAM system.

Basically looping through 2,700 partnumbers, then calling MySQL looking up the partnumber and all its related data, building a jason query and updating a third party system via an API.

The MySQL select is crashing the container, I upgraded to 1.0.1. I'm using JSON Array Aggregate as each linked table could have more than one result. i.e partnumber could be used in multiple years.

I have a rate limit of 1 message every 20 seconds to see if this would help MySQL but no go. Run about 50 records and she goes down.

CrashLoopBackOff   8 (3m17s ago)   34m

All the relevant table columns are indexed, especially the pivot tables for the joins.

[{"id":"affd6c530e4abdd1","type":"function","z":"5f7b654b5e96e1db","name":"Look Up Part Number","func":"let data = msg.payload;\n\nmsg.payload={}\nmsg.payload.partnumber = data.collection_title;\n\nlet query = \"SELECT JSON_ARRAYAGG( JSON_OBJECT('partnumber',pn.partnumber, 'name', pn.name, 'ShortDescription', pn.ShortDescription,\";\nquery += \"'year', vy.year, 'make', vm.make, 'model', vmod.model, 'engine', ve.engine, 'brand', vb.brand ) ) AS json \";\nquery += \"FROM vehicle_partnumbers AS pn \";\n//year\nquery += \"LEFT JOIN partnumber_vehicle_years_link AS vyl ON vyl.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_years AS vy ON vy.year_id = vyl.year_id \";\n\n\n//make\nquery += \"LEFT JOIN partnumber_vehicle_make_link AS vml ON vml.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_make AS vm ON vm.make_id = vml.make_id \";\n\n\n//model\nquery += \"LEFT JOIN partnumber_vehicle_model_link AS vmodl ON vmodl.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_model AS vmod ON vmod.model_id = vmodl.model_id \";\n\n\n//engine\nquery += \"LEFT JOIN partnumber_vehicle_engine_link AS vel ON vel.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_engine AS ve ON ve.engine_id = vel.engine_id \";\n\n\n//brand\nquery += \"LEFT JOIN partnumber_vehicle_brand_link AS vbl ON vbl.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_brand AS vb ON vb.brand_id = vbl.brand_id \";\n\nquery += \"WHERE partnumber = :partnumber\";\n\nmsg.topic = query;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1280,"y":2940,"wires":[["a117aa82fc0e8169"]]},{"id":"a117aa82fc0e8169","type":"mysql","z":"5f7b654b5e96e1db","mydb":"19c571b552634c36","name":"Holly DB","x":1460,"y":2940,"wires":[["a302f9f39facee8f"]]},{"id":"d424cc6f0b757563","type":"delay","z":"5f7b654b5e96e1db","name":"1 msg, 20s","pauseType":"rate","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"20","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"x":1090,"y":2940,"wires":[["affd6c530e4abdd1"]]},{"id":"a302f9f39facee8f","type":"switch","z":"5f7b654b5e96e1db","name":"Check with any Values","property":"payload[0].json","propertyType":"msg","rules":[{"t":"nempty"}],"checkall":"true","repair":false,"outputs":1,"x":1640,"y":2940,"wires":[["8d6b8e6d09732c01"]],"outputLabels":["Results Returned"]},{"id":"446cef2f66ea7c6c","type":"inject","z":"5f7b654b5e96e1db","name":"","props":[{"p":"reset","v":"","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":1090,"y":2860,"wires":[["d424cc6f0b757563"]]},{"id":"19c571b552634c36","type":"MySQLdatabase","name":"","host":"db","port":"3306","db":"vehicle_applications","tz":"","charset":"UTF8"}]

Any ideas?
thanks
Harry

2

You say " Node-Red MySQL 1.0.1" crashing MySQL" but have you tried running the same query outside of node-red?

3

Ran a bunch of the same queries directly in MySQL and its fine. Indecently this quires been used in a webhook for a few months with no issue. Given the nature of webhooks they don't always come in at the same time.

This is the first time we looped and called one after the other in quick succession.

I can build a quick PHP script to test the same thing out side of Node-Red in the week, but I need to get these done before Monday.

There maybe one of two partnumbers that come back with huge amount of data and take longer, I'm marking what is successful back into the database. So before it crashes we get what was done and only pick up again what needs to completed. kubernetes does eventually get the container back and up and running.

Fingers crossed I'm left with just a few partnumber and I can trace those more in-depth.

We're batch applying data to an api to save the users manually entering thousands of data points. We have 2,700 folders and we're telling the api to apply the data to the contents in each folder

The API is keeping up but the selects from the database don't seem to be clearing time, yet they take less than a second to come back.

Need to get this figure out in case we get similar asks

Harry

4

Please Ignore:

Once I manage to limp along and I was left with the offending part-number it was the data.

One part-number alone had:

517500 rows in set (1.37 sec)

Trying to bring that back as a JSON Array Aggregate is the issue. I didn't even realize they where that many permutations.

My apologizes, not a node-red issue at all,

Thanks
Harry

1 Like
5

Just for completeness, the issue was the results, the call I was making add 517,500 permutation, my mysql query, while is did work it was not efficient for that many rows.

I ended up rewriting my SQL using sub-quires, which made a huge difference in speed and robustness.

This may help some one else doing complexed multi-tabled calls and requiring JSON back.

[{"id":"8e8f251f82fdf280","type":"function","z":"5f7b654b5e96e1db","name":"Look Up Part Number","func":"let data = msg.payload;\n\nmsg.payload={}\nmsg.payload.partnumber = data.collection_title;\n\nlet query = \"SELECT pn.partnumber, pn.name, pn.ShortDescription,\";\n//year\nquery += \"(SELECT JSON_ARRAYAGG(JSON_OBJECT('year', subQueryYear.year)) FROM \";\nquery += \"(SELECT DISTINCT pn.partnumber,vy.year FROM vehicle_partnumbers AS pn \";\nquery += \"LEFT JOIN partnumber_vehicle_years_link AS vyl ON vyl.partnumber_id = pn.partnumber_id \"; \nquery += \"LEFT JOIN vehicle_years AS vy ON vy.year_id = vyl.year_id ORDER BY vy.year) subQueryYear \";\nquery += \"WHERE subQueryYear.partnumber = pn.partnumber) AS year,\";\n\n//make\nquery += \"(SELECT JSON_ARRAYAGG(JSON_OBJECT('make', subQueryMake.make)) FROM \";\nquery += \"(SELECT DISTINCT pn.partnumber,vm.make FROM vehicle_partnumbers AS pn \";\nquery += \"LEFT JOIN partnumber_vehicle_make_link AS vml ON vml.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_make AS vm ON vm.make_id = vml.make_id ORDER BY vm.make) subQueryMake \";\nquery += \"WHERE subQueryMake.partnumber = pn.partnumber)AS make,\";\n\n//model\nquery += \"(SELECT JSON_ARRAYAGG(JSON_OBJECT('model', subQueryModel.model)) FROM \";\nquery += \"(SELECT DISTINCT pn.partnumber,vmod.model FROM vehicle_partnumbers AS pn \";\nquery += \"LEFT JOIN partnumber_vehicle_model_link AS vmodl ON vmodl.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_model AS vmod ON vmod.model_id = vmodl.model_id ORDER BY vmod.model) subQueryModel \";\nquery += \"WHERE subQueryModel.partnumber = pn.partnumber)AS model,\";\n\n//engine\nquery += \"(SELECT JSON_ARRAYAGG(JSON_OBJECT('engine', subQueryEngine.engine)) FROM \";\nquery += \"(SELECT DISTINCT pn.partnumber,ve.engine FROM vehicle_partnumbers AS pn \";\nquery += \"LEFT JOIN partnumber_vehicle_engine_link AS vel ON vel.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_engine AS ve ON ve.engine_id = vel.engine_id ORDER BY ve.engine) subQueryEngine \";\nquery += \"WHERE subQueryEngine.partnumber = pn.partnumber)AS engine,\";\n\n//brand\nquery += \"(SELECT JSON_ARRAYAGG(JSON_OBJECT('brand', subQueryBrand.brand)) FROM \";\nquery += \"(SELECT DISTINCT pn.partnumber,vb.brand FROM vehicle_partnumbers AS pn \";\nquery += \"LEFT JOIN partnumber_vehicle_brand_link AS vbl ON vbl.partnumber_id = pn.partnumber_id \";\nquery += \"LEFT JOIN vehicle_brand AS vb ON vb.brand_id = vbl.brand_id ORDER BY vb.brand) subQueryBrand \";\nquery += \"WHERE subQueryBrand.partnumber = pn.partnumber)AS brand \";\n\n\nquery += \"FROM vehicle_partnumbers AS pn \";\nquery += \"WHERE pn.partnumber = :partnumber\";\n\nmsg.topic = query;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1240,"y":2940,"wires":[["a117aa82fc0e8169"]]}]

Harry

6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.