[MDEV-30379] Direct INSERT ON DUP KEY UPDATE causes misplaced records among partitions Created: 2023-01-11  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.6.4
Fix Version/s: 10.6, 10.11

Type: Bug Priority: Major
Reporter: Daniel YE Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When `spider_direct_dup_insert=1`, INSERT ... ON DUPLICATE KEY UPDATE queries are sent to data nodes directly to improve performance. Yet when operating on a Spider table with partitioning, the scenario where the partition key is changed is not taken account into by this mechanism. When a record's partition key is changed to a value that belongs to another partition, sending ON DUPLICATE KEY UPDATE directly results in misplacing the record in a partition it does not belong.

How to reproduce (tested on MariaDB 10.6.4):

MariaDB [mytest]> show create table test_table\G
 
*************************** 1. row ***************************
 
       Table: test_table
 
Create Table: CREATE TABLE `test_table` (
 
  `id` int(11) NOT NULL,
 
  `t` text DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `k_t` (`t`(5))
 
) ENGINE=SPIDER DEFAULT CHARSET=latin1
 
 PARTITION BY LIST (`id` MOD 2)
 
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "mytest_2", table "test_table", server "SPT2"' ENGINE = SPIDER,
 
 PARTITION `pt1` VALUES IN (1) COMMENT = 'database "mytest_3", table "test_table", server "SPT3"' ENGINE = SPIDER)
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mytest]> truncate table test_table;
 
Query OK, 0 rows affected (0.082 sec)
 
 
 
MariaDB [mytest]> insert into test_table(id) values(1);
 
Query OK, 1 row affected (0.003 sec)
 
 
 
MariaDB [mytest]> set spider_direct_dup_insert=1;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [mytest]> insert into test_table(id) values(1) on duplicate key update id=2;
 
Query OK, 1 row affected (0.003 sec)
 
 
 
MariaDB [mytest]> select * from test_table;
 
+----+------+
 
| id | t    |
 
+----+------+
 
|  2 | NULL |
 
+----+------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [mytest]> select * from test_table where id=2;
 
Empty set (0.001 sec)



 Comments   
Comment by Yuchen Pei [ 2023-02-07 ]

mtr testcase

storage/spider/mysql-test/spider/bugfix/t/mdev_30379.test

 
--echo #
 
--echo # MDEV-30379 Direct INSERT ON DUP KEY UPDATE causes misplaced records among partitions
 
--echo #
 
 
 
--disable_query_log
 
--disable_result_log
 
--source ../../t/test_init.inc
 
--enable_result_log
 
--enable_query_log
 
 
 
--connection child2_1
 
CREATE DATABASE auto_test_remote;
 
USE auto_test_remote;
 
eval CREATE TABLE tbl_a (
 
    a INT, PRIMARY KEY (a)
 
) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
 
 
 
--connection child2_2
 
CREATE DATABASE auto_test_remote2;
 
USE auto_test_remote2;
 
eval CREATE TABLE tbl_a (
 
    a INT, PRIMARY KEY (a)
 
) $CHILD2_2_ENGINE $CHILD2_2_CHARSET;
 
 
 
--connection master_1
 
CREATE DATABASE auto_test_local;
 
USE auto_test_local;
 
eval CREATE TABLE tbl_a (
 
    a INT, PRIMARY KEY (a)
 
) $MASTER_1_ENGINE
 
   PARTITION BY LIST (a MOD 2)
 
  (PARTITION pt0 VALUES IN (0) COMMENT = 'table "tbl_a", server "s_2_1"' ENGINE = SPIDER,
 
  PARTITION pt1 VALUES IN (1) COMMENT = 'table "tbl_a", server "s_2_2"' ENGINE = SPIDER);
 
 
 
insert into tbl_a values(1);
 
set spider_direct_dup_insert=1;
 
insert into tbl_a values(1) on duplicate key update a=2;
 
select * from tbl_a;
 
select * from tbl_a where a = 2;
 
 
 
--connection master_1
 
DROP DATABASE IF EXISTS auto_test_local;
 
 
 
--connection child2_1
 
DROP DATABASE IF EXISTS auto_test_remote;
 
 
 
--connection child2_2
 
DROP DATABASE IF EXISTS auto_test_remote2;
 
 
 
--disable_query_log
 
--disable_result_log
 
--source ../t/test_deinit.inc
 
--enable_query_log
 
--enable_result_log

storage/spider/mysql-test/spider/bugfix/t/mdev_30379.cnf

 
!include include/default_mysqld.cnf
 
!include ../my_1_1.cnf
 
!include ../my_2_1.cnf
 
!include ../my_2_2.cnf

storage/spider/mysql-test/spider/bugfix/r/mdev_30379.result

 
#
 
# MDEV-30379 Direct INSERT ON DUP KEY UPDATE causes misplaced records among partitions
 
#
 
for master_1
 
for child2
 
child2_1
 
child2_2
 
child2_3
 
for child3
 
connection child2_1;
 
CREATE DATABASE auto_test_remote;
 
USE auto_test_remote;
 
CREATE TABLE tbl_a (
 
a INT, PRIMARY KEY (a)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
connection child2_2;
 
CREATE DATABASE auto_test_remote2;
 
USE auto_test_remote2;
 
CREATE TABLE tbl_a (
 
a INT, PRIMARY KEY (a)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
connection master_1;
 
CREATE DATABASE auto_test_local;
 
USE auto_test_local;
 
CREATE TABLE tbl_a (
 
a INT, PRIMARY KEY (a)
 
) ENGINE=Spider
 
PARTITION BY LIST (a MOD 2)
 
(PARTITION pt0 VALUES IN (0) COMMENT = 'table "tbl_a", server "s_2_1"' ENGINE = SPIDER,
 
PARTITION pt1 VALUES IN (1) COMMENT = 'table "tbl_a", server "s_2_2"' ENGINE = SPIDER);
 
insert into tbl_a values(1);
 
set spider_direct_dup_insert=1;
 
insert into tbl_a values(1) on duplicate key update a=2;
 
select * from tbl_a;
 
a
 
2
 
select * from tbl_a where a = 2;
 
a
 
2
 
connection master_1;
 
DROP DATABASE IF EXISTS auto_test_local;
 
connection child2_1;
 
DROP DATABASE IF EXISTS auto_test_remote;
 
connection child2_2;
 
DROP DATABASE IF EXISTS auto_test_remote2;
 
for master_1
 
for child2
 
child2_1
 
child2_2
 
child2_3
 
for child3

Generated at Thu Feb 08 10:15:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.