This is a read-only copy of the MariaDB Knowledgebase generated on 2025-05-01. For the latest, interactive version please visit https://mariadb.com/kb/.

Routing Statements with MaxScale's Read/Write Split Router

Overview

The Read/Write Split Router (readwritesplit) uses well-defined rules to determine whether a statement can be routed to a replica server, or whether it needs to be routed to the primary server. Application designers must understand these rules to ensure that the router can properly load balance queries.

Statements Routed to the Primary Server

The following statements are routed to the primary server:

  • Queries within open transactions If the application uses explicit transactions, then all queries within the transaction will be routed to the primary server. Explicit transactions are used in the following cases:

For example, all queries will be routed to the primary server in this case:

SET SESSION autocommit=OFF;
SELECT * FROM hq_sales.invoices WHERE branch_id=1;
INSERT INTO hq_sales.invoices
   (customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
COMMIT;

And all queries will also be routed to the primary server in this case:

BEGIN;
SELECT * FROM hq_sales.invoices WHERE branch_id=1;
INSERT INTO hq_sales.invoices
   (customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
COMMIT;
  • Queries using stored procedures
  • Queries using stored functions
  • Queries using user-defined functions (UDF)
  • Queries that use temporary tables
  • EXECUTE statements that execute prepared statements

Statements Routed to a Replica Server

The following statements are routed to a replica server:

  • Queries that are read-only For example, this includes, but is not limited to, the following statements:

For example, the following queries would be routed to a replica:

SELECT @@global.alter_algorithm;
select @@my_user_var;
SHOW statements
  • Queries using built-in functions

Statements Routed to All Servers

The following statements are routed to all servers:

  • SET statements, including those embedded in read-only statements
  • USE statements
  • PREPARE statements that create prepared statements
  • Internal client commands, such as QUIT, PING, STMT RESET, and CHANGE USER.
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.