MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table

This follows up the previous fix in
commit c3c53926c467c95386ae98d61ada87294bd61478 (MDEV-26554).

ha_innobase::delete_table(): Work around the insufficient
metadata locking (MDL) during DML operations by acquiring exclusive
InnoDB table locks on all child tables. Previously, this was only
done on TRUNCATE and ALTER.

ibuf_delete_rec(), btr_cur_optimistic_delete(): Do not invoke
lock_update_delete() during change buffer operations.
The revised trx_t::commit(std::vector<pfs_os_file_t>&) will
hold exclusive lock_sys.latch while invoking fil_delete_tablespace(),
which in turn may invoke ibuf_delete_rec().

dict_index_t::has_locking(): A new predicate, replacing the dummy
!dict_table_is_locking_disabled(index->table). Used for skipping lock
operations during ibuf_delete_rec().

trx_t::commit(std::vector<pfs_os_file_t>&): Release the locks
and remove the table from the cache while holding exclusive
lock_sys.latch.

trx_t::commit_in_memory(): Skip release_locks() if dict_operation holds.

trx_t::commit(): Reset dict_operation before invoking commit_in_memory()
via commit_persist().

lock_release_on_drop(): Release locks while lock_sys.latch is
exclusively locked.

lock_table(): Add a parameter for a pointer to the table.
We must not dereference the table before a lock_sys.latch has
been acquired. If the pointer to the table does not match the table
at that point, the table is invalid and DB_DEADLOCK will be returned.

row_ins_foreign_check_on_constraint(): Improve the checks.
Remove a bogus DB_LOCK_WAIT_TIMEOUT return that was needed
before commit c5fd9aa562fb15e8d6ededceccbec0c9792a3243 (MDEV-25919).

row_upd_check_references_constraints(),
wsrep_row_upd_check_foreign_constraints(): Simplify checks.

4 files changed, 56 insertions, 8 deletions

diff --git a/mysql-test/suite/innodb/r/alter_crash_rebuild.result b/mysql-test/suite/innodb/r/alter_crash_rebuild.result
index b4d4e8ba85a..ab4c25c6f57 100644
--- a/mysql-test/suite/innodb/r/alter_crash_rebuild.result
+++ b/mysql-test/suite/innodb/r/alter_crash_rebuild.result
@@ -1,11 +1,9 @@
 CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB STATS_PERSISTENT=0;
 connect ddl,localhost,root;
-SET DEBUG_SYNC='after_trx_committed_in_memory SIGNAL stuck WAIT_FOR ever EXECUTE 2';
+SET DEBUG_SYNC='after_trx_committed_in_memory SIGNAL stuck WAIT_FOR ever';
 ALTER TABLE t1 ADD PRIMARY KEY(a);
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR stuck';
-SET DEBUG_SYNC='now SIGNAL ever';
-SET DEBUG_SYNC='now WAIT_FOR stuck';
 SET GLOBAL innodb_log_checkpoint_now=ON;
 # restart
 disconnect ddl;
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 737db83103c..da784ba2acf 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -960,11 +960,30 @@ ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-disconnect con1;
+connection con1;
+COMMIT;
+connection default;
 SET innodb_lock_wait_timeout=DEFAULT;
 TRUNCATE TABLE parent;
 ALTER TABLE parent FORCE, ALGORITHM=COPY;
 ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
 ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
 DROP TABLE child, parent;
+#
+# MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove
+# or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk))
+ENGINE=InnoDB;
+connection con1;
+SET FOREIGN_KEY_CHECKS=OFF;
+CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB;
+connection default;
+INSERT INTO t2 VALUES (1);
+connection con1;
+disconnect con1;
+connection default;
+DROP TABLE IF EXISTS t2, t1;
 # End of 10.6 tests
diff --git a/mysql-test/suite/innodb/t/alter_crash_rebuild.test b/mysql-test/suite/innodb/t/alter_crash_rebuild.test
index 500cd28e5c5..0ed3e4a1f9c 100644
--- a/mysql-test/suite/innodb/t/alter_crash_rebuild.test
+++ b/mysql-test/suite/innodb/t/alter_crash_rebuild.test
@@ -5,13 +5,11 @@
 CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB STATS_PERSISTENT=0;
 
 connect ddl,localhost,root;
-SET DEBUG_SYNC='after_trx_committed_in_memory SIGNAL stuck WAIT_FOR ever EXECUTE 2';
+SET DEBUG_SYNC='after_trx_committed_in_memory SIGNAL stuck WAIT_FOR ever';
 send ALTER TABLE t1 ADD PRIMARY KEY(a);
 
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR stuck';
-SET DEBUG_SYNC='now SIGNAL ever';
-SET DEBUG_SYNC='now WAIT_FOR stuck';
 
 SET GLOBAL innodb_log_checkpoint_now=ON;
 
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 71290c722d7..6f7e5948b05 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -965,7 +965,9 @@ ALTER TABLE parent FORCE, ALGORITHM=COPY;
 ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
 --error ER_LOCK_WAIT_TIMEOUT
 ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
-disconnect con1;
+connection con1;
+COMMIT;
+connection default;
 # Restore the timeout to avoid occasional races with purge.
 SET innodb_lock_wait_timeout=DEFAULT;
 TRUNCATE TABLE parent;
@@ -974,6 +976,37 @@ ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
 ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
 DROP TABLE child, parent;
 
+--echo #
+--echo # MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove
+--echo # or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk))
+ENGINE=InnoDB;
+
+--connection con1
+SET FOREIGN_KEY_CHECKS=OFF;
+--send
+CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB;
+
+--connection default
+--error 0,ER_NO_REFERENCED_ROW_2,ER_LOCK_DEADLOCK
+INSERT INTO t2 VALUES (1);
+
+--connection con1
+--error 0,ER_CANT_CREATE_TABLE
+--reap
+
+# Cleanup
+--disconnect con1
+--connection default
+--disable_warnings
+DROP TABLE IF EXISTS t2, t1;
+--enable_warnings
+
 --echo # End of 10.6 tests
 
 --source include/wait_until_count_sessions.inc