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

ReadWriteSplit's master connection can time out if session only issues read-only queries

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 2.1.6
    • 2.2.0
    • readwritesplit
    • None

    Description

      When a ReadWriteSplit session executes a read-only query outside of a transaction, it will get routed to a slave. When this happens, MaxScale does not use the master connection at all. Therefore, if a session executes only read-only queries, then its master connection could be allowed to time out due to wait_timeout.

      This can easily be reproduced with the following:

      mysql -h 127.0.0.1 -u maxscale -ppassword <<EOF
      CREATE DATABASE IF NOT EXISTS db1;
      CREATE TABLE IF NOT EXISTS db1.tab (str varchar(50));
      SET wait_timeout=10;
      SELECT 1;
      SELECT SLEEP(5);
      SELECT 1;
      SELECT SLEEP(5);
      SELECT 1;
      SELECT SLEEP(5);
      INSERT INTO db1.tab (str) VALUES ('str');
      EOF
      

      Output:

      [ec2-user@ip-172-30-0-106 ~]$ mysql -h 127.0.0.1 -u maxscale -ppassword <<EOF
      > CREATE DATABASE IF NOT EXISTS db1;
      > CREATE TABLE IF NOT EXISTS db1.tab (str varchar(50));
      > SET wait_timeout=10;
      > SELECT 1;
      > SELECT SLEEP(5);
      > SELECT 1;
      > SELECT SLEEP(5);
      > SELECT 1;
      > SELECT SLEEP(5);
      > INSERT INTO db1.tab (str) VALUES ('str');
      > EOF
      1
      1
      SLEEP(5)
      0
      1
      1
      SLEEP(5)
      0
      1
      1
      ERROR 2013 (HY000) at line 9: Lost connection to MySQL server during query
      

      If this problem is encounted in a Java application, then the error is a bit different. I've also attached a Java program that can be used to reproduce the issue.

      Output:

      [ec2-user@ip-172-30-0-106 ~]$ export CLASSPATH='/home/ec2-user/mariadb-java-client-2.1.0.jar:.'
      [ec2-user@ip-172-30-0-106 ~]$ javac ./TestMasterConnIdle.java
      [ec2-user@ip-172-30-0-106 ~]$ java TestMasterConnIdle
      Checking thread 10.
      Checking thread 8.
      Checking thread 11.
      Checking thread 9.
      Checking thread 10.
      Checking thread 8.
      Checking thread 11.
      SQLException: (conn:2073) Could not send query: unexpected end of stream, read 0 bytes from 4
      SQLState: 08
      VendorError: 0
      Checking thread 9.
      Checking thread 10.
      SQLException: (conn:2074) Could not send query: unexpected end of stream, read 0 bytes from 4
      SQLState: 08
      VendorError: 0
      Checking thread 8.
      SQLException: (conn:2072) Could not send query: unexpected end of stream, read 0 bytes from 4
      SQLState: 08
      VendorError: 0
      Checking thread 9.
      SQLException: (conn:2075) Could not send query: unexpected end of stream, read 0 bytes from 4
      SQLState: 08
      VendorError: 0
      

      As a potential fix, maybe ReadWriteSplit should route a COM_PING to a session's master connection whenever a query is routed to a slave connection?

      Attachments

        1. maxscale.cnf
          2 kB
          Geoff Montee
        2. TestMasterConnIdle.java
          3 kB
          Geoff Montee

        Issue Links

          Activity

            People

              Unassigned Unassigned
              GeoffMontee Geoff Montee (Inactive)
              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.