Haproxy + Mysql Lost connection to MySQL server during query

Question

I have setup a master-slave replication and added a haproxy before them.

#HA-Proxy version 1.4.24

global
    log /dev/log    local0
    log /dev/log    local1 notice
    chroot /var/lib/haproxy
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode    http
    option  httplog
    option  dontlognull
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
    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

frontend ft_readonly_mysql
    mode tcp
    timeout client 50000
    option clitcpka
    bind *:3308
    default_backend bk_readonly_mysql

backend bk_readonly_mysql
    mode tcp
    option srvtcpka
    timeout server 50000
    option mysql-check user haproxy_check
    server mysql1 127.0.0.1:3306 check
    server mysql2 172.16.19.250:3306 check

If my pymysql client connect to mysql via 3306 directly, it works well. If connect to mysql via haproxy 3308 port, it will throw OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') every several minutes.

I doubted it was due to haproxy config so that I have added clitcpka, srvtcpka, timeout client, timeout server. But it still throws Lost connection.

Update 0:

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

score 1 · Answer 1 · answered Jan 06 '23 at 16:29

TL;DR: Keep the timeouts in HAProxy and MySQL / MariaDB the same.

I've run into this issue on and off when I fire some long running quries. I'm posting the relevant section from an article here that has worked well for me:

MySQL Server has gone away

This is usually caused when HAProxy has closed the connection due to timeout or the connection is closed on the server side. Sometimes, you could see this when the server is restarting or the connection has reached one of the following timeouts.

Our recommendation is to configure the @net_read_timeout@ and @net_write_timeout@ value in MySQL / MariaDB with the same value as for @timeout client@ and @timeout server@ in HAProxy configuration file.

Based on the above, your configuration should look like this for HAProxy:

# haproxy.cfg
...
defaults
        timeout client  3600
        timeout server  3600
...

You can also add the timeouts to a specific frontend or backend configuration in the HAProxy config

And like this for MySQL / MariaDB:

# my.cnf
[mysqld]
...
net_read_timeout = 3600
net_write_timeout = 3600
...

Here is an excerpt from another article questioning the same recommendation:

Please keep in mind that there's no magic number which is the best setting for given variable. In one case longer timeouts may work better, in other case you would prefer shorter ones. This is up to you to decide what is the best setting for your environment, application and workload. It doesn't have to me 10800 seconds. It may be 30, 60, 3600 or anything else.

Haproxy + Mysql Lost connection to MySQL server during query

1 Answers1