[MDEV-24003] Multi-range Read fails on Spider Created: 2020-10-21  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Yongxin Xu Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None
Environment:


 Description   

How to repeat

On backends:

bk.sql

 
CREATE TABLE `tbl` (
 
  `id` int(11) NOT NULL,
 
  `flag` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
)ENGINE=INNODB;

On Spider

spider.sql

 
CREATE TABLE `tbl` (
 
  `id` int(11) NOT NULL,
 
  `flag` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=SPIDER DEFAULT CHARSET=utf8
 
PARTITION BY LIST (id % 2)
 
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "test", table "tbl", server "bk0"' ENGINE = SPIDER,
 
 PARTITION `pt1` VALUES IN (1) COMMENT = 'database "test", table "tbl", server "bk1"' ENGINE = SPIDER);
 
 
 
CREATE TABLE `m` (
 
  `id` int(11) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tbl` VALUES (101624109, 1);
 
INSERT INTO `tbl` VALUES (91272871, 1);
 
INSERT INTO `tbl` VALUES (94933594, 1);
 
INSERT INTO `tbl` VALUES (98646655, 1);
 
INSERT INTO `tbl` VALUES (13914947, 0);
 
INSERT INTO `tbl` VALUES (45051390, 0);
 
INSERT INTO `tbl` VALUES (30864034, 1);
 
INSERT INTO `tbl` VALUES (33671239, 0);
 
INSERT INTO `tbl` VALUES (39109063, 1);
 
INSERT INTO `tbl` VALUES (91137966, 1);
 
INSERT INTO `tbl` VALUES (95897689, 0);
 
INSERT INTO `tbl` VALUES (571307512, 1);
 
INSERT INTO `tbl` VALUES (35706155, 1);
 
INSERT INTO `tbl` VALUES (34044708, 0);

And then, excute

insert_select.sql

 
insert into `m`(id) select id from tbl where id not in (94933594) and flag = 1;

on spider, an error occurs

ERROR 1062 (23000): Duplicate entry '30864034' for key 'PRIMARY'

I traced the source code, it seems that as long as Spider uses the MRR feature, it will get twice as much data as the correct result (correct data and its replica).

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-13 ]

The bug reproduced on 10.3, but not on 10.2, 10.4, 10.5, and 10.6 The bug itself is problematic but it only affects 10.3. So, I keep the priority "major". cc: yongxinxu

Comment by Yongxin Xu [ 2021-08-13 ]

Ok, the BKA algorithm also uses MRR quick plan. In 10.3, joins with the BKA algorithm are fine. But when we tried to fix this problem, it would result in the wrong result of the BKA join, so we fixed the problem by simply prohibiting MRR from using.
If this is not reproducible on other versions, I think it is okay and not urgent.

Comment by Roel Van de Paar [ 2023-06-03 ]

Issue confirmed in 11.1:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER spider@localhost IDENTIFIED BY 'pwd';
 
GRANT ALL ON test.* TO spider@localhost;
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "../socket.sock",DATABASE 'test',USER 'spider',PASSWORD 'pwd');
 
CREATE TABLE t (id INT(11) NOT NULL,flag INT(11) DEFAULT NULL,PRIMARY KEY(id)) ENGINE=InnoDB;
 
CREATE TABLE ts (id INT(11) NOT NULL,flag INT(11) DEFAULT NULL,PRIMARY KEY(id)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"' DEFAULT CHARSET=utf8 PARTITION BY LIST (id %2) (PARTITION pt0 VALUES IN (0) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"', PARTITION pt1 VALUES IN (1) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"');
 
CREATE TABLE m (id INT(11) NOT NULL,PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO ts VALUES (101624109, 1);
 
INSERT INTO ts VALUES (91272871, 1);
 
INSERT INTO ts VALUES (94933594, 1);
 
INSERT INTO ts VALUES (98646655, 1);
 
INSERT INTO ts VALUES (13914947, 0);
 
INSERT INTO ts VALUES (45051390, 0);
 
INSERT INTO ts VALUES (30864034, 1);
 
INSERT INTO ts VALUES (33671239, 0);
 
INSERT INTO ts VALUES (39109063, 1);
 
INSERT INTO ts VALUES (91137966, 1);
 
INSERT INTO ts VALUES (95897689, 0);
 
INSERT INTO ts VALUES (571307512, 1);
 
INSERT INTO ts VALUES (35706155, 1);
 
INSERT INTO ts VALUES (34044708, 0);
 
INSERT INTO m (id) SELECT id FROM ts WHERE id NOT IN (94933594) AND flag=1;

Leads to:

11.1.0 4e5b771e980edfdad5c5414aa62c81d409d585a4 (Debug)

 
11.1.0-dbg>INSERT INTO m (id) SELECT id FROM ts WHERE id NOT IN (94933594) AND flag=1;
 
ERROR 1062 (23000): Duplicate entry '30864034' for key 'PRIMARY'

Comment by Roel Van de Paar [ 2023-06-03 ]

Issue present in 10.4+ (as tested), and possibly earlier versions.
Previous not-* version tags were incorrect.

Comment by Roel Van de Paar [ 2023-06-03 ]

MTR Testcase

--source include/have_innodb.inc
 
--source include/have_partition.inc
 
--let $SOCKET= `SELECT @@global.socket`
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER spider@localhost IDENTIFIED BY 'pwd';
 
GRANT ALL ON test.* TO spider@localhost;
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET",DATABASE 'test',USER 'spider',PASSWORD 'pwd');
 
CREATE TABLE t (id INT(11) NOT NULL,flag INT(11) DEFAULT NULL,PRIMARY KEY(id)) ENGINE=InnoDB;
 
CREATE TABLE ts (id INT(11) NOT NULL,flag INT(11) DEFAULT NULL,PRIMARY KEY(id)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"' DEFAULT CHARSET=utf8 PARTITION BY LIST (id %2) (PARTITION pt0 VALUES IN (0) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"', PARTITION pt1 VALUES IN (1) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"');
 
CREATE TABLE m (id INT(11) NOT NULL,PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO ts VALUES (101624109, 1);
 
INSERT INTO ts VALUES (91272871, 1);
 
INSERT INTO ts VALUES (94933594, 1);
 
INSERT INTO ts VALUES (98646655, 1);
 
INSERT INTO ts VALUES (13914947, 0);
 
INSERT INTO ts VALUES (45051390, 0);
 
INSERT INTO ts VALUES (30864034, 1);
 
INSERT INTO ts VALUES (33671239, 0);
 
INSERT INTO ts VALUES (39109063, 1);
 
INSERT INTO ts VALUES (91137966, 1);
 
INSERT INTO ts VALUES (95897689, 0);
 
INSERT INTO ts VALUES (571307512, 1);
 
INSERT INTO ts VALUES (35706155, 1);
 
INSERT INTO ts VALUES (34044708, 0);
 
INSERT INTO m (id) SELECT id FROM ts WHERE id NOT IN (94933594) AND flag=1;

Generated at Thu Feb 08 09:26:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.