How to fix ERROR 1203: User has too many connections in MySQL

Log in to MySQL as root or with appropriate privileges and check the current limit:

SELECT User, max_user_connections FROM mysql.user WHERE User='your_username';

If the result shows max_user_connections = 0, it means the global max_user_connections setting applies. Check the global setting:

SHOW VARIABLES LIKE 'max_user_connections';

The default is typically 0 (unlimited per user) or a low value like 10-20 on shared hosting.

Before making changes, see how many active connections the user currently has:

SELECT COUNT(*) as connection_count FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='your_username';

This helps you understand the actual usage pattern and determine what limit is appropriate.

If you need an immediate fix without restarting MySQL, use the SET GLOBAL command:

SET GLOBAL max_user_connections = 100;

Replace 100 with an appropriate value based on your application's needs. You can set values between 1 and 4294967295. This change takes effect immediately but is lost if MySQL restarts.

To make the change persistent, edit your MySQL configuration file:

On Linux/macOS: Look for /etc/mysql/my.cnf or /etc/my.cnf

On Windows: Look for my.ini in the MySQL installation directory (e.g., C:\Program Files\MySQL\MySQL Server 8.0\my.ini)

Find the [mysqld] section and add or update the line:

[mysqld]
max_user_connections = 100

For per-user limits, you can set individual limits that override the global setting:

ALTER USER 'your_username'@'localhost' WITH MAX_USER_CONNECTIONS 100;

After modifying the config file, restart MySQL for the changes to take effect:

# On Linux/macOS
sudo systemctl restart mysql

# Or
sudo service mysql restart

# On Windows
net stop MySQL80
net start MySQL80

Review your application code to ensure connections are properly closed:

Node.js/Express example:

const connection = mysql.createConnection(config);
connection.query('SELECT * FROM users', (error, results) => {
  if (error) throw error;
  console.log(results);
  connection.end(); // Always close the connection
});

Implement connection pooling to reuse connections instead of creating new ones for each request:

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

pool.query('SELECT * FROM users', (error, results) => {
  if (error) throw error;
  // Connection is automatically returned to the pool
});

This approach significantly reduces the number of simultaneous connections needed.

If you have long-running processes, implement a mechanism to close idle connections. In MySQL, you can adjust the interactive_timeout and wait_timeout variables:

SET GLOBAL interactive_timeout = 600;  -- 10 minutes
SET GLOBAL wait_timeout = 600;          -- 10 minutes for non-interactive connections

Or add to my.cnf:

[mysqld]
interactive_timeout = 600
wait_timeout = 600

This automatically closes idle connections after 10 minutes, freeing up connection slots.

Set up regular monitoring to prevent future occurrences:

-- View all active connections
SHOW PROCESSLIST;

-- Count connections per user
SELECT USER, COUNT(*) as connection_count FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY USER;

-- Find long-running queries holding connections
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 300 AND COMMAND != 'Sleep';

Use these queries regularly to identify connection leaks or inefficient query patterns. Consider setting up automated alerts when connection usage exceeds 80% of the limit.