MySQL Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR)
MySQL Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR)
If you get a too many connections error,Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR), when you try to connect to the mysqld server, this means that all available connections are in use by other clients.
To increase the concurrent connections what are allowed to connect to the server, change the max_connections system variable value. The default value was 100 prior to MySQL 5.1.15. The default value is 151. If you need to support more connections, you should set a larger value for this variable.
To check what were the max concurrent number of connections to the database server so far, check the Max_used_connections status. If the max_used_connections number is closed to max_connections variable value, the max_connections number should be increased.
Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR)
Message: Too many connections
The default value is 151 to improve performance when MySQL is used with the Apache Web server. To support more connections, set max_connections to a larger value. mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege.
MySQL: ERROR 1040: Too many connections
This basically tells that MySQL handles the maximum number of connections simultaneously and by default it handles 151 connections simultaneously.
These following reasons cause MySQL to run out connections.
- Slow Queries
- Data Storage Techniques
- 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 | 151 |
+-----------------+-------+Now 2 Ways to increase mysql connection.
1. Without Restart MySQL service – Temporary
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.
The command takes effect right after you execute it, but it only applies to the current session.
2. With Restart mysql services – Permanent
If you want it to be permanent until you re-adjust it the next time, you have to edit the configuration file my.cnf (normally it’s stored in /etc/my.cnf).
Under the [mysqld] section add the following line:
max_connections = 500
Then restart the MySQL server to take effect.
Here is the video explanation for the same.
