How to Fix MySQL Too Many Connections Error

In this tutorial, you'll learn about How to Fix MySQL Too Many Connections Error. We cover key concepts, practical examples, and best practices.

The Problem

Your application or client gets:

ERROR 1040 (HY000): Too many connections

MySQL has reached its max_connections limit. All connection slots are occupied, and new connections are rejected until existing ones close.

Quick Fix

1. Kill idle connections immediately

Log in as root (may need to use a super-user connection) and kill idle connections:

-- Find idle connections
SHOW FULL PROCESSLIST;

-- Kill connections that have been idle for more than 60 seconds
SELECT CONCAT('KILL ', id, ';') AS kill_query
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 60;

Copy the generated KILL queries and execute them.

2. Increase max_connections

Temporarily increase the limit:

SET GLOBAL max_connections = 500;

Make it permanent in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
max_connections = 500

Calculate a reasonable value based on RAM (see FAQ).

3. Reduce the connection timeout

Close idle connections faster:

[mysqld]
# Close idle connections after 60 seconds
wait_timeout = 60
interactive_timeout = 60

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

4. Use a connection pooler

Connection pools reuse database connections instead of opening new ones:

# Python with SQLAlchemy connection pooling
from sqlalchemy import create_engine

# Wrong — creates a new connection on every request
engine = create_engine('mysql://user:pass@localhost/db')

# Right — connection pool with limits
engine = create_engine(
    'mysql://user:pass@localhost/db',
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True
)

Node.js with mysql2:

// Wrong — no pooling
const connection = await mysql.createConnection({...});

// Right — use pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  password: 'pass',
  database: 'db',
  connectionLimit: 10,
  waitForConnections: true
});

5. Check for connection leaks

An application that does not close connections properly leaks them:

# Wrong — connection never closed
def query_user(user_id):
    conn = mysql.connector.connect(...)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    return cursor.fetchone()

# Right — context manager closes connection
def query_user(user_id):
    with mysql.connector.connect(...) as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
            return cursor.fetchone()

Prevention

• Implement connection pooling in all application layers.
• Set wait_timeout to close idle connections within 1-5 minutes.
• Monitor Threads_connected with alerts at 80% of max_connections.
• Use mysqladmin processlist in cron to kill stale connections.

Common Mistakes with too many connections

1. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors
2. Non-exhaustive pattern matches that compile with warnings then crash at runtime
3. Misunderstanding that String is [Char] with poor performance for large text operations

These mistakes appear frequently in real-world MYSQL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ