MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The MySQL server maintains an in-memory host cache that
contains client host name and IP address information and is
used to avoid Domain Name System (DNS) lookups. The
host_cache table exposes the
contents of this cache. The
host_cache_size system
variable controls the size of the host cache, as well as the
size of the host_cache table. For
operational and configuration information about the host
cache, see Section 7.1.12.3, “DNS Lookups 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 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 8.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 8.2.21, “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 8.2.21, “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)
TRUNCATE TABLE is permitted for
the host_cache table. It requires
the DROP privilege for the
table. Truncating the table flushes the host cache, which has
the effects described in
Flushing the Host Cache.