[MDEV-25817] after setting proxy_protocol_networks , success logon cannot reset connect error count Created: 2021-05-29  Updated: 2021-06-05

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: William Wong Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

linux x86-64

Attachments: Text File proxy-protocol-bug-testcase.txt    

 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 ~]#


Generated at Thu Feb 08 09:40:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.