MDEV-30233 DROP DATABASE test fails: Directory not empty

Some tests drop the default mtr database "test". This may fail due
to the directory not being empty. InnoDB may not delete all tables
immediately, due to the "background drop table queue" or its
replacement in commit 1bd681c8b3c5213ce1f7976940a7dc38b48a0d39
(the purge of history would clean up after a DDL operation during
which the server was killed).

Let us try to avoid "drop database test" whenever it is easily possible.
Where it is not, SET GLOBAL innodb_max_purge_lag_wait=0 will ensure
that the replacement of the "background drop table queue" will have
completed its job.

13 files changed, 31 insertions, 12 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 7f535fc3125..c4b66794e3d 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -4235,7 +4235,9 @@ drop table t1;
 #
 # MDEV-24019: query with recursive CTE when no default database is set
 #
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 with recursive a as
 (select 1 from dual union select * from a as r)
 select * from a;
@@ -4274,7 +4276,6 @@ a
 1
 deallocate prepare stmt;
 drop database db1;
-create database test;
 use test;
 #
 # MDEV-23406: query with mutually recursive CTEs when big_tables=1
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index f5babc6cb65..270a5023d31 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2729,7 +2729,9 @@ drop table t1;
 --echo # MDEV-24019: query with recursive CTE when no default database is set
 --echo #
 
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 
 let $q=
 with recursive a as
@@ -2757,7 +2759,6 @@ deallocate prepare stmt;
 
 drop database db1;
 
-create database test;
 use test;
 
 --echo #
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index fba61b354f3..82d34002617 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2217,8 +2217,9 @@ Warning	1931	Query execution was interrupted. The query examined at least ### ro
 # m_status == DA_OK_BULK' failed in Diagnostics_area::message()
 #
 call mtr.add_suppression("Sort aborted.*");
-DROP DATABASE test;
-CREATE DATABASE test;
+create database dummy;
+use dummy;
+drop database dummy;
 USE test;
 CREATE VIEW v AS SELECT table_schema  AS object_schema, table_name  AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema;
 SELECT * FROM v LIMIT ROWS EXAMINED 9;
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 2fac02d2fe0..469be44ef94 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1940,8 +1940,9 @@ SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
 
 call mtr.add_suppression("Sort aborted.*");
 
-DROP DATABASE test;
-CREATE DATABASE test;
+create database dummy;
+use dummy;
+drop database dummy;
 USE test;
 CREATE VIEW v AS SELECT table_schema  AS object_schema, table_name  AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema;
 
diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result
index afaafb4907c..a517a5e3c48 100644
--- a/mysql-test/main/mysqldump.result
+++ b/mysql-test/main/mysqldump.result
@@ -4469,6 +4469,7 @@ Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	E
 # MDEV-13336: add ignore-database option
 #           with --all-databases
 #
+SET GLOBAL innodb_max_purge_lag_wait=0;
 DROP DATABASE test;
 SHOW DATABASES LIKE 'test';
 Database (test)
diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test
index 89155a435b1..a099727ba38 100644
--- a/mysql-test/main/mysqldump.test
+++ b/mysql-test/main/mysqldump.test
@@ -1921,6 +1921,8 @@ SHOW EVENTS;
 --echo #           with --all-databases
 --echo #
 --exec $MYSQL_DUMP --default-character-set=utf8mb4 --ignore-database test --all-databases > $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql
+# Starting with MariaDB 10.6, ensure that DDL recovery will have completed.
+SET GLOBAL innodb_max_purge_lag_wait=0;
 DROP DATABASE test;
 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql
 SHOW DATABASES LIKE 'test';
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 7354e1dbb91..b042e27997f 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6723,7 +6723,9 @@ DROP TABLE t1;
 #
 # MDEV-24314: create view with derived table without default database
 #
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 create database db1;
 create table db1.t1 (a int);
 insert into db1.t1 values (3),(7),(1);
@@ -6753,7 +6755,6 @@ a
 drop view db1.v1;
 drop table db1.t1;
 drop database db1;
-create database test;
 use test;
 #
 # MDEV-16940: update of multi-table view returning error used in SP
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 987e96fc79d..11355c211ca 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6437,7 +6437,9 @@ DROP TABLE t1;
 --echo # MDEV-24314: create view with derived table without default database
 --echo #
 
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 
 create database db1;
 create table db1.t1 (a int);
@@ -6460,7 +6462,6 @@ drop view db1.v1;
 drop table db1.t1;
 drop database db1;
 
-create database test;
 use test;
 
 --echo #
diff --git a/mysql-test/suite/funcs_2/r/innodb_charset.result b/mysql-test/suite/funcs_2/r/innodb_charset.result
index 1ce5972eccc..6333e8739e3 100644
--- a/mysql-test/suite/funcs_2/r/innodb_charset.result
+++ b/mysql-test/suite/funcs_2/r/innodb_charset.result
@@ -1,3 +1,4 @@
+SET GLOBAL innodb_max_purge_lag_wait=0;
 set @save_character_set_database= @@character_set_database;
 DROP TABLE IF EXISTS test.t1;
 SET NAMES armscii8;
diff --git a/mysql-test/suite/funcs_2/t/innodb_charset.test b/mysql-test/suite/funcs_2/t/innodb_charset.test
index da4dea44ad7..631c20352d4 100644
--- a/mysql-test/suite/funcs_2/t/innodb_charset.test
+++ b/mysql-test/suite/funcs_2/t/innodb_charset.test
@@ -9,6 +9,10 @@
 --source include/no_valgrind_without_big.inc
 --source include/have_innodb.inc
 
+# Starting with MariaDB 10.6, ensure that DDL recovery will have completed
+# before DROP DATABASE test.
+SET GLOBAL innodb_max_purge_lag_wait=0;
+
 let $engine_type= InnoDB;
 --source suite/funcs_2/charset/charset_master.test
 
diff --git a/mysql-test/suite/innodb/r/information_schema_grants.result b/mysql-test/suite/innodb/r/information_schema_grants.result
index 04550bfa114..b2bd8b657a6 100644
--- a/mysql-test/suite/innodb/r/information_schema_grants.result
+++ b/mysql-test/suite/innodb/r/information_schema_grants.result
@@ -299,6 +299,7 @@ select count(*) > -1 from d_trx;
 count(*) > -1
 1
 connection default;
+SET GLOBAL innodb_max_purge_lag_wait=0;
 drop database test;
 create database test;
 drop user select_only@localhost;
diff --git a/mysql-test/suite/innodb/t/information_schema_grants.test b/mysql-test/suite/innodb/t/information_schema_grants.test
index 34565f76352..32eaefdacb3 100644
--- a/mysql-test/suite/innodb/t/information_schema_grants.test
+++ b/mysql-test/suite/innodb/t/information_schema_grants.test
@@ -306,6 +306,8 @@ select count(*) > -1 from i_trx;
 select count(*) > -1 from d_trx;
 
 connection default;
+# Starting with MariaDB 10.6, ensure that DDL recovery will have completed.
+SET GLOBAL innodb_max_purge_lag_wait=0;
 drop database test;
 create database test;
 drop user select_only@localhost;
diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test
index 27c140689c1..52b8e83695a 100644
--- a/mysql-test/suite/innodb/t/innodb-mdev7046.test
+++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test
@@ -31,6 +31,8 @@ RENAME TABLE t1 TO `t2_new..............................................end`;
 show warnings;
 drop table t1;
 
+# Starting with MariaDB 10.6, ensure that DDL recovery will have completed.
+SET GLOBAL innodb_max_purge_lag_wait=0;
 drop database test;
 create database test;
 use test;