I use node module node-red-node-mysql to connect my MySQL DB.
It works fine at begining.When I develop a lot of node red app with the same DataBase.
I have some issue appear there is too many connection to my databae.
Does single request to node red http-in create a connection to MySQL??
When the request finish and return response, the connection will be released or not??
Does anyone have the same issue??
How can I solve this issue??
This is down to the node itself. Unless it is a core node, you will need to contact the author, probably via the nodes GitHub site.
Ideally, any db node should use shared connections.
Yup, I think so.
But I think that every request comes from http-in node and access the flow, it will create a connection to access MySQL by every request.
I don’t have any idea about connection release.Does the mysql node finish the job and release or decide by MySQL server the max waiting time??
Maybe it should add a finally block when payload is ready release the connection.
Or use connection pool to manage MySQL connection in node red. I guess everyone use the MySQL for lightweight application, so no one encounter this situation.
I just want to know how to optmize connection utilization rate and prevent this problem happened again.
It looks to me (looking at the code) that the connection is released when NR ends.
It also looks to me that there is only one connection per database no matter how many flows are ysing that DB
You can check the number of connections by using this:
show status where `variable_name` = 'Threads_connected';
here is an inject node connected to a change node with that request set in the msg.topic:
[{"id":"bbef23cc.4e7f18","type":"inject","z":"87efc4bb.1be8b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":300,"wires":[["27fab34b.bc169c"]]},{"id":"27fab34b.bc169c","type":"change","z":"87efc4bb.1be8b","name":"Threads_connected","rules":[{"t":"set","p":"topic","pt":"msg","to":"show status where `variable_name` = 'Threads_connected';","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":300,"wires":[["9256b28d.d1e5b8"]]}]
Connect this to your MySQL node and put a debug node at the end. It will show you the number of connections.
Oh!! Thanks for your help, zenofmud.I’ll try it and check connection status.
A question I have is how many other applications are accessing the database? I thought I read that by default MySQL allows 151 connections.
I use the compose for MySQL as my database and use IBM Cloud to host my application.
I have 7 applications to access the database.
I use micro service architecture to build my system.
I ask the IBM consultant about the default connection size is 100.
originally you said:
How many applcations have you created?
7 applications I created.
I connect to my database finally.
And I see the Aborted Connections:5113581.
I guess my database just under attack,and I should set up white list to prevent some attack.
Thanks for your help~zenofmud.
Do you mean you have unprotected access to the database from the internet?
Um~not really sure.I just use compose that provides database service.
I create one and use it.That’s all.
About security I guess that compose could help me to do something well.
Such like Database as a service.
Do you not need a username/pwd to access it?
Nothing to do with Node-RED really ... but rule number 1 of creating cloud-based services is: for goodness sake don't allow anyone direct access to your database!
Yup~I totally agree that.But as a service at cloud, all I can do is trust the service provider can handle it.
Well probably this isn't the place to get into this discussion. But personally, I would always verify things. Cloud platforms can be so flexible that it is every bit as easy to mess up the security as using your own infrastructure. The vendor can only go so far since they have to provide tools that meet lots of different requirements. So when a vendor is providing a set of SaaS tools, there are probably configuration decisions you need to make for yourself rather than assuming the vendor's defaults are sufficient.
For example, when I set up or validate someone else's cloud configuration, I will always expect to see a protection layer documented and configured to ensure only the right people and services can reach it.
Anyway, more than enough from me on that subject. Just want to make sure that other people coming across this thread in the future are aware of the issues.
Thanks for your advise.I’ll check the security of my database service.
