ERROR 1040 (HY000): Too many connections

ERROR 1040 (HY000): Too many connections

ERROR 1040 (HY000): Too many connections

An application might face this issue when all the defined max_connections parameter value is exhausted. 

mysqld reserve a  +1 connection (i.e., max_connections + 1) for the user who is having the SUPER privilege. 

When this issue appears, Only the user with the SUPER privileges can be connected if any other user who has SUPER privileges is not already connected to the database and we have reached max_connections. 


1. Always make sure to DON'T give the SUPER privilege to any application user or any user which is being used inside the scripts. Because if that user has already been connected to db when "too many connections" issue appears, then that time root user can't be connected even though root user is having all privileges along with SUPER privileges. 

Here I am presenting 3 cases which will help us, how MySQL handles "too many connections" and reserve 1 connection. For below example, we have manually set the max_connections parameter value to 1. 

Case 1 :

When We have SUPER privilege with the only root user account and root user is already connected to the database. Then, in this case, an app user doesn't have SUPER privilege, It can't use reserve connection and will receive "Too many connections" error. 

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

+------------------------------------------+
| Grants for app@localhost                 |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'app'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

+--------------------------+
| @@global.max_connections |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

Terminal 1 :
[root@centos7-test-vm ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.25-28 Percona Server (GPL), Release 28, Revision c335905
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Terminal 2:
[root@centos7-test-vm ~]# mysql -u app -p
Enter password:
ERROR 1040 (08004): Too many connections
[root@centos7-test-vm ~]# 

Case 2:

When app user is already connected to the database and we have exhausted max_connections parameter value but As root user have SUPER privileges, It can use the reserve connection, and it can connect to database even though max_connection exhausted. 

Terminal 1:-
[root@centos7-test-vm ~]# mysql -u app -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.25-28 Percona Server (GPL), Release 28, Revision c335905
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Terminal 2:-
[root@centos7-test-vm ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.25-28 Percona Server (GPL), Release 28, Revision c335905
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

mysql> show global status like '%threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> select @@global.max_connections;
+--------------------------+
| @@global.max_connections |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec) 
Case 3 :

Here We have 3 users (app/abhinavbit/root). All user have SUPER privilege. Here you can observe First app user is connected and it exhausted the max_connection parameter value but As abhinavbit user have SUPER privilege, it can now use the reserve connection, and it can connect. 

Now you will see if any of the other users will try to connect db that will get "too many connections" error even though that is root user or any user with SUPER privileges. 


+-------------------------------------------------+
| Grants for app@localhost                        |
+-------------------------------------------------+
| GRANT SELECT, SUPER ON *.* TO 'app'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| Grants for abhinavbit@localhost                        |
+--------------------------------------------------------+
| GRANT SELECT, SUPER ON *.* TO 'abhinavbit'@'localhost' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------+
| @@global.max_connections |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

Terminal 1:-

[root@centos7-test-vm ~]# mysql -u app -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.25-28 Percona Server (GPL), Release 28, Revision c335905
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Terminal 2:-

[root@centos7-test-vm ~]# mysql -u abhinavbit -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.25-28 Percona Server (GPL), Release 28, Revision c335905
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
+--------------------------+
| @@global.max_connections |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

Terminal 3:-

[root@centos7-test-vm ~]# mysql -u root -p
Enter password:
ERROR 1040 (HY000): Too many connections
[root@centos7-test-vm ~]# 



2. There is an alternate way too, to forcefully take connection into the database using the gdb. 
You need to install gdb and then run below command (as explained in Percona blog).

gdb -p $(cat MySQL_PID_FILE_PATH) -ex "set max_connections=1000" -batch 

3. Check if there is any cron script is running which is using root user or any user with SUPER privileges. If possible, kill the script to release some connections. 

4. You can stop application services to release MySQL connections.

5. At the end, If none above options work then, it is recommended put some higher value of the max_connections parameter inside my.cnf file and then restart MySQL service. 

6. If you are using the percona server, then you can always set extra_max_connections parameter value other than the default value of 1. :) 
Note: This parameter value works with the extra_port variable.


***Never give SUPER privilege to non-admin user***





Photo by Berto Macario on Unsplash

Comments

Popular

SLEEP thread causing "Waiting for table metadata lock"

MySQL Memory Calculator

How to create CentOS VM using Vagrant

errno: 24 - Too many open files

How to properly shutdown MySQL before any maintenance activity

set --server-id to enable either a master or a slave

GTID Replication (Skip Transaction using empty transaction)

Master has purged binary logs containing GTIDs that the slave requires

How to Prepare for MySQL Certification Exam