| Bug #9680 | wrong error from cascading update | ||
|---|---|---|---|
| Submitted: | 6 Apr 2005 16:49 | Modified: | 14 Feb 2006 3:28 |
| Reporter: | Arnout Vreugdenhil | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S3 (Non-critical) |
| Version: | 4.* | OS: | Linux (redhat) |
| Assigned to: | Bugs System | Target Version: | |
[6 Apr 2005 16:59]
Heikki Tuuri
Hi!
Looks like the duplicate key error in test3 is somehow reported for table test2! When I
dropped test3, the update succeeded.
I will look at this later.
Regards,
Heikki
heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE test0 (
-> field1 varchar(8) NOT NULL default '',
-> PRIMARY KEY (field1),
-> UNIQUE KEY field1 (field1)
-> ) TYPE=InnoDB;
NTO test0 VALUES ('old');
INSERT INTO test0 VALUES ('other');
INSERT INTO test1 VALUES ('old','somevalu');
INSERT INTO test1 VALUES ('other','anyvalue');
INSERT INTO test2 VALUES ('old','somevalu');
INSERT INTO test2 VALUES ('other','anyvalue');
INSERT INTO test3 VALUES ('old');
INSERT INTO test3 VALUES ('other');
update test1 set field1 = 'other' where field2 = 'somevalu';
-- ERROR 1062: Duplicate entry 'other-somevalu' for key 1
-- there shouldn't have been any problems until table test3
-- instead it says other-somevalu already exists, which doesn't.
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql>
mysql> CREATE TABLE test1 (
-> field1 varchar(8) NOT NULL default '',
-> field2 varchar(8) NOT NULL default '',
-> PRIMARY KEY (field1,field2),
-> KEY field1 (field1,field2),
-> KEY field1_2 (field1),
-> CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test0`
-> (`field1`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE TABLE test2 (
-> field1 varchar(8) NOT NULL default '',
-> field2 varchar(8) NOT NULL default '',
-> UNIQUE KEY uniek (field1,field2),
-> KEY field1 (field1,field2),
-> KEY field1_2 (field1),
-> CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`field1`, `field2`) REFERENCES `
test1`
-> (`field1`, `field2`) ON DELETE CASCADE ON UPDATE CASCADE
-> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE TABLE test3 (
-> field1 varchar(8) NOT NULL default '',
-> PRIMARY KEY (field1),
-> UNIQUE KEY field1 (field1),
-> CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test2` (`f
ield1`)
-> ON DELETE CASCADE ON UPDATE CASCADE
-> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> INSERT INTO test0 VALUES ('old');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO test0 VALUES ('other');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql>
mysql> INSERT INTO test1 VALUES ('old','somevalu');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO test1 VALUES ('other','anyvalue');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> INSERT INTO test2 VALUES ('old','somevalu');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO test2 VALUES ('other','anyvalue');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql>
mysql> INSERT INTO test3 VALUES ('old');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO test3 VALUES ('other');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> update test1 set field1 = 'other' where field2 = 'somevalu';
ERROR 1062 (23000): Duplicate entry 'other-somevalu' for key 1
mysql>
mysql> -- ERROR 1062: Duplicate entry 'other-somevalu' for key 1
mysql> -- there shouldn't have been any problems until table test3
mysql> -- instead it says other-somevalu already exists, which doesn't.
mysql>
mysql> drop table test3;
Query OK, 0 rows affected (0.01 sec)
mysql> update test1 set field1 = 'other' where field2 = 'somevalu';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql>
[4 Jan 2006 17:30]
Heikki Tuuri
Assigning this to Osku.
[16 Jan 2006 15:56]
Osku Salerma
I have analyzed this somewhat. From what I can tell, InnoDB is behaving correctly, it's detecting the cascading failure leading to a duplicate key in the test3 table. The error message is generated entirely in MySQL code, and as far as I can see (and some comments seem to imply it), MySQL, when doing an update, updates all fields, not just the changed ones. Thus, when we return an error code from InnoDB for "DUPLICATE KEY", MySQL does not know which parts of the key were changed and thus reports the whole key. None of the above would matter if InnoDB returned a foreign key error for cascaded failures (then we could construct a better error message on our own (though I don't know if we could get an ASCII representation of the data as easily as MySQL does)), but I'm sure there's a reason they were implemented as duplicate key errors, and changing that would be a huge project.
[17 Jan 2006 11:54]
Heikki Tuuri
Hmm... we would need a new error code in MySQL: 'cascading duplicate key error'.
[19 Jan 2006 15:09]
Osku Salerma
Patch for bug
Attachment: 9680.patch (text/x-patch), 8.39 KiB.
[19 Jan 2006 15:10]
Osku Salerma
MySQL: Please review my attached patch that fixes this issue, and if ok, push to 5.1 and notify us.
[9 Feb 2006 17:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/2378
[9 Feb 2006 22:14]
Heikki Tuuri
Alex, please specify the 5.1 version where the patch was pushed, and put the bug report to the state 'Documenting'. Regards, Heikki
[10 Feb 2006 7:12]
Alexander Ivanov
Pushed to 5.1.7-beta, 5.2.0-alpha
[14 Feb 2006 3:28]
Paul DuBois
Noted in 5.1.7 changelog:
<para>
<literal>InnoDB</literal> could display an incorrect error
message for a cascading update. (Bug #9680)
</para>
[17 Jul 2006 0:41]
Adil Ahmad
Can we apply this patch to MySQL 5.0.19?
Description: The following example returns a weird error. It is a chain for foreign key connected tables named test0 to test3. Test3 should give an error: ERROR 1062: Duplicate entry 'other' for key 1 Instead one of the first tables returns this: ERROR 1062: Duplicate entry 'other-somevalu' for key 1 Fixing the expected error is hard enough. When it's the wrong error you're looking at it's even harder. ;) How to repeat: CREATE TABLE test0 ( field1 varchar(8) NOT NULL default '', PRIMARY KEY (field1), UNIQUE KEY field1 (field1) ) TYPE=InnoDB; CREATE TABLE test1 ( field1 varchar(8) NOT NULL default '', field2 varchar(8) NOT NULL default '', PRIMARY KEY (field1,field2), KEY field1 (field1,field2), KEY field1_2 (field1), CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test0` (`field1`) ) TYPE=InnoDB; CREATE TABLE test2 ( field1 varchar(8) NOT NULL default '', field2 varchar(8) NOT NULL default '', UNIQUE KEY uniek (field1,field2), KEY field1 (field1,field2), KEY field1_2 (field1), CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`field1`, `field2`) REFERENCES `test1` (`field1`, `field2`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; CREATE TABLE test3 ( field1 varchar(8) NOT NULL default '', PRIMARY KEY (field1), UNIQUE KEY field1 (field1), CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test2` (`field1`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; INSERT INTO test0 VALUES ('old'); INSERT INTO test0 VALUES ('other'); INSERT INTO test1 VALUES ('old','somevalu'); INSERT INTO test1 VALUES ('other','anyvalue'); INSERT INTO test2 VALUES ('old','somevalu'); INSERT INTO test2 VALUES ('other','anyvalue'); INSERT INTO test3 VALUES ('old'); INSERT INTO test3 VALUES ('other'); update test1 set field1 = 'other' where field2 = 'somevalu'; -- ERROR 1062: Duplicate entry 'other-somevalu' for key 1 -- there shouldn't have been any problems until table test3 -- instead it says other-somevalu already exists, which doesn't.