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

MaxScale 2.3 readwritesplit will not route temporary tables to master with leading comments

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 2.3.2
    • N/A
    • readwritesplit
    • None
    • Centos 6

    Description

      MaxScale will not route to master when comments are given in a multi-statement query.

      Orignal Query (redacted)

      # table of orders (cmp_order_id) which when completed,
      # will cause another order (dec_order_id) to be auto-declined.
      CREATE TEMPORARY TABLE tmp_order_decliners
      (
        cmp_order_id int unsigned not null,
        dec_order_id int unsigned not null,
        KEY cmp_order_id(cmp_order_id),
        KEY dec_order_id(dec_order_id)
      );
       
      # this is the mapping.
      INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES 
      $WCINCLUDE|OrderDeclineOrderList$
      ;
       
      # The actual query
      SELECT 
      CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="',
      IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()),
      '" enc_order_id="',
      eo.enc_order_id,
      '" status="-4" comments="xxxx-xxxxx by ',
      ceo.enc_order_id,
      ' (',
      ceo.order_name,
      ')" /></ORDERS>') AS `eorder_xml`,
      '?f=xxxxx&s=xxxxxx' AS `URL`
      FROM tmp_order_decliners t
      INNER JOIN xxxxxxx ceo
        ON ceo.order_id=t.cmp_order_id
        AND ceo.status=2
      INNER JOIN xxxxx eo
        ON eo.order_id=t.dec_order_id
        AND (eo.status=0 OR eo.status=3)
        AND eo.pat_id=ceo.pat_id
        AND ceo.completed_dt >= eo.create_dt
      

      Failed Query, sends to slave

      2019-01-16 16:49:15   info   : (307) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 1102, type: QUERY_TYPE_READ, stmt: CREATE TEMPORARY TABLE /* db=xxxxxxxx dbuser=xxxxxxxx file=xxxxxx.c func=SysRep_Execute user_comment=uid:xxxx,uname:xxxx,rc:RC201806,hdl:xxxxxxx,aid:xxxx line:1718*/ tmp_order_decliners (   cmp_order_id int unsigned not null,   dec_order_id int unsigned not null,   KEY cmp_order_id(cmp_order_id),   KEY dec_order_id(dec_order_id) );
      # this is the mapping.
      INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES
      (3151, 3152)
      ;
      # The actual query
      SELECT
      CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="', IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()), '" enc_order_id="', eo.enc_order_id, '" status="-4" comments="Auto-Declined by ', ceo.enc_order_id, ' (', ceo.order_name, ')" /></ORDERS>') AS `eorder_xml`, '?f=ajaxpost&s=encounterorders' AS `URL` FROM tmp_order_decliners t INNER JOIN encounter_orders ceo   ON ceo.order_id=t.cmp_order_id   AND ceo.status=2 INNER JOIN encounter_orders eo   ON eo.order_id=t.dec_order_id   A
      2019-01-16 16:49:15   info   : (307) Route query to slave: fw-mm-db1    [xxxxxxx]:3306 <
      

      Successful query, sends to master

      2019-01-16 17:01:03   info   : (414) Write operation detected, next 100 queries routed to master
      2019-01-16 17:01:03   info   : (414) Write operation detected, queries routed to master for 1 seconds
      2019-01-16 17:01:03   info   : (414) Added temporary table xxxxxxxx.tmp_order_decliners
      2019-01-16 17:01:03   info   : (414) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 1105, type: QUERY_TYPE_WRITE|QUERY_TYPE_CREATE_TMP_TABLE, stmt: CREATE TEMPORARY TABLE /* db=xxxxxxxx dbuser=xxxxxxxx file=xxxxxx.c func=SysRep_Perform user_comment=uid:xxxxx,uname:xxxxxx,rc:RC201806,hdl:xxxxxxx,aid:xxxx line:1904*/ tmp_order_decliners (   cmp_order_id int unsigned not null,   dec_order_id int unsigned not null,   KEY cmp_order_id(cmp_order_id),   KEY dec_order_id(dec_order_id) );
      # this is the mapping.
      INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES
      (3151, 3152)
      ;
      # The actual query
      SELECT
      CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="', IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()), '" enc_order_id="', eo.enc_order_id, '" status="-4" comments="Auto-Declined by ', ceo.enc_order_id, ' (', ceo.order_name, ')" /></ORDERS>') AS `eorder_xml`, '?f=ajaxpost&s=encounterorders' AS `URL` FROM tmp_order_decliners t INNER JOIN encounter_orders ceo   ON ceo.order_id=t.cmp_order_id   AND ceo.status=2 INNER JOIN encounter_orders eo   ON eo.order_id=t.dec_order_id
      2019-01-16 17:01:03   info   : (414) Route query to master: db2   [xxxxxxx]:3306 <
      2019-01-16 17:01:03   info   : (414) Reply complete, last reply from db2
      

      MaxScale Read/Writer Service / CCR and Query Log Filter config

      [MM-RW-Service]
      type=service
      router=readwritesplit
      max_slave_replication_lag=1
      strict_multi_stmt=1
      master_accept_reads=1
      servers=db1,db2
      user=maxscale
      password=xXXXXX
      filters=CCRFilter | QueryLogger
       
      [CCRFilter]
      type=filter
      module=ccrfilter
      time=1
      count=100
       
      # Query Log Filter
      [QueryLogger]
      type=filter
      module=qlafilter
      separator=" | "
      log_data=date,session,user,reply_time,query
      log_type=unified
      filebase=/var/log/maxscale/querylog
      

      Attachments

        1. test.c
          2 kB
          Dave Juntgen

        Issue Links

          Activity

            People

              markus makela markus makela
              djuntgen Dave Juntgen
              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.