Fix: ERROR 1226 (42000): User has exceeded the 'max_user_connections' resource

ERROR 1226 means one specific MySQL account has hit its personal cap on simultaneous connections. Unlike the server wide Too many connections, this limit is scoped to a single user, set either globally through the max_user_connections system variable or on the account itself with MAX_USER_CONNECTIONS. The server may be healthy and other accounts unaffected; this one account cannot open another session until existing ones close. In practice the cap is usually fine and something is holding sessions it should have released.

ERROR 1226 (42000): User 'app'@'%' has exceeded the 'max_user_connections' resource (current value: 50)

What this error means

MySQL tracks how many open sessions each account has. Two mechanisms cap it: the global max_user_connections variable, which applies the same ceiling to every account, and a per account resource limit stored with the user (ALTER USER ... WITH MAX_USER_CONNECTIONS n), which overrides the global value for that user. When a new connection would push the account past its effective cap, the server rejects it with error 1226 and names the current value. The error tells you three useful things: which account, from which host pattern, and what the cap actually is.

Common causes

• A connection leak in the application: sessions opened and never closed accumulating under the app account.
• Pool sizes multiplied across autoscaled instances, all authenticating as the same user.
• One shared account used by the application, cron jobs, BI dashboards, and engineers, all counting against a single cap.
• A managed or shared hosting provider enforcing a low per user cap that the application outgrew.
• Long running queries or transactions holding sessions open during traffic spikes, so turnover cannot keep up.
• A deploy that doubled instance counts (blue green, canary) without accounting for the temporary doubling of connections.

How to fix it

STABILIZE first. As an admin, kill the account’s stale sessions or raise its cap; both unblock new connections immediately without a restart.

1. DIAGNOSE: see the effective limits, global and per account.

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

A 0 means no limit at that level; the account level value, when nonzero, wins.

2. DIAGNOSE: see what the account’s sessions are actually doing and how long they have been idle.

SELECT id, host, db, command, time, state, LEFT(IFNULL(info,''), 80) AS query
FROM information_schema.processlist
WHERE user = 'app'
ORDER BY time DESC;

Many sessions in Sleep with large time values are the signature of a leak or an oversized idle pool.

3. STABILIZE: kill the longest idle sessions to free slots now.

KILL 12345;

Generate the statements in bulk when there are many:

SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE user = 'app' AND command = 'Sleep' AND time > 1800;

4. FIX the cap if the demand is legitimate: raise the account limit to match the real, budgeted connection count.

ALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 200;

Or adjust the global default if the policy should change for all accounts:

SET GLOBAL max_user_connections = 200;

Persist the global change in my.cnf so a restart does not revert it.

5. FIX the demand side: shrink per instance pool sizes so the fleet total fits the cap with headroom, set pool idle timeouts so sleeping connections get reclaimed, and fix code paths that open connections outside the pool. Give cron jobs, dashboards, and humans their own accounts with their own caps so they cannot starve the application.

6. Verify from the application host that new connections succeed under load, and watch the account’s session count settle at the expected steady state.

SELECT COUNT(*) FROM information_schema.processlist WHERE user = 'app';

How to prevent it

• Budget connections per account explicitly: sum pool sizes across all instances plus jobs, and keep the cap above that with margin.
• One account per consumer: application, migrations, cron, BI, and humans each get their own credentials and their own limit.
• Set pool idle timeouts and maximum lifetimes so leaked or sleeping sessions are reclaimed automatically.
• Alert on per account session counts approaching the cap, using processlist counts, before error 1226 appears.
• Account for deploy strategies that temporarily double instances when sizing caps.
• On shared hosting, know the provider’s cap and design the pool around it rather than discovering it in production.

When to call a senior engineer

Call for help when the account keeps hitting its cap after pools were tuned, when you cannot tell which of many services is leaking sessions under a shared credential, or when the connection budget across an autoscaling fleet needs designing rather than guessing. Erzon engineers can attribute every session to its source, split shared accounts safely without breaking running services, and leave you with limits and monitoring that keep one noisy consumer from taking the application down.