phpsymfonygalleryhaproxymysql-cluster

MySQL Server has gone away Error constantly appearing with haProxy


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

Solution

  • 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 :)