Haproxy + Mysql Lost connection to MySQL server during query
3
votes
1
answer
8255
views
I have setup a master-slave replication and added a haproxy before them.
#HA-Proxy version 1.4.24
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
user haproxy
group haproxy
daemon
defaults
log global
mode http
option httplog
option dontlognull
contimeout 5000
clitimeout 50000
srvtimeout 50000
errorfile 400 /etc/haproxy/errors/400.http
errorfile 403 /etc/haproxy/errors/403.http
errorfile 408 /etc/haproxy/errors/408.http
errorfile 500 /etc/haproxy/errors/500.http
errorfile 502 /etc/haproxy/errors/502.http
errorfile 503 /etc/haproxy/errors/503.http
errorfile 504 /etc/haproxy/errors/504.http
frontend ft_readonly_mysql
mode tcp
timeout client 50000
option clitcpka
bind *:3308
default_backend bk_readonly_mysql
backend bk_readonly_mysql
mode tcp
option srvtcpka
timeout server 50000
option mysql-check user haproxy_check
server mysql1 127.0.0.1:3306 check
server mysql2 172.16.19.250:3306 check
If my
pymysql
client connect to mysql via 3306 directly, it works well. If connect to mysql via haproxy 3308 port, it will throw OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query')
every several minutes.
I doubted it was due to haproxy config so that I have added clitcpka
, srvtcpka
, timeout client
, timeout server
. But it still throws Lost connection
.
Update 0:
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
Asked by gzc
(323 rep)
Mar 22, 2016, 09:52 AM
Last activity: Feb 22, 2025, 05:02 AM
Last activity: Feb 22, 2025, 05:02 AM