MySQL Bugs: #69807: Host cache counter isn't reset on valid connection

Bug #69807	Host cache counter isn't reset on valid connection
Submitted:	21 Jul 2013 22:54	Modified:	1 Aug 2013 7:26
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Windows (7)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
It does not appear that the HOST_CACHE table - and the underlying cache behavior itself - reflects the documented expected behavior which resets error counters for a host following a successful connection:

"If more than this many *successive* connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established *successfully* within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100 as of MySQL 5.6.6, 10 before that. "

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_connect_err...

(emphasis mine)

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SET GLOBAL max_connect_errors=
2;FLUSH HOSTS;SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_sch
ema.host_cache;"

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.6.11-winx64>telnet 192.168.2.8 3308 > nul

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  0 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  0 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>telnet 192.168.2.8 3308 > nul

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SET GLOBAL max_connect_errors=
2;FLUSH HOSTS;SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_sch
ema.host_cache;"

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.6.11-winx64>start telnet 192.168.2.8 3308

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  1 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  1 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>start telnet 192.168.2.8 3308

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  2 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  2 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>start telnet 192.168.2.8 3308

C:\mysql-5.6.11-winx64>bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM
_CONNECT_ERRORS FROM performance_schema.host_cache;"
+-------------+----------------+--------------------+
| IP          | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-------------+----------------+--------------------+
| 192.168.2.8 | YES            |                  3 |
+-------------+----------------+--------------------+

C:\mysql-5.6.11-winx64>bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
ERROR 1129 (HY000): Host '192.168.2.8' is blocked because of many connection err
ors; unblock with 'mysqladmin flush-hosts'

C:\mysql-5.6.11-winx64>

How to repeat:
bin\mysql -uroot -P3308 -e"SET GLOBAL max_connect_errors=2;FLUSH HOSTS;SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
start telnet 192.168.2.8 3308
timeout 11
bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
start telnet 192.168.2.8 3308
[wait until telnet times out]
bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"
bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
start telnet 192.168.2.8 3308
[wait until telnet times out]
bin\mysql -uroot -P3308 -e"SELECT IP, HOST_VALIDATED, SUM_CONNECT_ERRORS FROM performance_schema.host_cache;"
bin\mysql -utest_ps -P3308 -h192.168.2.8 -e"SELECT 1;"

Suggested fix:
1.  Ensure successful connections reset the host cache tracking for max_connect_errors as described in the manual.

This is a regression from 5.5:

C:\mysql-5.5.27-winx64>bin\mysql -uroot -P3307 -e"SET GLOBAL max_connect_errors=
2;FLUSH HOSTS;"

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8
ERROR 1129 (HY000): Host 'Crowder' is blocked because of many connection errors;
 unblock with 'mysqladmin flush-hosts'

C:\mysql-5.5.27-winx64>bin\mysql -uroot -P3307 -e"FLUSH HOSTS;"

C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit;
Bye

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8 -e"SELECT 1;"
+---+
| 1 |
+---+
| 1 |
+---+

C:\mysql-5.5.27-winx64>start telnet 192.168.2.8 3307

C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I am not sure if the problem is with host cache or (only) with P_S. Maybe just P_S does not *mirror* the host cache here?

Hi Peter,

The problem is with the host cache itself (and I'll speculate that the behavior regarding resetting the host-specific counter towards max_connect_errors was changed during implementation of P_S.HOST_CACHE).  This can be demonstrated without querying P_S.HOST_CACHE, as I did with 5.5.27 below:

1.  Create an account allowing access from any host.
2.  Confirm it works from remote host
3.  Using root, set global max_connect_errors to 2 and issue FLUSH HOSTS
4.  Do telnet from remote host to MySQL Sever instance twice, wait for timeout during handshake.
5.  Establish new connection from same remote host using account created in 1.
6.  Do telnet from remote host to MySQL Server instance once, wait for timeout during handshake.
7.  Try to establish new connection from same remote host using same account.

In 5.5.27, step #7 succeeds.  In 5.6.11, it fails.

The good news in all of this is that P_S.HOSTS allows DBAs to monitor whether hosts are approaching or exceeding max_connect_errors and take appropriate action (FLUSH HOSTS) to avoid affecting deployments.

[29 Jul 2013 11:42] Paul Dubois

Noted in 5.6.14, 5.7.2 changelogs.

A successful connection failed to reset the per-IP address counter
used to count successive connection failures. This could possibly
cause a host to be blocked, when the max_connect_errors limit was
reached.