Many of us have faced the issue of Too Many Connections error in MySQL/MariaDB with different errror codes like specified below:
(08004/1040): Too many connections
(HY000/1040): Too many connections
The above errors appear when all MySql/MariaDB connections allowed are currently being used up and there are more connections requests than allowed. Though this may also be case of poor development, if the database connections are not closed properly, but this tutorial assumes that the the development related optimisations have been applied properly and still this above issue is being faced.
To resolve this error, we need to increase the connections limit via the max_connections varibale of MySql/MariaDB. For this we will follow 3 different steps as below:
- Check the current max_connections value
- Change the max_connections value
- Update the Global values
1. Check the current max_connections value
In order to check the value please log in to MySql using the terminal as below:
mysql -u root -p
Once logged in, check the variables using the command:
The result lists out all the variables being used. You can scroll upwards to check the valur of variable max_connections. You can exit using quit; command.
2. Change the max_connections value
In order to change the value please open up the terminal, and edit the configuration file (the availability may differ on different OS installations):
sudo nano /etc/my.cnf
Once the file is open please enter the max connections value directly the first line
And add the number of connections that you want to specify as max (I am adding 1000 for example)
Please save the file and exit the file (Ctrl+O, Ctrl+X).
3. Update the Global values
Please update the global values of max_connections using MySql / MariaDB by logging in via terminal:
mysql -u root -p
You can the set the global value of using the command below (I am using 1000 for example):
SET GLOBAL max_connections=1000;
Thereafter you can quit database using the command below:
That is all you would require to resolve the Too Many Connections error of MySql / MariaDB. You can further monitor the performance using further Sql command or by using MySql Workbench community edition.