Behavior on active transaction when client connection is dropped about proxysql HOT 6 CLOSED

I was able to replicate the issue in a local environment and the problem happens when the client that holds the lock never sends a TCP FIN. In that case the lock stays alive. However, if I do the same directly to mysql server the behavior is the same.
To replicate the issue I ran a couple of containers with a mysqlserver + proxysql + custom docker network. Then, I attached another container to the network and with the mysql client started a transaction, ran select for update and then I dropped the container network interface with ifconfig eth0 down.

from proxysql.

@cldmartinez , the short answer is:

• if proxysql is not able to determine that the client dropped the connection (for example, it never sent a FIN) then it assumes the client is still there.

from proxysql.

Yes, I thought the only way to manage that would be having a network protocol with a keep alive mechanism but as far as I saw it is not the case. Thanks.

from proxysql.

ProxySQL can have TCP keepalive configured for client connections.
See mysql-tcp_keepalive_time and mysql-use_tcp_keepalive (false by default before 2.6.0)

from proxysql.

I will take a look.

from proxysql.

I tried and nothing happened (I am monitoring with tcpdump). Back to the definition "When mysql-use_tcp_keepalive is active, ProxySQL will start sending KeepAlive to the destination after the connection has been idle for tcp_keepalive_time seconds" it seems this mechanism apply for idle connection but in the case of one connection with an active transaction (my case) I wouldn't consider that connection as idle.

from proxysql.