6

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
Improve this question
3
  • Posting your HAProxy config would be a great help.
    – GregL
    Commented Oct 21, 2015 at 2:19
  • Review the HAProxy log for your connection, looking for the XX code that explains the reason each session disconnects. It sounds like HAProxy is configured for timeouts that are too short.
    – Michael - sqlbot
    Commented Oct 21, 2015 at 9:47
  • I went ahead and added the config file contents. I looked at so many examples and fixes that I started to get confused, here it is.
    – Hatem Jaber
    Commented Oct 21, 2015 at 13:27

2 Answers 2

Reset to default
11

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.

Improve this answer
9
  • That's the other issue that I'm facing also. When I connect from the Spring/Java app, the connection drops also during idle time. I'm trying to avoid having to wrap code with try/catch and working around it in that way. When I connect to any of the databases directly from the app it works fine, no timeouts. Does HaProxy not behave that way? Will it always be restricted to a timeout?
    – Hatem Jaber
    Commented Oct 21, 2015 at 13:43
  • 1
    According to the docs An unspecified timeout results in an infinite timeout, which is not recommended.. If you remove your the server and client timeout lines from the config it should allow the connections to stay open for ever. I'd still use one, but set it really high.
    – GregL
    Commented Oct 21, 2015 at 13:48
  • Does having connections with long timeout periods build up over time and cause issues with performance? I have been at this for 2 days and now realizing that there is really no other work around for this other than increasing the timeout.
    – Hatem Jaber
    Commented Oct 21, 2015 at 14:40
  • 1
    In fact, if you make the HAProxy timeout just a little longer than those in MySQL, you should be totally fine since MySQL will kill the connection before HAproxy does.
    – GregL
    Commented Oct 21, 2015 at 14:51
  • 1
    I encountered the same problem as OP as well. Turns out, it was a mismatch between my HAProxy timeouts, and the MySQL wait_timeout value. They should match, at least between the timeout server and the wait_timeout, otherwise you might have HAProxy keep a session open that the backend has terminated as idle.
    – Joshua Boniface
    Commented Feb 7, 2017 at 17:48
-2

Try to configure max_allowed_packet in mariaDB. Here is a relative link that may solve your problem.

http://stackoverflow.com/questions/10474922/error-2006-hy000-mysql-server-has-gone-away
Improve this answer
1
  • I'm afraid you're guessing.
    – Michael - sqlbot
    Commented Oct 21, 2015 at 9:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .