Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22246

Result rows duplicated by spider engine

Details

    Description

      I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

      G0:

      mysql> select * from test.t1;
      		 
      +----+--------+
      		 
      | id | node   |
      		 
      +----+--------+
      		 
      |  1 | DB-G0  |
      		 
      +----+--------+
      		 
      1 rows in set (0.00 sec)

      G1:

      mysql> select * from test.t1;
      		 
      +----+--------+
      		 
      | id | node   |
      		 
      +----+--------+
      		 
      |  2 | DB-G1  |
      		 
      +----+--------+
      		 
      1 rows in set (0.00 sec)

      I'm then using the spider engine offered by MariaDB 10.4 (mysqld Ver 10.4.12-MariaDB-1:10.4.12+maria~bionic-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)) to combine them like this:

      CREATE TABLE t1 (
      		 
         id BIGINT PRIMARY KEY,
      		 
         node TEXT
      		 
      ) ENGINE=Spider
      		 
      COMMENT='wrapper "mysql"'
      		 
      PARTITION BY HASH (id) (
      		 
       PARTITION par0 COMMENT = 'srv "G0", tbl "t1"',
      		 
       PARTITION par1 COMMENT = 'srv "G1", tbl "t1"'
      		 
      );

      It's working well for a simple query like this:

      MariaDB [(none)]> select * from test.t1;
      		 
      +-----+--------+
      		 
      | id  | node   |
      		 
      +-----+--------+
      		 
      |   1 | DB-G0  |
      		 
      |   2 | DB-G1  |
      		 
      +-----+--------+
      		 
      2 rows in set (0.013 sec)

      But it doubled the results when using this condition:

      MariaDB [(none)]> select * from test.t1 where id != 0;
      		 
      +-----+--------+
      		 
      | id  | node   |
      		 
      +-----+--------+
      		 
      |   1 | DB-G0  |
      		 
      |   2 | DB-G1  |
      		 
      |   1 | DB-G0  |
      		 
      |   2 | DB-G1  |
      		 
      +-----+--------+
      		 
      4 rows in set (0.017 sec)

      Here is my Spider related settings:

      #
      		 
      # * Spider
      		 
      #
      		 
      spider_bulk_update_mode      = 2
      		 
      spider_bulk_update_size      = 512K
      		 
      spider_bulk_size             = 512K
      		 
      spider_conn_wait_timeout     = 5
      		 
      spider_connect_timeout       = 3
      		 
      spider_direct_dup_insert     = 1
      		 
      spider_direct_order_limit    = 1
      		 
      spider_multi_split_read      = 1
      		 
      spider_net_read_timeout      = 5
      		 
      spider_net_write_timeout     = 5
      		 
      spider_quick_mode            = 3
      		 
      spider_quick_page_size       = 1000
      		 
      spider_remote_trx_isolation  = 1
      		 
      spider_support_xa            = 0
      		 
       
      		 
      spider_remote_time_zone        = +00:00
      		 
      spider_remote_access_charset   = utf8mb4
      		 
      spider_remote_autocommit       = 1
      		 
      spider_remote_default_database = 0
      		 
       
      		 
      spider_general_log          = 1
      		 
      spider_internal_sql_log_off = 0
      		 
      spider_log_result_errors    = 4

      I commented out all the above settings and tried again, the results are same.

      I've also changed the PARTITION BY HASH (id) to PARTITION BY KEY(id). And the result is exactly same, the duplicated rows still there.

      Attachments

        Activity

          People

            Kentoku Kentoku Shiba (Inactive)
            asbai Bai Yang
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.