Understanding MySQL max_connect_errors and Connection Failure Causes

Author: Wang Xiangfei, a test member of the iKangSheng R&D team responsible for database management platform testing. Original content.

Keywords: max connections, TCP protocol, MySQL protocol, parameter configuration

Phenomenon

During testing, the MySQL max_connections was set to 120 while sysbench was run with 200 concurrent inserts, which naturally exceeded the limit and produced errors.

When the concurrency was reduced to 100 (below the limit), inserts still failed, showing the same error as before.

Even a direct login with user test produced the same error.

Cause and Solution

The failure was due to the max_connect_errors system variable being exceeded. The simple fix is to execute FLUSH HOSTS .

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connect_errors

Analysis

Setting max_connect_errors to a low value (e.g., 2) and attempting wrong‑password logins showed that only protocol handshake errors are counted, not authentication errors.

The Performance Schema table host_cache stores the field SUM_CONNECT_ERRORS , which records the number of blocked connection errors per host. When this value reaches max_connect_errors , further connections from that host are rejected.

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/host-cache-table.html

Using telnet to simulate handshake failures, the author set max_connect_errors to 2 and observed SUM_CONNECT_ERRORS increase from 0 to 2 after two connection attempts.

After reaching the limit, further login attempts were blocked, confirming the cause of the original sysbench failure.

Since the default max_connect_errors is 100, the 200‑thread sysbench generated 109 connection errors (120 total connections minus 11 existing ones), exceeding the limit and causing the failures.

Extension

The documentation clarifies that the counted errors are MySQL protocol handshake failures, not TCP/IP handshake failures. TCP three‑way handshake succeeds (error 1040 is returned), but the MySQL handshake is aborted.

Packet capture images show the TCP handshake completing, followed by MySQL sending its handshake packet and then closing the connection without receiving the client’s authentication packet.

Telnet only sends TCP packets, not the MySQL authentication packet, so the server waits for the client’s login data, times out (connect_timeout=10), and records a MySQL protocol handshake failure.