Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 290 insertions, 0 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 00000000..00d052a0
--- /dev/null
+++ b/mysql-test/suite/innodb/r/update-cascade.result
@@ -0,0 +1,290 @@
+#
+# 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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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_start 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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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_start 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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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_start 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
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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 COLLATE=latin1_swedish_ci
+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_start 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
+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;