How MySQL Connection Control Plugins Can Cause Connection Pile‑up and How to Fix It

Introduction

MySQL 5.7.17 introduced the Connection Control Plugins, which add response delay after consecutive failed connection attempts to mitigate brute‑force attacks.

Improper use can cause connection backlog and database hangs, e.g., when a monitoring system repeatedly accesses the DB with a non‑existent user.

1. Problem Background and Cause

1.1 Scenario Description

In a client environment with the plugin enabled, the monitoring system uses a non‑existent user “igcam”, causing a large number of connections in Connect state to reach max_connections and trigger a “too many connections” error.

1.2 Root Cause

When the plugin is enabled it records failed connection attempts. After reaching connection_control_failed_connections_threshold (default 3), it delays creation of new connections. Even if the user is deleted, the plugin continues to record failures and delay responses, consuming connection resources.

2. Reproduction Scenario and Test Method

2.1 Install Connection Control Plugins

The plugin consists of two parts:

Connection_control : checks connection count and adds delay.

Connection_control_failed_login_attempts : records detailed failed login attempts.

Install with:

<code>INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
</code>

Verify installation:

<code>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
</code>

2.2 Parameter Configuration

Key parameters:

connection_control_failed_connections_threshold : allowed consecutive failures (default 3, 0 disables).

connection_control_min_connection_delay : minimum delay in ms (default 1000).

connection_control_max_connection_delay : maximum delay in ms (default 2147483647 ≈ 24 days).

Example configuration:

<code>SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;
-- set to 1 hour
SET GLOBAL connection_control_max_connection_delay = 3600000;
</code>

2.3 Effect Testing

Create and drop a test user monitor , set max_connections to 5, then run concurrent connections as monitor to observe delay.

<code>CREATE USER monitor@'127.0.0.1' IDENTIFIED BY 'monitor';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'127.0.0.1';
-- test connection
SELECT current_user();
DROP USER monitor@'127.0.0.1';
SET GLOBAL max_connections = 5;
while true; do time mysql -umonitor -p'monitor' -h127.0.0.1 -P3306 2>/dev/null; done
</code>

The result shows the monitor user occupies connections and its authentication state becomes “waiting in connection_control plugin”.

2.4 Delay Mechanism Analysis

After three consecutive failures the fourth attempt and subsequent ones are delayed, each additional failure adding one second.

2.5 Delay Calculation Formula

delay_time = min_delay * (current_count + 1 - threshold)

min_delay: minimum delay (default 1000 ms).

current_count: current failed attempts.

threshold: failure threshold (default 3).

If current_count exceeds threshold or is negative, delay is triggered and grows with the number of failures.

2.6 Counter Update Logic

Successful connections reset the counter to 0; the delay no longer affects that client until failures exceed the threshold again.

3. Summary and Mitigation

3.1 Problem Summary

Connection backlog reaching max_connections .

Database performance degradation.

Default configuration adds 1 s delay from the fourth failure.

3.2 Exception Handling

Limit maximum delay by setting connection_control_max_connection_delay to a smaller value (e.g., 1 hour).

Control concurrent connections for monitoring users, e.g.:

<code>CREATE USER 'monitor'@'127.0.0.1' WITH MAX_USER_CONNECTIONS 10;
</code>

Promptly intervene on abnormal users (password changes, connection anomalies).

In emergencies, reset the counter:

<code>SET GLOBAL connection_control_failed_connections_threshold = 0;
</code>

References

Connection Control Plugins: https://dev.mysql.com/doc/refman/8.0/en/connection-control-installation.html