Bug #35856 false unique constraint violation on multiple updates
Submitted: 5 Apr 2008 23:31 Modified: 16 Oct 2008 4:30
Reporter: Don Cohen Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any

[5 Apr 2008 23:31] Don Cohen
I'm not sure whether this complies with the sql spec - so far I don't see how to find out.  Perhaps you can tell me that too.
I define a table with a unique column:
 CREATE TABLE testtable(                                                 
     PRIMARY KEY (ID),                                                           
     ID INT(11) AUTO_INCREMENT NOT NULL,                                         
     WBS VARCHAR(150) unique not null                                            
Now insert a few rows:
insert into testtable(wbs) values ('a'),('aa');
And finally try this update:
 update testtable set wbs = concat('a', wbs);
ERROR 1062 (23000): Duplicate entry 'aa' for key 2

Does this meet the spec?  I'd have thought that I'd get a complaint only if the END RESULT of the update caused duplicate entries.  Here the end result should be 'aa' and 'aaa', which does not violate the constraint.  So I expected the update to work.

How to repeat:
do the three steps in description

Suggested fix:
First I want to know whether this is really legal sql behavior.
If not, then I hope someone there knows how to fix it.
[7 Apr 2008 3:56] Valeriy Kravchuk
This is a known and documented problem in MySQL that will be fixed eventually. Please, read 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. That is, constraints are only checked after the entire SQL statement has been processed."
[7 Apr 2008 5:09] Don Cohen
This is an odd place to document the problem with unique constraints, and for that matter even foreign key constraints.  After all, what is supposed to lead me to look at this if I think I know what a foreign key constraint means?  Is there one place where I can find other such problems so I don't have to be unpleasantly surprised more than once more?
If not I suggest that this would be a valuable addition to the documentation.
[16 Oct 2008 4:30] Valeriy Kravchuk
Duplicate of Bug #5573.