Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5
-
None
-
None
-
linux x86-64
Description
Hi all,
Our real environment is using Galera + HAProxy and we enable proxy protocol to identify client address in DB server. Found success logon cannot reset connect error count.
Checked the problem happened to simple MariaDB with HAProxy. Attached a test case using simple mariadb and HAProxy on AWS EC2. Kindly help.
Regarsds,
William
[root@ip-172-31-34-25 ~]# mysql -e "select @@max_connect_errors ;"
|
+----------------------+
|
| @@max_connect_errors |
|
+----------------------+
|
| 5 |
|
+----------------------+
|
[root@ip-172-31-34-25 ~]# mysql -e "SHOW VARIABLES LIKE 'performance_schema' ;"
|
+--------------------+-------+
|
| Variable_name | Value |
|
+--------------------+-------+
|
| performance_schema | ON |
|
+--------------------+-------+
|
[root@ip-172-31-34-25 ~]# LOCAL_IP=$(ifconfig | grep -w inet | grep -v 127.0.0.1 | awk '{ print $2}')
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # ------------------------------------------------------------------------------
|
[root@ip-172-31-34-25 ~]# # test without proxy protocol
|
[root@ip-172-31-34-25 ~]# # ------------------------------------------------------------------------------
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # make 4 connect errors
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3306
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3306
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3306
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3306
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # check connect error count
|
[root@ip-172-31-34-25 ~]# mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"
|
+--------------+-------------------------------------------------+--------------------+
|
| ip | host | SUM_CONNECT_ERRORS |
|
+--------------+-------------------------------------------------+--------------------+
|
| 172.31.34.25 | ip-172-31-34-25.ap-southeast-1.compute.internal | 4 |
|
+--------------+-------------------------------------------------+--------------------+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # a success logon
|
[root@ip-172-31-34-25 ~]# mysql -h ${LOCAL_IP} -P 3306 -e "select 'OK' ;"
|
+----+
|
| OK |
|
+----+
|
| OK |
|
+----+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # check connect error count should be reset
|
[root@ip-172-31-34-25 ~]# mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"
|
+--------------+-------------------------------------------------+--------------------+
|
| ip | host | SUM_CONNECT_ERRORS |
|
+--------------+-------------------------------------------------+--------------------+
|
| 172.31.34.25 | ip-172-31-34-25.ap-southeast-1.compute.internal | 0 |
|
+--------------+-------------------------------------------------+--------------------+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # ------------------------------------------------------------------------------
|
[root@ip-172-31-34-25 ~]# # test with proxy protocol
|
[root@ip-172-31-34-25 ~]# # ------------------------------------------------------------------------------
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# mysql -e "set global proxy_protocol_networks='*' ;"
|
[root@ip-172-31-34-25 ~]# mysql -e "select @@proxy_protocol_networks ;"
|
+---------------------------+
|
| @@proxy_protocol_networks |
|
+---------------------------+
|
| * |
|
+---------------------------+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# mv /etc/haproxy18/haproxy.cfg /etc/haproxy18/haproxy.cfg.original
|
[root@ip-172-31-34-25 ~]# echo "listen haproxy_test" >> /etc/haproxy18/haproxy.cfg
|
[root@ip-172-31-34-25 ~]# echo " bind *:3307" >> /etc/haproxy18/haproxy.cfg
|
[root@ip-172-31-34-25 ~]# echo " mode tcp" >> /etc/haproxy18/haproxy.cfg
|
[root@ip-172-31-34-25 ~]# echo " server local-mariadb localhost:3306 send-proxy-v2" >> /etc/haproxy18/haproxy.cfg
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# cat /etc/haproxy18/haproxy.cfg
|
listen haproxy_test
|
bind *:3307
|
mode tcp
|
server local-mariadb localhost:3306 send-proxy-v2
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# systemctl restart haproxy18
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
 |
[root@ip-172-31-34-25 ~]# # make 4 connect errors
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3307
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3307
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3307
|
[root@ip-172-31-34-25 ~]# nc -z -w 1 ${LOCAL_IP} 3307
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # check connect error count
|
[root@ip-172-31-34-25 ~]# mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"
|
+--------------+-------------------------------------------------+--------------------+
|
| ip | host | SUM_CONNECT_ERRORS |
|
+--------------+-------------------------------------------------+--------------------+
|
| 172.31.34.25 | ip-172-31-34-25.ap-southeast-1.compute.internal | 4 |
|
+--------------+-------------------------------------------------+--------------------+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # a success logon
|
[root@ip-172-31-34-25 ~]# mysql -h ${LOCAL_IP} -P 3307 -e "select 'OK' ;"
|
+----+
|
| OK |
|
+----+
|
| OK |
|
+----+
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]#
|
[root@ip-172-31-34-25 ~]# # check connect error count should be reset BUT NOT
|
[root@ip-172-31-34-25 ~]# mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"
|
+--------------+-------------------------------------------------+--------------------+
|
| ip | host | SUM_CONNECT_ERRORS |
|
+--------------+-------------------------------------------------+--------------------+
|
| 172.31.34.25 | ip-172-31-34-25.ap-southeast-1.compute.internal | 4 |
|
+--------------+-------------------------------------------------+--------------------+
|
[root@ip-172-31-34-25 ~]#
|