CRITICAL

How to Fix MySQL Error 1040: Too Many Connections

Quick Fix Summary

TL;DR

Immediately increase max_connections and kill idle processes to restore database access.

MySQL has exhausted its maximum allowed concurrent connections, blocking new client requests. This is a hard limit defined by the max_connections system variable.

Diagnosis & Causes

  • Application connection pool configured too large.
  • Connections not being properly closed after use.
  • max_connections variable set too low for production load.
  • Sudden traffic spike overwhelming connection capacity.
  • Database queries hanging, keeping connections open indefinitely.

    • Recovery Steps

    1

    Step 1: Emergency Connection Increase (Temporary)

    Dynamically raise the connection limit without restarting MySQL to immediately restore service.

    bash 
    mysql -u root -p -e "SET GLOBAL max_connections = 500;"
    2

    Step 2: Kill Idle/Problematic Connections

    Identify and terminate long-running or idle connections to free up slots for active traffic.

    bash 
    mysql -u root -p -e "SHOW PROCESSLIST;"
mysql -u root -p -e "KILL <process_id>;"
    3

    Step 3: Analyze Connection Sources

    Check which hosts/users are consuming the most connections to identify the problematic application.

    bash 
    mysql -u root -p -e "SELECT user, host, COUNT(*) as connections FROM information_schema.processlist GROUP BY user, host ORDER BY connections DESC;"
    4

    Step 4: Permanently Adjust Configuration

    Update the MySQL configuration file to make the increased connection limit persistent across restarts.

    bash 
    sudo nano /etc/mysql/my.cnf
[mysqld]
max_connections = 500
max_user_connections = 450
sudo systemctl restart mysql
    5

    Step 5: Implement Connection Pooling

    Configure your application to use a connection pool (like HikariCP) to efficiently manage database connections.

    yaml 
    # Example HikariCP configuration for Spring Boot (application.yml)
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
    6

    Step 6: Monitor and Set Alerts

    Implement monitoring to track connection usage and receive alerts before hitting the limit again.

    bash 
    # Example Prometheus Query for MySQL Connections
mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100

    Architect's Pro Tip

    "Set max_user_connections slightly lower than max_connections. This reserves a few 'admin' slots so you can always connect to diagnose and fix issues, even during a full outage."

    Frequently Asked Questions

    What's the default max_connections value in MySQL?

    The default is 151. For production systems, this is often far too low and must be increased based on expected concurrent load and application connection pool settings.

    Will increasing max_connections consume more RAM?

    Yes. Each connection requires a thread and buffer memory. Monitor your 'thread_stack' and per-thread buffer variables (like sort_buffer_size) as increasing connections significantly can raise total RAM usage.

    My app uses connection pooling. Why am I still hitting ERROR 1040?

    Your pool's 'maximumPoolSize' might be set too high across multiple application instances or servers. Multiply (instances * pool size) to ensure the total doesn't exceed your database's max_connections.

    Related MySQL Guides

    ERROR 1062

    How to Fix MySQL ERROR 1062: Duplicate Entry
    ERROR 1205 (HY000)

    How to Fix MySQL Error 1205: Lock Wait Timeout Exceeded
    ERROR 2003

    How to Fix MySQL Error 2003 (HY000): Can't Connect to Server