The readwritesplit module fails to send read only transactions to slaves if created by SET SESSION TRANSACTION READ ONLY.
How to Reproduce
The problem can be reproduced as follows.
- Check out https://github.com/mariadb-corporation/maxscale-docker/tree/master/maxscale
- Change to :latest tags.
- Do docker-compose up.
- Connect with mysql client to port 4006 (the readwrite port)
- Execute "SET SESSION TRANSACTION READ ONLY;"
- Execute "SET autocommit=0;" or "START TRANSACTION;"
- Execute "SELECT @@hostname;" multiple times
- Note how the one and same host shows up.
- Terminal command "docker ps" to see that the hostname is the master node.
In this case I expected to see different slave hostnames show up because that would mean the read only transaction was executed on a slave.
Use Case and Clarification
If the transaction is started with START TRANSACTION READ ONLY it will be properly sent to slaves. This is however not always possible.
In my concrete use case I'm developing a Spring Boot application with Hibernate and mariadb-connector/j. I want to leverage the slave nodes in read only transactions created with the @Transactional(readOnly=true) Spring Boot annotation.
Hibernate starts transactions via JDBC using SET autocommit=0; and can not be instructed to start them using START TRANSACTION READ ONLY. We can however make the mariadb-connector/j start read only connections using a JDBC url like: jdbc:mariadb:replication://maxscale:3306,maxscale:3306/databasename?assureReadOnly=true. So by using replication mode, repeating host:port twice and using assureReadOnly=true Hibernate properly uses connections that were started with SET SESSION TRANSACTION READ ONLY in situations where @Transactional(readOnly=true) Spring annotations were used.
I have checked with wireshark and the end result of queries sent by Hibernate goes something like:
SET SESSION TRANSACTION READ ONLY;
It would be great if MaxScale supported this. A potential workaround would be to set up a second read only listener, but it would be great not having to do that.
Hibernate will always open a transaction. Even when reading, but that makes sense from a data integrity perspective since the object relational mapping work often takes multiple select statements.
I think SpringBoot + Hibernate is a reasonably common usecase. A usecase that MaxScale does not seem to support well at all. What happens is that for pretty much all configurations all traffic will just go to the master node.