ERROR 1040 (HY000): Too many connections: MySQL

ERROR 1040 (HY000): Too many Connections





Login to your MySQL Server and check following

show global status like '%max_used%';

+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 3                   |
| Max_used_connections_time | 2017-06-08 10:39:06 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)


show variables like '%max%connection%';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 3     |
| max_user_connections | 2     |
+----------------------+-------+
2 rows in set, 1 warning (0.00 sec)

Now was you can see here total allowed Connection (max_connections) are 3 and all the Connections are already used. Trying to establish session (conection) beyond this value is causing ERROR 1040 (HY000): Too many Connections error.


Solutions

Identify ideal Connection and kill if possible to free up the Connection. In my case both the session (ID 11 and 12 ) are sleeping and I can kill any one of them to free up 1 Connection.

Please be Aware of the risk if you are trying to kill the session in Prod Environment


show processlist;

+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 11 | test | localhost:55377 | menagerie | Sleep   |  887 |          | NULL             |
| 12 | test | localhost:55488 | menagerie | Sleep   |  643 |          | NULL             |
| 15 | root | localhost:55880 | NULL      | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+

kill 11;

Query OK, 0 rows affected (0.00 sec)

show processlist;

+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 12 | test | localhost:55488 | menagerie | Sleep   |  913 |          | NULL             |
| 16 | root | localhost:56735 | NULL      | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+
2 rows in set (0.00 sec)


OR

Increase the value of max_connections variable

set global max_connections=10;

Query OK, 0 rows affected (0.00 sec)

show variables like '%max%connection%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 10    |
| max_user_connections | 2     |
+----------------------+-------+
2 rows in set, 1 warning (0.00 sec)


Note:- Please beaware of that the value changed using set command here will not be persistent acroos the restart of your MySQL Server and therefore to make the Setting persistent across the restart please modify my.cnf or my.ini accordingly whatever applicable


If you like my work then please like the post and leave your comment in the Comment section


No comments:

Post a Comment