Saturday, March 29, 2008

Variable's Day Out #3: max_connect_errors


Applicable To MySQL Server
Server Startup Option --max_connect_errors=<value>
Scope Global
Dynamic Yes
Possible Values Integer
Range: 1 - 4294967295
Default Value 10
Category Security


This variable determines how many interrupted connections can occur from a host. If the number of interrupted connections from this host surpasses this number, that host is blocked from further connections. All of the following, as listed here, will cause the counter to be incremented.

  1. Client program did not call mysql_close() before exiting.
  2. Client had been sleeping more than wait_timeout or interactive_timeout without issuing any requests to the server.
  3. Client program ended abruptly in the middle of a data transfer.
  4. Client doesn't have privileges to connect to a database.
  5. Client uses incorrect password.
  6. It takes more than connect_timeout seconds to get a connect packet.

You can always unblock the hosts with a FLUSH HOSTS statement.

Prior to 5.1, any error would just increment the counter for a host. In 5.1, if a host is not yet blocked, any successful handshake resets the counter to 0.


It depends on the system admin, how they want to use this variable. For example, If the MySQL server is at a physically distant location from it's clients, one might not want unnecessary network issues to get a host blocked. Otherwise, if the expected client machines are somewhat known, one might not want to invite hackers.

I generally consider a higher value for this variable to be a security threat, as some unfriendly people may use it for a DOS attack. It's better to keep the value low and system admin aware of the hosts being blocked.

Read More:


Hope this post was helpful to you. Keep posting your comments.


Anonymous said...

How do you get a list of the hosts being blocked? I am trying to find a way to monitor for this condition.

Parvesh Garg said...

as per my knowledge, there is no direct way of knowing the blocked hosts. MySQL stores this information in host cache, to which there is no direct access.

Meanwhile, if you are experimental enough, Jeremy Cole posted a patch some time ago that implemented SHOW HOST CACHE command. You may check his post here

Anonymous said...


Where this counter is set. On client side or Server side? Say, I have two nodes A & B. A is trying to connect to B, but blocked. Now where the "maximum_connect_error" is set for A. B's server side or A's client side?