Binlog Filter

This filter was introduced in MariaDB MaxScale 2.3.0.

• Binlog Filter

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}

Comment Filter

• Comment Filter

  • Overview

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}

Optimistic Transaction Execution Filter

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

This filter will replace the BEGIN and START TRANSACTION SQL commands withSTART TRANSACTION READ ONLY. If the transaction is fully read-only, the transaction completes normally. However, if a write happens in the middle of a transaction, the filter issues a ROLLBACK command and then replays the read-only part of the transaction, including the original BEGIN statement. If the results of the replayed read-only part of the transaction is identical to the one that was returned to the client, the transaction proceeds normally. If the result checksum does not match, the connection is closed to prevent a write with the wrong transaction state from happening.

Configuration

To add the filter to a service, define an instance of the filter and then add it to a service's filters list:

This can also be done at runtime with:

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

Maxrows

This filter was introduced in MariaDB MaxScale 2.1.

LDI Filter

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}

Psreuse

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.

Enabling this feature will increase memory usage of a session. The amount of memory stored per prepared statement is proportional to the length of the prepared SQL statement and the number of parameters the statement has.

Configuration

To add the filter to a service, define an instance of the filter and then add it to a service's filters list:

Limitations

• If the SQL in the prepared statement is larger than 1677723 bytes, the prepared statement will not be cached.

• If the same SQL is prepared more than once at the same time, only one of them will succeed. This happens as the prepared statement reuse uses the SQL string in the comparison to detect if a statement is already prepared.

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

Hintfilter

This filter adds routing hints to a service. The filter has no parameters.

• Hintfilter

• Hint Syntax

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 themariadb 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}

Lua Filter

The luafilter is a filter that calls a set of functions in a Lua script.

Read the Lua language documentation for information on how to write Lua scripts.

Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.

Settings

The luafilter has two parameters. They control which scripts will be called by the filter. Both parameters are optional but at least one should be defined. If both global_script and session_script are defined, the entry points in both scripts will be called.

global_script

The global Lua script. The parameter value is a path to a readable Lua script which will be executed.

This script will always be called with the same global Lua state and it can be used to build a global view of the whole service.

session_script

The session level Lua script. The parameter value is a path to a readable Lua script which will be executed once for each session.

Each session will have its own Lua state meaning that each session can have a unique Lua environment. Use this script to do session specific tasks.

Lua Script Calling Convention

The entry points for the Lua script expect the following signatures:

• nil createInstance(name) - global script only, called when the script is first loaded

  • When the global script is loaded, it first executes on a global level before the luafilter calls the createInstance function in the Lua script with the filter's name as its argument.

• nil newSession(string, string) - new session is created

These functions, if found in the script, will be called whenever a call to the matching entry point is made.

Script Template

Here is a script template that can be used to try out the luafilter. Copy it into a file and add global_script=<path to script> into the filter configuration. Make sure the file is readable by the maxscale user.

Functions Exposed by the Luafilter

The luafilter exposes the following functions that can be called inside the Lua script API endpoints. The callback function in which they can be called is documented after the function signature. If the functions are called outside of the correct callback function, they raise a Lua error.

• string mxs_get_sql() (use: routeQuery)

• Returns the SQL of the query being executed. This returns an empty string for any query that is not a text protocol query (COM_QUERY). Support for prepared statements is not yet implemented.

• string mxs_get_type_mask() (use: routeQuery)

Example Configuration and Script

Here is a minimal configuration entry for a luafilter definition.

And here is a script that opens a file in /tmp/ and logs output to it.

Limitations

• mxs_get_sql() and mxs_get_canonical() do not work with queries done with the binary protocol.

• The Lua code is not restricted in any way which means excessively slow execution of it can cause the MaxScale process to become slower or to be aborted due to a SystemD watchdog timeout.

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

Consistent Critical Read Filter

This filter was introduced in MariaDB MaxScale 2.1.

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 can be specified as documented but the 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 of_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, case, extended

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}

Throttle

This filter was added in MariaDB MaxScale 2.3

Top Filter

• Top Filter

  • Overview

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, case, extended

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}

Named Server Filter

• Named Server Filter

  • Overview

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, case, extended

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 hint)

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}

Tee Filter

Query Log All Filter

• Query Log All Filter

  • Overview

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, ignorecase, extended

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

Only log queries from hosts that match this pattern. If the source parameter is used, the value of source_match is ignored.

Here is an example pattern that matches the loopback interface as well as the address 192.168.0.109:

source_exclude

• Type:

• Mandatory: No

• Dynamic: Yes

Exclude all queries from hosts that match this pattern. If the source parameter is used, the value of source_exclude is ignored.

Here is an example pattern that excludes the loopback interface as well as the address 192.168.0.109:

log_type

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: session, unified, stdout

The type of log file to use.

log_data

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: service, session, date

Type of data to log in the log files.

The durations reply_time and total_reply_time are by default in milliseconds, but can be specified to another unit using duration_unit.

The log entry is written when the last reply from the server is received. Prior to version 6.2 the entry was written when the query was received from the client, or if reply_time was specified, on first reply from the server.

NOTE The error_msg is the raw message from the server. Even if use_canonical_form is set the error message may contain user defined constants. For example:

Starting with MaxScale 24.02, the query parameter now correctly logs the execution of binary protocol commands as SQL (). The execution of batched statements (COM_STMT_BULK_LOAD) used by some connectors is not logged.

duration_unit

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: milliseconds

The unit for logging a duration. The unit can be milliseconds or microseconds. The abbreviations ms for milliseconds and us for microseconds are also valid. This option is available as of MaxScale version 6.2.

use_canonical_form

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

When this option is true the canonical form of the query is logged. In the canonical form all user defined constants are replaced with question marks. This option is available as of MaxScale version 6.2.

flush

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

Flush log files after every write.

append

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

separator

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: ","

Defines the separator string between elements of log entries. The value should be enclosed in quotes.

newline_replacement

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: " "

Default value is " " (one space). SQL-queries may include line breaks, which, if printed directly to the log, may break automatic parsing. This parameter defines what should be written in the place of a newline sequence (\r, \n or \r\n). If this is set as the empty string, then newlines are not replaced and printed as is to the output. The value should be enclosed in quotes.

Limitations

• Trailing parts of SQL queries that are larger than 16MiB are not logged. This means that the log output might contain truncated SQL.

• Batched execution using COM_STMT_BULK_EXECUTE is not converted into their textual form. This is done due to the large volumes of data that are usually involved with batched execution.

Examples

Example 1 - Query without primary key

Imagine you have observed an issue with a particular table and you want to determine if there are queries that are accessing that table but not using the primary key of the table. Let's assume the table name is PRODUCTS and the primary key is called PRODUCT_ID. Add a filter with the following definition:

The result of using this filter with the service used by the application would be a log file of all select queries querying PRODUCTS without using the PRODUCT_ID primary key in the predicates of the query. Executing SELECT * FROM PRODUCTS would log the following into /var/logs/qla/SelectProducts:

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

Rewrite Filter

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

• Mandatory: No

• Dynamic: Yes

• Default: true

Default case sensitivity for templates

log_replacement

• Type: boolean

• 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: boolean

• Default: From maxscale.cnf

Overrides the global case sensitivity of a template.

ignore_whitespace

• Type: boolean

• Default: true

Ignore whitespace differences in the match template and input sql.

continue_if_matched

• Type: boolean

• 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: boolean

• 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}

Masking

This filter was introduced in MariaDB MaxScale 2.1.

Transaction Performance Monitoring Filter

Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.

• Transaction Performance Monitoring Filter

Overview

The Transaction Performance Monitoring (TPM) filter is a filter module for MaxScale that monitors every SQL statement that passes through the filter. The filter groups a series of SQL statements into a transaction by detecting 'commit' or 'rollback' statements. It logs all committed transactions with necessary information, such as timestamp, client, SQL statements, latency, etc., which can be used later for transaction performance analysis.

Configuration

The configuration block for the TPM filter requires the minimal filter options in it's section within the maxscale.cnf file, stored in /etc/maxscale.cnf.

Filter Options

The TPM filter does not support any filter options currently.

Settings

The TPM filter accepts a number of optional parameters.

Filename

The name of the output file created for performance logging. The default filename is tpm.log.

Source

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

User

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

Delimiter

The optional delimiter parameter defines a delimiter that is used to distinguish columns in the log. The default delimiter is :::.

Query_delimiter

The optional query_delimiter defines a delimiter that is used to distinguish different SQL statements in a transaction. The default query delimiter is @@@.

Named_pipe

named_pipe is the path to a named pipe, which TPM filter uses to communicate with 3rd-party applications (e.g., ). Logging is enabled when the router receives the character '1' and logging is disabled when the router receives the character '0' from this named pipe. The default named pipe is /tmp/tpmfilter and logging is disabled by default.

For example, the following command enables the logging:

Similarly, the following command disables the logging:

Log Output Format

For each transaction, the TPM filter prints its log in the following format:

<timestamp> | <server_name> | <user_name> | <latency of the transaction> | <latencies of individual statements in the transaction> (delimited by 'query_delimiter') | <actual SQL statements>

Examples

Example 1 - Log Transactions for Performance Analysis

You want to log every transaction with its SQL statements and latency for future transaction performance analysis.

Add a filter with the following definition:

After the filter reads the character '1' from its named pipe, the following is an example log that is generated from the above TPM filter with the above configuration:

Note that 3 and 6 are latencies of each transaction in milliseconds, while 0.165 and 0.123 are latencies of the first statement of each transaction in milliseconds.

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