MySQL Error 1040: Too many connections

If you encounter MySQL error 1040: Too many connections, when attempting to connect to the MySQL server, it means that the connection limit has reached the maximum limit.

By default, 100 connections can be handled simultaneously by MySQL. This limit can be controlled by the max_connections, which is a system variable defined in the configuration file my.cnf . MySQL actually permits max_connections+1 client connections. However, this additional connection beyond the max_connections value is reserved for the administrator to connect to the server and check the Processlist to diagnose problem and fix it. Hence, for all unprivileged users the maximum limit will be the max_connections.

Solution for MySQL error – Too many connections

METHOD 1 – Temporary Method

1. Login to MySQL as root

2. Enter the below command to see the max_connection value.

Show variables like “max_connections”;

This will return the below result:

+---------------------+--------+
|   Variable_name     |  Value |
+---------------------+--------+
|   max_connections   |  100   |
+---------------------+--------+

3. Change the value to eg:200. Using the below comment

set global max_connections = 200;

Following the above steps will change the max_connections immediately and does not require a MySQL restart. When MySQL is next restarted, this change will roll back to the default value.

METHOD 2 – Permanent Method

In order to make the max_connections value permanent, follow the below steps:

1. SSH into your MySQL server as root

2. Edit the MySQL configuration file at /etc/my.cnf file

vi /etc/my.cnfFind max_connections under mysqld section

3. Find max_connections under mysqld section

4. Insert/modify the max_connections to the desired value

max_connections = 200;

5. Restart MySQL, using below command

/etc/init.d/mysql restart

Note: Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections. As increasing the number of connections will increase the amount of RAM required for MySQL to run.

Additional Steps

If you know that the number of connections is well within the maximum limit and still you are getting ‘MySQL error 1040: Too many connection’. This could be because you are not closing the connections that you are opening. You can check this using the below query inside the MySQL prompt:

show processlist;

Trying all the above troubleshooting should fix the error. If you still face the issue then have your system administrator check this further.