Chris West Chris West - 1 month ago 6
PHP Question

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 wich 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.

Heres 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


I appreciate any help that Leeds me to an solution in this problem

Answer

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