Rechercher dans le manuel MySQL

5.4.2.5 Rule-Based Error Log Filtering (log_filter_dragnet)

Contents

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:

  1. INSTALL COMPONENT 'file://component_log_filter_dragnet';
  2. 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 5.4.2.1, “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:

  1. SET GLOBAL dragnet.log_error_filter_rules =
  2.   '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:

  1. SET GLOBAL dragnet.log_error_filter_rules =
  2.   '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:

  1. 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:

  1. 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. 

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

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

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

field: {
    core_field
  | optional_field
  | user_defined_field
}

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

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

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

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

value: {
    string_literal
  | integer_literal
  | float_literal
  | error_symbol
  | priority
}

count: integer_literal
window_size: integer_literal

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

integer_literal:
    sequence of characters in [0-9] class

float_literal:
    integer_literal[.integer_literal]

error_symbol:
    valid MySQL error symbol such as ER_ACCESS_DENIED_ERROR or ER_STARTUP

priority: {
    ERROR
  | WARNING
  | INFORMATION
}

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 priority values 1, 2, and 3 can be specified as ERROR, WARNING, and INFORMATION. Priority symbols are recognized only in comparisons with the prio field. These comparisons are equivalent:

    1. IF prio == INFORMATION THEN ...
    2. 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:

    1. IF err_code == ER_STARTUP THEN ...
    2. 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:

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

    Using perror, determine the error symbols: 

    shell> perror 10927 10914 1131
MySQL error code MY-010927 (ER_ACCESS_DENIED_FOR_USER_ACCOUNT_LOCKED):
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:

    1. IF err_code == ER_ACCESS_DENIED_FOR_USER_ACCOUNT_LOCKED
    2.   OR err_code == ER_ABORTING_USER_CONNECTION THEN drop.
    3. IF err_code == ER_PASSWORD_ANONYMOUS_USER THEN drop.

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.

Contents Haut

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:

    1. 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:

    1. 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:

    1. IF myfield == 2 THEN unset myfield.
    2. IF myfield == 2 THEN unset.

Contents Haut

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 priority name or an integer literal. Priority symbols are recognized only in comparisons with the prio field. These comparisons are equivalent:

    1. IF prio == INFORMATION THEN ...
    2. 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

    There is also a priority of SYSTEM, but system messages cannot be filtered and are always written to the error log.

    In general, message priorities are determined as follows:

    Is the situation or event actionable?

    • Yes: Is the situation or event ignorable?

      • Yes: Priority is WARNING.

      • No: Priority is ERROR.

    • No: Is the situation or event mandatory?

      • Yes: Priority is SYSTEM.

      • No: Priority is INFORMATION.

    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 priority. For example, to discard events with lower priority than warnings, test for priority values higher than WARNING:

    1. 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):

      1. IF prio > ERROR THEN drop.

    • Errors and warnings (log_error_verbosity=2):

      1. IF prio > WARNING THEN drop.

    • Errors, warnings, and notes (log_error_verbosity=3):

      1. 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:

    1. IF err_code == ER_ACCESS_DENIED_ERROR THEN ...
    2. 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/distance.cc file, write the comparison like this:

    1. IF source_file == "distance.cc" 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.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-error-log-rule-based-filtering.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut