mySQL Error 1040: Too Many Connection

Question

How to fix these, "SQL Error 1040: Too Many Connection" even I try to put

max_user_connection=500

still "Too many connection"

Answer 1

MySQL: ERROR 1040: Too many connections

This basically tells that MySQL handles the maximum number of connections simultaneously and by default it handles 100 connections simultaneously.

These following reasons cause MySQL to run out connections.

1. Slow Queries

2. Data Storage Techniques

3. Bad MySQL configuration

I was able to overcome this issues by doing the followings.

Open MySQL command line tool and type,

show variables like "max_connections";

This will return you something like this.

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

You can change the setting to e.g. 200 by issuing the following command without having to restart the MySQL server.

set global max_connections = 200;

Now when you restart MySQL the next time it will use this setting instead of the default.

Keep in mind that increase of the number of connections will increase the amount of RAM required for MySQL to run.

Answer 2

It is worth knowing that if you run out of usable disc space on your server partition or drive, that this will also cause MySQL to return this error. If you're sure it's not the actual number of users connected then the next step is to check that you have free space on your MySQL server drive/partition.

Edit January 2019:
This is true of MySQL 5.7 and 5.8 . I do not know for versions above this.

Answer 3

If you are running out of connections like this, chances are excellent that you are not closing the connections that you have open.

Review code that opens connections and ensure the connections are closed as soon as practical. Typically you want to make use of the using keyword around anything that implements IDisposable (including database connections) to ensure that such objects are disposed as soon as they leave the scope where they are needed.

You can check the current number of active connections with this query:

show processlist

Reference: MySQL show status - active or total connections?

Answer 4

This error occurs, due to connection limit reaches the maximum limit, defined in the configuration file my.cnf.

In order to fix this error, login to MySQL as root user (Note: you can login as root, since, mysql will pre-allocate additional one connection for root user) and increase the max_connections variable by using the following command:

SET GLOBAL max_connections = 500;

This change will be there, until next server restart. In order to make this change permanent, you have to modify in your configuration file. You can do this by,

vi /etc/my.cnf

[mysqld]
max_connections = 500

This article has detailed step by step workaround to fix this error. Have a look at it, I hope it may help you. Thanks.

Answer 5

You have to change max_connections to increase total permitted connections.

And set max_user_connections back to default 0 => no limit unless you need to limit this per user connections.

MySQL Too many connections

Answer 6

In your program you would have opened the connections and left open without closing it.

I faced a same problem and finally identified.

I added this code, after try catch.

finally{
    try {
        rs.close();
        p.close();
        conn.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

Answer 7

To see how many connections are configured for your DB to use:

select @@max_connections;

To change it:

set global max_connections = 200;

To see how many are connected at the current time:

show processlist;

I also wrote this piece of software to help me create a nice spreadsheet of transactions over time so I can track down which queries are the problem:

Answer 8

Try this :

open the terminal and type this command : sudo gedit /etc/mysql/my.cnf

Paste the line in my.cnf file: set-variable=max_connections=500

Answer 9

I was getting this error even though I had all my Connections wrapped in using statements. The thing I was overlooking is how long those connections were staying open.

I was doing something like this:

using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
    conn.Open();
    foreach(var m in conn.Query<model>(sql))
    {
        // do something that takes a while, accesses disk or even open up other connections
    }
}

Remember that connection will not close until everything in the loop is done and if the loop creates more connections, they can really start adding up.

This is better:

List<model> models = null;
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
    conn.Open();

    models = conn.Query<model>(sql).ToList(); // to list is needed here since once the connection is closed you can't step through an IEnumerable   
}

foreach(var m in models)
{
    // do something that takes a while, accesses disk or even open up other connections
}

That way your connection is allowed to close and is released back to the connection pool before you go to do other things

Answer 10

This issue occurs mostly when the maximum allowed concurrent connections to MySQL has exceeded. The max connections allowed is stored in the gloobal variable max_connections. You can check it by show global variables like max_connections; in MySQL

You can fix it by the following steps:

Step1:

Login to MySQL and run this command: SET GLOBAL max_connections = 100;

Now login to MySQL, the too many connection error fixed. This method does not require server restart.

Step2:

Using the above step1 you can resolve ths issue but max_connections will roll back to its default value when mysql is restarted.

In order to make the max_connection value permanent, update the my.cnf file.

Stop the MySQL server: Service mysql stop

Edit the configuration file my.cnf: vi /etc/mysql/my.cnf

Find the variable max_connections under mysqld section.

[mysql]

max_connections = 300

Set into higher value and save the file.

Start the server: Service mysql start

Note: Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections.

MySQL pre-allocate memory for each connections and de-allocate only when the connection get closed. When new connections are querying, system should have enough resources such memory, network and computation power to satisfy the user requests.

Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional request. And finally. it is very important to close the connections which are completed transaction on the server.

Answer 11

Check if your current running application is still accessing your mysql and has consumed all the DB connections.

So if you try to access mysql from workbench , you will get "too many connections" error.

stop your current running web application which is holding all those db connections and then your issue will be solved.

Answer 12

The issue noted clearly in https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html:

If clients encounter Too many connections errors when attempting to connect to the MySQL server, all available connections are in use by other clients.

I got resolved the issue after a few minutes, it seems that connection was been released by other clients, also I tried with restarting vs and workbench at same time.

Answer 13

Problem resolved by restarting the database windows service

Answer 14

Latest Solution

If you are using windows go to the following path :

‪C:\xampp\mysql\bin\my.ini

And add the below under the [mysqld] section :

max_connections = 500

Answer 15

Before increasing the max_connections variable, you have to check how many non-interactive connection you have by running show processlist command.

If you have many sleep connection, you have to decrease the value of the "wait_timeout" variable to close non-interactive connection after waiting some times.

https://serverfault.com/questions/281420/should-i-worry-about-mysql-sleep-status-process-in-processlist/959315#959315