MySQL "ERROR 1040: Too many connections": get in, find who holds them, fix it

1. Get in anyway

MySQL allows one connection more than max_connections, reserved for an account with the CONNECTION_ADMIN (or older SUPER) privilege. On Debian/Ubuntu, sudo mysql logs you in as root through the socket and usually gets it. If someone already used that one too:

• MySQL 8.0.14+ can have a separate admin port that isn’t counted in the limit: admin_address and admin_port (default 33062) in the config — worth setting up before you need it.
• MariaDB has a similar extra_port / extra_max_connections.

2. See who holds the connections

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS n
FROM information_schema.processlist
GROUP BY user, client, command
ORDER BY n DESC;

+------+-----------+---------+-----+
| user | client    | command | n   |
+------+-----------+---------+-----+
| app  | 10.0.1.14 | Sleep   | 118 |
| app  | 10.0.1.15 | Query   |  27 |
| app  | 10.0.1.15 | Sleep   |   4 |
+------+-----------+---------+-----+

The command column tells you which case you’re in:

• Mostly Sleep → idle connections that clients keep open (section 3).
• Many Query, with a high time → queries piling up (section 4).

For details, sorted by how long each has been in its state:

SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

3. Idle connections (Sleep)

Every application server keeps a pool of connections. The total is easy to underestimate: 6 app instances × a pool of 30 = 180 connections — more than 151 before a single extra request. Add workers, cron jobs and a second deploy that overlaps the first one, and you’re over.

Fix it on the client side first:

• Size the pools to fit: instances × pool size should stay well below max_connections, leaving room for admins and jobs.
• Close connections in scripts and jobs when they finish.
• Use a proxy like ProxySQL when many app instances share one database.

On the server, wait_timeout (default 28800 seconds = 8 hours) decides how long an idle connection may stay. Lowering it cleans up forgotten connections faster — but set it above your pool’s idle timeout, or apps will get “server has gone away” on connections the server closed:

SET GLOBAL wait_timeout = 600;

4. Queries piling up (Query)

If many connections are busy, something is slow, and new requests keep arriving while old ones wait. Look at state in the list above:

• Waiting for table metadata lock — an ALTER TABLE (or a long open transaction) blocks everybody using that table. Find the transaction that holds it and end it, or wait for the ALTER.
• Sending data, executing, with large time — slow queries: check them with EXPLAIN, add the missing index.

To end a connection or a query:

KILL 4217;          -- ends connection 4217
KILL QUERY 4217;    -- stops only its current query

5. Raise the limit — if you really need more

If the connections are legitimate and busy, raise the limit. Right away, without a restart:

SET PERSIST max_connections = 300;     -- MySQL 8: applies now and survives a restart

On MariaDB (no SET PERSIST), use SET GLOBAL max_connections = 300; and also add max_connections = 300 under [mysqld] in the config file, or it’s lost at the next restart.

Every connection costs memory (its own buffers, on top of the shared ones), so don’t set it to 5000 “just in case” — you’d trade “too many connections” for the server running out of memory.