Rechercher dans le manuel MySQL

26.12.17.1 The host_cache Table

Table des matières

The host_cache table provides access to the contents of the host cache, which contains client host name and IP address information and is used to avoid Domain Name System (DNS) lookups. The host_cache_size system variable controls the size of the host cache, as well as the size of the host_cache table that exposes the cache contents. For operational and configuration information about the host cache, see Section 8.12.4.2, “DNS Lookup Optimization and the Host Cache”.

Because the host_cache table exposes the contents of the host cache, it can be examined using SELECT statements. This may help you diagnose the causes of connection problems. The Performance Schema must be enabled or this table is empty.

The host_cache table has these columns:

  • IP

    The IP address of the client that connected to the server, expressed as a string.

  • HOST

    The resolved DNS host name for that client IP, or NULL if the name is unknown.

  • HOST_VALIDATED

    Whether the IP-to-host name-to-IP DNS resolution was performed successfully for the client IP. If HOST_VALIDATED is YES, the HOST column is used as the host name corresponding to the IP so that additional calls to DNS can be avoided. While HOST_VALIDATED is NO, DNS resolution is attempted for each connection attempt, until it eventually completes with either a valid result or a permanent error. This information enables the server to avoid caching bad or missing host names during temporary DNS failures, which would negatively affect clients forever.

  • SUM_CONNECT_ERRORS

    The number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES).

    Once SUM_CONNECT_ERRORS for a given host reaches the value of max_connect_errors, new connections from that host are blocked. The SUM_CONNECT_ERRORS value can exceed the max_connect_errors value because multiple connection attempts from a host can occur simultaneously while the host is not blocked. Any or all of them can fail, independently incrementing SUM_CONNECT_ERRORS, possibly beyond the value of max_connect_errors.

    Suppose that max_connect_errors is 200 and SUM_CONNECT_ERRORS for a given host is 199. If 10 clients attempt to connect from that host simultaneously, none of them are blocked because SUM_CONNECT_ERRORS has not reached 200. If blocking errors occur for five of the clients, SUM_CONNECT_ERRORS is increased by one for each client, for a resulting SUM_CONNECT_ERRORS value of 204. The other five clients succeed and are not blocked because the value of SUM_CONNECT_ERRORS when their connection attempts began had not reached 200. New connections from the host that begin after SUM_CONNECT_ERRORS reaches 200 are blocked.

  • COUNT_HOST_BLOCKED_ERRORS

    The number of connections that were blocked because SUM_CONNECT_ERRORS exceeded the value of the max_connect_errors system variable.

  • COUNT_NAMEINFO_TRANSIENT_ERRORS

    The number of transient errors during IP-to-host name DNS resolution.

  • COUNT_NAMEINFO_PERMANENT_ERRORS

    The number of permanent errors during IP-to-host name DNS resolution.

  • COUNT_FORMAT_ERRORS

    The number of host name format errors. MySQL does not perform matching of Host column values in the mysql.user system table against host names for which one or more of the initial components of the name are entirely numeric, such as 1.2.example.com. The client IP address is used instead. For the rationale why this type of matching does not occur, see Section 6.2.4, “Specifying Account Names”.

  • COUNT_ADDRINFO_TRANSIENT_ERRORS

    The number of transient errors during host name-to-IP reverse DNS resolution.

  • COUNT_ADDRINFO_PERMANENT_ERRORS

    The number of permanent errors during host name-to-IP reverse DNS resolution.

  • COUNT_FCRDNS_ERRORS

    The number of forward-confirmed reverse DNS errors. These errors occur when IP-to-host name-to-IP DNS resolution produces an IP address that does not match the client originating IP address.

  • COUNT_HOST_ACL_ERRORS

    The number of errors that occur because no users are permitted to connect from the client host. In such cases, the server returns ER_HOST_NOT_PRIVILEGED and does not even ask for a user name or password.

  • COUNT_NO_AUTH_PLUGIN_ERRORS

    The number of errors due to requests for an unavailable authentication plugin. A plugin can be unavailable if, for example, it was never loaded or a load attempt failed.

  • COUNT_AUTH_PLUGIN_ERRORS

    The number of errors reported by authentication plugins.

    An authentication plugin can report different error codes to indicate the root cause of a failure. Depending on the type of error, one of these columns is incremented: COUNT_AUTHENTICATION_ERRORS, COUNT_AUTH_PLUGIN_ERRORS, COUNT_HANDSHAKE_ERRORS. New return codes are an optional extension to the existing plugin API. Unknown or unexpected plugin errors are counted in the COUNT_AUTH_PLUGIN_ERRORS column.

  • COUNT_HANDSHAKE_ERRORS

    The number of errors detected at the wire protocol level.

  • COUNT_PROXY_USER_ERRORS

    The number of errors detected when proxy user A is proxied to another user B who does not exist.

  • COUNT_PROXY_USER_ACL_ERRORS

    The number of errors detected when proxy user A is proxied to another user B who does exist but for whom A does not have the PROXY privilege.

  • COUNT_AUTHENTICATION_ERRORS

    The number of errors caused by failed authentication.

  • COUNT_SSL_ERRORS

    The number of errors due to SSL problems.

  • COUNT_MAX_USER_CONNECTIONS_ERRORS

    The number of errors caused by exceeding per-user connection quotas. See Section 6.3.6, “Setting Account Resource Limits”.

  • COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS

    The number of errors caused by exceeding per-user connections-per-hour quotas. See Section 6.3.6, “Setting Account Resource Limits”.

  • COUNT_DEFAULT_DATABASE_ERRORS

    The number of errors related to the default database. For example, the database does not exist or the user has no privileges to access it.

  • COUNT_INIT_CONNECT_ERRORS

    The number of errors caused by execution failures of statements in the init_connect system variable value.

  • COUNT_LOCAL_ERRORS

    The number of errors local to the server implementation and not related to the network, authentication, or authorization. For example, out-of-memory conditions fall into this category.

  • COUNT_UNKNOWN_ERRORS

    The number of other, unknown errors not accounted for by other columns in this table. This column is reserved for future use, in case new error conditions must be reported, and if preserving the backward compatibility and structure of the host_cache table is required.

  • FIRST_SEEN

    The timestamp of the first connection attempt seen from the client in the IP column.

  • LAST_SEEN

    The timestamp of the most recent connection attempt seen from the client in the IP column.

  • FIRST_ERROR_SEEN

    The timestamp of the first error seen from the client in the IP column.

  • LAST_ERROR_SEEN

    The timestamp of the most recent error seen from the client in the IP column.

The host_cache table has these indexes:

  • Primary key on (IP)

  • Index on (HOST)

The FLUSH HOSTS statement, TRUNCATE TABLE host_cache statement, and mysqladmin flush-hosts command have the same effect: They clear the host cache, remove all rows from the host_cache table that exposes the cache contents, and unblock any blocked hosts (see Section B.6.2.5, “Host 'host_name' is blocked”). FLUSH HOSTS and mysqladmin flush-hosts require the RELOAD privilege. TRUNCATE TABLE requires the DROP privilege for the host_cache table.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-host-cache-table.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut