7.4.2.6 Rule-Based Error Log Filtering (log_filter_dragnet) (original) (raw)

7.4.2.6 Rule-Based Error Log Filtering (log_filter_dragnet)

The log_filter_dragnet log filter component enables log filtering based on user-defined rules.

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

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 atSection 7.4.2.1, “Error Log Configuration”. Those instructions apply to other error-logging system variables as well.

With log_filter_dragnet enabled, define its filter rules by setting thedragnet.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 thelog_sink_internal filter with a setting oflog_error_verbosity=2.

For readability, you might find it preferable to list the rules on separate lines. For example:

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

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 assigningdragnet.log_error_filter_rules using SET PERSIST rather thanSET GLOBAL to make the setting persist across server restarts. Alternatively, add the setting to the server option file.

When using log_filter_dragnet,log_error_suppression_list is ignored.

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 usingSET PERSIST rather thanSET 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 oflog_filter_dragnet operation in more detail:

Grammar for log_filter_dragnet Rule Language

The following grammar defines the language forlog_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
  | err_code
  | err_symbol
  | SQL_state
  | subsystem
}

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

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 theAND 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. For readability and portability, symbolic values are preferable (where applicable) to numeric values.

IF prio == INFORMATION THEN ...  
IF prio == 3 THEN ...  
IF err_code == ER_STARTUP THEN ...  
IF err_code == 1408 THEN ...

Error symbols are recognized only in comparisons with theerr_code field and user-defined fields.
To find the error symbol corresponding to a given error code number, use one of these methods: 

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

Using perror, determine the error symbols: 

$> 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: 

IF err_code == ER_ACCESS_DENIED_FOR_USER_ACCOUNT_LOCKED  
  OR err_code == ER_ABORTING_USER_CONNECTION THEN drop.  
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 andlog_filter_dragnet does not resolve them to the corresponding numeric value. Also, typos may go undetected, whereas an error occurs immediately onSET for attempts to use an unquoted symbol unknown to the server.

Actions for log_filter_dragnet Rules

log_filter_dragnet supports these actions in filter rules:

IF err_code == ER_PLUGIN_SHUTTING_DOWN_PLUGIN THEN throttle 5.

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

IF prio <= INFORMATION THEN throttle 1000/3600 ELSE throttle 100/3600.  
IF myfield == 2 THEN unset myfield.  
IF myfield == 2 THEN unset.
Field References in log_filter_dragnet Rules

log_filter_dragnet rules support references to core, optional, and user-defined fields in error events.

Core Field References

The log_filter_dragnet grammar atGrammar for log_filter_dragnet Rule Language names the core fields that filter rules recognize. For general descriptions of these fields, seeSection 7.4.2.3, “Error Event Fields”, with which you are assumed to be familiar. The following remarks provide additional information only as it pertains specifically to core field references as used withinlog_filter_dragnet rules.

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

The following table shows the permitted priority levels.

Event Type Priority Symbol Numeric Priority
Error event ERROR 1
Warning event WARNING 2
Note/information event INFORMATION 3
There is also a message priority ofSYSTEM, but system messages cannot be filtered and are always written to the error log.
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 priority lower than warnings, test for priority values higher thanWARNING: 
IF prio > WARNING THEN drop.

The following examples show thelog_filter_dragnet rules to achieve an effect similar to eachlog_error_verbosity value permitted by the log_filter_internal filter: 

IF prio > ERROR THEN drop.  
IF prio > WARNING THEN drop.  
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.
IF err_code == ER_ACCESS_DENIED_ERROR THEN ...  
IF err_code == 1045 THEN ...
Optional Field References

The log_filter_dragnet grammar atGrammar for log_filter_dragnet Rule Language names the optional fields that filter rules recognize. For general descriptions of these fields, seeSection 7.4.2.3, “Error Event Fields”, with which you are assumed to be familiar. The following remarks provide additional information only as it pertains specifically to optional field references as used withinlog_filter_dragnet rules.

IF source_file == "distance.cc" THEN ...
User-Defined Field References

Any field name in a log_filter_dragnet filter rule not recognized as a core or optional field name is taken to refer to a user-defined field.