ERROR 1040 (HY000): Too many connections

By

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

Post a Comment

Popular

SLEEP thread causing "Waiting for table metadata lock"

By
Image
SLEEP thread causing "Waiting for table metadata lock" We might have faced some scenarios once our db connection threads are getting locked and we try to find the culprit thread id which is locking the thread, to kill and release the connections.  Here I am going to focus on one of the scenarios when db connection threads are locked at state " waiting for table metadata lock " and when we see the processlist, we can't find any thread which is running and using the same table, i.e., all threads are in SLEEP state except the locked threads. Such as :  mysql [localhost:5726] {msandbox} (world) > show processlist\G *************************** 1. row ***************************            Id: 5          User: rsandbox          Host: localhost:60624            db: NULL       Command: Binlog Dump          Time: 111519         State: Master has sent all binlog to slave; waiting for more updates          Info: NULL     Ro
Read more

MySQL Memory Calculator

By
Image
MySQL Memory Calculator Check out the “ MySQL Memory Calculator ” added to my blog page, which will be helpful to calculate MySQL memory usage during tuning of MySQL memory parameters. https://www.abhinavbit.com/p/mysql-memory-calculator.html Photo by  Kelly Sikkema  on  Unsplash
Read more

How to create CentOS VM using Vagrant

By
Image
Create CentOS VM using Vagrant 1. Download and install the latest Oracle VM VirtualBox.  https://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html Like (For Mac OS X) https://download.oracle.com/virtualbox/6.0.6/VirtualBox-6.0.6-130049-OSX.dmg 2. Download and install the latest Vagrant. https://www.vagrantup.com/downloads.html Like (For Mac OS X) https://releases.hashicorp.com/vagrant/2.2.4/vagrant_2.2.4_x86_64.dmg 3. Download " centos /7" Vagrant Box using below command. abhinavs -Air : ~ agupta$ vagrant box add centos /7 ==> box: Loading metadata for box ' centos /7'      box : URL: https://vagrantcloud.com/centos/7 This box can work with multiple providers! The providers that it can work with are listed below. Please review the list and choose the provider you will be working with. 1) hyperv 2) libvirt 3) virtualbox 4) vmware_desktop Enter your choice: 3 ==> box: Addin
Read more

errno: 24 - Too many open files

By
Image
errno: 24 - Too many open files You might see this error inside MySQL's error log file or during mysql_upgrade operation. Can't open file: ‘./db/table.frm' (errno: 24 - Too many open files) To solve this issue, you will need to change the open files limit at system and db service level. --> To check and change this limit at the system level, we use ulimit client tool to set it dynamically. * Check current system open file limit. root@test:~# ulimit -a core file size          (blocks, -c) 0 data seg size           (kbytes, -d) unlimited scheduling priority             (-e) 0 file size               (blocks, -f) unlimited pending signals                 (-i) 515256 max locked memory       (kbytes, -l) 64 max memory size         (kbytes, -m) unlimited open files                      (-n) 1024 pipe size            (512 bytes, -p) 8 POSIX message queues     (bytes, -q) 819200 real-time priority              (-r) 0 stack size     
Read more

How to properly shutdown MySQL before any maintenance activity

By
Image
How to properly shutdown MySQL before any maintenance activity We might have different scenarios once we need to stop MySQL service before performing either server or database activity like patching/upgrade.   Before performing such activity, we need to make sure that we stop MySQL service properly to avoid any unforeseen crashes once our maintenance activity complete and MySQL service is started.  Here I would like to share the below steps which should be performed and take care to properly stop MySQL service.  Step 1: Ensure we don't have any long-running queries. Manually verify by checking the processlist. show processlist;  Step 2: If it is the master node, then ensure to stop application write hits on the database by doing either stop an app and put in maintenance OR if we have any failover solution then move write hits on another node.  Step 3: If it is a slave node, then STOP replication and note down the stopped replication co
Read more

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

By
set --server-id to enable either a master or a slave Below issue can be faced, when we run CHANGE MASTER command to setup replication. mysql> change master to master_host='xx.xx.xx.xx',master_user='rep_user',master_password="xxxxxxx", master_log_file='db-01-test-bin.000064',master_log_pos=95854072,master_auto_position=0; ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log. Solution :- Here It is assumed, We have already set server-id parameter value different on master and slave node but still, we have the above error. It happens once, you performed the server packages upgrade(like from 5.6 to 5.7) and just started MySQL service and going to run CHANGE MASTER command. So, to solve this issue, Run mysql_upgrade command to update/upgrade system tables and then restart M
Read more

GTID Replication (Skip Transaction using empty transaction)

By
Image
GTID Replication (Skip Transaction using empty transaction) To skip SQL thread's error in GTID replication setup, Insert empty transaction. PROBLEM :  db -test (none ) > show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: xxxxx                   Master_User: rep_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: db -test-02-bin . 006725           Read_Master_Log_Pos: 4508785                Relay_Log_File: db -test-relay-bin . 000015                 Relay_Log_Pos: 2146333         Relay_Master_Log_File: db -test-02-bin . 006725              Slave_IO_Running: Yes             Slave_SQL_Running: No               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Igno
Read more

Master has purged binary logs containing GTIDs that the slave requires

By
Master has purged binary logs containing GTIDs that the slave requires Problem :  Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' Solution : stop slave; Query OK, 0 rows affected (0.00 sec) CHANGE MASTER TO MASTER_AUTO_POSITION = 0; Query OK, 0 rows affected (0.01 sec) start slave; Query OK, 0 rows affected (0.00 sec)
Read more

How to Prepare for MySQL Certification Exam

By
Image
How to Prepare for MySQL Certification Exam Here I am going to suggest MySQL DBA, student or any newbie that how they can prepare for "Oracle's MySQL 5.x Database Administrator" certification exam.  Step 1: Lab -- Prepare your local MySQL lab environment and make sure to install the respective MySQL version on your lab. (i.e., if you are going to appear on MySQL 5.6 DBA exam 1Z0-883 then install MySQL 5.6 on your lab)  -- You can follow my below blog post to create your local MySQL lab environment.  How to create CentOS VM using Vagrant Install MySQL on CentOS using DBdeployer Step 2: Book + HandsOn Experience -- If you don't have below book, then go right now and purchase this book. (Here Please, you don't judge that below-suggested book is for 5.0 version whereas you are going to prepare for MySQL 5.6/5.7 DBA certification exam. Just go ahead and purchase below book. In the end, you will definitely say it was your
Read more