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

Explicit transactions without a default database do not work as expected with schemarouter

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.5.23, 6.4.4, 22.08.3
    • 6.4.5, 22.08.4
    • schemarouter
    • None
    • MXS-SPRINT-174

    Description

      The following transaction demonstrates the problem:

      BEGIN;
      SELECT id FROM db1.t1 FOR UPDATE;
      UPDATE db1.t1 SET id = id + 1 WHERE id = 1;
      COMMIT;
      

      The BEGIN and COMMIT are routed according to the queries without explicit databases case mentioned in the limitations. The result of this is that the queries are not routed to the same server where the SQL content of the transaction is sent.

      Here is a log excerpt from MaxScale that demonstrates the problem:

      2023-01-03 12:14:24   info   : (3) Found matching user 'maxuser'@'127.0.0.1' for client 'maxuser'@'::ffff:127.0.0.1' with sufficient privileges.
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Connected Master, Running in   'server1'
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Connected Slave, Running in    'server2'
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Connected Master, Running in   'server3'
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Connected Running in   'server4'
      2023-01-03 12:14:24   info   : (3) Started SchemaRouter-Router client session [3] for 'maxuser' from ::ffff:127.0.0.1
      2023-01-03 12:14:24   info   : (3) Connected to 'server1' with thread id 19
      2023-01-03 12:14:24   info   : (3) Connected to 'server2' with thread id 19
      2023-01-03 12:14:24   info   : (3) Connected to 'server3' with thread id 16621
      2023-01-03 12:14:24   info   : (3) Connected to 'server4' with thread id 764
      2023-01-03 12:14:24   info   : (3) Authentication to 'server4' succeeded.
      2023-01-03 12:14:24   info   : (3) Authentication to 'server1' succeeded.
      2023-01-03 12:14:24   info   : (3) Authentication to 'server2' succeeded.
      2023-01-03 12:14:24   info   : (3) Authentication to 'server3' succeeded.
      2023-01-03 12:14:24   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 10, type: QUERY_TYPE_BEGIN_TRX, stmt: begin 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); > Command: COM_QUERY, stmt: begin 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to         server1 <
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Reply complete from 'server1'
      2023-01-03 12:14:24   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 37, type: QUERY_TYPE_READ|QUERY_TYPE_WRITE, stmt: select id from db1.t1 for update 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); > Command: COM_QUERY, stmt: select id from db1.t1 for update 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Query targets table on server 'server4'
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to         server4 <
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Reply complete from 'server4'
      2023-01-03 12:14:24   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 34, type: QUERY_TYPE_WRITE, stmt: update db1.t1 set id = id + 1 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); > Command: COM_QUERY, stmt: update db1.t1 set id = id + 1 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Query targets table on server 'server4'
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to         server4 <
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Reply complete from 'server4'
      2023-01-03 12:14:24   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 11, type: QUERY_TYPE_COMMIT, stmt: commit 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); > Command: COM_QUERY, stmt: commit 
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to         server1 <
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Reply complete from 'server1'
      2023-01-03 12:14:24   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x01) COM_QUIT, plen: 5, type: QUERY_TYPE_SESSION_WRITE, stmt:  
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); > Command: COM_QUIT, stmt:  
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to master: server1
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to slave: server2
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to master: server3
      2023-01-03 12:14:24   info   : (3) [schemarouter] (SchemaRouter-Router); Route query to slave: server4
      2023-01-03 12:14:24   info   : (3) Stopped SchemaRouter-Router client session [3]
      

      This also affects transactions that use autocommit=0 but commit it using the COMMIT SQL statement. Transactions that commit the transaction with SET autocommit=1 are not affected by this.

      The ideal thing to do would be to delay the routing of the explicit transaction start until the server on which it is started is known. A less ideal solution that would still solve this is to treat all BEGIN and COMMIT commands as session commands and route them to all backends.

      Attachments

        Activity

          People

            markus makela markus makela
            markus makela markus makela
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.