📋

Description

MySQL Error 1226 indicates that a specific user has surpassed one of their allocated resource limits on the MySQL server. This typically occurs when a user's activity, such as concurrent connections or queries per hour, exceeds the predefined quota, preventing further operations.

💬

Error Message

User '%s' has exceeded the '%s' resource (current value: %ld)

🔍

Known Causes

4 known causes

⚠️

Excessive Concurrent Connections

The user has opened more active connections to the MySQL server than their allocated `MAX_USER_CONNECTIONS` limit allows.

⚠️

Hourly Query Limit Reached

The user has executed more SQL queries within the last hour than permitted by their `MAX_QUERIES_PER_HOUR` setting.

⚠️

Hourly Update Limit Reached

The user has performed more update operations within the last hour than allowed by their `MAX_UPDATES_PER_HOUR` resource limit.

⚠️

Unclosed Application Connections

Client applications may not be properly closing database connections, leading to an accumulation that exceeds the user's connection limit.

🛠️

Solutions

5 solutions available

1. Increase max_user_connections easy

Allow more connections per user

1

Check current user limit

SHOW VARIABLES LIKE 'max_user_connections';

2

Increase globally

SET GLOBAL max_user_connections = 100;

3

Or set per-user limit

ALTER USER 'username'@'localhost' WITH MAX_USER_CONNECTIONS 50;

4

Make permanent in my.cnf

[mysqld]
max_user_connections = 100

2. Close Unused Connections easy

Release connections that are no longer needed

1

See current connections for your user

SELECT * FROM information_schema.PROCESSLIST WHERE USER = 'your_username';

2

Kill idle connections if needed

-- Find sleeping connections
SELECT ID, USER, HOST, TIME, STATE FROM information_schema.PROCESSLIST 
WHERE USER = 'your_username' AND COMMAND = 'Sleep' AND TIME > 300;

-- Kill specific connection
KILL 12345;

3. Use Connection Pooling medium

Reuse connections instead of creating new ones

1

Node.js with mysql2 pool

const mysql = require('mysql2');
const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  database: 'db',
  connectionLimit: 10,  // Reuses connections
  waitForConnections: true
});

// Use pool.query() instead of creating new connections

2

Python with SQLAlchemy

from sqlalchemy import create_engine
engine = create_engine(
    'mysql://user:pass@localhost/db',
    pool_size=10,
    max_overflow=20
)

4. Fix Connection Leaks medium

Ensure code properly closes connections

1

Always close connections in finally block

// JavaScript
try {
  connection = await pool.getConnection();
  await connection.query('SELECT ...');
} finally {
  if (connection) connection.release();
}

2

Python context manager

with engine.connect() as conn:
    result = conn.execute(query)
# Connection automatically returned to pool

5. Create Separate Users medium

Distribute connections across multiple users

1

Create dedicated users for different apps

CREATE USER 'app1_user'@'%' IDENTIFIED BY 'password';
CREATE USER 'app2_user'@'%' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app1_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app2_user'@'%';

🔗

Related Errors

5 related errors

MySQL Error 1226: User Resource Limit Exceeded