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

          Ascending order - Click to sort in descending order
          asbai Bai Yang created issue -
          asbai Bai Yang made changes -
          Field Original Value New Value
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}

          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.4 [ 22408 ]
          Assignee Kentoku Shiba [ kentoku ]
          asbai Bai Yang made changes -
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

          G0:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 2 | DB-S/2 |
          | 4 | DB-S/4 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          asbai Bai Yang made changes -
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

          G0:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 2 | DB-S/2 |
          | 4 | DB-S/4 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

          G0:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 2 | DB-S/2 |
          | 4 | DB-S/4 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

          G1:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 1 | DB-S/1 |
          | 3 | DB-S/3 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          asbai Bai Yang made changes -
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

          G0:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 2 | DB-S/2 |
          | 4 | DB-S/4 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

          G1:
          {code:sql}
          mysql> select * from test.t1;
          +----+--------+
          | id | node |
          +----+--------+
          | 1 | DB-S/1 |
          | 3 | DB-S/3 |
          +----+--------+
          2 rows in set (0.00 sec)
          {code}

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          asbai Bai Yang made changes -
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          asbai Bai Yang made changes -
          Description I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.           		I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

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

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

          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:

          {code:sql}
          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"'
          );
          {code}

          It's working well for a simple query like this:
          {code:sql}
          MariaDB [(none)]> select * from test.t1;
          +-----+--------+
          | id | node |
          +-----+--------+
          | 1 | DB-G0 |
          | 2 | DB-G1 |
          +-----+--------+
          2 rows in set (0.013 sec)
          {code}

          But it doubled the results when using this condition:
          {code:sql}
          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)
          {code}


          Here is my Spider related settings:
          {code:ini}
          #
          # * 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
          {code}

          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.
          ralf.gebhardt Ralf Gebhardt made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          Kentoku Kentoku Shiba (Inactive) made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Kentoku Kentoku Shiba (Inactive) made changes -
          Fix Version/s 10.4.15 [ 24507 ]
          Fix Version/s 10.5.6 [ 24508 ]
          Fix Version/s 10.6.0 [ 24431 ]
          Fix Version/s 10.4 [ 22408 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.5.7 [ 25019 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.5.6 [ 24508 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.4.16 [ 25020 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.4.15 [ 24507 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 107139 ] MariaDB v4 [ 157612 ]

          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.