1

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    |
+----------------------------+----------+
4
  • seems like connection time out is not appropriate..look for show variables like '%timeout%'; to see what time out values you have set. Moreover after how much time it gives error? – Nawaz Sohail Mar 22 '16 at 10:15
  • see update 0. The first time I saw this error costed 5 mins. – gzc Mar 22 '16 at 10:28
  • I'm running into this issue currently. Seems like this is indeed a configuration issue on the HAProxy side per this thread: dba.stackexchange.com/q/61793/35702. Did you every solve your issue? – k427h1c Jul 5 '18 at 19:42
  • Threads stackoverflow.com/a/37426548/399435 and serverfault.com/a/730565/140709 also confirm that the timeout server and timeout client parameters need to tweaked on HAProxy to resolve this issue. I'm going to give this a shot and hopefully it'll resolve my issues. – k427h1c Jul 5 '18 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.