MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key failbb-10.2-MDEV-13206

This is caused by following change:

commit 95d29c99f01882ffcc2259f62b3163f9b0e80c75
Author: Marko Mäkelä <marko.makela@oracle.com>
Date:   Tue Nov 27 11:12:13 2012 +0200

    Bug#15920445 INNODB REPORTS ER_DUP_KEY BEFORE CREATE UNIQUE INDEX COMPLETED

    There is a phase during online secondary index creation where the index has
    been internally completed inside InnoDB, but does not 'officially' exist yet.
    We used to report ER_DUP_KEY in these situations, like this:

    ERROR 23000: Can't write; duplicate key in table 't1'

    What we should do is to let the 'offending' operation complete, but report an
    error to the
    ALTER TABLE t1 ADD UNIQUE KEY (c2):

    ERROR HY000: Index c2 is corrupted
    (This misleading error message should be fixed separately:
    Bug#15920713 CREATE UNIQUE INDEX REPORTS ER_INDEX_CORRUPT INSTEAD OF DUPLICATE)

    row_ins_sec_index_entry_low(): flag the index corrupted instead of
    reporting a duplicate, in case the index has not been published yet.

    rb:1614 approved by Jimmy Yang

Problem is that after we have found duplicate key on primary key
we continue to get necessary gap locks in secondary indexes to
block concurrent transactions from inserting the searched records.
However, search from unique index used in foreign key constraint
could return DB_NO_REFERENCED_ROW if INSERT .. ON DUPLICATE KEY UPDATE
does not contain value for foreign key column. In this case
we should return the original DB_DUPLICATE_KEY error instead
of DB_NO_REFERENCED_ROW.

Imported also missing test case from MySQL 5.7 for

commit 25781c154396dbbc21023786aa3be070057d6999
Author: Annamalai Gurusami <annamalai.gurusami@oracle.com>
Date:   Mon Feb 24 14:00:03 2014 +0530

    Bug #17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX

    Problem:

    When INSERT ... ON DUPLICATE UPDATE or REPLACE statements are used, then
    after encountering a DB_DUPLICATE_KEY error, we continue to process all
    the unique secondary indexes to place the necessary gap locks.  The
    problem is in the following scenario:

    1. The table has one primary index, one unique secondary index and
       one non-unique secondary index.
    2. The INSERT ... ON DUPLICATE UPDATE ... is executed on the table.
    3. Insert into the clustered index reported DB_DUPLICATE_KEY.  This
       error information is saved.  We proceed to take gap locks in all
       unique secondary indexes.
    4. Insert into the unique secondary index reported DB_LOCK_WAIT.
    5. Step 4 is repeated from a higher layer row_ins().  When this is
       done, the earlier error information saved in step 3 is lost.
    6. Next instead of taking just gap locks or skipping non-unique
       secondary indexes, because of loss of information regarding the
       error already saved, an actual insert is performed on the non-unique
       secondary index.  This triggers the assert.

    Solution:

    Save the error information in a non-local location so that it is not lost.

    rb#4723 approved by Kevin.

5 files changed, 170 insertions, 0 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
new file mode 100644
index 00000000000..474ebf33bbd
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result
@@ -0,0 +1,60 @@
+set sql_mode='';
+set innodb_strict_mode=0;
+CREATE TABLE `v` (
+`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+INSERT v values (1);
+CREATE TABLE `vp` (
+`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
+`v_id` int(10) unsigned NOT NULL,
+`p_id` int(10) unsigned NOT NULL,
+`ppp` varchar(255) NOT NULL,
+PRIMARY KEY (`id`),
+UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
+KEY `FK_vp_v` (`v_id`),
+CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+INSERT vp VALUES (12, 1, 100, 'text12');
+INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);
+Warnings:
+Warning	1364	Field 'v_id' doesn't have a default value
+Warning	1364	Field 'p_id' doesn't have a default value
+SELECT * FROM vp;
+id	v_id	p_id	ppp
+12	1	100	test12-2
+DROP TABLE vp, v;
+CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;
+INSERT into t1 values (1);
+CREATE TABLE t2 (
+i int not null primary key,
+vi int not null,
+m int,
+UNIQUE KEY (vi),
+CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT into t2 VALUES (1, 1, 100);
+INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;
+Warnings:
+Warning	1364	Field 'vi' doesn't have a default value
+SELECT * FROM t2;
+i	vi	m
+1	1	3
+DROP TABLE t2,t1;
+CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;
+INSERT into t1 values (1);
+CREATE TABLE t2 (
+i int not null primary key,
+vi int not null,
+m int,
+KEY (vi),
+CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT into t2 VALUES (1, 1, 100);
+INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;
+Warnings:
+Warning	1364	Field 'vi' doesn't have a default value
+SELECT * FROM t2;
+i	vi	m
+1	1	3
+DROP TABLE t2, t1;
diff --git a/mysql-test/suite/innodb/r/innodb-replace-debug.result b/mysql-test/suite/innodb/r/innodb-replace-debug.result
new file mode 100644
index 00000000000..e2ea686c48a
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-replace-debug.result
@@ -0,0 +1,11 @@
+#
+# Bug#17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX
+#
+create table t1 (f1 int primary key, f2 int, f3 int, unique key k1(f2),
+key k2(f3)) engine=innodb;
+insert into t1 values (14, 24, 34);
+set debug = '+d,row_ins_sec_index_entry_timeout';
+Warnings:
+Warning	1287	'@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead
+replace into t1 values (14, 25, 34);
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test
new file mode 100644
index 00000000000..f5cc5f43364
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test
@@ -0,0 +1,64 @@
+--source include/have_innodb.inc
+
+#
+# MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail
+#
+set sql_mode='';
+set innodb_strict_mode=0;
+
+CREATE TABLE `v` (
+  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+ 
+INSERT v values (1);
+ 
+CREATE TABLE `vp` (
+  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
+  `v_id` int(10) unsigned NOT NULL,
+  `p_id` int(10) unsigned NOT NULL,
+  `ppp` varchar(255) NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
+  KEY `FK_vp_v` (`v_id`),
+  CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+ 
+INSERT vp VALUES (12, 1, 100, 'text12');
+INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);
+SELECT * FROM vp;
+DROP TABLE vp, v;
+
+CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;
+INSERT into t1 values (1);
+ 
+CREATE TABLE t2 (
+  i int not null primary key,
+  vi int not null,
+  m int,
+  UNIQUE KEY (vi),
+  CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+ 
+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;
+
+CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;
+INSERT into t1 values (1);
+ 
+CREATE TABLE t2 (
+  i int not null primary key,
+  vi int not null,
+  m int,
+  KEY (vi),
+  CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+ 
+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;
diff --git a/mysql-test/suite/innodb/t/innodb-replace-debug.test b/mysql-test/suite/innodb/t/innodb-replace-debug.test
new file mode 100644
index 00000000000..250c90ba5b1
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-replace-debug.test
@@ -0,0 +1,13 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+
+--echo #
+--echo # Bug#17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX
+--echo #
+
+create table t1 (f1 int primary key, f2 int, f3 int, unique key k1(f2),
+  key k2(f3)) engine=innodb;
+insert into t1 values (14, 24, 34);
+set debug = '+d,row_ins_sec_index_entry_timeout';
+replace into t1 values (14, 25, 34);
+drop table t1;
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index a4c395610f5..d82d828b47d 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -3633,6 +3633,28 @@ row_ins(
 					}
 					break;
 				}
+
+				DBUG_RETURN(err);
+			case DB_NO_REFERENCED_ROW:
+				/* When we are in REPLACE statement or
+				INSERT .. ON DUPLICATE UPDATE statement,
+				we should process all the unique secondary
+				indexes, even after we encourter a duplicate
+				error. Now foreign key constraint could also
+				use unique secondary index and if INSERT
+				statement does not provide value for
+				foreign key column we could encourter
+				DB_NO_REFERENCED_ROW error. Therefore,
+				if we have saved dup error we should return
+				that error code not DB_NO_REFERENCED_ROW
+				to process ON DUPLICATE UPDATE. */
+				if (thr_get_trx(thr)->isolation_level
+				    >= TRX_ISO_REPEATABLE_READ
+				    && thr_get_trx(thr)->duplicates
+				    && node->duplicate) {
+					ut_ad(dict_index_is_unique(node->index));
+					err = DB_DUPLICATE_KEY;
+				}
 				// fall through
 			default:
 				DBUG_RETURN(err);