Routing Statements with MaxScale's Read/Write Split Router

Queries that write to the database.

For example, this includes, but is not limited to, the following statements:

• INSERTINSERT

• INSERT ... RETURNING

• UPDATEUPDATE

• DELETEDELETE

• REPLACEREPLACE

• REPLACE ... RETURNING

• LOAD DATA INFILELOAD DATA INFILE

Queries that modify the database (DDL)

For example, this includes, but is not limited to, the following statements:

• CREATE DATABASECREATE DATABASE

• ALTER DATABASEALTER DATABASE

• DROP DATABASEDROP DATABASE

• CREATE TABLECREATE TABLE

• ALTER TABLEALTER TABLE

• DROP TABLEDROP TABLE

• CREATE VIEWCREATE VIEW

• ALTER VIEWALTER VIEW

• DROP VIEWDROP VIEW

• CREATE SEQUENCE

• ALTER SEQUENCEALTER SEQUENCE

• DROP SEQUENCE

• CREATE TRIGGERCREATE TRIGGER

• DROP TRIGGERDROP TRIGGER

• CREATE PROCEDURECREATE PROCEDURE

• ALTER PROCEDUREALTER PROCEDURE

• DROP PROCEDUREDROP PROCEDURE

• CREATE FUNCTIONCREATE FUNCTION

• ALTER FUNCTIONALTER FUNCTION

• DROP FUNCTIONDROP FUNCTION

• CREATE USERCREATE USER

• ALTER USERALTER USER

• DROP USERDROP USER

• CREATE ROLE

• DROP ROLE

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:

• When autocommit is set to OFF.

• When BEGIN is executed.

• When START TRANSACTIONSTART TRANSACTION is executed.

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

EXECUTEEXECUTE statements that execute prepared statements

Queries that are read-only

For example, this includes, but is not limited to, the following statements:

• SELECTSELECT

Queries that read system or user-defined variables

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

SELECT @@global.alter_algorithm;

select @@my_user_var;

SHOW statements

For example, this includes, but is not limited to, the following statements:

• SHOW CHARACTER SETSHOW CHARACTER SET

• SHOW COLLATIONSHOW COLLATION

• SHOW COLUMNSSHOW COLUMNS

• SHOW CREATE DATABASESHOW CREATE DATABASE

• SHOW CREATE FUNCTIONSHOW CREATE FUNCTION

• SHOW CREATE PROCEDURESHOW CREATE PROCEDURE

• SHOW CREATE SEQUENCE

• SHOW CREATE TABLESHOW CREATE TABLE

• SHOW CREATE TRIGGERSHOW CREATE TRIGGER

• SHOW CREATE USERSHOW CREATE USER

• SHOW CREATE VIEWSHOW CREATE VIEW

• SHOW DATABASESSHOW DATABASES

• SHOW ENGINESSHOW ENGINES

• SHOW TABLESSHOW TABLES

• SHOW VARIABLESSHOW VARIABLES

Queries using built-in functionsbuilt-in functions

SETSET statements, including those embedded in read-only statements

USEUSE statements

PREPAREPREPARE statements that create prepared statements

Internal client commands, such as QUIT, PING, STMT RESET, and CHANGE USER.