search

stackhero-io / node-red-contrib-stackhero-mysql

Node-RED node to read and write to a MySQL or a MariaDB database. Compatible with TLS (SSL) and "Caching SHA2 password" authentication method.
16 stars 7 forks source link

New connections piles on top of each other #5

Closed geekgarage closed 3 years ago

geekgarage commented 3 years ago

Hi,

I'm new to NodeRED 1.2.7 and MariaDB 10 but not new to MySQL and MSSQL, and when i suddenly got an error stating 'Too many connections' i knew something was wrong.

So i had a look in the status -> monitor through phpmyadmin and I can see new connections every time i do a full deploy and those new conncetions do close, but they leave behind a running process that never stops, every time i do a deploy in NodeRED where the SQL have been edited and nees to be redeployed. I use the same database connection profile for all nodes.

https://i.imgur.com/MNOhd3x.png

All this culminate into getting an error 'too many connections' and i end up having to reboot my whole sql to get it runnung again

This is the exact error i get after deploying quite a few times mysqli_real_connect(): (HY000/1040): Too many connections

geekgarage commented 3 years ago

@Bacto any solution to the above issue. i have to restart my synology every time to clear the process list and then i can deploy once again for a while before it is locking up again

geekgarage commented 3 years ago

For any one else who runs nodered on synology. only version 1.0.2 is available in 'Manage Palette'. instead open the container and create a new shell. type 'npm i node-red-contrib-stackhero-mysql@1.0.3' reboot the container and the above issue is fixed.

geekgarage commented 3 years ago

nope, still not fixed. just got the same issue again. I can still login to root on phpmyadmin and i get these errors. This is way over my head, but from what i can see it is all referring back to this node https://i.imgur.com/g9d4O1Q.png

EDIT: I check if i needed to restart the NAS itself because it was the MariaDB that was the issue or the connection/processes got reset if I rebooted the NodeRED container, and the latter was enough. so this is 100% a node issue

Bacto commented 3 years ago

Hi @geekgarage,

I tried to reproduce this with MariaDB 10.5.8 and 8.0.22 but I don't have the same behaviour as you. I've done multiple select/insert and flow deploy. Can you have a look at PhpMyAdmin/Status/Processes to see what are those processes?

geekgarage commented 3 years ago

I've created a video going through my whole setup and showing what is what, explaining how I've set it up. Since you couldn't reproduce it using mariadb i've set it all up from scratch using only docker containers and switched to MySQL with the same result. I'm also showing my flows and such so you can analyze if I'm doing something stupid and causing this. but from my math it's stacking every single SQL connection in my subflow for each subflow every time i deplay. i have used the subflow 3 times throughout my main flows, with 7 sql connections inside the subflow (total 21) and it is stacking 21 every sing time i deploy. If you want i'm up for testing and even do teamviewer if you want so you can go into detail with it. If interested catch me at discord.geekgarage.dk

But here is the video of the whole debacle https://youtu.be/-Kmls2mIX80 (ready in HD)

EDIT: After the video i also did a fresh container in docker of nodered, and it's the same issue. EDIT2: I've tried taking it out of subflow and into the main flow, without any change. Also Updating to 1.0.4 has not change to the issue EDIT3: Process List https://i.imgur.com/QPdntib.png and https://i.imgur.com/MkvVgb9.png https://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-too-many-connections

So in conclusion, they don't disconnect from what my limited coding knowledge tells me. So i'm back at the node issue

EDIT4: Another thing i noticed in the above process list. I changed 'SET session wait_timeout=20;' and then had a look in the list again. the connections are kept in sleep state but they reset the counter every 20 second.

Bacto commented 3 years ago

Awesome video! Thank you! I understood what happened and resolved the issue. You can update to 1.0.5 :) Thanks again!

geekgarage commented 3 years ago

No problem, I'll update when the update has been pushed. just kinda mind boggeling that you don't have the issue :) Thx for the awesome work!

geekgarage commented 3 years ago

can't see the update in the manager or on https://flows.nodered.org/node/node-red-contrib-stackhero-mysql

Bacto commented 3 years ago

It is available now :)

geekgarage commented 3 years ago

It is available now :)

It works like a champ, thank you for the fast reply and fix!