Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 45.3Mb
PDF (A4) - 45.3Mb
PDF (RPM) - 40.8Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 208.5Kb
Man Pages (Zip) - 310.7Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual Error Log Filtering

Error log configuration normally includes one log filter component and one or more log writer component. For error log filtering, MySQL offers a choice of components:

log_filter_internal: Priority-Based Error Log Filtering

Error log verbosity control is a simple form of log filtering based on error event priority. It is implemented by the log_filter_internal log filter component. To affect how log_filter_internal permits or suppresses error, warning, and information events intended for the error log, set the log_error_verbosity system variable. log_filter_internal is built in and enabled by default, but if disabled, changes to log_error_verbosity have no effect.

Permitted log_error_verbosity values are 1 (errors only), 2 (errors and warnings), 3 (errors, warnings, and notes).

If log_error_verbosity is set to 2 or greater, the server logs messages about statements that are unsafe for statement-based logging. If the value is 3, the server logs aborted connections and access-denied errors for new connection attempts. See Section B.6.2.10, “Communication Errors and Aborted Connections”.

If you use replication, setting log_error_verbosity to 2 or greater is recommended, to get more information about what is happening, such as messages about network failures and reconnections.

If a slave server has log_error_verbosity set to 2 or greater, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.

Selected important system messages about non-error situations are printed to the error log regardless of the log_error_verbosity value. These messages include startup and shutdown messages, and some significant changes to settings.

In the MySQL error log, system messages are labeled as System. Other log writers might or might not follow the same convention, and in the resulting logs, system messages might be assigned the label used for the information level of severity, such as Note or Information. If you apply any additional filtering or redirection for logging based on the labeling of messages, system messages do not override your filter, but are handled by it in the same way as other messages.

log_filter_dragnet: Rule-Based Error Log Filtering

The log_filter_dragnet log filter component enables log filtering based on user-defined rules. To define the applicable rules, set the dragnet.log_error_filter_rules system variable.

To enable the log_filter_dragnet filter, first load the filter component, then modify the log_error_services value. The following example enables log_filter_dragnet in combination with the built-in log writer:

INSTALL COMPONENT 'file://component_log_filter_dragnet';
SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';

To set log_error_services to take effect at server startup, use the instructions at Section, “Error Log Component Configuration”. Those instructions apply to other error-logging system variables as well.

With log_filter_dragnet enabled, define its filter rules by setting the dragnet.log_error_filter_rules system variable. A rule set consists of zero or more rules, where each rule is an IF statement terminated by a period (.) character. If the variable value is empty (zero rules), no filtering occurs.

Example 1. This rule set drops information events, and, for other events, removes the source_line field:

SET GLOBAL dragnet.log_error_filter_rules =
  'IF prio>=INFORMATION THEN drop. IF EXISTS source_line THEN unset source_line.';

The effect is similar to the filtering performed by the log_sink_internal filter with a setting of log_error_verbosity=2.

Example 2: This rule limits information events to no more than one per 60 seconds:

SET GLOBAL dragnet.log_error_filter_rules =
  'IF prio>=INFORMATION THEN throttle 1/60.';

Once you have the filtering configuration set up as you desire, consider assigning dragnet.log_error_filter_rules using SET PERSIST rather than SET GLOBAL to make the setting persist across server restarts. Alternatively, add the setting to the server option file.

To stop using the filtering language, first remove it from the set of error logging components. Usually this means using a different filter component rather than no filter component. For example:

SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal';

Again, consider using using SET PERSIST rather than SET GLOBAL to make the setting persist across server restarts.

Then uninstall the filter log_filter_dragnet component:

UNINSTALL COMPONENT 'file://component_log_filter_dragnet';

The following sections describe aspects of log_filter_dragnet operation in more detail:

log_filter_dragnet Rule Language

The following grammar defines the language for log_filter_dragnet filter rules. Each rule is an IF statement terminated by a period (.) character. The language is not case sensitive.

    IF condition THEN action
    [ELSEIF condition THEN action] ...
    [ELSE action]

condition: {
    field comparator value
  | [NOT] EXISTS field
  | condition {AND | OR}  condition

action: {
  | throttle {count | count / window_size}
  | set field [:= | =] value
  | unset [field]

field: {
  | optional_field
  | user_defined_field

core_field: {
  | msg
  | prio
  | label
  | err_code
  | err_symbol
  | SQL_state
  | subsystem

optional_field: {
  | OS_errmsg
  | user
  | host
  | thread
  | query_id
  | source_file
  | source_line
  | function

    sequence of characters in [a-zA-Z0-9_] class

comparator: {== | != | <> | >= | => | <= | =< | < | >}

value: {
  | integer_literal
  | float_literal
  | error_symbol
  | severity

count: integer_literal
window_size: integer_literal

    sequence of characters quoted as '...' or "..."

    sequence of characters in [0-9] class


    valid MySQL error symbol such as ER_ACCESS_DENIED_ERROR or ER_STARTUP

severity: {

Simple conditions compare a field to a value or test field existence. To construct more complex conditions, use the AND and OR operators. Both operators have the same precedence and evaluate left to right.

To escape a character within a string, precede it by a backslash (\). A backslash is required to include backslash itself or the string-quoting character, optional for other characters.

For convenience, log_filter_dragnet supports symbolic names for comparisons to certain fields. Where applicable, symbolic values are preferable to numeric values for readability and portability.

  • Event severity values 1, 2, and 3 can be specified as ERROR, WARNING, and INFORMATION. Severity symbols are recognized only in comparisons with the prio field. These comparisons are equivalent:

    IF prio == INFORMATION THEN ...
    IF prio == 3 THEN ...
  • Error codes can be specified in numeric form or as the corresponding error symbol. For example, ER_STARTUP is the symbolic name for error 1408, so these comparisons are equivalent:

    IF err_code == ER_STARTUP THEN ...
    IF err_code == 1408 THEN ...

    Error symbols are recognized only in comparisons with the err_code field and user-defined fields.

    To find the error symbol corresponding to a given error code number, use one of these methods:

    Suppose that a rule set with error numbers looks like this:

    IF err_code == 10927 OR err_code == 10914 THEN drop.
    IF err_code == 1131 THEN drop.

    Using perror, determine the error symbols:

    shell> perror 10927 10914 1131
    Access denied for user '%-.48s'@'%-.64s'. Account is locked.
    MySQL error code MY-010914 (ER_ABORTING_USER_CONNECTION):
    Aborted connection %u to db: '%-.192s' user: '%-.48s' host:
    '%-.64s' (%-.64s).
    MySQL error code MY-001131 (ER_PASSWORD_ANONYMOUS_USER):
    You are using MySQL as an anonymous user and anonymous users
    are not allowed to change passwords

    Substituting error symbols for numbers, the rule set becomes:


Symbolic names can be specified as quoted strings for comparison with string fields, but in such cases the names are strings that have no special meaning and log_filter_dragnet does not resolve them to the corresponding numeric value. Also, typos may go undetected, whereas an error is thrown immediately on SET for attempts to use an unquoted symbol unknown to the server.

log_filter_dragnet Rule Actions

log_filter_dragnet supports these actions in filter rules:

  • drop: Drop the current log event (do not log it).

  • throttle: Apply rate limiting to reduce log verbosity for events matching particular conditions. The argument indicates a rate, in the form count or count/window_size. The count value indicates the permitted number of events to log per time window. The window_size value is the time window in seconds; if omitted, the default window is 60 seconds. Both values must be integer literals.

    This rule throttles plugin-shutdown messages to 5 per 60 seconds:

    IF err_code == ER_PLUGIN_SHUTTING_DOWN_PLUGIN THEN throttle 5.

    This rule throttles errors and warnings to 1000 per hour and information messages to 100 per hour:

    IF prio <= INFORMATION THEN throttle 1000/3600 ELSE throttle 100/3600.
  • set: Assign a value to a field (and cause the field to exist if it did not already). In subsequent rules, EXISTS tests against the field name are true, and the new value can be tested by comparison conditions.

  • unset: Discard a field. In subsequent rules, EXISTS tests against the field name are false, and comparisons of the field against any value are false.

    In the special case that the condition refers to exactly one field name, the field name following unset is optional and unset discards the named field. These rules are equivalent:

    IF myfield == 2 THEN unset myfield.
    IF myfield == 2 THEN unset.
log_filter_dragnet Rule Fields

log_filter_dragnet supports core, optional, and user-defined fields in rules:

  • A core field is set up automatically for error events. However, its presence in the event is not guaranteed because a core field, like any type of field, may be unset by filter rules. If so, the field will be found missing by later rules within the rule set and by components that execute after the filter (such as log writers).

  • An optional field is normally absent but may be present for certain event types. When present, an optional field provides additional event information as appropriate and available.

  • A user-defined field is any field with a name that is not already defined as a core or optional field. A user-defined field does not exist until created with the set action.

As implied by the preceding description, any given field may be absent, either because it was not present in the first place, or was discarded by a filtering rule. For log writers, the effect of field absence is writer specific. For example, a writer might omit the field from the log message, indicate that the field is missing, or substitute a default. When in doubt, use a filter rule to unset the field, then check what the log writer does with it.

These fields are core fields:

  • time

    The event timestamp.

  • msg

    The event message string.

  • prio

    The event priority, to indicate error, warning, or note/information event. This field corresponds to severity in syslog.

    In comparisons, each priority can be specified as a symbolic severity name or an integer literal. Severity symbols are recognized only in comparisons with the prio field. These comparisons are equivalent:

    IF prio == INFORMATION THEN ...
    IF prio == 3 THEN ...

    The following table shows the permitted priority levels.

    Event Type Priority Symbol Numeric Priority
    Error events ERROR 1
    Warning events WARNING 2
    Note/information events INFORMATION 3

    Priority values follow the principle that higher priorities have lower values, and vice versa. Priority values begin at 1 for the most severe events (errors) and increase for events with decreasing severity. For example, to discard events with lower priority than warnings, test for priority values higher than WARNING:

    IF prio > WARNING THEN drop.

    The following examples show the log_filter_dragnet rules to achieve an effect similar to each log_error_verbosity value permitted by the log_filter_internal filter:

    • Errors only (log_error_verbosity=1):

      IF prio > ERROR THEN drop.
    • Errors and warnings (log_error_verbosity=2):

      IF prio > WARNING THEN drop.
    • Errors, warnings, and notes (log_error_verbosity=3):

      IF prio > INFORMATION THEN drop.

      This rule can actually be omitted because there are no prio values greater than INFORMATION, so effectively it drops nothing.

  • err_code

    The numeric event error code. In comparisons, the value to test can be specified as a symbolic error name or an integer literal. Error symbols are recognized only in comparisons with the err_code field and user-defined fields. These comparisons are equivalent:

    IF err_code == ER_ACCESS_DENIED_ERROR THEN ...
    IF err_code == 1045 THEN ...
  • err_symbol

    The event error symbol, as a string; for example, 'ER_DUP_KEY'. err_symbol values are intended more for identifying particular lines in log output than for use in filter rule comparisons because log_filter_dragnet does not resolve comparison values specified as strings to the equivalent numeric error code.

  • SQL_state

    The event SQLSTATE value, as a string; for example '23000'.

  • subsystem

    The subsystem in which the event occurred. Possible values are InnoDB (the InnoDB storage engine), Repl (the replication subsystem), Server (otherwise).

Optional fields fall into the following categories:

Additional information about the error, such as the error signaled by the operating system or the error lable:

  • OS_errno

    The operating system error number.

  • OS_errmsg

    The operating system error message.

  • label

    The label corresponding to the prio value, as a string. Filter rules can change the label for log writers that support custom labels. label values are intended more for identifying particular lines in log output than for use in filter rule comparisons because log_filter_dragnet does not resolve comparison values specified as strings to the equivalent numeric priority.

Identification of the client for which the event occurred:

  • user

    The client user.

  • host

    The client host.

  • thread

    The thread ID.

  • query_id

    The query ID.

Debugging information:

  • source_file

    The source file in which the event occurred. The file name should omit any leading path. For example, to test for the sql/gis/ file, write the comparison like this:

    IF source_file == "" THEN ...
  • source_line

    The line within the source file at which the event occurred.

  • function

    The function in which the event occurred.

  • component

    The component or plugin in which the event occurred.