| Bug #5573 | Wrong/undocumented behaviour on UNIQUE column mass update | ||
|---|---|---|---|
| Submitted: | 14 Sep 2004 16:58 | Modified: | 8 Apr 2008 16:28 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[14 Sep 2004 17:49]
Igor Mendelev
This is an absolutely unacceptable behavior for InnoDB!
[17 Sep 2004 12:18]
Heikki Tuuri
Hi! I have now updated the online manual: ------------------------------------------------- manual.texi: Add a note that InnoDB and MySQL check UNIQUE and FOREIGN KEY constraints row-by-row, even in SQL statements that modify several rows; this differs from SQL st andards, where the check should only be AFTER the SQL statement has been wholly processed ------------------------------------------------- and I am changing the status of this bug report to a feature request. Thank you, Heikki
[8 Apr 2008 16:28]
MySQL Verification Team
Thank you for the bug report.
[3 Oct 2008 16:19]
Konstantin Osipov
This is actually a storage engine bug, not MySQL bug.
[16 Oct 2008 4:31]
Valeriy Kravchuk
Bug #35856 was marked as a duplicate of this one.
[7 May 2012 9:32]
Emiliano Heyns
How can this be a feature request and not a fatal bug? I've just tripped over this. This bug is 8 years old!
Description: If we have a modifying statement affecting several rows MySQL seems to check CONSTRAINTs per every involved row as single unit and not per all involved rows as set. So the last update below gives: ERROR 23000: Duplicate entry '2' for key 1 MySQL seems to treat the UPDATE as a set of of atomic one row update units. The SQL standard and also the NIST test suite expect, that this update is successful, because the UNIQUE constraint is satisfied before and after the statement. They treat the complete statement as atomic unit. BTW: The transactional storage engines InnoDB and BDB show the same strange behaviour. My environment: - Intel PC with Linux(SuSE 9.0) - MySQL 4.1 compiled from source last ChangeSet@1.1996.1.2, 2004-09-12 How to repeat: use test ; --disable_warnings DROP TABLE IF EXISTS T1; --enable_warnings eval CREATE TABLE T1 (F1 DECIMAL(4) NOT NULL, F2 CHAR(8), UNIQUE (F1)) ; INSERT INTO T1 VALUES (1,'CCA'); INSERT INTO T1 VALUES (2,'CCB'); UPDATE T1 SET F1 = F1 + 1; Suggested fix: Please implement a behaviour according to the SQL standard at least for InnoDB or document the current MySQL behaviour within the manual 1.8.5 MySQL Differences from Standard SQL.