summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/schema.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/schema.test
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/schema.test')
-rw-r--r--mysql-test/main/schema.test163
1 files changed, 163 insertions, 0 deletions
diff --git a/mysql-test/main/schema.test b/mysql-test/main/schema.test
new file mode 100644
index 00000000..b984ebb8
--- /dev/null
+++ b/mysql-test/main/schema.test
@@ -0,0 +1,163 @@
+#
+# 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
+--source include/default_charset.inc
+
+create schema foo;
+show create schema foo;
+show schemas;
+drop schema foo;
+
+
+--echo #
+--echo # Bug #48940 MDL deadlocks against mysql_rm_db
+--echo #
+
+connect(con2, localhost, root);
+
+connection default;
+
+CREATE SCHEMA schema1;
+CREATE TABLE schema1.t1 (a INT);
+
+START TRANSACTION;
+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;
+COMMIT;
+
+connection con2;
+--reap
+
+connection default;
+disconnect con2;
+
+
+--echo #
+--echo # Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
+--echo #
+
+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 #
+
+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