MySQL Server has gone away Error constantly appearing with haProxy

Question

I'm getting this message quite often. I looked through a couple of solutions so far, but haven't found anything to solve this issue. But first here's my setup:

I using an PHP7 Environment which is split up into different elements for Scalability Reason.

1 NGINX WebServer
2-n Applications Server Using PHP7-FPM with haproxy & memcache
2-n Database Server with Gallery Cluster Configured
1 Job Server with PHP7-CLI & haproxy
1 login Server for Application Distribution (nfs) and System Maintenance

Login Server is also the Main Login for Developers

My problem is, that I get occasionally the following message:

Warning: PDOStatement::execute(): MySQL server has gone away

After restarting haproxy on either of the app server it works again for 3-10 Page views. But after it I get the same message again.

Please help me to find my mistake.

Here's my haproxy config:

global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

defaults
    log global
    retries 2
    timeout connect 10000
    timeout server 10000
    timeout client 10000


listen mysql-cluster
    log global
    timeout connect 10000
    timeout server 10000
    timeout client 10000
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user proxy
    balance roundrobin
    server db1  1.1.1.1:3306 check
    server db2  1.1.1.2:3306 check

This solved my problem, so leaving this for reference. https://serverfault.com/questions/730403/error-2006-hy000-mysql-server-has-gone-away-haproxy-galera — Krish, Jun 29 '18 at 23:36

score 1 · Accepted Answer · answered Oct 20 '16 at 22:32

1

After researching and Testing for a while I have found the core reason for my issues.

first of all, the permissions of the proxy user where not reflected onto the second server. And I had some issues with the write feature. So I added another Connection on Port 3307 which is responsible for writing. To keep the cluster Idea, I set the main database on connection one and the others as backup if the others fail.

I Setup everything according to the article at

http://severalnines.com/tutorials/mysql-load-balancing-haproxy-tutorial

thanks for the help of the people that gave me a call when they saw this post :)

answered Oct 20 '16 at 22:32

Chris West

741
13
36

What? Did you mean that you set up read/write splitting through HAProxy as per the link you shared? Or... something else? – Ernie Aug 03 '18 at 16:34
I have an multi master multi Server setup. So on one host (via virtual maschines) I have an master and one slave running. The master syncs with the others. To have some load balancing I use ha proxy, which is aware of the other masters and theire load. ha proxy has the open ports 3306 and 3307 (3306 for read only slave connection) (3307 for write operations). So the symfony app has itself two servers, that it can see. The haproxy on Port 3306 and 3307. Symfony doesn't have to do any loadbalancing. Everything is done through haproxy – Chris West Aug 06 '18 at 01:06

score 1 · Answer 2 · answered Dec 21 '18 at 09:05

1

Just adapt your timeout settings

timeout server 10m timeout client 10m

answered Dec 21 '18 at 09:05

Thibault Richard

356
2
11

MySQL Server has gone away Error constantly appearing with haProxy

2 Answers2