Overview

With the comment filter it is possible to define comments that are injected before the actual statements. These comments appear as sql comments when they are received by the server.

Settings

The Comment filter requires one mandatory parameter to be defined.

inject

• Type: string

• Mandatory: Yes

• Dynamic: Yes

A parameter that contains the comment injected before the statements. There is also defined variable $IP that can be used to comment the IP address of the client in the injected comment. Variables must be written in all caps.

Examples

Example 1 - Inject IP address of the connected client into statements

as comment.

The following configuration adds the IP address of the client to the comment.

In this example when MaxScale receives statement like:

It would look like

when received by server.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The Maxrows filter is capable of restricting the amount of rows that a SELECT, a prepared statement or stored procedure could return to the client application.

If a resultset from a backend server has more rows than the configured limit or the resultset size exceeds the configured size, an empty result will be sent to the client.

Configuration

The Maxrows filter is easy to configure and to add to any existing service.

Settings

The Maxrows filter has no mandatory parameters. Optional parameters are:

max_resultset_rows

• Type: number

• Mandatory: No

• Dynamic: Yes

• Default: (no limit)

Specifies the maximum number of rows a resultset can have in order to be returned to the user.

If a resultset is larger than this an empty result will be sent instead.

max_resultset_size

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: 64Ki

Specifies the maximum size a resultset can have in order to be sent to the client. A resultset larger than this, will not be sent: an empty resultset will be sent instead.

max_resultset_return

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: empty

Specifies what the filter sends to the client when the rows or size limit is hit, possible values:

• an empty result set

• an error packet with input SQL

• an OK packet

Example output with ERR packet:

debug

• Type: number

• Mandatory: No

• Dynamic: Yes

• Default: 0

An integer value, using which the level of debug logging made by the Maxrows filter can be controlled. The value is actually a bitfield with different bits denoting different logging.

• 0 (0b00000) No logging is made.

• 1 (0b00001) A decision to handle data form server is logged.

To log everything, give debug a value of 3.

Example Configuration

Here is an example of filter configuration where the maximum number of returned rows is 10000 and maximum allowed resultset size is 256KB

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

This filter prevents writes from being done and returns a read-only error if one is attempted. The error that is returned is ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, error number 1792 with SQLSTATE 25006 and the message Cannot execute statement in a READ ONLY transaction.

Configuration

This filter is an implicit filter and has no configuration parameters and does not need its own configuration section. To enable it, simply add it to the service using the module name.

Limitations

The filter prevents writes in two ways: by parsing the SQL and detecting writes and by setting the default mode of transactions to read-only. The parsing will prevent any writes that are detected from reaching the database and the read-only mode of transactions will prevent writes in stored procedures and user functions as well as act as a backup method in case the parsing is ineffective in detecting a write.

The SET TRANSACTION READ WRITE and START TRANSACTION READ WRITE statements are blocked by this filter. This is done as a safeguard against accidental removal of the read-only transaction mode. However, if the client disables the read-only mode directly via SET tx_read_only=0, the change is not detected by the filter and writes are possible.

Given that the detection of writes is based on parsing and that parsing is not 100% effective in detecting all possible permutations of this statement (e.g. PREPARE and EXECUTE from user variables is not detected), this cannot be used to prevent a mischievous user from being able to turn off the read-only mode.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The ldi (LOAD DATA INFILE) filter was introduced in MaxScale 23.08.0 and it extends the MariaDB LOAD DATA INFILE syntax to support loading data from any object storage that supports the S3 API. This includes cloud offerings like AWS S3 and Google Cloud Storage as well as locally run services like Minio.

If the filename starts with either S3:// or gs://, the path is interpreted as a S3 object file. The prefix is case-insensitive. For example, the following command would load the file my-data.csv from the bucket my-bucket into the table t1.

How to Upload Data

Here is a minimal configuration for the filter that can be used to load data from AWS S3:

The first step is to move the file to be loaded into the same region that MaxScale and the MariaDB servers are in. One factor in the speed of the upload is the network latency and minimizing it by moving the source and the destination closer improves the data loading speed.

The next step is to connect to MaxScale and prepare the session for an upload by providing the service account access and secret keys.

Once the credentials are configured, the data loading can be started:

Data Uploads with MariaDB Xpand

This feature has been removed in MaxScale 24.02.

Common Problems With Data Loading

Missing Files

If you are using self-hosted object storage programs like Minio, a common problem is that they do not necessarily support the newer virtual-hosted-style requests that is used by AWS. This usually manifests as an error either about a missing file or a missing bucket.

If the host parameter is set to a hostname, it's assumed that the object storage supports the newer virtual-hosted-style requests. If this not the case, the filter must be configured with protocol_version=1.

Conversely, if the host parameter is set to a plain IP address, it is assumed that it does not support the newer virtual-hosted-style request. If the host does support it, the filter must be configured with protocol_version=2.

Settings

key

• Type: string

• Mandatory: No

• Dynamic: Yes

The S3 access key used to perform all requests to it.

This must be either configured in the MaxScale configuration file or set withSET @maxscale.ldi.s3_key='<key>' before starting the data load.

secret

• Type: string

• Mandatory: No

• Dynamic: Yes

The S3 secret key used to perform all requests to it.

This must be either configured in the MaxScale configuration file or set withSET @maxscale.ldi.s3_secret='<secret>' before starting the data load.

region

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: us-east-1

The S3 region where the data is located.

The value can be overridden with SET @maxscale.ldi.s3_region='<region>' before starting the data load.

host

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: s3.amazonaws.com

The location of the S3 object storage. By default the original AWS S3 host is used. The corresponding value for Google Cloud Storage isstorage.googleapis.com.

The value can be overridden with SET @maxscale.ldi.s3_host='<host>' before starting the data load.

port

• Type: integer

• Mandatory: No

• Dynamic: Yes

• Default: 0

The port on which the S3 object storage is listening. If unset or set to the value of 0, the default S3 port is used.

The value can be overridden with SET @maxscale.ldi.s3_port=<port> before starting the data load. Note that unlike the other values, the value for this variable must be an SQL integer and not an SQL string.

no_verify

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

If set to true, TLS certificate verification for the object storage is skipped.

use_http

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

If set to true, communication with the object storage is done unencrypted using HTTP instead of HTTPS.

protocol_version

• Type: integer

• Mandatory: No

• Dynamic: Yes

• Default: 0

Which protocol version to use. By default the protocol version is derived from the value of host but this automatic protocol version deduction will not always produce the correct result. For the legacy path-style requests used by older S3 storage buckets, the value must be set to 1. All new buckets use the protocol version 2.

For object storage programs like Minio, the value must be set to 1 as the bucket name cannot be resolved via the subdomain like it is done for object stores in the cloud.

import_user

This parameter has been removed in MaxScale 24.02.

import_password

This parameter has been removed in MaxScale 24.02.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The Consistent Critical Read (CCR) filter allows consistent critical reads to be done through MaxScale while still allowing scaleout of non-critical reads.

When the filter detects a statement that would modify the database, it attaches a routing hint to all following statements done by that connection. This routing hint guides the routing module to route the statement to the primary server where data is guaranteed to be in an up-to-date state. Writes from one session do not, by default, propagate to other sessions.

Note: This filter does not work with prepared statements. Only text protocol queries are handled by this filter.

Controlling the Filter with SQL Comments

The triggering of the filter can be limited further by adding MaxScale supported comments to queries and/or by using regular expressions. The query comments take precedence: if a comment is found it is obeyed even if a regular expression parameter might give a different result. Even a comment cannot cause a SELECT-query to trigger the filter. Such a comment is considered an error and ignored.

The comments must follow the and the HintFilter needs to be in the filter chain before the CCR-filter. If a query has a MaxScale supported comment line which defines the parameter ccr, that comment is caught by the CCR-filter. Parameter values match and ignore are supported, causing the filter to trigger (match) or not trigger (ignore) on receiving the write query. For example, the query

would normally cause the filter to trigger, but does not because of the comment. The match-comment typically has no effect, since write queries by default trigger the filter anyway. It can be used to override an ignore-type regular expression that would otherwise prevent triggering.

Settings

The CCR filter has no mandatory parameters.

time

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: 60s

The time window during which queries are routed to the primary. The duration value will always be rounded to the nearest second. If no explicit unit has been specified, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. The default value for this parameter is 60 seconds.

When a data modifying SQL statement is processed, a timer is set to the value time. Once the timer has elapsed, all statements are routed normally. If a new data modifying SQL statement is processed within the time window, the timer is reset to the value of time.

Enabling this parameter in combination with the count parameter causes both the time window and number of queries to be inspected. If either of the two conditions are met, the query is re-routed to the primary.

count

• Type: count

• Mandatory: No

• Dynamic: Yes

• Default: 0

The number of SQL statements to route to primary after detecting a data modifying SQL statement. This feature is disabled by default.

After processing a data modifying SQL statement, a counter is set to the value of count and all statements are routed to the primary. Each executed statement after a data modifying SQL statement cause the counter to be decremented. Once the counter reaches zero, the statements are routed normally. If a new data modifying SQL statement is processed, the counter is reset to the value of count.

match

• Type:

• Mandatory: No

• Dynamic: No

• Default: ""

These control which statements trigger statement re-routing. Only non-SELECT statements are inspected. For CCRFilter, the exclude-parameter is instead named ignore, yet works similarly.

ignore

• Type:

• Mandatory: No

• Dynamic: No

• Default: ""

See documentation for .

options

• Type:

• Mandatory: No

• Dynamic: No

• Values: ignorecase

Regular expression options for match and ignore.

global

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

global is a boolean parameter that when enabled causes writes from one connection to propagate to all other connections. This can be used to work around cases where one connection writes data and another reads it, expecting the write done by the other connection to be visible.

This parameter only works with the time parameter. The use of global andcount at the same time is not allowed and will be treated as an error.

Example Configuration

Here is a minimal filter configuration for the CCRFilter which should solve most problems with critical reads after writes.

With this configuration, whenever a connection does a write, all subsequent reads done by that connection will be forced to the primary for 5 seconds.

This prevents read scaling until the modifications have been replicated to the replicas. For best performance, the value of time should be slightly greater than the actual replication lag between the primary and its replicas. If the number of critical read statements is known, the count parameter could be used to control the number reads that are sent to the primary.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The binlogfilter can be combined with a binlogrouter service to selectively replicate the binary log events to replica servers.

The filter uses two settings, match and exclude, to determine which events are replicated. If a binlog event does not match or is excluded, the event is replaced with an empty data event. The empty event is always 35 bytes which translates to a space reduction in most cases.

When statement-based replication is used, any query events that are filtered out are replaced with a SQL comment. This causes the query event to do nothing and thus the event will not modify the contents of the database. The GTID position of the replicating database will still advance which means that downstream servers replicating from it keep functioning correctly.

The filter works with both row based and statement based replication but we recommend using row based replication with the binlogfilter. This guarantees that there are no ambiguities in the event filtering.

Settings

match

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Include queries that match the regex. See next entry, exclude, for more information.

exclude

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Exclude queries that match the regex.

If neither match nor exclude are defined, the filter does nothing and all events are replicated. This filter does not accept regular expression options as a separate setting, such settings must be defined in the patterns themselves. See the for more information.

The two settings are matched against the database and table name concatenated with a period. For example, the string the patterns are matched against for the database test and table t1 is test.t1.

For statement based replication, the pattern is matched against all the tables in the statements. If any of the tables matches the match pattern, the event is replicated. If any of the tables matches the exclude pattern, the event is not replicated.

rewrite_src

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

See the next entry, rewrite_dest, for more information.

rewrite_dest

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

rewrite_src and rewrite_dest control the statement rewriting of the binlogfilter. The rewrite_src setting is a PCRE2 regular expression that is matched against the default database and the SQL of statement based replication events (query events). rewrite_dest is the replacement string which supports the normal PCRE2 backreferences (e.g the first capture group is $1, the second is $2, etc.).

Both rewrite_src and rewrite_dest must be defined to enable statement rewriting.

When statement rewriting is enabled must be used. The filter will disallow replication for all replicas that attempt to replicate with traditional file-and-position based replication.

The replacement is done both on the default database as well as the SQL statement in the query event. This means that great care must be taken when defining the rewriting rules. To prevent accidental modification of the SQL into a form that is no longer valid, use database and table names that never occur in the inserted data and is never used as a constant value.

Example Configuration

With the following configuration, only events belonging to database customers are replicated. In addition to this, events for the table orders are excluded and thus are not replicated.

For more information about the binlogrouter and how to use it, refer to the .

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The optimistictrx filter implements optimistic transaction execution. The filter is designed for a use-case where most of the transactions are read-only and writes happen rarely but each set of read-only statements is still grouped into a read-write transaction (i.e. START TRANSACTION, BEGIN orSET autocommit=0

Overview

The psreuse filter reuses identical prepared statements inside the same client connection. This filter only works with binary protocol prepared statements and not with text protocol prepared statements executed with the PREPARE SQL command.

When this filter is enabled and the connection prepares an identical prepared statement multiple times, instead of preparing it on the server the existing prepared statement handle is reused. This also means that whenever prepared statements are closed by the client, they will be left open by readwritesplit.

Overview

With the masking filter it is possible to obfuscate the returned value of a particular column.

For instance, suppose there is a table person that, among other columns, contains the column ssn where the social security number of a person is stored.

With the masking filter it is possible to specify that when the ssn field is queried, a masked value is returned unless the user making the query is a specific one. That is, when making the query

instead of getting the real result, as in

the ssn would be masked, as in

Note that the masking filter should be viewed as a best-effort solution intended for protecting against accidental misuse rather than malicious attacks.

Security

From MaxScale 2.3 onwards, the masking filter will reject statements that use functions in conjunction with columns that should be masked. Allowing function usage provides a way for circumventing the masking, unless a firewall filter is separately configured and installed.

Please see the configuration parameter for how to change the default behaviour.

From MaxScale 2.3.5 onwards, the masking filter will check the definition of user variables and reject statements that define a user variable using a statement that refers to columns that should be masked.

Please see the configuration parameter for how to change the default behaviour.

From MaxScale 2.3.5 onwards, the masking filter will examine unions and if the second or subsequent SELECT refer to columns that should be masked, the statement will be rejected.

Please see the configuration parameter for how to change the default behaviour.

From MaxScale 2.3.5 onwards, the masking filter will examine subqueries and if a subquery refers to columns that should be masked, the statement will be rejected.

Please see the configuration parameter for how to change the default behaviour.

Note that in order to ensure that it is not possible to get access to masked data, the privileges of the users should be minimized. For instance, if a user can create tables and perform inserts, he or she can execute something like

to get access to the cleartext version of a masked field ssn.

From MaxScale 2.3.5 onwards, the masking filter will, if any of theprevent_function_usage, check_user_variables, check_unions orcheck_subqueries parameters is set to true, block statements that cannot be fully parsed.

Please see the configuration parameter for how to change the default behaviour.

From MaxScale 2.3.7 onwards, the masking filter will treat any strings passed to functions as if they were fields. The reason is that as the MaxScale query classifier is not aware of whether ANSI_QUOTES is enabled or not, it is possible to bypass the masking by turning that option on.

Before this change, the content of the field ssn would have been returned in clear text even if the column should have been masked.

Note that this change will mean that there may be false positives if ANSI_QUOTES is not enabled and a string argument happens to be the same as the name of a field to be masked.

Please see the configuration parameter [treat_string_arg_as_field(#treat_string_arg_as_field) for how to change the default behaviour.

Limitations

The masking filter can only be used for masking columns of the following types: BINARY, VARBINARY, CHAR, VARCHAR, BLOB, TINYBLOB,MEDIUMBLOB, LONGBLOB, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT

Currently, the masking filter can only work on packets whose payload is less than 16MB. If the masking filter encounters a packet whose payload is exactly that, thus indicating a situation where the payload is delivered in multiple packets, the value of the parameter large_payloads specifies how the masking filter should handle the situation.

Configuration

The masking filter is taken into use with the following kind of configuration setup.

Settings

The masking filter has one mandatory parameter - rules.

rules

• Type: path

• Mandatory: Yes

• Dynamic: Yes

Specifies the path of the file where the masking rules are stored. A relative path is interpreted relative to the module configuration directory of MariaDB MaxScale. The default module configuration directory is_/etc/maxscale.modules.d_.

warn_type_mismatch

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: never

With this optional parameter the masking filter can be instructed to log a warning if a masking rule matches a column that is not of one of the allowed types.

large_payload

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: ignore

This optional parameter specifies how the masking filter should treat payloads larger than 16MB, that is, payloads that are delivered in multiple MySQL protocol packets.

The values that can be used are ignore, which means that columns in such payloads are not masked, and abort, which means that if such payloads are encountered, the client connection is closed. The default is abort.

Note that the aborting behaviour is applied only to resultsets that contain columns that should be masked. There are no limitations on resultsets that do not contain such columns.

prevent_function_usage

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should behave if a column that should be masked, is used in conjunction with some function. As the masking filter works only on the basis of the information in the returned result-set, if the name of a column is not present in the result-set, then the masking filter cannot mask a value. This means that the masking filter basically can be bypassed with a query like:

If the value of prevent_function_usage is true, then all statements that contain functions referring to masked columns will be rejected. As that means that also queries using potentially harmless functions, such as LENGTH(masked_column), are rejected as well, this feature can be turned off. In that case, the firewall filter should be setup to allow or reject the use of certain functions.

require_fully_parsed

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should behave in case any of prevent_function_usage, check_user_variables,check_unions or check_subqueries is true and it encounters a statement that cannot be fully parsed,

If true, then statements that cannot be fully parsed (due to a parser limitation) will be blocked.

Note that if this parameter is set to false, then prevent_function_usage,check_user_variables, check_unions and check_subqueries are rendered less effective, as it with a statement that cannot be fully parsed may be possible to bypass the protection that they are intended to provide.

treat_string_arg_as_field

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should treat strings used as arguments to functions. If true, they will be handled as fields, which will cause fields to be masked even if ANSI_QUOTES has been enabled and " is used instead of backtick.

check_user_variables

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should behave with respect to user variables. If true, then a statement like

will be rejected if ssn is a column that should be masked.

check_unions

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should behave with respect to UNIONs. If true, then a statement like

will be rejected if b is a column that should be masked.

check_subqueries

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

This optional parameter specifies how the masking filter should behave with respect to subqueries. If true, then a statement like

will be rejected if a is a column that should be masked.

Rules

The masking rules are expressed as a JSON object.

The top-level object is expected to contain a key rules whose value is an array of rule objects.

Each rule in the rules array is a JSON object, expected to contain the keys replace, with, applies_to andexempted. The two former ones are obligatory and the two latter ones optional.

replace

The value of this key is an object that specifies the column whose values should be masked. The object must contain the keycolumn and may contain the keys table and database. The value of these keys must be a string.

If only column is specified, then a column with that name matches irrespective of the table and database. If table is specified, then the column matches only if it is in a table with the specified name, and if database is specified when the column matches only if it is in a database with the specified name.

NOTE If a rule contains a table/database then if the resultset does not contain table/database information, it will always be considered a match if the column matches. For instance, given the rule above, if there is a table person2, also containing an ssn field, then a query like

will not return masked values, but a query like

will only return masked values, even if the ssn values fromperson2 in principle should not be masked. The same effect is observed even with a nonsensical query like

even if nothing from person2 should be masked. The reason is that as the resultset contains no table information, the values must be masked if the column name matches, as otherwise the masking could easily be circumvented with a query like

The optional key match makes partial replacement of the original value possible: only the matched part would be replaced with the fill character. The match value must be a valid pcre2 regular expression.

obfuscate

The obfuscate rule allows the obfuscation of the value by passing it through an obfuscation algorithm. Current solution uses a non-reversible obfuscation approach.

However, note that although it is in principle impossible to obtain the original value from the obfuscated one, if the range of possible original values is limited, it is straightforward to figure out the possible original values by running all possible values through the obfuscation algorithm and then comparing the results.

The minimal configuration is:

Output example for Db field name = 'remo'

with

The value of this key is an object that specifies what the value of the matched column should be replaced with for the replace rule. Currently, the object is expected to contain either the key value or the key fill. The value of both must be a string with length greater than zero. If both keys are specified, value takes precedence. If fill is not specified, the default X is used as its value.

If value is specified, then its value is used to replace the actual value verbatim and the length of the specified value must match the actual returned value (from the server) exactly. If the lengths do not match, the value offill is used to mask the actual value.

When the value of fill (fill-value) is used for masking the returned value, the fill-value is used as many times as necessary to match the length of the return value. If required, only a part of the fill-value may be used in the end of the mask value to get the lengths to match.

applies_to

With this optional key, whose value must be an array of strings, it can be specified what users the rule is applied to. Each string should be a MariaDB account string, that is, % is a wildcard.

If this key is not specified, then the masking is performed for all users, except the ones exempted using the key exempted.

exempted

With this optional key, whose value must be an array of strings, it can be specified what users the rule is not applied to. Each string should be a MariaDB account string, that is, % is a wildcard.

Module commands

Read documentation for details about module commands.

The masking filter supports the following module commands.

reload

Reload the rules from the rules file. The new rules are taken into use only if the loading succeeds without any errors.

MyMaskingFilter refers to a particular filter section in the MariaDB MaxScale configuration file.

Example

In the following we configure a masking filter MyMasking that should always log a warning if a masking rule matches a column that is of a type that cannot be masked, and that should abort the client connection if a resultset package is larger than 16MB. The rules for the masking filter are in the file masking_rules.json.

Configuration

masking_rules.json

The rules specify that the data of a column whose name is ssn, should be replaced with the string 012345-ABCD. If the length of the data is not exactly the same as the length of the replacement value, then the data should be replaced with as many X characters as needed.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The throttle filter is used to limit the maximum query frequency (QPS - queries per second) of a database session to a configurable value. The main use cases are to prevent a rogue session (client side error) and a DoS attack from overloading the system.

The throttling is dynamic. The query frequency is not limited to an absolute value. Depending on the configuration the throttle will allow some amount of high frequency queries, or especially short bursts with no frequency limitation.

Overview

The database firewall acts as a protective barrier for a database, ensuring that only expected queries are allowed through. It learns the typical patterns of queries during a training period and then uses this knowledge to block any queries that do not match what it has learned. This helps safeguard the database from unauthorized access or potentially harmful operations.

The firewall has two primary modes; a learning mode during which it learns what kind of statements are allowed and an enforcing mode during which it rejects or warns about non-allowed statements. In addition there is an idle mode, when the filter neither learns, nor enforces and a supervising mode, when the filter checks the incoming statements but takes no action but only warns about non-allowed statements.

When in the learning mode a representative workload should be run through the filter. While the learning mode is active, the filter collects the canonical form of all statements that pass through it. The canonical form of a statement is the statement with all literals replaced with a question mark. For instance, the following two statements

are different, but the canonical form of both is SELECT * FROM t WHERE f = ?.

When the learning mode is finished, the allowed statements are persistently stored in a . The file is not specific to the MaxScale instance where it was created and can be copied to other MaxScale installations.

When in the enforcing mode, the filter checks whether the canonical form of a statement is found in the set of allowed canonical statements. If it is, the statement is let through. If it is not, the behaviour depends upon the value of .

As an example, suppose the firewall is in the learning mode and it encounters the following statements:

That results in the following canonical statements:

When switched to the enforcing mode, the following statements will be let through

but the following will not

While learning, the user information will be retained and saved together with the statements. When enforcing, the value of defines whether the allowed statements are user specific or whether the union of all statements will be allowed for all users.

Allowlist

The allowed canonical statements will be stored in a file in the sub-directory firewall in the data directory of MaxScale, which typically is /var/lib/maxscale. The name of the file will be <Filter>-allowed_statements.json, where <Filter> is the name of the firewall filter in the MaxScale configuration file.

For instance, with a configuration like this:

The name of the file will be MyFirewall-allowed_statements.json.

The file is not specific to the MaxScale instance where it was created, but can, for instance, in an HA setup be copied to another MaxScale.

The firewall filter saves the allow list when its is switched from learning to something else but idle. If the old allow list needs to be retained, it should be copied before the firewall filter is taken out from the learning mode.

The format of the file is JSON and sufficiently self-explanatory that it can be edited manually. With caution, since, if there are syntax errors that prevent it from being read, MaxScale will not start.

Settings

mode

• Type:

• Mandatory: No

• Dynamic: Yes

This enumeration option specifies the mode of the firewall.

• idle: The firewall is idle, it neither learns nor enforces.

• learn-clear: The firewall first clears the existing allow list and then learns the valid statements from the traffic passing through it.

• learn-append: The firewall learns the valid statements from the traffic passing through it and appends them to the existing list.

If the firewall is in a learning mode, switching it to any other non-learning mode but idle, will cause the allow list to be saved. Switching the firewall from a learning mode to idle, will cause the learnt statements to be discarded and a possible existing will be left unchanged.

Even if there is no allow list, the firewall can be put in the supervise or the enforce mode. The result is equivalent with having and empty allow list, that is, no statements will be allowed.

action

• Type:

• Mandatory: No

• Dynamic: Yes

This enumeration option specifies the following action, if the firewall is in enforcing mode and it encounters a statement that is not allowed:

• return-error: An error is returned to the client.

• disconnect: The client is disconnected.

user_scope

• Type:

• Mandatory: No

• Dynamic: Yes

This enumeration option specifies whether the firewall blocks a user based upon the statements executed by that user during the learning mode, or whether it blocks all users based upon the union of statements executed by all users during the learning mode.

If the scope is collective a user not active during the learning mode will be able to execute the same statements the users that were active during the learning mode will. However, if the scope is individual, a user not active during the learning mode, will not be able to execute any statements.

With users, e.g. admins, whose statements should not be subject to the firewall can be listed.

exclude_users

• Type: stringlist

• Mandatory: No

• Dynamic: Yes

This string list option specifies users that are excluded and whose statements will not be subject to the firewall. The users can be specified with or without a host and quoting can be used.

Logging

When the firewall is in supervise mode, a firewall violation will cause a warning to be logged:

When the firewall is in 'enforce' mode, a firewall violation will cause the event , which, by default, will cause a warning to be logged to the MaxScale log file, if is enabled, and a syslog event to be generated, if is enabled.

Workflow

The first step when taking the firewall into use, is to create the filter

and add it to the service

Since the firewall filter was just configured, the firewall will start up in the idle mode.

The second step is to switch the firewall to the learning mode.

The firewall will switch to the learning mode and start collecting the canonical form of the statements that flow through it. While the firewall is learning, a representative workload should be run through it. In this case, since there was no allow list, it does not matter whether the mode is set to to learn-clear or learn-append.

The third step, once there is confidence that the firewall has seen all relevant statements, is to switch the firewall to supervising mode.

At this point, the canonical statements will be saved to the file <datadir>/firewall/MyFirewall-allowed_statements.json.

Since the firewall is now in the supervise mode, all statements will be checked, but no action will be taken in case of a statement that is not allowed. A warning will be logged, which allows it to be detected whether there are statements that currently are not allowed that should be allowed.

The last step is to switch the firewall to the enforcing mode.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The namedserverfilter is a MariaDB MaxScale filter module able to route queries to servers based on regular expression (regex) matches. Since it is a filter instead of a router, the NamedServerFilter only sets routing suggestions. It requires a compatible router to be effective. Currently, bothreadwritesplit and hintrouter take advantage of routing hints in the data packets. This filter uses the PCRE2 library for regular expression matching.

Configuration

The filter accepts settings in two modes: legacy and indexed. Only one of the modes may be used for a given filter instance. The legacy mode is meant for backwards compatibility and allows only one regular expression and one server name in the configuration. In indexed mode, up to 25 regex-server pairs are allowed in the form match01 - target01, match02 - target02 and so on. Also, in indexed mode, the server names (targets) may contain a list of names or special tags ->master or ->slave.

All parameters except the deprecated match and target parameters can be modified at runtime. Any modifications to the filter configuration will only affect sessions created after the change has completed.

Below is a configuration example for the filter in indexed-mode. The legacy mode is not recommended and may be removed in a future release. In the example, a SELECT on TableOne (match01) results in routing hints to two named servers, while a SELECT on TableTwo is suggested to be routed to the primary server of the service. Whether a list of server names is interpreted as a route-to-any or route-to-all is up to the attached router. The HintRouter sees a list as a suggestion to route-to-any. For additional information on hints and how they can also be embedded into SQL-queries, see .

Settings

NamedServerFilter requires at least one matchXY - targetXY pair.

matchXY

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

matchXY defines a against which the incoming SQL query is matched. XY must be a number in the range 01 - 25. Each match-setting pairs with a similarly indexed target-setting. If one is defined, the other must be defined as well. If a query matches the pattern, the filter attaches a routing hint defined by the target-setting to the query. The options-parameter affects how the patterns are compiled.

options

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: ignorecase

for matchXY.

targetXY

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The hint which is attached to the queries matching the regular expression defined by matchXY. If a compatible router is used in the service the query will be routed accordingly. The target can be one of the following:

• a server or service name (adds a HINT_ROUTE_TO_NAMED_SERVER hint)

• a list of server names, comma-separated (adds severalHINT_ROUTE_TO_NAMED_SERVER hints)

• ->master (adds a HINT_ROUTE_TO_MASTER

The support for service names was added in MaxScale 6.3.2. Older versions of MaxScale did not accept service names in the target parameters.

source

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

This optional parameter defines an IP address or mask which a connecting client's IP address is matched against. Only sessions whose address matches this setting will have this filter active and performing the regex matching. Traffic from other client IPs is simply left as is and routed straight through.

Since MaxScale 2.1 it's also possible to use % wildcards:

Note that using source=% to match any IP is not allowed.

Since MaxScale 2.3 it's also possible to specify multiple addresses separated by comma. Incoming client connections are subsequently checked against each.

user

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

This optional parameter defines a username the connecting client username is matched against. Only sessions that are connected using this username will have the match and routing hints applied to them. Traffic from other users is simply left as is and routed straight through.

Additional remarks

The maximum number of accepted match - target pairs is 25.

In the configuration file, the indexed match and target settings may be in any order and may skip numbers. During SQL-query matching, however, the regexes are tested in ascending order: match01, match02, match03 and so on. As soon as a match is found for a given query, the routing hints are written and the packet is forwarded to the next filter or router. Any remaining match regexes are ignored. This means the match - target pairs should be indexed in priority order, or, if priority is not a factor, in order of decreasing match probability.

Binary-mode prepared statements (COM_STMT_PREPARE) are handled by matching the prepared sql against the match-parameters. If a match is found, the routing hints are attached to any execution of that prepared statement. Text- mode prepared statements are not supported in this way. To divert them, use regular expressions which match the specific "EXECUTE"-query.

Examples

Example 1 - Route queries targeting a specific table to a server

This will route all queries matching the regular expression *from *users to the server named server2. The filter will ignore character case in queries.

A query like SELECT * FROM users would be routed to server2 where as a query like SELECT * FROM accounts would be routed according to the normal rules of the router.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The top filter is a filter module for MariaDB MaxScale that monitors every SQL statement that passes through the filter. It measures the duration of that statement, the time between the statement being sent and the first result being returned. The top N times are kept, along with the SQL text itself and a list sorted on the execution times of the query is written to a file upon closure of the client session.

Configuration

Example minimal configuration:

Settings

The top filter has one mandatory parameter, filebase, and a number of optional parameters.

filebase

• Type: string

• Mandatory: Yes

• Dynamic: Yes

The basename of the output file created for each session. The session ID is added to the filename for each file written. This is a mandatory parameter.

The filebase may also be set as the filter, the mechanism to set the filebase via the filter option is superseded by the parameter. If both are set the parameter setting will be used and the filter option ignored.

count

• Type: number

• Mandatory: No

• Dynamic: Yes

• Default: 10

The number of SQL statements to store and report upon.

match

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

the queries logged by the filter.

exclude

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

the queries logged by the filter.

options

• Type:

• Mandatory: No

• Dynamic: No

• Values: ignorecase

for match and exclude.

source

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

Defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will be logged.

user

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

Defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will result in results being generated.

Examples

Example 1 - Heavily Contended Table

You have an order system and believe the updates of the PRODUCTS table is causing some performance issues for the rest of your application. You would like to know which of the many updates in your application is causing the issue.

Add a filter with the following definition:

Note the exclude entry, this is to prevent updates to the PRODUCTS_STOCK table from being included in the report.

Example 2 - One Application Server is Slow

One of your applications servers is slower than the rest, you believe it is related to database access but you are not sure what is taking the time.

Add a filter with the following definition:

In order to produce a comparison with an unaffected application server you can also add a second filter as a control.

In the service definition add both filters

You will then have two sets of logs files written, one which profiles the top 20 queries of the slow application server and another that gives you the top 20 queries of your control application server. These two sets of files can then be compared to determine what if anything is different between the two.

Output Report

The following is an example report for a number of fictitious queries executed against the employees example database available for MySQL.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The tee filter is a "plumbing" fitting in the MariaDB MaxScale filter toolkit. It can be used in a filter pipeline of a service to make copies of requests from the client and send the copies to another service within MariaDB MaxScale.

Please Note: Starting with MaxScale 2.2.0, any client that connects to a service which uses a tee filter will require a grant for the loopback address, i.e. 127.0.0.1.

Configuration

The configuration block for the TEE filter requires the minimal filter parameters in its section within the MaxScale configuration file. The service to send the duplicates to must be defined.

Settings

The tee filter requires a mandatory parameter to define the service to replicate statements to and accepts a number of optional parameters.

target

• Type: target

• Mandatory: No

• Dynamic: Yes

• Default: none

The target where the filter will duplicate all queries. The target can be either a service or a server. The duplicate connection that is created to this target will be referred to as the "branch target" in this document.

service

• Type: service

• Mandatory: No

• Dynamic: Yes

• Default: none

The service where the filter will duplicate all queries. This parameter is deprecated in favor of the target parameter and will be removed in a future release. Both target and service cannot be defined.

match

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

What queries should be included.

exclude

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

What queries should be excluded.

options

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: ignorecase

How regular expressions should be interpreted.

source

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional source parameter defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will be replicated.

user

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional user parameter defines a user name that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username are replicated.

sync

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

Enable synchronous routing mode. When configured with sync=true, the filter will queue new queries until the response from both the main and the branch target has been received. This means that for n executed queries, n - 1 queries are guaranteed to be synchronized. Adding one extra statement (e.g. SELECT 1) to a batch of statements guarantees that all previous SQL statements have been successfully executed on both targets.

In the synchronous routing mode, a failure of the branch target will cause the client session to be closed.

Limitations

• All statements that are executed on the branch target are done in an asynchronous manner. This means that when the client receives the response there is no guarantee that the statement has completed on the branch target. The sync feature provides some synchronization guarantees that can be used to verify successful execution on both targets.

• Any errors on the branch target will cause the connection to it to be closed. If target is a service, it is up to the router to decide whether the connection is closed. For direct connections to servers, any network errors cause the connection to be closed. When the connection is closed, no new queries will be routed to the branch target.

With sync=true, a failure of the branch target will cause the whole session to be closed.

Module commands

Read documentation for details about module commands.

The tee filter supports the following module commands.

tee disable [FILTER]

This command disables a tee filter instance. A disabled tee filter will not send any queries to the target service.

tee enable [FILTER]

Enable a disabled tee filter. This resumes the sending of queries to the target service.

Examples

Example 1 - Replicate all inserts into the orders table

Assume an order processing system that has a table called orders. You also have another database server, the datamart server, that requires all inserts into orders to be replicated to it. Deletes and updates are not, however, required.

Set up a service in MariaDB MaxScale, called Orders, to communicate with the order processing system with the tee filter applied to it. Also set up a service to talk to the datamart server, using the DataMart service. The tee filter would have as its service entry the DataMart service, by adding a match parameter of "insert into orders" would then result in all requests being sent to the order processing system, and insert statements that include the orders table being additionally sent to the datamart server.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

Hint Syntax

Note: If a query has more than one comment only the first comment is processed. Always place any MaxScale related comments first before any other comments that might appear in the query.

Comments and comment types

The client connection will need to have comments enabled. For example the mariadb and mysql command line clients have comments disabled by default and they need to be enabled by passing the --comments or -c option to it. Most, if not all, connectors keep all comments intact in executed queries.

For comment types, use either -- (notice the whitespace after the double hyphen) or # after the semicolon or /* ... */ before the semicolon.

Inline comment blocks, i.e. /* .. */, do not require a whitespace character after the start tag or before the end tag but adding the whitespace is advised.

Hint body

All hints must start with the maxscale tag.

The hints have two types, ones that define a server type and others that contain name-value pairs.

Routing destination hints

These hints will instruct the router to route a query to a certain type of a server.

Route to primary

A master value in a routing hint will route the query to a primary server. This can be used to direct read queries to a primary server for a up-to-date result with no replication lag.

Route to replica

A slave value will route the query to a replica server. Please note that the hints will override any decisions taken by the routers which means that it is possible to force writes to a replica server.

Route to named server

A server value will route the query to a named server. The value of<server name> needs to be the same as the server section name in maxscale.cnf. If the server is not used by the service, the hint is ignored.

Route to last used server

A last value will route the query to the server that processed the last query. This hint can be used to force certain queries to be grouped to the same server.

Name-value hints

These control the behavior and affect the routing decisions made by the router. Currently the only accepted parameter is the readwritesplit parametermax_slave_replication_lag. This will route the query to a server with a lower replication lag than this parameter's value.

Hint stack

Hints can be either single-use hints, which makes them affect only one query, or named hints, which can be pushed on and off a stack of active hints.

Defining named hints:

Pushing a hint onto the stack:

Popping the topmost hint off the stack:

You can define and activate a hint in a single command using the following:

You can also push anonymous hints onto the stack which are only used as long as they are on the stack:

Prepared Statements

The hintfilter supports routing hints in prepared statements for both thePREPARE and EXECUTE SQL commands as well as the binary protocol prepared statements.

Binary Protocol

With binary protocol prepared statements, a routing hint in the prepared statement is applied to the execution of the statement but not the preparation of it. The preparation of the statement is routed normally and is sent to all servers.

For example, when the following prepared statement is prepared with the MariaDB Connector-C function mariadb_stmt_prepare and then executed withmariadb_stmt_execute the result is always returned from the primary:

Support for binary protocol prepared statements was added in MaxScale 6.0 ().

The protocol commands that the routing hints are applied to are:

• COM_STMT_EXECUTE

• COM_STMT_BULK_EXECUTE

• COM_STMT_SEND_LONG_DATA

• COM_STMT_FETCH

Support for direct execution of prepared statements was added in MaxScale 6.2.0. For example the MariaDB Connector-C uses direct execution whenmariadb_stmt_execute_direct is used.

Text Protocol

Text protocol prepared statements (i.e. the PREPARE and EXECUTE SQL commands) behave differently. If a PREPARE command has a routing hint, it will be routed according to the routing hint. Any subsequent EXECUTE command will not be affected by the routing hint in the PREPARE statement. This means they must have their own routing hints.

The following example is the recommended method of executing text protocol prepared statements with hints:

The PREPARE is routed normally and will be routed to all servers. TheEXECUTE will be routed to the primary as a result of it having the route to master hint.

Examples

Routing SELECT queries to primary

In this example, MariaDB MaxScale is configured with the readwritesplit router and the hint filter.

Behind MariaDB MaxScale is a primary server and a replica server. If there is replication lag between the primary and the replica, read queries sent to the replica might return old data. To guarantee up-to-date data, we can add a routing hint to the query.

The first INSERT query will be routed to the primary. The following SELECT query would normally be routed to the replica but with the added routing hint it will be routed to the primary. This way we can do an INSERT and a SELECT right after it and still get up-to-date data.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The Regex filter is a filter module for MariaDB MaxScale that is able to rewrite query content using regular expression matches and text substitution. The regular expressions use the PCRE2 syntax.

PCRE2 library uses a different syntax than POSIX to refer to capture groups in the replacement string. The main difference is the usage of the dollar character instead of the backslash character for references e.g. $1 instead of\1. For more details about the replacement string differences, please read the chapter in the PCRE2 manual.

Configuration

The following demonstrates a minimal configuration.

Settings

The Regex filter has two mandatory parameters: match and replace.

match

• Type:

• Mandatory: Yes

• Dynamic: Yes

Defines the text in the SQL statements that is replaced.

options

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: ignorecase

The options-parameter affects how the patterns are compiled as .

replace

• Type: string

• Mandatory: Yes

• Dynamic: Yes

This is the text that should replace the part of the SQL-query matching the pattern defined in match.

source

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional source parameter defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will have the match and replacement applied to them.

user

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional user parameter defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will have the match and replacement applied to them.

log_file

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional log_file parameter defines a log file in which the filter writes all queries that are not matched and matching queries with their replacement queries. All sessions will log to this file so this should only be used for diagnostic purposes.

log_trace

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

The optional log_trace parameter toggles the logging of non-matching and matching queries with their replacements into the log file on the info level. This is the preferred method of diagnosing the matching of queries since the log level can be changed at runtime. For more details about logging levels and session specific logging, please read the .

Examples

Example 1 - Replace MySQL 5.1 create table syntax with that for later versions

MySQL 5.1 used the parameter TYPE = to set the storage engine that should be used for a table. In later versions this changed to be ENGINE =. Imagine you have an application that you cannot change for some reason, but you wish to migrate to a newer version of MySQL. The regexfilter can be used to transform the create table statements into the form that could be used by MySQL 5.5

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The rewrite filter allows modification of sql queries on the fly. Reasons for modifying queries can be to rewrite a query for performance, or to change a specific query when the client query is incorrect and cannot be changed in a timely manner.

The examples will use Rewrite Filter file format. See below.

Syntax

Native syntax

Rewriter native syntax uses placeholders to grab and replace parts of text.

Placeholders

The syntax for a plain placeholder is @{N} where N is a positive integer.

The syntax for a placeholder regex is @{N:regex}. It allows more control when needed.

The below is a valid entry in rf format. For demonstration, all options are set. This entry is a do-nothing entry, but illustrates placeholders.

If the input sql is select id, name from my_table where id = 42 then @{2} = "id, name" and @{3} = "42". Since the replace template is identical to the match template the end result is that the output sql will be the same as the input sql.

Placeholders can be used as forward references.@{1:^}select @{2}, count(*) from @{3} group by @{2}. For a match, the two @{2} text grabs must be equal.

Match template

The match template is used to match against the sql to be rewritten.

The match template can be partial from mytable. But the actual underlying regex match is always for the whole sql. If the match template does not start or end with a placeholder, placeholders are automatically added so that the above becomes @{1}from mytable@{2}. The automatically added placeholders cannot be used in the replace template.

Matching the whole input also means that Native syntax does not support (and is not intended to support) scan and replace. Only the first occurrence of the above from mytable can be modified in the replace template. However, one can selectively choose to modify e.g. the first through third occurrence of from mytable by writingfrom mytable @{1} from mytable @{2} from mytable @{3}.

For scan and replace use a different regex_grammar (see below).

Replace template

The replace template uses the placeholders from the match template to rewrite sql.

An important option for smooth matching is ignore_whitespace, which is on (true) by default. It creates the match regex in such a way that the amount and kind of whitespace does not affect matching. However, to make ignore_whitespace always work, it is important to add whitespace where allowed. If "id=42" is in the match template then only the exact "id=42" can match. But if "id = 42" is used, andignore_whitespace is on, both "id=42" and "id = 42" will match.

Another example, and what not to do:

That works, but because the match lacks specific detail about the expected sql, things are likely to break. In this caseshow indexes from my_table would no longer work.

The minimum detail in this case could be:

but if more detail is known, like something specific in the where clause, that too should be added.

Placeholder Regex

Syntax: @{N:regex}

In a placeholder regex the character } must be escaped to \} (for literal matching). Plain parenthesis "()" indicate capturing groups, which are internally used by the Native grammar. Thus plain parentheses in a placeholder regex will break matching. However, non-capturing groups can be used: e.g. @{1:(:?Jane|Joe)}. To match a literal parenthesis use an escape, e.g. \(.

Suppose an application is misbehaving after an upgrade and a quick fix is needed. This query select zip from address_book where str_id = "AZ-124" is correct, but if the id is an integer the where clause should be id = 1234.

Using plain regular expressions

For scan and replace the regex_grammar must be set to something else than Native. An example will illustrate the usage.

Replace all occurrences of "wrong_table_name" with "correct_table_name". Further, if the replacement was made then replace all occurrences of wrong_column_name with correct_column_name.

Configuration

Adding a rewrite filter.

Settings

template_file

• Type: string

• Mandatory: Yes

• Dynamic: Yes

• Default: No default value

Path to the template file.

regex_grammar

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: Native

Default regex_grammar for templates

case_sensitive

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

Default case sensitivity for templates

log_replacement

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

Log replacements at NOTICE level.

Settings per template in the template file

regex_grammar

• Type: string

• Values: Native, ECMAScript, Posix, EPosix, Awk, Grep, EGrep

Overrides the global regex_grammar of a template.

case_sensitive

• Type:

• Default: From maxscale.cnf

Overrides the global case sensitivity of a template.

ignore_whitespace

• Type:

• Default: true

Ignore whitespace differences in the match template and input sql.

continue_if_matched

• Type:

• Default: false

If a template matches and the replacement is done, continue to the next template and apply it to the result of the previous rewrite.

what_if

• Type:

• Default: false

Do not make the replacement, only log what would have been replaced (NOTICE level).

Rewrite file format

The rf format for an entry is:

The character # starts a single line comment when it is the first character on a line.

Empty lines are ignored.

The rf format does not need any additional escaping to what the basic format requires (see Placeholder Regex).

Options are specified as follows:

The colon must stick to the option name.

The separators % and %% must be the exact content of their respective separator lines.

The templates can span multiple lines. Whitespace does not matter as long as ignore_whitespace = true. Always use space where space is allowed to maximize the utility ofignore_whitespace.

Example

Json file format

The json file format is harder to read and edit manually. It will be needed if support for editing of rewrite templates is added to the GUI.

All double quotes and escape characters have to be escaped in json, i.e '"' and '\'.

The same example as above is:

Reload template file

The configuration is re-read if any dynamic value is updated even if the value does not change.

Reference

• ECMAScript

• Posix

• EPosix

• Awk

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The Query Log All (QLA) filter logs query content. Logs are written to a file in CSV format. Log elements are configurable and include the time submitted and the SQL statement text, among others.

Configuration

A minimal configuration is below.

Log Rotation

The qlafilter logs can be rotated by executing the maxctrl rotate logs command. This will cause the log files to be reopened when the next message is written to the file. This applies to both unified and session type logging.

Settings

The QLA filter has one mandatory parameter, filebase, and a number of optional parameters. These were introduced in the 1.0 release of MariaDB MaxScale.

filebase

• Type: string

• Mandatory: Yes

• Dynamic: No

The basename of the output file created for each session. A session index is added to the filename for each written session file. For unified log files,.unified is appended.

match

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Include queries that match the regex.

exclude

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Exclude queries that match the regex.

options

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: case

The extended option enables PCRE2 extended regular expressions.

user

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: ""

Limit logging to sessions with this user.

source

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: ""

Limit logging to sessions with this client source address.

user_match

• Type:

• Mandatory: No

• Dynamic: Yes

Only log queries from users that match this pattern. If the user parameter is used, the value of user_match is ignored.

Here is an example pattern that matches the users alice and bob:

user_exclude

• Type:

• Mandatory: No

• Dynamic: Yes

Exclude all queries from users that match this pattern. If the user parameter is used, the value of user_exclude is ignored.

Here is an example pattern that excludes the users alice and bob:

source_match

• Type:

• Mandatory: No

• Dynamic: Yes