ProxySQL reset connection if it starts with COM_QUIT #3342
Comments
|
Hi @spumer . We can evaluate how easy or complicated is to implement this. |
|
Hi, after further inspection we have diagnosed which one was the underlying issue to this. Looking at the connections that are being terminated by ProxySQL, we can see the exact conditions in which each of those connections are terminated. ProxySQL was going to reply with a 'greeting response' packet when the connection is terminated, at this point ProxySQL is handling the connection just fine. But the state of the dataflow from the datastream is unexpected, since there is data in both ends of it, situation that should never happens, so, why it's taking place here? Theory of the issue was the following:
This explanation is also backed up by the following experimental data. Fist this traffic capture from the unpatched version of ProxySQL: There is repeated pattern, we can see there are messages of size 121 sent from the client to ProxySQL these are the auth requests by HAProxy: So, the only reason there are more than 1 consecutive of these packets, is because of the connections that ProxySQL is closing, because they hit the timing described previously, and HAProxy is retrying, in fact, if more of 4 of these packets are founds, HAProxy will consider the server to be down. If this is correct, to prevent this situation, the simpler solution would be to enforce ProxySQL to always sent the 'initial handshake packet' as soon as it's generated, this way we will be preventing ProxySQL finding data at both ends of the data stream. After a minimal patch: We can observe how the traffic being capture has changed: Now the pattern is exactly the same as connecting directly to MySQL, no further messages are displayed in HAProxy, and no messages are displayed in ProxySQL informing about the termination of a connection. |
Closes #3342: ProxySQL reset connection if it starts with COM_QUIT
Closes #3342: ProxySQL reset connection if it starts with COM_QUIT
|
Test on 2.0.18 Same problem on HAProxy side, but now no errors on ProxySQL side
|
|
Hi @spumer , I tested again in v2.0.18, and HAProxy version: And I don't see any issues either in ProxySQL side, or in HAProxy side, HAProxy log stays clear, with no signs of healthchecks failing. Besides, my output captures are also clean as the ones I previously shared (I'm adding a wireshark output too for clarity):
As you can see, the packets with length
Since I can't reproduce the issue you are experiencing, could you please either supply further information about:
Or alternatively a reproducible test case showing your issue. Thank you. |
|
Yea, sorry for that. I had to provide it from the beggining :) # lsb_release -r
Release: 18.04
# haproxy
HA-Proxy version 1.8.8-1ubuntu0.11 2020/06/22
# uname -r
4.15.0-140-generic# cat /etc/haproxy/haproxy.cfg
global
user haproxy
group haproxy
daemon
defaults
log global
retries 2
timeout connect 5000
timeout client 50000
timeout server 50000
listen mysql-cluster
bind 127.0.0.1:4040
mode tcp
option mysql-check user haproxy_check
option log-health-checks
balance roundrobin
server mysql-1 xx.xx.xx.xx:3306 check
server mysql-2 xx.xx.xx.xx:3306 check
server mysql-3 188.127.xx.xx:6033 checkNo error logs, this messages occured only on haproxy restart # cat /var/log/haproxy.log.1
Apr 11 07:35:56 localhost haproxy[103512]: [WARNING] 099/202615 (103512) : Reexecuting Master process
Apr 11 07:37:38 localhost haproxy[103512]: [WARNING] 100/073556 (103512) : Exiting Master process...
Apr 11 07:37:38 localhost haproxy[103512]: [ALERT] 100/073556 (103512) : Current worker 49380 exited with code 143
Apr 11 07:37:38 localhost haproxy[103512]: [WARNING] 100/073556 (103512) : Former worker 103514 exited with code 143
Apr 11 07:37:38 localhost haproxy[103512]: [WARNING] 100/073556 (103512) : All workers exited. Exiting... (143) |
|
Hi @spumer, testing your setup I have found that the version of HAProxy that you use, uses the old Protocol::HandshakeResponse320, this response is extremely old, and unsupported by ProxySQL, looks like HAProxy defaulted to Reason why I didn't experienced your issue. If you replace this line of you config file: in favor of this one: The problem should go away. Please confirm if this solves your issue. Thank you. |
Problem is gone! Thank you very much! |
Description
I'm trying make zero-downtime migration from MASTER (MariaDB) to BALANCER (ProxySQL)
Now i have installed haproxy on my machines and all local applications use that address.
Now HAProxy balance connection between two MASTER IPs (main and reserved).
I have read-only replica (MariaDB) and want split traffic. For that i was add balancer (ProxySQL) address to HAProxy config.
ProxySQL address tagged as unhealthy and traffic not routed to him.
I'm investigate that issue and here is results.
Issue
HAProxy check mysql health status by sending
COM_QUITto the server and waitOKand close connection (see here)This behaviour described in MySQL protocol official documentation
MariaDB flow (got
OK, all fine)ProxySQL flow (no
OKresponse, TCP RST only):Error log
ProxySQL print this errors for each HAProxy connection attempt:
2021-03-13 17:42:58 mysql_data_stream.cpp:411:check_data_flow(): [ERROR] Session=0x7f53a545b800, DataStream=0x7f53a5448c00 -- Data at both ends of a MySQL data stream: IN <1 bytes 0 packets> , OUT <1 bytes 0 packets>
2021-03-13 17:42:58 MySQL_Thread.cpp:4439:process_all_sessions(): [WARNING] Closing unhealthy client connection 94.19x.xx.xx:51320
The text was updated successfully, but these errors were encountered: