1 files changed, 179 insertions, 0 deletions
diff --git a/mysql-test/main/schema.test b/mysql-test/main/schema.test
new file mode 100644
index 00000000000..8f9047e2e3f
--- /dev/null
+++ b/mysql-test/main/schema.test
@@ -0,0 +1,179 @@
+#
+# Just a couple of tests to make sure that schema works.
+#
+# Drop mysqltest1 database, as it can left from the previous tests.
+#
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+--disable_warnings
+drop database if exists mysqltest1;
+--enable_warnings
+
+create schema foo;
+show create schema foo;
+show schemas;
+drop schema foo;
+
+
+--echo #
+--echo # Bug #48940 MDL deadlocks against mysql_rm_db
+--echo #
+
+--disable_warnings
+DROP SCHEMA IF EXISTS schema1;
+--enable_warnings
+
+connect(con2, localhost, root);
+
+connection default;
+
+CREATE SCHEMA schema1;
+CREATE TABLE schema1.t1 (a INT);
+
+SET autocommit= FALSE;
+INSERT INTO schema1.t1 VALUES (1);
+
+connection con2;
+--send DROP SCHEMA schema1
+
+connection default;
+let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
+                     WHERE state= 'Waiting for table metadata lock' 
+                     AND info='DROP SCHEMA schema1';
+--source include/wait_condition.inc
+# Error 1 is from ALTER DATABASE when the database does not exist.
+# Listing the error twice to prevent result diffences based on filename.
+--error 1,1
+ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8;
+SET autocommit= TRUE;
+
+connection con2;
+--reap
+
+connection default;
+disconnect con2;
+
+
+--echo #
+--echo # Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
+--echo #
+
+--disable_warnings
+DROP SCHEMA IF EXISTS schema1;
+--enable_warnings
+
+connect (con2, localhost, root);
+
+connection default;
+CREATE SCHEMA schema1;
+CREATE TABLE schema1.t1 (id INT);
+LOCK TABLE schema1.t1 WRITE;
+
+connection con2;
+--send DROP SCHEMA schema1
+
+connection default;
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 
+  WHERE state='Waiting for schema metadata lock' and info='DROP SCHEMA schema1';
+--source include/wait_condition.inc
+
+--echo # CREATE SCHEMA used to give a deadlock.
+--echo # Now we prohibit CREATE SCHEMA in LOCK TABLES mode.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+CREATE SCHEMA IF NOT EXISTS schema1;
+
+--echo # UNLOCK TABLES so DROP SCHEMA can continue.
+UNLOCK TABLES;
+
+connection con2;
+--reap
+
+connection default;
+disconnect con2;
+
+
+--echo #
+--echo # Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
+--echo #
+
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (a INT);
+INSERT INTO db1.t1 VALUES (1), (2);
+
+connect (con1, localhost, root);
+HANDLER db1.t1 OPEN;
+
+connection default;
+--echo # Sending:
+--send DROP DATABASE db1
+
+connect (con2, localhost, root);
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
+  WHERE state='Waiting for table metadata lock' AND info='DROP DATABASE db1';
+--source include/wait_condition.inc
+
+connection con1;
+# All these statements before resulted in deadlock.
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+HANDLER t1 CLOSE;
+
+connection default;
+--echo # Reaping: DROP DATABASE db1
+--reap
+disconnect con1;
+disconnect con2;
+
+
+--echo #
+--echo # Tests for increased CREATE/ALTER/DROP DATABASE concurrency with
+--echo # database name locks.
+--echo # 
+
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+DROP DATABASE IF EXISTS db2;
+--enable_warnings
+
+connect (con2, localhost, root);
+connect (con3, localhost, root);
+
+connection default;
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (id INT);
+START TRANSACTION;
+INSERT INTO db1.t1 VALUES (1);
+
+connection con2;
+--echo # DROP DATABASE should block due to the active transaction
+--echo # Sending:
+--send DROP DATABASE db1
+
+connection con3;
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 
+  WHERE state='Waiting for table metadata lock' and info='DROP DATABASE db1';
+--source include/wait_condition.inc
+--echo # But it should still be possible to CREATE/ALTER/DROP other databases.
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+
+connection default;
+--echo # End the transaction so DROP DATABASE db1 can continue
+COMMIT;
+
+connection con2;
+--echo # Reaping: DROP DATABASE db1
+--reap
+
+connection default;
+disconnect con2;
+disconnect con3;
+
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc