MySQL Error: "too many connections"

Are you getting a MySQL error: too many connections? Here's what the error means, and what you can do to resolve it.

MySQL Error “too many connections”: What’s the Problem?

When logging into MySQL, you may be occasionally rejected and receive an error message like “too many connections“. This means that the maximum number of clients that can connect to the server has been reached. Either one client will have to wait for another to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about maximum allowed connections to a server can be found using the SHOW VARIABLES statement:

$ mysql –u root –p

mysql> SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 50 |
+-----------------+-------+
1 row in set (0.00 sec)

Currently occupied connections can be found by the following statement:

mysql> SHOW GLOBAL STATUS LIKE 'threads_connected';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 50    |
+-------------------+-------+
1 row in set (0.00 sec)

If the number of threads_connected value is the same as max_connections, then MySQL server returns “too many connections” error messages to the client. That is:

Number of new connections allowed = max_connections - threads_connected = 50 - 50 = 0

We can see how many connections are currently active by executing following statement:

mysql> SHOW GLOBAL STATUS LIKE 'threads_running';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 10     |
+-----------------+-------+
1 row in set (0.00 sec)

This means that 40 connections (i.e. treads_connected - tread_running = 50 - 10 =40 )  are idle in the database.

First, you should ensure that your applications are closing connections to the server when they are no longer needed. However, you can solve this error by increasing the value of the max_connections variable, and possibly decreasing the value of wait_timeout if you expect that many of the connections to your server are not being actively used.

MySQL Error “too many connections”: Solution

We can increase the value of max_connections parameter by runtime (temporarily) and set its value permanently in the MySQL configuration file.

Changing the max_connections parameter (Temporarily)

To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p

mysql> SET GLOBAL max_connections = 300;

Note: It’s not necessary to have a currently connected client disconnect to run this SQL statement as root. The server permits one more connection than the value of the max_connections. One connection is reserved for the root user or any other accounts that have SUPER privilege.

When the MySQL server daemon (mysqld) is restarted, the value above will set back to the previous value that exists in my.cnf or my.ini file. To make permanent changes, use the method described here:

Changing the max_connections parameter (Permanently)

A better method to change max_connections parameter would be to add this option to the options file (my.cnf or my.ini, depending on your system) so that it takes effect next time the server is restarted. Assuming you are using /etc/my.cnf file, add the below line to it.

# vi /etc/my.cnf
max_connections = 300

Now restart the mysqld daemon for the changes to take effect.

For CentOS/RHEL 6:

# service mysqld restart

For CentOS/RHEL 7:

# systemctl restart mysqld

SingleStoreDB

SingleStoreDB is a real-time, distributed SQL database that unifies transactions and analytics in a single engine to drive low-latency access to large datasets, simplifying the development of fast, modern enterprise applications.

Built for developers and architects, SingleStoreDB delivers 10-100 millisecond performance on complex queries — all while ensuring your business can effortlessly scale.

SingleStoreDB is MySQL wire compatible and offers the familiar syntax of SQL, but is based on modern underlying technology that allows infinitely higher speed and scale versus MySQL. This one of the many reasons that SingleStore is the #1 top-rated relational database on TrustRadius.

For more information on how SingleStore is related and can turbocharge your MySQL, visit our MySQL page.

Resources:

• MySQL Error Command "error 1016 can't open file" — What is this error? How do you typically resolve it?
• Three Common MySQL Errors

Try SingleStoreDB free.