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.

Today my DB server reached the maximum number of connections and my web applications (in another server) show "too many connections" error message. After that I restarted MySQL server but a new problem shown. The applications start showing another error " Can't connect to MySQL server  'DB server IP' on (4)". I can connect to MySQL locally and run quires without any problem. I can telnet from web-server to port # 3306 in the DB server. I cleared the MySQL error log file and I did the test again but I did not find any problem. I restarted both servers to make sure all connections are killed but still the same problem exists after restarting the servers. Network is fine, no network problem between both servers, no packets drop, no latency, no error messages.After three hours the DB server back to work normally!

what is the expected reasons for this problem? Why the server back to work after three hours? is there any time-out parameter related to this?

 Edit: My application is PHP and web-server is apache2.
share|improve this question

2 Answers 2

Maybe you could run FLUSH HOSTS;

According to http://dev.mysql.com/doc/refman/5.0/en/flush.html

Empties the host cache tables. You should flush the host tables if some of your hosts change IP address or if you get the error message Host 'host_name' is blocked. When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host tables enables further connection attempts from the host. See Section C.5.2.6, “Host 'host_name' is blocked”. You can start mysqld with --max_connect_errors=999999999 to avoid this error message

I would also look into lowering the foloowing:
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size

and increasing max_connections

share|improve this answer
    
I increased the max_connect_error and max_connections and I rebooted (hard reboot) the server but I faced the same error. I think rebooting the server (hard reboot) will flush hosts. Am I right? –  usef_ksa Apr 17 '11 at 18:17
    
You may need to do two things: 1. wget mysqltuner.pl 2. perl mysqltuner.pl and let it report how memory mysql thinks it should have. It may be too high. You have to then adjust the 5 parameters I mentioned in my answer. –  RolandoMySQLDBA Apr 18 '11 at 2:47

It sounds like your developers are not correctly closing their connections in the code. In C#, that would be something like this:

var cn = new MySqlConnection(" connection string " );
var cmd = new MySqlCommand(" sql string ", cn);

cn.Open():
cmd.ExecuteNonQuery();
cn.Close();

The above code is fundamentally flawed, because an error in the sql code will result in an exception, and thus the code to close your connection is never reached. Instead, they need to write it like this:

MySqlConnection cn;
try
{
    cn = new MySqlConnection(" connection string ");
    var cmd = new MySqlCommand(" sql string ", cn);

    cn.Open():
    cmd.ExecuteNonQuery();
}
finally
{
    cn.Close();
}

And actually C# has some better patterns they should really use. But this illustrates the point in a way that applies to other languages as well — whatever your platform, the client code to close your database connections must happen in a way that accounts for exceptions.

share|improve this answer
    
My application is PHP. My web-server is apache2. Yes the application do not close the connection because it will be automatically closed after the end of script execution.php.net/manual/en/function.mysql-close.php –  usef_ksa Apr 17 '11 at 18:06
    
@usef_ksa - something is keeping it from doing the normal release of resources at the end of your script execution, then. –  Joel Coel Apr 17 '11 at 19:31

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.