How to Fix “Too Many Connections” (Error 1040) in MySQL and MariaDB

When your website traffic surges or an unindexed query locks database tables, client applications may crash with the error: “ERROR 1040 (08004): Too many connections”.

This error occurs when the number of active client connections reaches the ceiling defined by the max_connections server variable. Once this limit is reached, MySQL immediately refuses all subsequent connection attempts from web servers, API clients, and background workers.

---

Quick Answer

1. Log in immediately using the dedicated administrative connection:

   mysql -u root -p

   (MySQL reserves one extra connection above max_connections for users with SUPER or CONNECTION_ADMIN privileges).
2. Increase the connection ceiling live without restarting the database:

   SET GLOBAL max_connections = 500;

3. Kill sleeping processes that are holding connections open:

   -- View sleeping threads
   SELECT id, user, host, time, command FROM information_schema.processlist WHERE command = 'Sleep' ORDER BY time DESC;

4. Update /etc/mysql/my.cnf permanently with max_connections = 500 and lower wait_timeout = 60.

---

Symptoms

• Visitors encounter HTTP 500 errors or: “Database connection error: Too many connections”.
• Application logs fill with SQLSTATE[08004] [1040] Too many connections.
• Standard database management tools (such as phpMyAdmin) refuse to open.
• Monitoring tools report Threads_connected equaling max_connections.

---

Common Causes

1. Default max_connections Too Low: Default configurations often set max_connections = 151, which is inadequate for websites with hundreds of concurrent visitors.
2. Sleeping Connection Leaks (Sleep State): Web applications (PHP, Python, Ruby) opening persistent connections or failing to close connections, leaving hundreds of threads sleeping for hours.
3. Slow/Unindexed Queries Stacking Up: When a slow query takes 20 seconds to finish, subsequent queries queue behind it, spawning new connections until the pool is exhausted.
4. Traffic Surges or DDoS Attacks: Sudden legitimate marketing traffic or malicious HTTP floods consuming all database worker threads.
5. Operating System File Descriptor Limits: The Linux OS restricting the number of open file descriptors allocated to the mysql user.

---

Before You Start

• MySQL reserves one extra connection (max_connections + 1) exclusively for administrative accounts (root). If you cannot connect locally, do not panic—connect over local SSH using sudo mysql or mysql -u root -p.
• Raising max_connections to 5,000 on a server with 2GB of RAM can cause the server to crash from out-of-memory errors. Each connection consumes RAM for thread buffers.
• For custom high-traffic database tuning, consult our server performance optimization team.

---

Step 1 — Gain Emergency Access to MySQL

Even when public connections are exhausted, connect to MySQL locally using root credentials:

sudo mysql

Or connect via the dedicated administrative port/credentials:

mysql -u root -p

If Even Root Is Blocked

If root cannot connect, a misconfigured script is likely connecting as root. You can kill active connections directly from the Linux shell:

# Find and terminate hung PHP workers holding database sockets:
sudo systemctl reload php8.2-fpm || sudo systemctl reload php-fpm

Reloading PHP-FPM closes all client web sockets, immediately freeing up database slots for administrative login.

---

Step 2 — Check Active and Peak Connection Metrics

Inside the MySQL shell, check your current connection limits and usage:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

Interpreting the Results

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| max_connections       | 151   |
| Threads_connected     | 151   |
| Max_used_connections  | 151   |
+-----------------------+-------+

• Threads_connected equals max_connections, confirming every connection slot is occupied.

---

Step 3 — Inspect Active Threads and Identify Sleeping Queries

Find out what the connections are actually doing:

SHOW FULL PROCESSLIST;

Or summarize connections by command state:

SELECT command, COUNT(*) AS count 
FROM information_schema.processlist 
GROUP BY command 
ORDER BY count DESC;

Scenario A: Hundreds of Threads in “Sleep” State

If you see 140 connections in Sleep state with Time values over 300 seconds, your application is opening connections and leaving them open.

Terminate sleeping connections older than 60 seconds with this SQL query:

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

Copy the generated output and execute it, or kill specific thread IDs:

KILL 12841;
KILL 12842;

Scenario B: Threads Stalled with “Waiting for table metadata lock”

If multiple threads are waiting for a table lock, find the oldest non-sleep query at the top of the processlist and terminate it:

KILL 10102;

---

Step 4 — Increase max_connections Live (No Restart)

Immediately relieve connection pressure by increasing the global connection limit:

SET GLOBAL max_connections = 500;

Verify that the new limit is active:

SHOW VARIABLES LIKE 'max_connections';

Your applications will immediately be able to connect again without waiting for a server reboot.

---

Step 5 — Make the Fix Permanent in my.cnf

The SET GLOBAL command resets if MySQL restarts. Make your configuration permanent by updating /etc/mysql/my.cnf (or /etc/my.cnf):

sudo nano /etc/mysql/my.cnf

Add or adjust these directives under the [mysqld] section:

[mysqld]
# Increase connection limit
max_connections = 500

# Aggressively close idle sleeping connections after 60 seconds (default is 28800)
wait_timeout = 60
interactive_timeout = 60

# Max size of connect errors before host is blocked
max_connect_errors = 1000

Lowering wait_timeout to 60 Seconds

By setting wait_timeout = 60, MySQL automatically terminates abandoned connections after 1 minute instead of holding the slot for 8 hours (28800 seconds).

---

Step 6 — Adjust Linux Systemd Open File Limits

On Linux servers, MySQL cannot open more connections than its allocated operating system file descriptor limit (LimitNOFILE).

Check your current MySQL open file limits:

cat /proc/$(pidof mysqld)/limits | grep "Max open files"

If the limit is 1024, create a systemd override directory:

sudo systemctl edit mysql

Add these lines to the override file:

[Service]
LimitNOFILE=65535

Reload systemd and restart MySQL:

sudo systemctl daemon-reload
sudo systemctl restart mysql || sudo systemctl restart mariadb

---

Step 7 — Verify Resolution

1. Check your website in a browser to confirm normal operation.
2. Query MySQL status to monitor connection headroom:

   SHOW STATUS LIKE 'Threads_connected';

3. Verify that Threads_connected now stays comfortably below max_connections.

---

Common Mistakes

1. Setting max_connections = 5000 blindly: Every MySQL connection consumes RAM for per-thread buffers (sort_buffer_size, read_buffer_size, join_buffer_size). Setting connection limits too high can cause catastrophic Out-of-Memory crashes during traffic spikes.
2. Ignoring the underlying slow queries: Increasing max_connections from 150 to 500 when slow queries take 30 seconds each only delays the inevitable—the server will simply hit 500 connections a few minutes later.
3. Using persistent database connections (pconnect) improperly: In PHP, enabling persistent connections without connection pooling can hold hundreds of database threads indefinitely.

---

Prevention Checklist

• Set wait_timeout = 60 to automatically close dead application connections.
• Enable the MySQL slow query log (slow_query_log = 1, long_query_time = 2) to identify queries causing thread backups.
• Deploy Redis or Memcached caching to serve reads from memory rather than querying the database for every page visit.
• Review our server performance optimization services for full connection pool tuning.

---

Quick Reference Commands

Task	Command
Emergency local login	sudo mysql
View active connection count	SHOW STATUS LIKE 'Threads_connected';
Increase connections live	SET GLOBAL max_connections = 500;
View processlist	SHOW FULL PROCESSLIST;
Kill specific thread	KILL <thread_id>;
Check Linux open file limit	cat /proc/$(pidof mysqld)/limits | grep open

---

Frequently Asked Questions

How do I calculate the safe maximum for max_connections?

Use this formula: Available RAM for connections = Total Server RAM - (innodb_buffer_pool_size + OS overhead). Then divide by average thread buffer size (typically 2MB–5MB). On an 8GB RAM server with a 4GB InnoDB buffer pool, setting max_connections = 400 to 500 is generally safe.

Why does MySQL reserve one extra connection?

MySQL intentionally reserves max_connections + 1 connections. When standard users exhaust all available slots, an administrator connecting with SUPER or CONNECTION_ADMIN privileges can still log in to diagnose the issue and terminate rogue threads.

Can persistent PHP connections cause Error 1040?

Yes. If your PHP code uses PDO::ATTR_PERSISTENT => true or legacy mysql_pconnect, PHP workers do not close database connections when the HTTP request finishes. Under high traffic, this quickly exhausts all database connection slots.

How does ServerCare360 assist with high-concurrency database issues?

Our certified server performance optimization and emergency server support engineers audit MySQL thread pools, optimize connection timeouts, tune query performance, implement caching architectures, and maintain 24/7 database uptime.