mySQL Error 1040: Too Many Connection

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.

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.

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?

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.