hintfilter

Overview

Hint Filter allows services to interpret routing hints, which can be specified in a comment when a query is executed.

USAGE

A query comment defines the routing behavior.

Several flavors of comment are supported. When using the double-hyphen style, whitespace is required after the double hyphen.

SELECT @@hostname; -- maxscale route to master

SELECT @@hostname; # maxscale route to master

SELECT @@hostname /* maxscale route to master */ ;

DETAILS

MaxScale Query Hint Filter is available for the Enterprise Server With Replica(s) topology.

MaxScale Query Hint Filter (hintfilter) uses comments to route queries to the desired server. For cloud databases on SkySQL, this can be used to query status and configuration variable values on servers other than the Primary (master).

MaxScale Query Hint Filter is enabled by default. Query Hints are applied when executing queries via the Read/Write Port of the cloud database.

Comment Transmission

With hintfilter, a query comment defines the routing behavior.

Some database clients will not by default transmit query comments to the database server. Additional client configuration may be required for proper transmission of query comments.

For the mariadb (or mysql) command-line database client, the --comments command-line option enables transmission of comments to the database server.

For example:

$ mariadb --comments --host HOSTNAME.skysql.mariadb.com \
    --port WRITEPORT --user USERNAME -p --ssl-ca PEMFILE.pem \
    -e "SELECT @@hostname; -- maxscale route to master"

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

[Read-Write-Service]
transaction_replay_retry_on_deadlock=true
type=service
user=maxscale_dbdgf12120236
connection_keepalive=200s
filters=Hint
master_failure_mode=fail_on_write
password=asdfghjkl1234567890ASD!
transaction_replay=true
master_accept_reads=true
router=readwritesplit
transaction_replay_attempts=10
transaction_replay_max_size=10Mi

Route to Write Servers

To route to a server that accepts writes (the Primary):

SELECT @@hostname; -- maxscale route to master

Route to Read Servers

To route to a server that accepts reads:

SELECT @@hostname; -- maxscale route to slave

By default, master_accept_reads is enabled, so queries routed to read servers may be routed to either the Primary or a Replica.

If master_accept_reads is disabled, queries are routed only to Replicas. To disable master_accept_reads:

  1. Access the Configuration ManagerConfiguration Manager.

  2. Create or edit a database configuration. Set the master_accept_reads parameter to false.

  3. Apply the configuration to your service.

Route to a Specific Server

To route to a specific server:

SELECT @@hostname; -- maxscale route to server <server name>

Route to the Last Queried Server

To route to the server that processed the last query, allowing repeat querying of the same server:

SELECT @@hostname; -- maxscale route to last

Route by Replication Lag

To route to the server with replication lag lower than a threshold:

SELECT @@hostname; -- maxscale max_slave_replication_lag=<seconds>

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08

  • Present starting in MariaDB MaxScale 23.08.1.

23.02

  • Present starting in MariaDB MaxScale 23.02.1.

22.08

  • Present starting in MariaDB MaxScale 22.08.1.

6

  • Present starting in MariaDB MaxScale 6.0.0.

2.5

  • Present starting in MariaDB MaxScale 2.5.0.

2.4

  • Present starting in MariaDB MaxScale 2.4.0.

Release Series

History

22.08

  • Present starting in MariaDB MaxScale 22.08.1.

EXTERNAL REFERENCES