Mysql ssh tunnel with schedule trigger not closing connection

[si458]

Describe the problem/error/question

i created an workflow which queries our mysql server via ssh tunnel every minute,
gets data then runs a local command, and when it finishes, it puts the results into google sheets

but what ive seen is the MySQL SSH Tunnel is not CLOSING the connection after its done its query?

insteads its leaves it open and also lsof shows hundreds of CLOSE_WAIT connections too?

What is the error message (if any)?

lsof -Pni | grep :3306

sshd 77152 simon 10u IPv4 37912932 0t0 TCP 127.0.0.1:56174->127.0.0.1:3306 (CLOSE_WAIT)
sshd 77152 simon 11u IPv4 37913103 0t0 TCP 127.0.0.1:35722->127.0.0.1:3306 (CLOSE_WAIT)
sshd 77152 simon 12u IPv4 37913290 0t0 TCP 127.0.0.1:49184->127.0.0.1:3306 (CLOSE_WAIT)
sshd 77152 simon 13u IPv4 37916856 0t0 TCP 127.0.0.1:55408->127.0.0.1:3306 (CLOSE_WAIT)

Please share your workflow

Share the output returned by the last node

Information on your n8n setup

• n8n version: 1.108.2
• Database (default: SQLite): docker
• n8n EXECUTIONS_PROCESS setting (default: own, main): default
• Running n8n via (Docker, npm, n8n cloud, desktop app): docker
• Operating system: docker

[barn4k]

hello @si458

You can set the timeouts on the server side

[si458]

so please can you help me with what values need to be set?

as ive already googled and tried a few but they dont work and i still see this problem

[Mulen]

Basically it’s the application’s responsibility to close properly the TCP socket connection. Maybe n8n mysql node does not close it properly.

[si458]

from what im seeing it is running its query and then closing the mysql connection but not fully closing the ssh

ive even tested without a schedule and run normally and the same thing happens

click run workflow, does it thing, but leaves behind a CLOSE_WAIT

the only thing i havent tested is a new mysql server/vm

[barn4k]

Normally yes. But if it doesn’t work from the n8n side, we still have another option.

[barn4k]

you can change these values in the / etc / ssh / sshd_config:

ClientAliveInterval 60
ClientAliveCountMax 3

That means the connection should die in about 3 * 60 = 180 seconds

[si458]

sadly that hasnt worked either
(see attached pic .170 is my n8n, .242 is my mariadb on my librenms server)

it seems to open an ssh session and keep it open as expected
(unless its suppose to close the ssh after its been run)

but when it runs the mysql query and finishes, its not closing the connection afterwards?

also spotted if i deactivate my workflow, the ssh session is still active and doesnt close the connection?

[Mulen]

Can confirm this is happening. MySQL node via ssh tunnel leave the connections in CLOSE_WAIT state indefinitely

ss -tunp --tcp state close-wait dst :3306

Netid           Recv-Q           Send-Q                     Local Address:Port                      Peer Address:Port          Process
tcp             0                0                                  [::1]:54950                            [::1]:3306           users:(("sshd",pid=2357,fd=24))
tcp             0                0                                  [::1]:60624                            [::1]:3306           users:(("sshd",pid=2357,fd=21))
tcp             0                0                                  [::1]:54956                            [::1]:3306           users:(("sshd",pid=2357,fd=25))
tcp             0                0                                  [::1]:60644                            [::1]:3306           users:(("sshd",pid=2357,fd=23))
tcp             0                0                                  [::1]:60638                            [::1]:3306           users:(("sshd",pid=2357,fd=22))

@si458 You can kill them with the command below

ss -tunp --tcp state close-wait dst :3306 --kill

They have to fix the MySQL node to close the connection properly.

@barn4k I tried also to set the alive intervals, it does not work.