Hi Team,
I have recently upgraded Percona MySQL server from 5.7 to 8.0.39, I frequently see timeout issue.
For timeout after executing flush hosts, the issue is resolved. Want to know why this is happening? Is it the issue with the mysql client or db setting ? I have max connections set to 1500
{
"result": "FAILURE",
"messages": [
"x_db_pool_SQA - Connection is not available, request timed out after 15000ms."
]
}
2024-12-11T17:31:30.050Z ERROR 1305682 --- [0.1-8012-exec-1] c.abc.event.dao.MySQLEventServiceDao : Failed to obtain JDBC Connection
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection
Caused by: java.sql.SQLException: CLI-specific condition, message from server: "Host '00.00.00.00' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1500 |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> SHOW STATUS WHERE variable_name = 'Threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 232 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(host) FROM information_schema.processlist;
+-------------+
| COUNT(host) |
+-------------+
| 239 |
+-------------+
1 row in set, 1 warning (0.00 sec)
Thanks
Hello @shirisha
Host ‘00.00.00.00’ is blocked because of many connection errors;
This means that your application is connecting to MySQL, but failing to auth (bad password), and others. Repeated failures makes MySQL block them. This should not ever happen in a normal production environment. You should examine the application logs to determine the reasons for failure.
You can also SELECT * FROM performance_schema.host_cache; and see errors from connection attempts.
If you’re using hostnames/DNS, consider switching to pure IP, as DNS can be a source of connection failure if MySQL cannot resolve the hostname.
I see this output. Can you please help me to debug or fix this ?
Note: I have masked the IP’s
mysql> SELECT * FROM performance_schema.host_cache;
+----------------+------+----------------+--------------------+---------------------------+---------------------------------+---------------------------------+---------------------+---------------------------------+---------------------------------+---------------------+-----------------------+-----------------------------+--------------------------+------------------------+-------------------------+-----------------------------+-----------------------------+------------------+-----------------------------------+--------------------------------------------+-------------------------------+---------------------------+--------------------+----------------------+---------------------+---------------------+---------------------+---------------------+
| IP | HOST | HOST_VALIDATED | SUM_CONNECT_ERRORS | COUNT_HOST_BLOCKED_ERRORS | COUNT_NAMEINFO_TRANSIENT_ERRORS | COUNT_NAMEINFO_PERMANENT_ERRORS | COUNT_FORMAT_ERRORS | COUNT_ADDRINFO_TRANSIENT_ERRORS | COUNT_ADDRINFO_PERMANENT_ERRORS | COUNT_FCRDNS_ERRORS | COUNT_HOST_ACL_ERRORS | COUNT_NO_AUTH_PLUGIN_ERRORS | COUNT_AUTH_PLUGIN_ERRORS | COUNT_HANDSHAKE_ERRORS | COUNT_PROXY_USER_ERRORS | COUNT_PROXY_USER_ACL_ERRORS | COUNT_AUTHENTICATION_ERRORS | COUNT_SSL_ERRORS | COUNT_MAX_USER_CONNECTIONS_ERRORS | COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS | COUNT_DEFAULT_DATABASE_ERRORS | COUNT_INIT_CONNECT_ERRORS | COUNT_LOCAL_ERRORS | COUNT_UNKNOWN_ERRORS | FIRST_SEEN | LAST_SEEN | FIRST_ERROR_SEEN | LAST_ERROR_SEEN |
+----------------+------+----------------+--------------------+---------------------------+---------------------------------+---------------------------------+---------------------+---------------------------------+---------------------------------+---------------------+-----------------------+-----------------------------+--------------------------+------------------------+-------------------------+-----------------------------+-----------------------------+------------------+-----------------------------------+--------------------------------------------+-------------------------------+---------------------------+--------------------+----------------------+---------------------+---------------------+---------------------+---------------------+
| 11.11.11.11 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13990 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 08:29:45 | 2024-12-16 18:02:47 | 2024-12-16 08:29:45 | 2024-12-16 18:02:43 |
| 12.12.12.12 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 16:06:32 | 2024-12-16 17:46:21 | 2024-12-16 16:06:32 | 2024-12-16 16:06:32 |
| 13.13.13.13 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 13:15:51 | 2024-12-16 17:14:01 | 2024-12-16 13:15:51 | 2024-12-16 13:15:51 |
| 14.14.14.14 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 08:29:43 | 2024-12-16 15:48:37 | 2024-12-16 08:29:43 | 2024-12-16 08:29:43 |
| 15.15.15.15 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 13:54:58 | 2024-12-16 15:01:58 | 2024-12-16 13:54:58 | 2024-12-16 13:54:58 |
| 16.16.16.16 | NULL | YES | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-12-16 11:03:23 | 2024-12-16 13:58:52 | 2024-12-16 11:03:23 | 2024-12-16 11:03:23 |
+----------------+------+----------------+--------------------+---------------------------+---------------------------------+---------------------------------+---------------------+---------------------------------+---------------------------------+---------------------+-----------------------+-----------------------------+--------------------------+------------------------+-------------------------+-----------------------------+-----------------------------+------------------+-----------------------------------+--------------------------------------------+-------------------------------+---------------------------+--------------------+----------------------+---------------------+---------------------+---------------------+---------------------+
6 rows in set (0.00 sec)
mysql> select ip,COUNT_HANDSHAKE_ERRORS FROM performance_schema.host_cache WHERE IP='11.11.11.11';
+---------------+------------------------+
| ip | COUNT_HANDSHAKE_ERRORS |
+---------------+------------------------+
| 11.11.11.11 | 13999 |
+---------------+------------------------+
1 row in set (0.00 sec)
Yep, now you know that host is having some issue connecting to MySQL. This could be SSL related, or older client library trying to connect to newer MySQL, or could be a health check, firewall, load-balancer, etc.
You can increase max_connect_errors which will reduce the time between host flushes, but it does not address the underlying issue causing the handshake errors. It’s essential to identify and resolve the root cause of the errors to prevent ongoing blocks.
The connect errors keep growing until I run flush hosts, I’m still trying to find the root cause for these errors.
While debugging I have noticed existing accounts except mysql.infoschema are still using their old authentication plugin, probably this is the reason for the handshake error ?
Though the default authentication plugin I see is caching_sha2_password when trying to connect to the MySQL host.
mysql> SELECT IP,SUM_CONNECT_ERRORS,COUNT_HANDSHAKE_ERRORS FROM performance_schema.host_cache;
+---------------+--------------------+------------------------+
| IP | SUM_CONNECT_ERRORS | COUNT_HANDSHAKE_ERRORS |
+---------------+--------------------+------------------------+
| 11.11.11.11 | 0 | 14646 |
+---------------+--------------------+------------------------+
1 row in set (0.01 sec)
mysql> select user,host,plugin from mysql.user ;
+-----------+-----------+-----------------------+
| user | host | plugin |
+-----------+-----------+-----------------------+
| abcd | % | mysql_native_password |
| grafana | % | mysql_native_password |
| wxyz | % | mysql_native_password |
| admin | localhost | mysql_native_password |
| backup | localhost | mysql_native_password |
| clusteradmin | localhost | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| root | localhost | mysql_native_password |
+-----------+-----------+-----------------------+
That could be the issue, as the caching_sha2 plugin requires SSL connections. Could be that some of the connection library for some users is incompatible.
I would try disabling one or two users, and see if the connection errors stop. If they do, then those are the users having the issue.