Bug #90307 host blocking limit seems not to be used correctly
Submitted: 5 Apr 5:52 Modified: 5 Apr 11:28
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.4-rc-log OS:Any
Assigned to: CPU Architecture:Any
Tags: blocking, host_cache, performance_schema

[5 Apr 5:52] Simon Mudd
Description:
I notice on a server I've been testing on the following:

1. Global variables has:

| max_connect_errors            | 15000                                  |

2. Checking the host_cache I see:

oot@host [performance_schema]> select host, sum_connect_errors, COUNT_HOST_BLOCKED_ERRORS from host_cache where sum_connect_errors > 0;
+----------------------------------------+--------------------+---------------------------+
| host                                   | sum_connect_errors | COUNT_HOST_BLOCKED_ERRORS |
+----------------------------------------+--------------------+---------------------------+
| host-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |              15002 |                      6765 |
+----------------------------------------+--------------------+---------------------------

The connect error on this host was due to the use of the sha256 caching plugin and the client library not understanding how to connect to MySQL.

Anyway the issue I see here (while small) is that the limit in the number of allowed connections is 15000, whereas there were 15002 connect errors. I'd expect to only see 15000.  Looks like an "off by 2" error.

How to repeat:
see above.

Suggested fix:
Limit the number of connections to 15000 at which point you start to block.
[5 Apr 5:52] Simon Mudd
To clarify: seen on 8.0.4-rc. Not checked other versions of MySQL.
[5 Apr 5:58] Simon Mudd
Related: https://bugs.mysql.com/bug.php?id=82727
[5 Apr 6:01] Simon Mudd
The previous bug is related as I'd like to do the following which does not work:

root@host [performance_schema]> delete from host_cache where sum_connect_errors > 0;
ERROR 1142 (42000): DELETE command denied to user 'root'@'localhost' for table 'host_cache'
[5 Apr 11:22] Umesh Shastry
Hello Simon,

Thank you for the report and feedback.
I'm mostly seeing "off by 1" in my 20+ attempts.

Thanks,
Umesh
[5 Apr 11:24] Umesh Shastry
-- 8.0.4

rm -rf 90307
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/90307 --log-error-verbosity=3
bin/mysqld --no-defaults --max_connect_errors=15000 --basedir=$PWD --datadir=$PWD/90307 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/90307/log.err --log-error-verbosity=3 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.4: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> create user 'ushastry'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'ushastry'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 15000 |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> select host, sum_connect_errors, COUNT_HOST_BLOCKED_ERRORS from host_cache where sum_connect_errors > 0;
Empty set (0.00 sec)

-- 5.7.21 clients

- first session

/export/umesh/server/binaries/GABuilds/mysql-5.7.21: for i in {1..10000}; do bin/mysql -uushastry -h hod03 --protocol=tcp --port=3333; done
.
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

- second session

/export/umesh/server/binaries/GABuilds/mysql-5.7.21: for i in {1..15100}; do bin/mysql -uroot -h hod03 --protocol=tcp --port=3333; done
.
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

-- mysql cli session

mysql> select host, sum_connect_errors, COUNT_HOST_BLOCKED_ERRORS from host_cache where sum_connect_errors > 0;
Empty set (0.00 sec)

.
mysql> select host, sum_connect_errors, COUNT_HOST_BLOCKED_ERRORS,HOST_VALIDATED from host_cache where sum_connect_errors > 0;
+---------------------+--------------------+---------------------------+----------------+
| host                | sum_connect_errors | COUNT_HOST_BLOCKED_ERRORS | HOST_VALIDATED |
+---------------------+--------------------+---------------------------+----------------+
| hod03.no.oracle.com |              15001 |                     25199 | YES            |
+---------------------+--------------------+---------------------------+----------------+
1 row in set (0.00 sec)
[5 Apr 11:25] Umesh Shastry
Screenshot

Attachment: 90307.png (image/png, text), 60.40 KiB.

[5 Apr 11:28] Simon Mudd
The difference may be due to lack of locking and concurrency triggering the off by 2.
It's not a big deal but if the code is not correctly handling the edge case (your confirmed off by 1 issue) then that should be addressed.

If performance issues make it undesirable to fix this then docs should be updated to indicate the limit is an approximate and not hard limit as might be expected. (that may be fine)