AW: [EXT] Re: error HAproxy with Galera Cluster v4

[Marno Krahmer]

Hey,

There actually is some stuff in the haproxy documentation about this:
https://docs.haproxy.org/2.9/configuration.html#4-option%20mysql-check

MySQL will block a client host when it does more unsuccessful authentication 
requests than configured in the global variable “max_connect_errors”.

This can happen when you do health check more frequently than “real” MySQL 
connection come it.

You can change the value of max_connect_errors according to the documentation: 
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connect_errors

Running a “FLUSH HOSTS;” on the affected MySQL node will (temporarily) solve 
that problem too.

If you don’t want to change that variable, you can either decrease the healh 
check interval, or could use a different health check mechanism.

In our company, we use a small script running on every MySQL-Node, that exposes 
an HTTP-Enpoint, reporting the MySQL-state.
Then haproxy is making a HTTP-Request for monitoring and allows us to configure 
expected response code & content.

Cheers
Marno


Von: Willy Tarreau 
Datum: Freitag, 10. Mai 2024 um 14:28
An: Iglesias Paz, Jaime 
Cc: haproxy@formilux.org 
Betreff: [EXT] Re: error HAproxy with Galera Cluster v4
Hello,

On Fri, May 10, 2024 at 12:00:17PM +, Iglesias Paz, Jaime wrote:
> Hey guys, I have a problem with HAProxy and Galera Cluster v4 MySQL (3 
> nodes). I boot the HAProxy server and it returns the following error:
>
> may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
> may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
> may 10 13:48:20 phaproxysql1 haproxy[661]: [NOTICE] 130/134820 (661) : New 
> worker #1 (663) forked
> may 10 13:48:20 phaproxysql1 systemd[1]: Started HAProxy Load Balancer.
> may 10 13:48:20 phaproxysql1 haproxy[663]: [WARNING] 130/134820 (663) : 
> Server galeramanagerprd/nodo1prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host 'X' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : 
> Server galeramanagerprd/nodo2prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host 'X' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : 
> Server galeramanagerprd/nodo3prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host '' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : 
> haproxy version is 2.2.9-2+deb11u6
> may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : path 
> to executable is /usr/sbin/haproxy
> may 10 13:48:21 phaproxysql1 haproxy[663]: [ALERT] 130/134821 (663) : proxy 
> 'galeramanagerprd' has no server available!
>
> The haproxy.cfg configuration file:
> 
> defaults
> log global
> modehttp
> option  httplog
> option  dontlognull
> timeout connect 5000
> timeout client  5
> timeout server  5
> errorfile 400 /etc/haproxy/errors/400.http
> errorfile 403 /etc/haproxy/errors/403.http
> errorfile 408 /etc/haproxy/errors/408.http
> errorfile 500 /etc/haproxy/errors/500.http
> errorfile 502 /etc/haproxy/errors/502.http
> errorfile 503 /etc/haproxy/errors/503.http
> errorfile 504 /etc/haproxy/errors/504.http
>
> listen galeramanagerprd
> bind *:3306
> balance source
> mode tcp
> #option tcplog
> option tcpka
> option mysql-check user haproxy
> server nodo1prd X:3306 check weight 1
> server nodo2prd X:3306 check weight 1
> server nodo3prd X:3306 check weight 1
> 
>
> (*) for security I change the IPs to X
>
> Reviewing the documentation I can't find where the problem may be.

That reminds me of something a long time ago, where there was a limit on
the number of check a mysql server would accept from a same IP address,
and it was necessary to change the setting to unlimited. I don't remember
the details but there was something to do using some insert commands. I
don't know if this is still needed after all these years, but the error
message strongly suggests something like this.

Willy

Re: error HAproxy with Galera Cluster v4

[Willy Tarreau]

Hello,

On Fri, May 10, 2024 at 12:00:17PM +, Iglesias Paz, Jaime wrote:
> Hey guys, I have a problem with HAProxy and Galera Cluster v4 MySQL (3 
> nodes). I boot the HAProxy server and it returns the following error:
> 
> may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
> may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
> may 10 13:48:20 phaproxysql1 haproxy[661]: [NOTICE] 130/134820 (661) : New 
> worker #1 (663) forked
> may 10 13:48:20 phaproxysql1 systemd[1]: Started HAProxy Load Balancer.
> may 10 13:48:20 phaproxysql1 haproxy[663]: [WARNING] 130/134820 (663) : 
> Server galeramanagerprd/nodo1prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host 'X' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : 
> Server galeramanagerprd/nodo2prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host 'X' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : 
> Server galeramanagerprd/nodo3prd is DOWN, reason: Layer7 wrong status, code: 
> 1129, info: "Host '' is blocked because of many connection errors; 
> unblock>
> may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : 
> haproxy version is 2.2.9-2+deb11u6
> may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : path 
> to executable is /usr/sbin/haproxy
> may 10 13:48:21 phaproxysql1 haproxy[663]: [ALERT] 130/134821 (663) : proxy 
> 'galeramanagerprd' has no server available!
> 
> The haproxy.cfg configuration file:
> 
> defaults
> log global
> modehttp
> option  httplog
> option  dontlognull
> timeout connect 5000
> timeout client  5
> timeout server  5
> errorfile 400 /etc/haproxy/errors/400.http
> errorfile 403 /etc/haproxy/errors/403.http
> errorfile 408 /etc/haproxy/errors/408.http
> errorfile 500 /etc/haproxy/errors/500.http
> errorfile 502 /etc/haproxy/errors/502.http
> errorfile 503 /etc/haproxy/errors/503.http
> errorfile 504 /etc/haproxy/errors/504.http
> 
> listen galeramanagerprd
> bind *:3306
> balance source
> mode tcp
> #option tcplog
> option tcpka
> option mysql-check user haproxy
> server nodo1prd X:3306 check weight 1
> server nodo2prd X:3306 check weight 1
> server nodo3prd X:3306 check weight 1
> 
> 
> (*) for security I change the IPs to X
> 
> Reviewing the documentation I can't find where the problem may be.

That reminds me of something a long time ago, where there was a limit on
the number of check a mysql server would accept from a same IP address,
and it was necessary to change the setting to unlimited. I don't remember
the details but there was something to do using some insert commands. I
don't know if this is still needed after all these years, but the error
message strongly suggests something like this.

Willy

error HAproxy with Galera Cluster v4

[Iglesias Paz, Jaime]

Hey guys, I have a problem with HAProxy and Galera Cluster v4 MySQL (3 nodes). 
I boot the HAProxy server and it returns the following error:

may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
may 10 13:48:20 phaproxysql1 haproxy[661]: Proxy stats started.
may 10 13:48:20 phaproxysql1 haproxy[661]: [NOTICE] 130/134820 (661) : New 
worker #1 (663) forked
may 10 13:48:20 phaproxysql1 systemd[1]: Started HAProxy Load Balancer.
may 10 13:48:20 phaproxysql1 haproxy[663]: [WARNING] 130/134820 (663) : Server 
galeramanagerprd/nodo1prd is DOWN, reason: Layer7 wrong status, code: 1129, 
info: "Host 'X' is blocked because of many connection errors; unblock>
may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : Server 
galeramanagerprd/nodo2prd is DOWN, reason: Layer7 wrong status, code: 1129, 
info: "Host 'X' is blocked because of many connection errors; unblock>
may 10 13:48:21 phaproxysql1 haproxy[663]: [WARNING] 130/134821 (663) : Server 
galeramanagerprd/nodo3prd is DOWN, reason: Layer7 wrong status, code: 1129, 
info: "Host '' is blocked because of many connection errors; unblock>
may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : haproxy 
version is 2.2.9-2+deb11u6
may 10 13:48:21 phaproxysql1 haproxy[663]: [NOTICE] 130/134821 (663) : path to 
executable is /usr/sbin/haproxy
may 10 13:48:21 phaproxysql1 haproxy[663]: [ALERT] 130/134821 (663) : proxy 
'galeramanagerprd' has no server available!

The haproxy.cfg configuration file:

defaults
log global
modehttp
option  httplog
option  dontlognull
timeout connect 5000
timeout client  5
timeout server  5
errorfile 400 /etc/haproxy/errors/400.http
errorfile 403 /etc/haproxy/errors/403.http
errorfile 408 /etc/haproxy/errors/408.http
errorfile 500 /etc/haproxy/errors/500.http
errorfile 502 /etc/haproxy/errors/502.http
errorfile 503 /etc/haproxy/errors/503.http
errorfile 504 /etc/haproxy/errors/504.http

listen galeramanagerprd
bind *:3306
balance source
mode tcp
#option tcplog
option tcpka
option mysql-check user haproxy
server nodo1prd X:3306 check weight 1
server nodo2prd X:3306 check weight 1
server nodo3prd X:3306 check weight 1


(*) for security I change the IPs to X

Reviewing the documentation I can't find where the problem may be.

Thank you very much for your help.

Jaime Iglesias Paz
Técnico Mant. Equipos e Sist. Informáticos
Axencia Instituto Enerxético de Galicia (INEGA)
Tel: 981 541 543
jaime.iglesias@xunta.gal
Avelino Pousa Antelo nº5 - Santiago de Compostela

[1673520051082]

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS PERSOAIS  (RXPD 2016/679 e LOPDGDD 
3/2018)

Responsable do tratamento: INSTITUTO ENERXÉTICO DE GALICIA (INEGA)
Finalidades do tratamento: A xestión da súa solicitude e a tramitación 
administrativa que no seu caso se poida derivar da mesma.
Lexitimación para o tratamento: O cumprimento dunha tarefa en interese público 
ou o exercicio de poderes públicos, segundo o artigo 6.1,e) do RXPD. 
Consentimento das persoas interesadas, cando corresponda.
Destinatarios dos datos: As Administracións Públicas no exercicio das súas 
competencias, cando sexa necesario para a xestión da súa solicitude.
Exercicio de dereitos: As persoas interesadas poderán solicitar o acceso, 
rectificación ou supresión dos seus datos, así como exercitar outros dereitos 
ou retirar no seu caso o consentimento outorgado a través da sede electrónica, 
no enderezo físico do INEGA ou enviando un correo electrónico a 
protecciondedatos.in...@xunta.gal
Contacto delegado de protección de datos e información adicional: 
dpd.c...@xunta.gal   /  
protecciondedatos.in...@xunta.gal
Confidencialidade: A información contida nesta comunicación é confidencial. No 
caso de que vostede non sexa o destinatario autorizado, rogámoslle que nolo 
comunique e proceda á súa eliminación.