Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 23 '11 at 22:53

This question came from our site for professional and enthusiast programmers.

2  
Step 1 - figure out why you have so many connection. Step 2 - resolve the issue causing connections to not close. –  Oded Apr 23 '11 at 18:20
    
Windows, or *nix? –  Joe Oct 7 '11 at 15:33
add 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.

share|improve this answer
add comment

You have to following options:

  1. Start -> Run -> Services.msc
    Find MySQL, rightclick and choose restart

  2. Start -> Run -> CMD
    net stop MySQL enter
    net start MySQL enter

  3. Kill all instances of mySQLd.exe in the taskpanel. (not recommended)
share|improve this answer
add comment

if you have access to your server, restart mysql. if you dont have access to your server, simply wait.

share|improve this answer
    
how can I restart it? I do have access to it –  Lucas Apr 23 '11 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. –  T4u Apr 23 '11 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 –  Lucas Apr 23 '11 at 17:29
    
1. Press Start > Control Panel > Administrative Controls > Services 2. Select mysql, right click then select Restart on the options. –  T4u Apr 23 '11 at 17:32
    
I did it but it keeps telling me "too many connections" –  Lucas Apr 23 '11 at 17:44
show 2 more comments

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.

share|improve this answer
add comment

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!

share|improve this answer
1  
Just 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. –  Chris Lively Oct 7 '11 at 15:56
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.