🚨 MySQL Error Guide: 'ERROR 1040 (HY000)' Too Many Connections

Overview

ERROR 1040 (HY000): Too many connections means MySQL has reached max_connections and refuses new clients. The server is up and healthy, but every connection slot is in use:

ERROR 1040 (HY000): Too many connections

MySQL reserves one extra slot above max_connections for a user with the SUPER/CONNECTION_ADMIN privilege, so an admin can usually still log in to fix things even when normal clients are locked out. The error appears at connect time and typically hits an entire application tier at once: every new request fails while existing connections keep working.

It usually stems from a connection leak or an undersized pool, not from genuine load — a few hundred connections almost always means connections are being held open, not actively running queries.

Symptoms

• New connections fail instantly with Too many connections; existing ones still work.
• An app tier throws 1040 in bursts, often after a deploy or a downstream slowdown.
• SHOW PROCESSLIST shows many threads in Sleep.
• Threads_connected sits at or just below max_connections.

mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 151   |
+-------------------+-------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Common Root Causes

1. Connections are leaked and sitting in Sleep

The most common cause: application connections are opened, used, and never returned/closed. They sit in Sleep consuming slots.

SELECT command, COUNT(*) FROM information_schema.processlist GROUP BY command;

+---------+----------+
| command | COUNT(*) |
+---------+----------+
| Sleep   | 142      |
| Query   | 6        |
| Connect | 3        |
+---------+----------+

142 sleeping connections doing nothing while only 6 run queries is a textbook leak or oversized idle pool.

2. max_connections is set too low for the workload

A default of 151 is fine for a single small app but tiny for many pods each with a pool.

SELECT @@max_connections, @@max_used_connections;

+-------------------+------------------------+
| @@max_connections | @@max_used_connections |
+-------------------+------------------------+
| 151               | 151                    |
+-------------------+------------------------+

max_used_connections equal to max_connections means you hit the ceiling. The true demand is (pods) x (pool size).

3. Pool sizes multiplied across many instances

Each app instance keeps its own pool. Scaling from 5 to 30 pods with a pool of 10 jumps from 50 to 300 connections — past a 151 limit.

SELECT user, COUNT(*) AS conns
FROM information_schema.processlist
GROUP BY user ORDER BY conns DESC;

+------+-------+
| user | conns |
+------+-------+
| app  | 148   |
| root | 1     |
+------+-------+

148 connections from one app user is the pool-times-instances effect.

4. wait_timeout too high, so idle connections never drop

A long wait_timeout keeps abandoned connections alive for hours, starving the pool.

SELECT @@wait_timeout, @@interactive_timeout;

+----------------+-----------------------+
| @@wait_timeout | @@interactive_timeout |
+----------------+-----------------------+
| 28800          | 28800                 |
+----------------+-----------------------+

28800 seconds (8 hours) means a leaked connection lingers all day. Lowering this lets the server reap idle threads sooner.

5. A per-user resource limit (max_user_connections)

A single account can be capped below the global limit, so it hits 1040 while the server has free slots overall.

SELECT user, host, max_user_connections FROM mysql.user WHERE user = 'app';

+------+------+----------------------+
| user | host | max_user_connections |
+------+------+----------------------+
| app  | %    | 50                   |
+------+------+----------------------+

ERROR 1203 / 1040: User app already has more than 'max_user_connections' active connections

6. OS-level limits cap effective connections

Even with a high max_connections, the open_files_limit / file-descriptor ceiling can prevent MySQL from actually opening that many.

mysql -u root -p -e "SHOW VARIABLES LIKE 'open_files_limit';"
cat /proc/$(pgrep -o mysqld)/limits | grep "open files"

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
Max open files            1024    1024    files

A 1024 fd limit caps connections well below a configured max_connections=500.

Diagnostic Workflow

Step 1: Connect as admin and confirm the ceiling

The reserved super slot lets you in even when full:

mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';"

If Threads_connected equals max_connections, you have hit the wall.

Step 2: Break down what the connections are doing

SELECT command, state, COUNT(*) AS n
FROM information_schema.processlist
GROUP BY command, state ORDER BY n DESC;

A wall of Sleep points to leaks/idle pool; a wall of Query in Sending data or statistics points to genuine slow-query load.

Step 3: Identify the heaviest user/host

SELECT user, host, COUNT(*) AS conns
FROM information_schema.processlist
GROUP BY user, host ORDER BY conns DESC LIMIT 10;

This isolates the offending app/pool.

Step 4: Free slots immediately (mitigation)

Kill long-idle sleeping threads to recover:

-- Generate kill statements for connections sleeping > 600s
SELECT CONCAT('KILL ', id, ';') AS stmt
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600;

Run the generated KILL statements, or restart the leaking app tier.

Step 5: Raise the limit safely if demand is legitimate

SET GLOBAL max_connections = 500;

Make it permanent and ensure the OS fd limit supports it:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
max_connections = 500
open_files_limit = 5000

Then restart MySQL so open_files_limit takes effect (it cannot be raised at runtime).

Example Root Cause Analysis

After scaling a service from 8 to 24 pods, the entire tier starts failing with ERROR 1040. The DBA logs in via the reserved slot and checks the breakdown:

SELECT command, COUNT(*) FROM information_schema.processlist GROUP BY command;

+---------+----------+
| command | COUNT(*) |
+---------+----------+
| Sleep   | 144      |
| Query   | 5        |
+---------+----------+

Almost all connections are idle. Per user:

SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user;

+------+----------+
| user | COUNT(*) |
+------+----------+
| app  | 148      |
+------+----------+

The app pool is 10 per pod x 24 pods = 240 desired connections against max_connections=151. The connections are mostly idle — the pool is oversized, not the database overloaded.

Fix: shrink the per-pod pool to 4 (4 x 24 = 96, well under the limit) and lower wait_timeout to 120s so leaks self-heal. After the rollout, Threads_connected settles around 90 and the 1040 errors stop. Raising max_connections alone would have only delayed the next spike.

Prevention Best Practices

• Size pools to actual instance count: total desired connections (instances x pool size) must stay under max_connections with headroom. Right-size the pool before raising the server limit.
• Set a modest wait_timeout (e.g. 120-300s) so leaked or idle connections are reaped automatically instead of lingering for hours.
• Put a proxy (ProxySQL, MySQL Router) or a server-side connection multiplexer in front of high-fanout app tiers so thousands of clients share a small backend pool.
• Alert on Threads_connected / max_connections crossing ~75% so you fix capacity before clients get 1040.
• Ensure open_files_limit (and the systemd LimitNOFILE) comfortably exceeds your target max_connections; raising one without the other does nothing.
• For a quick read on whether a 1040 spike is leak vs. load, the free incident assistant can summarize a processlist snapshot. More in MySQL guides.

Quick Command Reference

# Current usage vs ceiling
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections'; SHOW VARIABLES LIKE 'max_connections';"

-- What are the connections doing?
SELECT command, state, COUNT(*) n FROM information_schema.processlist
GROUP BY command, state ORDER BY n DESC;

-- Heaviest user/host
SELECT user, host, COUNT(*) conns FROM information_schema.processlist
GROUP BY user, host ORDER BY conns DESC LIMIT 10;

-- Reap long-idle connections
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600;

-- Raise limit at runtime (and persist + bump fd limit in my.cnf)
SET GLOBAL max_connections = 500;

-- Per-user cap and idle timeout
SELECT user, host, max_user_connections FROM mysql.user WHERE user = 'app';
SELECT @@wait_timeout, @@interactive_timeout;

Conclusion

ERROR 1040 (HY000) means every connection slot is taken. The usual root causes:

1. Leaked or idle connections sitting in Sleep and never released.
2. max_connections set too low for the real workload.
3. Per-instance pools multiplied across many app instances.
4. A wait_timeout so high that abandoned connections never drop.
5. A per-user max_user_connections cap below the global limit.
6. An OS file-descriptor / open_files_limit ceiling capping effective connections.

Log in through the reserved super slot, break the processlist down by command and user, and decide whether you are leaking idle connections (shrink the pool, lower wait_timeout) or genuinely out of capacity (raise max_connections and the fd limit). The leak case is by far the most common.