Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.4
-
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) |