Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3980

readwritesplit not understanding SET SESSION TRANSACTION READ ONLY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 6.2.1
    • Fix Version/s: N/A
    • Component/s: Core
    • Labels:
      None

      Description

      In Short
      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.

      1. Check out https://github.com/mariadb-corporation/maxscale-docker/tree/master/maxscale
      2. Change to :latest tags.
      3. Do docker-compose up.
      4. Connect with mysql client to port 4006 (the readwrite port)
      5. Execute "SET SESSION TRANSACTION READ ONLY;"
      6. Execute "SET autocommit=0;" or "START TRANSACTION;"
      7. Execute "SELECT @@hostname;" multiple times
      8. Note how the one and same host shows up.
      9. 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;
      SET AUTOCOMMIT=0
      SELECT @@hostname;

      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.

      Reflection
      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              markus makela markus makela
              Reporter:
              oloflarsson Olof Larsson
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.