Bug #45817 Please add SHOW command for inc_host_errors(max_connect_errors)
Submitted: 29 Jun 2009 7:17 Modified: 20 Mar 2012 16:35
Reporter: Meiji KIMURA Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Marc Alff
Triage: Needs Triage: D5 (Feature request)

[29 Jun 2009 7:17] Meiji KIMURA
Description:
Now inc_host_errors() increase error count for max_connect_errors.
But there is no methond to check current count.

Please add feature for checking current error count for max_connect_errors.
I supposed that it should be implemented as 'SHOW GLOBAL VARIABLES;'.

How to repeat:
N/A.

Suggested fix:
N/A.
[29 Jun 2009 10:19] Valerii Kravchuk
Do you want current number of errors to be reported for each host? Then it is NOT for SHOW GLOBAL STATUS, IMHO, as there can be hundred(s) of individual hosts...
[30 Jun 2009 0:24] Meiji KIMURA
You are right.

It is impossible to show all cached host via 'SHOW GLOBAL VARIABLES;'.

So we have to make new 'SHOW' option like 'SHOW HOSTCACHELIST;'.
[30 Jun 2009 8:24] Valerii Kravchuk
Thank you for clarification. I had changed Synopsis a bit to reflect the real feature that would be nice to have some day.
[30 Jun 2009 13:10] Mikiya Okuno
I made a patch which displays host information from I_S like below:

mysql> select * from information_schema.known_hosts;
+--------------+----------+--------+
| IP_ADDRESS   | HOSTNAME | ERRORS |
+--------------+----------+--------+
| 192.168.1.8  | xel-naga |      0 | 
| 192.168.1.33 | sol-hfx  |      0 | 
+--------------+----------+--------+
2 rows in set (0.00 sec)

Looks good?
[30 Jun 2009 13:10] Mikiya Okuno
patch for mysql 5.1.35

Attachment: known_hosts.patch (application/octet-stream, text), 3.99 KiB.

[30 Jun 2009 14:07] Mikiya Okuno
another patch

Attachment: show-hosts.patch (application/octet-stream, text), 5.84 KiB.

[30 Jun 2009 14:09] Mikiya Okuno
i modified something in show-hosts.patch as follows:

o made INFORMATION_SCHEMA.KNOWN_TABLES hidden
o added SHOW HOSTS command
o ensure SHOW HOSTS command needs SUPER priv

This must be secure enough.

Kind regards ;)
[3 Dec 2009 0:50] Andrew Dalgleish
See bug 24906
[6 Feb 2010 19:20] Matt Lord
This is related to this feature request:
http://bugs.mysql.com/bug.php?id=50963
[20 Mar 2012 16:35] 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).