Mysql – Cannot connect to MySQL server after too many connections

connectionMySQL

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.

Best Answer

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.

Related Topic