MySQL ER_CON_COUNT_ERROR

The MySQL server has reached its maximum allowed number of simultaneous client connections, defined by the `max_connections` system variable. New connection attempts are rejected until existing connections are closed.

Common Causes

The `max_connections` configuration value is set too low for the application's load.
A surge in traffic or a misconfigured connection pool creating too many connections.
Application bugs or long-running queries causing connections to remain open and not be returned to the pool.
Connections not being properly closed after use, leading to a gradual accumulation.

How to Fix

1 Increase the Global Connection Limit

Temporarily or permanently raise the `max_connections` limit. This is a common fix for production systems, but ensure your server has enough RAM (each connection uses thread buffers).

BASH

 $ # Set dynamically (requires SUPER privilege, resets on restart)
SET GLOBAL max_connections = 500;

# Set permanently in MySQL config file (e.g., /etc/my.cnf)
[mysqld]
max_connections = 500

2 Kill Idle or Problematic Connections

If you cannot connect to administer, you may need to restart MySQL or connect via a privileged single-user mode. Once connected, identify and terminate idle connections to free up slots.

BASH

 $ # View all current processes/connections
SHOW PROCESSLIST;

# Kill a specific connection by its ID
KILL <process_id>;

# Kill all sleeping (idle) connections (use with extreme caution)
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE Command = 'Sleep' INTO OUTFILE '/tmp/kill_idle.sql';
source /tmp/kill_idle.sql;

3 Configure Connection Pooling & Timeouts

Prevent the issue by configuring your application's connection pool (e.g., HikariCP, C3P0) correctly and setting MySQL server timeouts to clean up stale connections.

BASH

 $ # Set interactive and non-interactive wait timeouts (in seconds)
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

# Example HikariCP properties for a Java app (application.yml)
# spring:
#   datasource:
#     hikari:
#       maximum-pool-size: 20
#       connection-timeout: 30000
#       idle-timeout: 600000

4 Check Connection Limit and Usage

Diagnose the current limits and connection count to understand the scale of the problem.

BASH

 $ # Check the current max_connections and connection count
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

# Calculate connection usage percentage
SELECT 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') AS 'Connected',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') AS 'Max',
    ROUND((Threads_connected / max_connections) * 100, 2) AS 'Usage %'
FROM (SELECT @@max_connections AS max_connections, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') AS Threads_connected) AS stats;