The host_cache Table

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 DNS lookups. (See , "DNS Lookup Optimization and the Host Cache".) The host_cache 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.

The server handles rows in the host_cache table like this:

1. When the first TCP client connection reaches the server from a given IP address, a new host_cache row is created with the IP column set to the client IP, HOST set to NULL, and HOST_VALIDATED set to NO. This row is also used for subsequent client connections from the same originating IP.
2. If HOST_VALIDATED column for the client IP row is NO, the server attempts an IP-to-host name DNS resolution. If that is successful, the HOST column is updated with the resolved host name and HOST_VALIDATED is set to YES. If resolution is unsuccessful, the action taken depends on whether the error is permanent or transient. For permanent failures, the HOST column is NULL and HOST_VALIDATED is set to YES. For transient failures, the HOST and HOST_VALIDATED columns remain unchanged. (Another DNS resolution attempt occurs the next time a client connects from this IP.)
3. If an error occurs while processing an incoming client connection from a given IP address, the server updates the corresponding COUNT_XXX_ERRORS statistics columns in the row for that IP. The kinds of errors accounted for are indicated by the column descriptions.

FLUSH HOSTS and TRUNCATE TABLE host_cache have the same effect: They clear the host cache. This also empties the and empty the contents of the host_cache table (because it is the visible representation of the cache) and unblocks any blocked hosts (see "Host 'host_name' is blocked".) FLUSH HOSTS requires the RELOAD privilege. TRUNCATE TABLE requires the DROP privilege for the host_cache table.

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 calls to DNS can be avoided. While HOST_VALIDATED is NO, DNS resolution is attempted again for each connect, 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 affect clients forever.

• SUM_CONNECT_ERRORS

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

• 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. MariaDB does not perform matching of Host column values in the mysql.user 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. See , "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 user from the client host can possibly log in. 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 a proxy user A is proxied to another user B who does not exist.

• COUNT_PROXY_USER_ACL_ERRORS

  The number of errors detected when a 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 , "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 , "Setting Account Resource Limits".

• COUNT_DEFAULT_DATABASE_ERRORS

  The number of errors related to the default database. For example, the database did not exist or the user had no privileges for accessing 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 table 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 last 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 last error seen from the client in the IP column.

The host_cache table was added in MariaDB 5.6.5.

Retornar