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:
None 
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
Description:
If I have a LIST partitioned table,
and I say INSERT ... ON DUPLICATE KEY column=X,
and X isn't defined in the partition list,
crash.

How to repeat:
mysql> create table tak (s1 int, primary key (s1)) partition by list (s1) (partition p1 values in (1));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tak values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tak values (1) on duplicate key update s1 = 2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[24 Nov 2005 20:54] Miguel Solorzano
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)