MDEV-13498 DELETE with CASCADE constraints takes long time / MDEV-13246

MDEV-13498 is a performance regression that was introduced in MariaDB 10.2.2
by commit fec844aca88e1c6b9c36bb0b811e92d9d023ffb9
which introduced some Galera-specific conditions that were being
evaluated even if the write-set replication was not enabled.

MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
is a correctness regression that was introduced by the same commit.

Especially the subcondition
	!(parent && que_node_get_type(parent) == QUE_NODE_UPDATE)
which is equivalent to
	!parent || que_node_get_type(parent) != QUE_NODE_UPDATE
makes little sense. If parent==NULL, the evaluation would proceed to the
std::find() expression, which would dereference parent. Because no SIGSEGV
was observed related to this, we can conclude that parent!=NULL always
holds. But then, the condition would be equivalent to
	que_node_get_type(parent) != QUE_NODE_UPDATE
which would not make sense either, because the std::find() expression
is actually assuming the opposite when casting parent to upd_node_t*.

It looks like this condition never worked properly, or that
it was never properly tested, or both.

wsrep_must_process_fk(): Helper function to check if FOREIGN KEY
constraints need to be processed. Only evaluate the costly std::find()
expression when write-set replication is enabled.

Also, rely on operator<<(std::ostream&, const id_name_t&) and
operator<<(std::ostream&, const table_name_t&) for pretty-printing
index and table names.

row_upd_sec_index_entry(): Add !wsrep_thd_is_BF() to the condition.
This is applying part of "Galera MW-369 FK fixes"
https://github.com/codership/mysql-wsrep/commit/f37b79c6dab101310a45a9e8cb23c0f98716da52
that is described by the following part of the commit comment:
    additionally: skipping wsrep_row_upd_check_foreign_constraint if thd has
    BF, essentially is applier or replaying
    This FK check would be needed only for populating parent row FK keys
    in write set, so no use for appliers

2 files changed, 117 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 33b5ea1dc56..f3ae826ddb8 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -169,3 +169,56 @@ SET FOREIGN_KEY_CHECKS=DEFAULT;
 LOCK TABLE staff WRITE;
 UNLOCK TABLES;
 DROP TABLES staff, store;
+SET FOREIGN_KEY_CHECKS=1;
+#
+# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
+#
+CREATE TABLE users (
+id int unsigned AUTO_INCREMENT PRIMARY KEY,
+name varchar(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_groups (
+id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
+host_user_id int unsigned NOT NULL UNIQUE,
+CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_users (
+matchmaking_group_id bigint unsigned NOT NULL,
+user_id int unsigned NOT NULL,
+PRIMARY KEY (matchmaking_group_id,user_id),
+UNIQUE KEY user_id (user_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+CONSTRAINT FOREIGN KEY (user_id)
+REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_maps (
+matchmaking_group_id bigint unsigned NOT NULL,
+map_id tinyint unsigned NOT NULL,
+PRIMARY KEY (matchmaking_group_id,map_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
+BEGIN;
+UPDATE users SET name = 'qux' WHERE id = 1;
+connect  con1,localhost,root,,;
+SET innodb_lock_wait_timeout= 1;
+DELETE FROM matchmaking_groups WHERE id = 10;
+disconnect con1;
+connection default;
+COMMIT;
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id	user_id
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id	map_id
+SELECT * FROM users;
+id	name
+1	qux
+2	bar
+DROP TABLE
+matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 4a8da381e8c..72c7c34cc0d 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -1,4 +1,5 @@
 --source include/have_innodb.inc
+--source include/count_sessions.inc
 
 --echo #
 --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
@@ -137,3 +138,66 @@ SET FOREIGN_KEY_CHECKS=DEFAULT;
 LOCK TABLE staff WRITE;
 UNLOCK TABLES;
 DROP TABLES staff, store;
+SET FOREIGN_KEY_CHECKS=1;
+
+--echo #
+--echo # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
+--echo #
+
+CREATE TABLE users (
+  id int unsigned AUTO_INCREMENT PRIMARY KEY,
+  name varchar(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_groups (
+  id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
+  host_user_id int unsigned NOT NULL UNIQUE,
+  CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+  ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_users (
+  matchmaking_group_id bigint unsigned NOT NULL,
+  user_id int unsigned NOT NULL,
+  PRIMARY KEY (matchmaking_group_id,user_id),
+  UNIQUE KEY user_id (user_id),
+  CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+  REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT FOREIGN KEY (user_id)
+  REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_maps (
+  matchmaking_group_id bigint unsigned NOT NULL,
+  map_id tinyint unsigned NOT NULL,
+  PRIMARY KEY (matchmaking_group_id,map_id),
+  CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+  REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
+
+BEGIN;
+UPDATE users SET name = 'qux' WHERE id = 1;
+
+--connect (con1,localhost,root,,)
+SET innodb_lock_wait_timeout= 1;
+DELETE FROM matchmaking_groups WHERE id = 10;
+--disconnect con1
+
+--connection default
+COMMIT;
+--sorted_result
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+--sorted_result
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+--sorted_result
+SELECT * FROM users;
+
+DROP TABLE
+matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+
+--source include/wait_until_count_sessions.inc