diff options
author | Jan Lindström <jan.lindstrom@mariadb.com> | 2018-02-05 18:21:28 +0200 |
---|---|---|
committer | Jan Lindström <jan.lindstrom@mariadb.com> | 2018-02-05 18:21:28 +0200 |
commit | 60f51af755ea9d07c20a596ba21de184816fa265 (patch) | |
tree | 419e3b30ded268e1d2ad3f9ef095501ec68e587e | |
parent | 9390ff53fc39e34976cf051ce33649f9316bb8e6 (diff) | |
download | mariadb-git-60f51af755ea9d07c20a596ba21de184816fa265.tar.gz |
MDEV-15042: INSERT ON DUPLICATE KEY UPDATE produces error 1032 (Can't find record)bb-10.2-MDEV-15042
Problem was that wrong error message was returned when insert
returned FK-error and there was no duplicate key to process.
row_ins
If error from insert was DB_NO_REFERENCED_ROW and there was
no duplicate key we should ignore ON DUPLICATE KEY UPDATE
and return original error message.
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-on-duplicate-update.result | 17 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-on-duplicate-update.test | 23 | ||||
-rw-r--r-- | storage/innobase/row/row0ins.cc | 14 |
3 files changed, 53 insertions, 1 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result index 474ebf33bbd..f1068e40f72 100644 --- a/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result +++ b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result @@ -58,3 +58,20 @@ SELECT * FROM t2; i vi m 1 1 3 DROP TABLE t2, t1; +CREATE TABLE parent ( +id INT PRIMARY KEY AUTO_INCREMENT +) ENGINE=INNODB; +CREATE TABLE child ( +parent_id INT NOT NULL PRIMARY KEY, +id INT NOT NULL, +CONSTRAINT fk_c_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=INNODB; +INSERT INTO child (id, parent_id) VALUES (1, 1); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_c_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) +INSERT INTO child (id, parent_id) VALUES (1, 1) ON DUPLICATE KEY UPDATE id = VALUES(id); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_c_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) +select * from parent; +id +select * from child; +parent_id id +drop table child, parent; diff --git a/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test index cc80198d24a..9604ad39c48 100644 --- a/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test +++ b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test @@ -61,3 +61,26 @@ INSERT into t2 VALUES (1, 1, 100); INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; SELECT * FROM t2; DROP TABLE t2, t1; + +# +# MDEV-15042: INSERT ON DUPLICATE KEY UPDATE produces error 1032 (Can't find record) +# +CREATE TABLE parent ( + id INT PRIMARY KEY AUTO_INCREMENT +) ENGINE=INNODB; + +CREATE TABLE child ( + parent_id INT NOT NULL PRIMARY KEY, + id INT NOT NULL, + CONSTRAINT fk_c_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=INNODB; + +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO child (id, parent_id) VALUES (1, 1); + +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO child (id, parent_id) VALUES (1, 1) ON DUPLICATE KEY UPDATE id = VALUES(id); + +select * from parent; +select * from child; +drop table child, parent; diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index fc6f932db81..1f4e057a202 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -3696,12 +3696,24 @@ row_ins( placing all gaplocks. */ err = DB_DUPLICATE_KEY; break; - } else if (!node->duplicate) { + } else if (err == DB_DUPLICATE_KEY && + !node->duplicate) { /* Save 1st dup error. Ignore subsequent dup errors. */ node->duplicate = node->index; thr_get_trx(thr)->error_state = DB_DUPLICATE_KEY; + } else if (err == DB_NO_REFERENCED_ROW) { + /* As a example consider + case INSERT INTO child (id) + VALUES (1) ON DUPLICATE + KEY UPDATE id = + VALUES(id); + where (1) does not cause + duplicate key. Thus + we should return original + DB_NO_REFERENCED_ROW */ + DBUG_RETURN(err); } break; } |