Bug #99701 mysql_router return Too many connection errors
Submitted: 27 May 2020 3:01 Modified: 28 May 2020 16:36
Reporter: pc leon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Router Severity:S2 (Serious)
Version:8.0.20 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[27 May 2020 3:01] pc leon
Description:
mysqlrouter:
MySQL Router  Ver 8.0.20 for Linux on x86_64 (MySQL Community - GPL)
mysql:
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

[dba@test-dba-mysql02-1 ~]$ mysql -utest -p -h192.168.1.200 -P6446
Enter password:
ERROR 1129 (HY000): Too many connection errors from 172.16.1.220

mysql-router logs:

2020-05-26 16:18:59 routing INFO [7f07254d4700] [routing:cluster_rw] 4 connection errors for 192.168.1.200:49296 (max 100)
2020-05-26 16:18:59 routing INFO [7f07271f1700] [routing:cluster_rw] 5 connection errors for 192.168.1.200:49246 (max 100)
2020-05-26 16:18:59 routing INFO [7f0826beb700] [routing:cluster_rw] 6 connection errors for 192.168.1.200:49253 (max 100)
2020-05-26 16:18:59 routing INFO [7f07267e7700] [routing:cluster_rw] 7 connection errors for 192.168.1.200:49269 (max 100)
2020-05-26 16:18:59 routing INFO [7f0724bcb700] [routing:cluster_rw] 8 connection errors for 192.168.1.200:49160 (max 100)
2020-05-26 16:18:59 routing INFO [7f07254d4700] [routing:cluster_rw] fd=21 Pre-auth socket failure 192.168.1.200:49282: Copy client->server failed: unexpected conn
ection close
2020-05-26 16:18:59 routing INFO [7f08259d9700] [routing:cluster_rw] 9 connection errors for 192.168.1.200:49164 (max 100)
2020-05-26 16:18:59 routing INFO [7f0824fcf700] [routing:cluster_rw] 10 connection errors for 192.168.1.200:49176 (max 100)
2020-05-26 16:18:59 routing INFO [7f0724ccc700] [routing:cluster_rw] 11 connection errors for 192.168.1.200:49189 (max 100)
2020-05-26 16:18:59 routing INFO [7f08256d6700] [routing:cluster_rw] 12 connection errors for 192.168.1.200:49196 (max 100)
2020-05-26 16:18:59 routing INFO [7f0824bcb700] [routing:cluster_rw] 13 connection errors for 192.168.1.200:49175 (max 100)
2020-05-26 16:18:59 routing INFO [7f06270f0700] [routing:cluster_rw] 14 connection errors for 192.168.1.200:49185 (max 100)
2020-05-26 16:18:59 routing INFO [7f0825ddd700] [routing:cluster_rw] 15 connection errors for 192.168.1.200:49172 (max 100)
2020-05-26 16:18:59 routing INFO [7f0825cdc700] [routing:cluster_rw] 16 connection errors for 192.168.1.200:49206 (max 100)
2020-05-26 16:18:59 routing INFO [7f0724dcd700] [routing:cluster_rw] 17 connection errors for 192.168.1.200:49205 (max 100)
2020-05-26 16:18:59 routing INFO [7f07248c8700] [routing:cluster_rw] 18 connection errors for 192.168.1.200:49166 (max 100)
2020-05-26 16:18:59 routing INFO [7f0825ada700] [routing:cluster_rw] 19 connection errors for 192.168.1.200:49170 (max 100)
2020-05-26 16:18:59 routing INFO [7f0627efe700] [routing:cluster_rw] 20 connection errors for 192.168.1.200:49209 (max 100)
2020-05-26 16:18:59 routing INFO [7f0627dfd700] [routing:cluster_rw] 21 connection errors for 192.168.1.200:49165 (max 100)
2020-05-26 16:18:59 routing INFO [7f08243c3700] [routing:cluster_rw] 22 connection errors for 192.168.1.200:49207 (max 100)
2020-05-26 16:18:59 routing INFO [7f0727bfb700] [routing:cluster_rw] 23 connection errors for 192.168.1.200:49223 (max 100)
2020-05-26 16:18:59 routing INFO [7f08272f2700] [routing:cluster_rw] 24 connection errors for 192.168.1.200:49228 (max 100)
2020-05-26 16:18:59 routing INFO [7f07273f3700] [routing:cluster_rw] 25 connection errors for 192.168.1.200:49230 (max 100)
2020-05-26 16:18:59 routing INFO [7f08270f0700] [routing:cluster_rw] 26 connection errors for 192.168.1.200:49236 (max 100)
2020-05-26 16:18:59 routing INFO [7f0724fcf700] [routing:cluster_rw] 27 connection errors for 192.168.1.200:49187 (max 100)
2020-05-26 16:18:59 routing INFO [7f08249c9700] [routing:cluster_rw] 28 connection errors for 192.168.1.200:49215 (max 100)

How to repeat:
i can't repeat it.
but i get this error by these steps:

create a innodb_cluster
then use sysbench to write it:
sysbench --mysql-host=192.168.1.200 --mysql-port=6446 --mysql-user=test --mysql-password=xxx --mysql-db=sbtest --oltp-tables-count=5 --oltp-table-size=1000000 --num-threads=20 --max-requests=10000 --report-interval=1 --max-time=300 --test=/home/dba/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua run

then kill -9 on the primary_node and recovery , several times later i got the error upon.
i can connect primary_node ,secondary_node not use router.
then i retry to connect many times by router, even after few days later, it still not works. 
i have to restart mysqlrouter to fix it .

Is that a bug?
[27 May 2020 9:00] MySQL Verification Team
Hi,

How is your router configured?

all best
Bogdan
[27 May 2020 9:49] pc leon
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/data/cluster/log
runtime_folder=/data/cluster/run
data_folder=/data/cluster/data
keyring_path=/data/cluster/data/keyring
master_key_path=/data/cluster/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/data/cluster/data/state.json

[logger]
level = INFO

[metadata_cache:cluster]
cluster_type=gr
router_id=1
user=mysql_router1_l34q8b02u3bq
metadata_cluster=cluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:cluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://cluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:cluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:cluster_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://cluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:cluster_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

i generate it by using bootstrap
[27 May 2020 9:55] pc leon
i check the doc again , found max_connect_errors params default 100
and the doc said 
"If a connection from a host 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. To flush the host cache, execute a FLUSH HOSTS statement, a TRUNCATE TABLE statement that truncates the Performance Schema host_cache table, or a mysqladmin flush-hosts command.“
so i should flush the host cache, but how to flush hosts in mysql_router? i can't connect on it , or i should do this on primary_node?
[28 May 2020 16:36] MySQL Verification Team
Hi,

You need to connect from "non-blocked" host in order to flush hosts. 

all best
Bogdan