TiDB can't support HAProxy mysql-check

[fatpa]

Environment:

• TiDB Version: v2.1.9
• HAProxy Version: v1.9.8

We use HAProxy as the load-balance service of the TiDB. When using HAProxy MySQL health checks for server testing (mysql-check), and we got failed. But using the same configuration on one of our MySQL instances, it works.

So i want to know that is the TiDB can support HAProxy mysql-check protocol, or my HAProxy setting got some trouble on TiDB?

[XuHuaiyu]

Hi, @fatpa, thanks for your feedback
Is there any message returned by the HAProxy MySQL health checker for why TiDB failed?

[fatpa]

The HAProxy statistics report page show all of the TiDB port status are DOWN, but the HAProxy log show nothing.

Our HAProxy setting just add

option mysql-check user haproxy_check

The mysql user is work when we use the mysql-client directly.

[morgo]

@fatpa The HA proxy logs should show an error such as this one:

Nov 15 00:08:51 localhost haproxy[1671]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 timeout, check duration: 2002ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

Note that TiDB defaults to port 4000 instead of 3306. It should otherwise work the same way as MySQL though.

[fatpa]

Hi @morgo, could you show me your HAProxy configuration?

[morgo]

@fatpa the MySQL part should look something like this guide but with port 4000. You can expose this as 3306 to applications still if you wish i.e:

listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.0.0.1:4000 check
    server mysql-2 10.0.0.2:4000 check

[fatpa]

@morgo It looks same as my configuration, but it can't work
Here is mine:

listen tidb
    bind 0.0.0.0:3306
    mode tcp
    option tcplog
    timeout connect 5s
    timeout client 1800s
    timeout server 1800s
    maxconn 4096
    option mysql-check user haproxy_check
    balance leastconn
    server mysql-1 10.0.0.1:4000 check inter 2000 rise 3 fall 3
    server mysql-2 10.0.0.2:4000 check inter 2000 rise 3 fall 3
    server mysql-3 10.0.0.3:4000 check inter 2000 rise 3 fall 3

[fatpa]

I'm sorry about the log, cos the log level is set as notify.
I change the log level and try it again, now i got the error msg:

May 27 15:18:27 localhost haproxy[4842]: Server tidb/test1 is DOWN, reason: Layer7 wrong status, code: 0, info: "malform packet error", check duration: 1ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

[morgo]

@fatpa Thank you for the error message. I believe this is a TiDB bug: #8812

It is fixed in TiDB 3.0. Do you mind testing to confirm?

@imtbkcat do you know if it is feasible to backport to 2.1?

[fatpa]

@morgo Thanks.
I will upgrade my TiDB Cluster to 3.0-rc version, and check if it can work or not.

[fatpa]

@morgo It works!!