MySQL Bugs: #19921: Got Duplicate entry when updates a primary key with unique values

Bug #19921	Got Duplicate entry when updates a primary key with unique values
Submitted:	18 May 2006 22:53	Modified:	19 May 2006 5:07
Reporter:	Jerry Zheng	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.18, 4.0.26	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
It seems that MYISAM really do not have any transaction concept even for a single statement. The following example shows that MYISAM check the unique contraints for every row change on fly instead of do it at the end of the statement when I try to shifting the primary key (or any unique key) value using update statement.

Not sure if this is can be addressed or it is too deep down into the MYISAM storage system.

How to repeat:
mysql> create table a (i int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> create table b (i int primary key, j int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into b values (1,4), (2,3), (3,7);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  update a, b set a.i=b.j where a.i=b.i;
ERROR 1062: Duplicate entry '3' for key 1

Suggested fix:
Let the system check constraints at end of ecah statement. Allows in statement inconsistency.

Even worse, The statement changes some rows and stop in the middle.

Here is a new example:

mysql> create table a (i int primary key, init_i int);

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into a values (1,1),(2,2),(3,3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql> create table b (i int primary key, j int);

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into b values (1,4), (2,3), (3,7);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql>  update a, b set a.i=b.j where a.i=b.i;

ERROR 1062: Duplicate entry '3' for key 1

 

 

The worst is that some of the records were changed already:

 

mysql> select * from a;

+---+--------+

| i | init_i |

+---+--------+

| 4 |      1 | <--- changed

| 2 |      2 |

| 3 |      3 |

+---+--------+

3 rows in set (0.00 sec)

Thank you for a problem report. The behaviour you described is a deviation from standard, but known. MySQL in general check constraints row by row (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html):

"Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY  constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking."

For InnoDB this will be fixed some day. Not sure about MyISAM. As you have no information to "rollback" changes, I see no easy way to implement proper constraints handling in it.