Killing SQL Connections

Hello,

I am struggeling with my amount of SQL Connections some time now.

But i think i found a solution.

i am able to get all current process ID's and theyre state.
My next idea is to retrieve all process ID's and kill them every hour.

But i have a Problem i cant get the ID in a variable.

The structure of the mesage is the following:

DB ID

The copied Path for the ID is: payload[0].Id

i use :
test = msg.payload[0].Id
msg.payload.test = test;

but i dont get anything in the Debug window.

what i am doing wrong?

Greetings

Chorum

1 - How many sql nodes do you have in your flows?
2 - What version of sql are you using - MySQL, MSSQL, SQLite?
3 - What is the full name of the sql node you are using - node-red-contrib-xxxxxx?
4 - What version of Node-RED are you using?

@Chorum the specific issue you have with the Function code is that you are trying to set a property of an Array type.

The Debug sidebar cannot show properties of Arrays other than its 'normal' contents.

So you could change your code to

msg.test = test

or

msg.payload = test

and you'll be able to see the value in the Debug sidebar.

And, what is the connection timeout set to? The connections should be cleaning themselves up as SQL nodes execute queries until the timeouts occur, I don't recall from memory, what the default SQL timeout is, but it is like 30 seconds or something like that. Also, what did you set the concurrent connection count to for the SQL server? I often set that value above 100, so I can let pooling handle the connections without direct intervention.

Hello,

  1. I had 3 SQL - Nodes , today i cut it down to 1. The SQL Node has 25 Connections but i use 34 Querys in 1.2 seconds. I have to test if i can use only one node.

  2. MySQL

  3. The Name of the Node is node-red-node-mysql

  4. Node Red Version 0.20.5

I will try that knolleary!

The Connectiontimeout is set to 300 seconds.
I will test now if my process list getting Shorter with only one DB Node.

Mhmm there is only one DB Node in my flow but under Configuration i see the Node with the Number "6" behind it ?

The following number, is the number of flows/nodes that are sharing that resource, i.e. configuration node.

I do the same, as I create flows, and notice that additional connections are set, where appropriate (most of the time) I go back and make sure the connection configuration node is shared. It is a bit of pain, but SQL configuration nodes, MQTT broker nodes, etc. all seem to create unique connections... which makes sense as the default behavior. But it would be nice if there was a way to consolidate the connections when desired. Maybe their is, and I just have not discovered it yet?