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
Hatem Jaber
  • 163
  • 1
  • 7
  • Posting your HAProxy config would be a great help. – GregL Oct 21 '15 at 02: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 Oct 21 '15 at 09: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 Oct 21 '15 at 13:27

2 Answers2

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.

GregL
  • 9,370
  • 2
  • 25
  • 36
  • 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 Oct 21 '15 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 Oct 21 '15 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 Oct 21 '15 at 14:40
  • I would think keeping them around for ever would be bad long term, specifically from a resource usage perspective. How much time goes by before it becomes an issue would depend on your hardware and how many sessions are being re-created. Having timeouts many days in length will limit the number of times your clients throw an error, while still providing a way for HAProxy to purge old, idle connections. Ultimately, if the client ends the connection, HAProxy should pick that up and clear it out anyway. This is just for idle connections. – GregL Oct 21 '15 at 14:45
  • 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 Oct 21 '15 at 14:51
  • I set the wait_timeout in mysql/mariadb to a smaller value than the one in haproxy and still no luck, i'm still getting this error: `ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 78 Current database: *** NONE ***` – Hatem Jaber Oct 21 '15 at 15:45
  • If you're testing it from the command-line, you'd have to set `interactive_timeout`. – GregL Oct 21 '15 at 17:30
  • After playing with this for a while and finally getting my head wrapped around it, i realized after having a bunch of terminal windows open that each "session" has a timeout and that the timeout value is related to each one specifically. Working with only one terminal did not allow me to see what you were referring to when you made the change in the example to 10m, it's making sense. – Hatem Jaber Oct 26 '15 at 16:12
  • 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 Feb 07 '17 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
technoob
  • 142
  • 1
  • 14