MySQL Bugs: #90307: host blocking limit seems not to be used correctly

Bug #90307	host blocking limit seems not to be used correctly
Submitted:	5 Apr 2018 5:52	Modified:	1 Nov 2018 16:09
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	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

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.

To clarify: seen on 8.0.4-rc. Not checked other versions of MySQL.

Related: https://bugs.mysql.com/bug.php?id=82727

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'

Hello Simon,

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

Thanks,
Umesh

-- 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)

Screenshot

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

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)

Posted by developer:
 
It is possible for host_cache.SUM_CONNECT_ERRORS to exceed the value of max_connect_errors, so this is not an error. I've added some explanation and an example at https://dev.mysql.com/doc/refman/8.0/en/host-cache-table.html to describe how this can occur.