I've opened too many connections to MySQL and now I can't access it. It says "Too many connections". How can I close all of these connections?
I also don't know how to restart it, which may resolve the problem.
The SUPER privilege enables an account to use CHANGE MASTER TO, KILL
or mysqladmin kill to kill threads belonging to other accounts (you
can always kill your own threads), PURGE BINARY LOGS, configuration
changes using SET GLOBAL to modify global system variables, the
mysqladmin debug command, enabling or disabling logging, performing
updates even if the read_only system variable is enabled, starting and
stopping replication on slave servers, specification of any account in
the DEFINER attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
max_connections system variable is reached.
In light of this, regular client connections should not have SUPER privilege. Once the number of DB Connections reached = max_connections, only one more connection will be allowed and that one connetion has to have SUPER privilege. If everyone and his grandmother has the SUPER privilege, all bets are off and nobody can login.
Some web applications will always report "too many connections" if they can not connect to the database. If you are getting this error even after a MySQL restart then the real error is probably "no connection". Try connecting to MySQL some other way, command line, phpmydmin or another MySQL driven site on the same server.
If you can connect to MySQL via alternate means check the connection string in your website (The bit that gives username and password for MySQL) then connect with those credentials, something may have been changed but not been updated in the script.
If you can't connect to MySQL at all and restarting MySQL did not fix it then you will need to look at your event log (You said it was Windows server in a comment). Restarting the server is a shortcut that will rule out lots of issues, also check your not out of disk space.
I solved it! It was a mistake in the C# code, where I accidentally looped the "connection.Open()". -.-
Class1 said 'hey Class2, open the connection!' and Class2 answered 'connection opened! now open a connection, Class1!'. Then Class1 opened a connection and said to Class2 'open a connection too!' and soooo on...
I'm sorry for the dumbness, I just hadn't realized about that -.-
You should be able to get into mysql with the root user.
From there you can run a "show processlist".
You can then run kill 'Id' to kill the connections.
I answered this question on mass killing DB Connections in the DBA StackExchange back in May 2011.
What can allow a person to login to mysql under such stressful conditions? The answer is quite simple : DO NOT GIVE EVERYBODY THE SUPER PRIVILEGE !!!
Why the SUPER Privilege ?
According to the MySQL Documentation:
In light of this, regular client connections should not have SUPER privilege. Once the number of DB Connections reached = max_connections, only one more connection will be allowed and that one connetion has to have SUPER privilege. If everyone and his grandmother has the SUPER privilege, all bets are off and nobody can login.
You have the three following options:
Start -> Run -> Services.msc
Find MySQL, rightclick and choose restart
Start -> Run -> CMD
net stop MySQL enter
net start MySQL enter
if you have access to your server, restart mysql. if you dont have access to your server, simply wait.
Some web applications will always report "too many connections" if they can not connect to the database. If you are getting this error even after a MySQL restart then the real error is probably "no connection". Try connecting to MySQL some other way, command line, phpmydmin or another MySQL driven site on the same server.
If you can connect to MySQL via alternate means check the connection string in your website (The bit that gives username and password for MySQL) then connect with those credentials, something may have been changed but not been updated in the script.
If you can't connect to MySQL at all and restarting MySQL did not fix it then you will need to look at your event log (You said it was Windows server in a comment). Restarting the server is a shortcut that will rule out lots of issues, also check your not out of disk space.
I solved it! It was a mistake in the C# code, where I accidentally looped the "connection.Open()". -.-
Class1 said 'hey Class2, open the connection!' and Class2 answered 'connection opened! now open a connection, Class1!'. Then Class1 opened a connection and said to Class2 'open a connection too!' and soooo on...
I'm sorry for the dumbness, I just hadn't realized about that -.-
3 days stuck (Y)
Cheers!
You should be able to get into mysql with the root user.
From there you can run a "show processlist". You can then run kill 'Id' to kill the connections.