MDEV-15219 FOREIGN KEY CASCADE or SET NULL operations will not resume after lock wait

This corruption was introduced in MDEV-13331. It would have been caught
by the MySQL 5.7 test innodb.update-cascade which MariaDB was missing
until now.

row_ins_check_foreign_constraint(): Never replace err == DB_LOCK_WAIT
with other values than DB_LOCK_WAIT_TIMEOUT.

3 files changed, 517 insertions, 3 deletions

diff --git a/mysql-test/suite/innodb/r/update-cascade.result b/mysql-test/suite/innodb/r/update-cascade.result
new file mode 100644
index 00000000000..6ec7a8cae50
--- /dev/null
+++ b/mysql-test/suite/innodb/r/update-cascade.result
@@ -0,0 +1,292 @@
+#
+# Bug #18451287 REDUNDANT DELETE MARKING AFTER DB_LOCK_WAIT
+#
+create table t1 (f1 int primary key, f2 blob) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+foreign key (f2) references t1(f1) on update cascade) engine=innodb;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` blob DEFAULT NULL,
+  PRIMARY KEY (`f1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `f2` (`f2`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values (1, repeat('+', 20000));
+insert into t1 values (2, repeat('-', 20000));
+insert into t1 values (3, repeat('=', 20000));
+insert into t2 values (1, 2);
+select f1, right(f2, 20) as p2 from t1;
+f1	p2
+1	++++++++++++++++++++
+2	--------------------
+3	====================
+select f1, f2 from t2;
+f1	f2
+1	2
+connect con1,localhost,root,,test;
+start transaction;
+select f1, f2 from t2 for update;
+f1	f2
+1	2
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+update t1 set f1 = 10 where f1 = 2;
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+set debug_sync='now SIGNAL go_upd';
+connection default;
+# reap: update t1 set f1 = 10 where f1 = 2;
+select f1, right(f2, 20) as p2 from t1;
+f1	p2
+1	++++++++++++++++++++
+3	====================
+10	--------------------
+select f1, f2 from t2;
+f1	f2
+1	10
+drop table t2, t1;
+set debug_sync = reset;
+#
+# Test Scenario: Two tables t1 -> t2 are involved in update cascade.
+# If DB_LOCK_WAIT happens when t1 is being updated and FK constraints
+# are being checked in t2, then retry must happen on t1.  The update
+# cascade happens in secondary index. For secondary index testing,
+# blobs are not needed.
+#
+create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+foreign key (f2) references t1(f2) on update cascade) engine=innodb;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `k1` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `f2` (`f2`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values (1, 91);
+insert into t2 values (1, 91);
+select f1, f2 from t1;
+f1	f2
+1	91
+select f1, f2 from t2;
+f1	f2
+1	91
+connection con1;
+start transaction;
+select f1, f2 from t2 for update;
+f1	f2
+1	91
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+update t1 set f2 = 28 where f2 = 91;
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+set debug_sync='now SIGNAL go_upd';
+connection default;
+# reap: update t1 set f1 = 10 where f1 = 2;
+select f1, f2 from t1;
+f1	f2
+1	28
+select f1, f2 from t2;
+f1	f2
+1	28
+drop table t2, t1;
+set debug_sync = reset;
+#
+# Test Scenario: Three tables t1 -> t2 -> t3 are involved in update cascade.
+# If DB_LOCK_WAIT happens when t2 is being updated, then retry must happen
+# on t2.
+#
+create table t1 (f1 int primary key, f2 blob) engine=innodb;
+create table t2 (f1 int primary key, f2 blob,
+foreign key (f1) references t1(f1) on update cascade) engine=innodb;
+create table t3 (f1 int primary key, f2 blob,
+foreign key (f1) references t2(f1) on update cascade) engine=innodb;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` blob DEFAULT NULL,
+  PRIMARY KEY (`f1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f1` int(11) NOT NULL,
+  `f2` blob DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `f1` int(11) NOT NULL,
+  `f2` blob DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t2` (`f1`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values (2, repeat('-', 20000));
+insert into t2 values (2, repeat('%', 20000));
+insert into t3 values (2, repeat('+', 20000));
+select f1, right(f2, 20) as p2 from t1;
+f1	p2
+2	--------------------
+select f1, right(f2, 20) as p2 from t2;
+f1	p2
+2	%%%%%%%%%%%%%%%%%%%%
+select f1, right(f2, 20) as p2 from t3;
+f1	p2
+2	++++++++++++++++++++
+connection con1;
+start transaction;
+select f1 from t3 for update;
+f1
+2
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+update t1 set f1 = 10 where f1 = 2;
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+# The table t1 is updated.
+# In t2 delete-mark happened. Retry will happen on t2.
+# In t3 yet to be updated.
+set session transaction isolation level read uncommitted;
+start transaction;
+select f1, right(f2, 20) as p2 from t1;
+f1	p2
+10	--------------------
+select f1, right(f2, 20) as p2 from t2;
+f1	p2
+select f1, right(f2, 20) as p2 from t3;
+f1	p2
+2	++++++++++++++++++++
+commit;
+set debug_sync='now SIGNAL go_upd';
+connection default;
+# reap: update t1 set f1 = 10 where f1 = 2;
+start transaction;
+select f1, right(f2, 20) as p2 from t1;
+f1	p2
+10	--------------------
+select f1, right(f2, 20) as p2 from t2;
+f1	p2
+10	%%%%%%%%%%%%%%%%%%%%
+select f1, right(f2, 20) as p2 from t3;
+f1	p2
+10	++++++++++++++++++++
+commit;
+drop table t3, t2, t1;
+set debug_sync = reset;
+#
+# Test Scenario: Three tables t1 -> t2 -> t3 are involved in update
+# cascade. If DB_LOCK_WAIT happens when t2 is being updated, then
+# retry must happen on t2.  The update cascade is happening via
+# secondary index (hence blobs are not needed).
+#
+create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+foreign key (f2) references t1(f2) on update cascade) engine=innodb;
+create table t3 (f1 int primary key, f2 int,
+foreign key (f2) references t2(f2) on update cascade) engine=innodb;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `k1` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `f2` (`f2`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `f1` int(11) NOT NULL,
+  `f2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f1`),
+  KEY `f2` (`f2`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t2` (`f2`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 values (2, 91);
+insert into t2 values (2, 91);
+insert into t3 values (2, 91);
+select f1, f2 from t1;
+f1	f2
+2	91
+select f1, f2 from t2;
+f1	f2
+2	91
+select f1, f2 from t3;
+f1	f2
+2	91
+connection con1;
+start transaction;
+select f1 from t3 for update;
+f1
+2
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+update t1 set f2 = 28 where f2 = 91;
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+# The table t1 is updated.
+# In t2 delete-mark happened. Retry will happen on t2.
+# In t3 yet to be updated.
+set session transaction isolation level read uncommitted;
+start transaction;
+select f1, f2 from t1;
+f1	f2
+2	28
+select f1, f2 from t2;
+f1	f2
+select f1, f2 from t3;
+f1	f2
+2	91
+commit;
+set debug_sync='now SIGNAL go_upd';
+disconnect con1;
+connection default;
+# reap: update t1 set f2 = 28 where f2 = 91;
+start transaction;
+select f1, f2 from t1;
+f1	f2
+2	28
+select f1, f2 from t2;
+f1	f2
+2	28
+select f1, f2 from t3;
+f1	f2
+2	28
+commit;
+drop table t3, t2, t1;
+set debug_sync = reset;
diff --git a/mysql-test/suite/innodb/t/update-cascade.test b/mysql-test/suite/innodb/t/update-cascade.test
new file mode 100644
index 00000000000..de8294703b4
--- /dev/null
+++ b/mysql-test/suite/innodb/t/update-cascade.test
@@ -0,0 +1,221 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+--echo #
+--echo # Bug #18451287 REDUNDANT DELETE MARKING AFTER DB_LOCK_WAIT
+--echo #
+
+create table t1 (f1 int primary key, f2 blob) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+   foreign key (f2) references t1(f1) on update cascade) engine=innodb;
+
+show create table t1;
+show create table t2;
+insert into t1 values (1, repeat('+', 20000));
+insert into t1 values (2, repeat('-', 20000));
+insert into t1 values (3, repeat('=', 20000));
+insert into t2 values (1, 2);
+
+select f1, right(f2, 20) as p2 from t1;
+select f1, f2 from t2;
+
+connect(con1,localhost,root,,test);
+start transaction;
+select f1, f2 from t2 for update;
+
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+send update t1 set f1 = 10 where f1 = 2;
+
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+set debug_sync='now SIGNAL go_upd';
+
+connection default;
+--echo # reap: update t1 set f1 = 10 where f1 = 2;
+reap;
+
+select f1, right(f2, 20) as p2 from t1;
+select f1, f2 from t2;
+
+drop table t2, t1;
+
+set debug_sync = reset;
+
+--echo #
+--echo # Test Scenario: Two tables t1 -> t2 are involved in update cascade.
+--echo # If DB_LOCK_WAIT happens when t1 is being updated and FK constraints
+--echo # are being checked in t2, then retry must happen on t1.  The update
+--echo # cascade happens in secondary index. For secondary index testing,
+--echo # blobs are not needed.
+--echo #
+
+create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+   foreign key (f2) references t1(f2) on update cascade) engine=innodb;
+
+show create table t1;
+show create table t2;
+insert into t1 values (1, 91);
+insert into t2 values (1, 91);
+
+select f1, f2 from t1;
+select f1, f2 from t2;
+
+connection con1;
+start transaction;
+select f1, f2 from t2 for update;
+
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+send update t1 set f2 = 28 where f2 = 91;
+
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+set debug_sync='now SIGNAL go_upd';
+
+connection default;
+--echo # reap: update t1 set f1 = 10 where f1 = 2;
+--reap
+
+select f1, f2 from t1;
+select f1, f2 from t2;
+
+drop table t2, t1;
+
+set debug_sync = reset;
+
+--echo #
+--echo # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update cascade.
+--echo # If DB_LOCK_WAIT happens when t2 is being updated, then retry must happen
+--echo # on t2.
+--echo #
+
+create table t1 (f1 int primary key, f2 blob) engine=innodb;
+create table t2 (f1 int primary key, f2 blob,
+   foreign key (f1) references t1(f1) on update cascade) engine=innodb;
+create table t3 (f1 int primary key, f2 blob,
+   foreign key (f1) references t2(f1) on update cascade) engine=innodb;
+
+show create table t1;
+show create table t2;
+show create table t3;
+
+insert into t1 values (2, repeat('-', 20000));
+insert into t2 values (2, repeat('%', 20000));
+insert into t3 values (2, repeat('+', 20000));
+
+select f1, right(f2, 20) as p2 from t1;
+select f1, right(f2, 20) as p2 from t2;
+select f1, right(f2, 20) as p2 from t3;
+
+connection con1;
+start transaction;
+select f1 from t3 for update;
+
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+send update t1 set f1 = 10 where f1 = 2;
+
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+
+--echo # The table t1 is updated.
+--echo # In t2 delete-mark happened. Retry will happen on t2.
+--echo # In t3 yet to be updated.
+set session transaction isolation level read uncommitted;
+start transaction;
+select f1, right(f2, 20) as p2 from t1;
+select f1, right(f2, 20) as p2 from t2;
+select f1, right(f2, 20) as p2 from t3;
+commit;
+
+set debug_sync='now SIGNAL go_upd';
+
+connection default;
+--echo # reap: update t1 set f1 = 10 where f1 = 2;
+--reap;
+
+start transaction;
+select f1, right(f2, 20) as p2 from t1;
+select f1, right(f2, 20) as p2 from t2;
+select f1, right(f2, 20) as p2 from t3;
+commit;
+
+drop table t3, t2, t1;
+
+set debug_sync = reset;
+
+--echo #
+--echo # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update
+--echo # cascade. If DB_LOCK_WAIT happens when t2 is being updated, then
+--echo # retry must happen on t2.  The update cascade is happening via
+--echo # secondary index (hence blobs are not needed).
+--echo #
+
+create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
+create table t2 (f1 int primary key, f2 int,
+   foreign key (f2) references t1(f2) on update cascade) engine=innodb;
+create table t3 (f1 int primary key, f2 int,
+   foreign key (f2) references t2(f2) on update cascade) engine=innodb;
+
+show create table t1;
+show create table t2;
+show create table t3;
+
+insert into t1 values (2, 91);
+insert into t2 values (2, 91);
+insert into t3 values (2, 91);
+
+select f1, f2 from t1;
+select f1, f2 from t2;
+select f1, f2 from t3;
+
+connection con1;
+start transaction;
+select f1 from t3 for update;
+
+connection default;
+set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
+send update t1 set f2 = 28 where f2 = 91;
+
+connection con1;
+set debug_sync='now WAIT_FOR upd_waiting';
+rollback;
+
+--echo # The table t1 is updated.
+--echo # In t2 delete-mark happened. Retry will happen on t2.
+--echo # In t3 yet to be updated.
+set session transaction isolation level read uncommitted;
+start transaction;
+select f1, f2 from t1;
+select f1, f2 from t2;
+select f1, f2 from t3;
+commit;
+
+set debug_sync='now SIGNAL go_upd';
+disconnect con1;
+
+connection default;
+--echo # reap: update t1 set f2 = 28 where f2 = 91;
+--reap;
+
+start transaction;
+select f1, f2 from t1;
+select f1, f2 from t2;
+select f1, f2 from t3;
+commit;
+
+drop table t3, t2, t1;
+
+set debug_sync = reset;
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index c59bc051517..75ddac440b9 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -1881,9 +1881,10 @@ do_possible_lock_wait:
 
 		thr->lock_state = QUE_THR_LOCK_NOLOCK;
 
-		err = check_table->to_be_dropped
-			? DB_LOCK_WAIT_TIMEOUT
-			: trx->error_state;
+		if (check_table->to_be_dropped
+		    || trx->error_state == DB_LOCK_WAIT_TIMEOUT) {
+			err = DB_LOCK_WAIT_TIMEOUT;
+		}
 
 		my_atomic_addlint(&check_table->n_foreign_key_checks_running,
 				  -1);