Home / server / Questions / 260652
In Process
usef_ksa
Asked: 2011-04-18 06:30:04 +0800 CST

Cannot connect to MySQL server after too many connections?

  • 772

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.
connection mysql

2 Answers

  1. 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.

    • 2

  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

    • 1