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.
-
3Step 1 - figure out why you have so many connection. Step 2 - resolve the issue causing connections to not close.– OdedApr 23, 2011 at 18:20
-
Windows, or *nix?– JoeOct 7, 2011 at 15:33
-
Pull the network cable?– Joel CoelSep 9, 2015 at 1:27
Add a comment
|
6 Answers
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:
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.
answered Oct 7, 2011 at 15:29
You have the three following options:
Start -> Run -> Services.msc
Find MySQL, rightclick and choose restartStart -> Run -> CMD
net stop MySQL enter
net start MySQL enter- Kill all instances of mySQLd.exe in the taskpanel. (not recommended)
if you have access to your server, restart mysql. if you dont have access to your server, simply wait.
-
how can I restart it? I do have access to it– LucasApr 23, 2011 at 17:22
-
if you are running on linux, try writing "sudo /etc/rc.d/mysql restart". if you are on another distro or os, google for help.– T4uApr 23, 2011 at 17:24
-
windows. I did google for help but I couldn't find anything! it tells something about cmd but the server denies access– LucasApr 23, 2011 at 17:29
-
1. Press Start > Control Panel > Administrative Controls > Services 2. Select mysql, right click then select Restart on the options.– T4uApr 23, 2011 at 17:32
-
I did it but it keeps telling me "too many connections"– LucasApr 23, 2011 at 17:44
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!
-
2Just so you know, it is no longer common practice to create connections in secondary classes. Instead, you should establish the connection at the point where you are going to issue your query. Further, all of the connection and command code should be wrapped in USING clauses because they are unmanaged resources. Lower levels than your code are much better at handling connection pooling to keep response times up while lowering db server load.– NotMeOct 7, 2011 at 15:56
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.
