Maxscale R/W split - SELECTS hitting master?

[emmalee]

Using Maxscale 6.3.0 in Read-Write Split, with 1 master and 2 slaves( all MariaDB 10.4), MASTER not configured to accept reads, is it normal that Maxscale redirect these to master? If yes, why?

1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721611 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721611 Close stmt

[tahishae]

This is currently expected behavior: the queries in question are the preparation and closing of prepared statements. Readwritesplit currently routes these to all nodes in the cluster instead of only on the node where it ends up being executed. The messages tell that the SQL is not actually executed on the node as there are no Execute stmt messages.

Readwritesplit does this both to verify that all nodes respond similarly and to provide a way of distributing load without having to prepare the statement again in case a need for it arises.

---

The most common case where SELECT statments are routed to a master node in the cluster is when they are done inside a transaction. The second most common case is when they use some special function or variable (e.g. @@last_insert_id, https://mariadb.com/kb/en/sequences/ ) that forces it to be treated as if it were a write.

Some frameworks disable autocommit by default which makes all statements a part of a transaction. If you explicitly open a read-only transaction with START TRANSACTION READ ONLY, readwritesplit will route all reads inside it to the same slave server in the cluster.

A good way to debug these sort of routing problems is to enable the info log level. Add log_info=true under the [maxscale] section of your configuration file or run maxctrl enable log-priority info to do it with MaxCtrl. The log will contain all executed SQL statements, their classification result (read or write), whether a transaction is open as well as other relevant bits of information. Using this you should be able to determine why the statements are routed the way they are.