Mysql – ERROR 2006 (HY000): MySQL Server Has Gone Away… (HaProxy / Galera)

haproxyjavamariadbMySQL

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

Best Answer

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.

Related Topic