Bug #15236 | Partitions: crash, if Insert .. on duplicate key causes update of existing row | ||
---|---|---|---|
Submitted: | 24 Nov 2005 20:47 | Modified: | 9 Mar 2006 15:42 |
Reporter: | Peter Gulutzan | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
Version: | 5.1.2-alpha-debug | OS: | Linux (SUSE 10.0) |
Assigned to: | Bugs System | CPU Architecture: | Any |
[24 Nov 2005 20:47]
Peter Gulutzan
[24 Nov 2005 20:54]
MySQL Verification Team
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.1.2-alpha-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table tak (s1 int, primary key (s1)) partition by list (s1) -> (partition p1 values in (1)); Query OK, 0 rows affected (0.01 sec) mysql> insert into tak values (1); Query OK, 1 row affected (0.01 sec) mysql> insert into tak values (1) on duplicate key update s1 = 2; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql>
[18 Jan 2006 19:04]
Matthias Leich
Other example: create table tak (s1 int, s2 int, primary key (s1,s2)) partition by hash (s2); insert into tak values (1,1); insert into tak values (1,1) on duplicate key update s1 = 3; # The equivalent update tak set s1 = 1, s2 = 3 where s1 = 1; # is harmless !! gives also a crash. This leads me to the conclusion that neither the LIST type partitioning nor the missing partition with values in (2) play any role. I assume the conditions necessary for the occurence of the bug are: - insert .... on duplicate key statement with values equal to an already existing record "degenerates" to an update of the existing record - The update changes a column of the primary key. Note: It is not needed that this column is the first column of the PRIMARY KEY nor that this column is used within the partitioning function (at least this column is not mentioned though it may play a role in physical implementation).
[9 Mar 2006 15:42]
Sergey Vlasenko
The current 5.1-bk gives the following result on statements mentioned in bug description: mysql> create table tak (s1 int, primary key (s1)) partition by list (s1) (parti tion p1 values in (1)); Query OK, 0 rows affected (0.86 sec) mysql> insert into tak values (1); Query OK, 1 row affected (0.48 sec) mysql> insert into tak values (1) on duplicate key update s1 = 2; ERROR 1504 (HY000): Table has no partition for value 2 mysql> mysql> create table t3 (s1 int, s2 int, primary key (s1,s2)) partition by hash ( s2); Query OK, 0 rows affected (0.20 sec) mysql> insert into t3 values (1,1); Query OK, 1 row affected (0.98 sec) mysql> insert into t3 values (1,1) on duplicate key update s1 = 3; Query OK, 2 rows affected (0.15 sec) mysql> select * from t3; +----+----+ | s1 | s2 | +----+----+ | 3 | 1 | +----+----+ 1 row in set (0.87 sec)