Description:
There is no tcp keepalive for connection between client and mysql-proxy.
Tested with mysql-proxy versions 0.8.3, 0.8.4 and 0.8.5 running on CentOS versions 6.6 (2.6.32-504.3.3) and 5.9 (2.6.18-348) x86_64
This presents risk in case of network issue to leave inactive connections on mysql server that may even hold locks.
Imagine scenario:
Application wants to connects the proxy and perform the following:
1. lock tables table1,table2 write
2. run some query against the locked tables
3. unlock tables
What will happen if connection between the client and the proxy is lost before step 3 (for example physically remove the cable).
Looking at mysql server the connection will look like sleeping conniton, but it will exist and will not be dropped because the link between the proxy and mysql sever is ok! Tables will stay locked and so on ...
And on the mysql server it will be very difficult to distinguish this connection from the others, because all are from 127.0.0.1
If the client is connecting directly to mysql server, and connection is lost - mysql server is tcp keep alive "complaint" and will drop the connection and locks will be released.
mysql-proxy is the only option for query analysis and aggregation and qrti in Mysql Enterprise Monitor (when monitoring mysqls older that 5.6).
What I want to point is that if one wants to use mysql enterprise monitor for monitoring older versions - instead of making thinks better it is very possible to get the negative effect of too many connections..., table locks and... slow queries ...
How to repeat:
Here is simple case:
mysql client (192.168.200.213)
mysql proxy (192.168.200.200:4040)
mysql server (192.168.200.200:3306)
mysql-proxy and mysql server are on the same host, sysctl values are set as following on it:
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 2
net.ipv4.tcp_keepalive_intvl = 5
on the client machine connect to mysql through proxy:
# mysql --host=192.168.200.200 --port=4040 --user=proxusr -p123456
on the server check the status and timers of the connection
# netstat -napTo | grep 192.168.200.213
tcp 0 0 192.168.200.200:4040 192.168.200.213:51252 ESTABLISHED 39352/mysql-proxy off (0.00/0/0)
As you can see tcp keep alive is not active!!!
(This also can be checked with tcpdump)
Suggested fix:
In my case I used libkeepalive library that can enable tcp keepalive in programs using shared libraries and also keep alive parameters can be set per program (not system wide). http://libkeepalive.sourceforge.net/
Before starting mysql-proxy, make libkeepalive.so loadable
# export LD_PRELOAD=./libkeepalive.so
Start mysql-proxy
#./mysql-proxy ...
connect to from the client to the proxy, and on the mysql proxy host check connection status and timers
# netstat -napTo | grep 192.168.200.213
tcp 0 0 192.168.200.200:4040 192.168.200.213:51253 ESTABLISHED 39679/mysql-proxy keepalive (25.56/0/0)
This is working.
However I think that it would be better if proper tcp keep alive is implemented in mysql-proxy core.
