Bug #24906 No command to monitor blocked hosts
Submitted: 8 Dec 2006 12:16 Modified: 20 Mar 2012 16:33
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Logging Severity:S4 (Feature request)
Version:5.0.x OS:Any (any)
Assigned to: Marc Alff CPU Architecture:Any
Tags: flush hosts, max_connect_errors
Triage: Triaged: D5 (Feature request)

[8 Dec 2006 12:16] Kristian Koehntopp
Description:
According to http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html and http://dev.mysql.com/doc/refman/5.0/en/flush.html, a host is blocked from connecting when it has more than max_connect_errors (default 10) connection errors. It can be unblocked using the "flush hosts" command.

According to the manual there is no way to monitor the list of blocked hosts. It is therefore impossible to write a nagios or other monitoring agent that will alert the customer of an ongoing connection problem properly (with hostnames).

How to repeat:
Have a setup where a host is blocked due to connection errors by the server. Try to find out when this happens and try to generate a message in monitoring to inform operating of the problem.

Suggested fix:
Implement a SHOW command for this list. Create an INFORMATION_SCHEMA table (or PERFORMANCE_SCHEMA table) listing

- the hostname or ip of the blocked hosts
- a failed attempt counter
- the timestamp of the first recorded attempt
- the timestamp of the latest recorded attempt
- more detailed error information for each host, if available (such as a reason)
[8 Dec 2006 18:18] Valeriy Kravchuk
Thank you for a reasonable feature request.
[8 Oct 2008 12:50] Paul Kelly
Hi has been open for a _long_ time now, how about it gets put into a new build sometime soon?
[3 Dec 2009 0:49] Andrew Dalgleish
See bug 45817
[20 Mar 2012 16:33] Paul Dubois
Noted in 5.6.5 changelog.

MySQL now provides improved access to the host cache, which contains
client host name and IP address information and is used to avoid DNS
lookups. These improvements have been implemented: 

* A host_cache Performance Schema table exposes the contents of the
  host cache so that it can be examined using SELECT statements. The
  Performance Schema must be enabled or this table is empty. 

  If you upgrade to this release of MySQL from an earlier version, you
  must run mysql_upgrade (and restart the server) to incorporate this
  change into the performance_schema database. 

* The cache size is configurable using the host_cache_size system
  variable. Setting the size to 0 disables the host cache.This is
  similar to starting the server with --skip-host-cache, but
  host_cache_size is more flexible because it can also be used to
  resize, enable, or disable the host cache at runtime, not just at
  server startup. If you start the server with --skip-host-cache to
  disable the host cache, it cannot be re-enabled at runtime. 

* There are Connection_errors_xxx status variables that provide
  information about the nature of connection errors and that can help
  diagnose connection problems. 

Improved access to host cache contents makes it possible to answer
questions such as how many hosts are cached, or how close hosts are
to being blocked (by checking whether the
host_cache.SUM_CONNECT_ERRORS column is approaching the value of the
max_connect_errors system variable).