ERROR 2006 (HY000): MySQL Server Has Gone Away... (HaProxy / Galera)
I setup a cluster of 3 MariaDB servers in a multi-master setup which is working fine. I added a 4th server for HaProxy to load balance and round robin so that we can have a little redundancy which is also working, except for 1 issue.
I am logged into the MySQL client on the HaProxy server running the following query:
show variables like 'server_id';
I am getting the results, but if I wait for a period of 5 or more seconds and run the query again, i get the following error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 52
Current database: *** NONE ***
Right after that i get the result with a different server_id which indicates that the load balancing is working. The problem that this is causing is that when i try to connect from my Spring/Java app, I can run queries for that short period of time and the connection drops. I figure if I can solve it on the HaProxy server, that would solve the other issues that I am having.
EDIT: ADDED HAPROXY.CFG
global
log 127.0.0.1 local0 notice
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
user haproxy
group haproxy
maxconn 4096
daemon
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
listen mysql-cluster
bind 0.0.0.0:3306
mode tcp
option tcpka
option mysql-check user haproxy_check
balance roundrobin
server mysql-1 192.168.10.241:3306 check inter 1000 rise 3 fall 1
server mysql-2 192.168.10.242:3306 check inter 1000 rise 3 fall 1
server mysql-3 192.168.10.243:3306 check inter 1000 rise 3 fall 1
listen stats
bind 192.168.10.211:8080
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth USER:PASSWORD
For a long-running session that's idle, like running mysql
on the command-line, your timeout client
and timeout server
are much too short.
They're probably fine for a web server hitting the DB cause the connection is only open for as long as it needs, and each page load creates a new connection.
To allow the connections to stay open for 10 minutes, change
timeout server 5000
timeout client 5000
to
timeout server 10m
timeout client 10m
You can go even higher if you want. I've got RDS clusters that have a 7 hour timeout and it works just fine.
The docs have more details about timeout client, timeout server, and the short-hand used for the time specifier.