drop database if exists mysqltest1;
create schema foo;
show create schema foo;
Database	Create Database
foo	CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
show schemas like 'foo';
Database (foo)
foo
drop schema foo;
#
# Bug #48940 MDL deadlocks against mysql_rm_db
#
DROP SCHEMA IF EXISTS schema1;
# Connection default
CREATE SCHEMA schema1;
CREATE TABLE schema1.t1 (a INT);
SET autocommit= FALSE;
INSERT INTO schema1.t1 VALUES (1);
# Connection 2
DROP SCHEMA schema1;
# Connection default
ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8;
ERROR 42Y07: Database 'schema1' doesn't exist
SET autocommit= TRUE;
# Connection 2
# Connection default
#
# Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
#
DROP SCHEMA IF EXISTS schema1;
# Connection default
CREATE SCHEMA schema1;
CREATE TABLE schema1.t1 (id INT);
LOCK TABLE schema1.t1 WRITE;
# Connection con2
DROP SCHEMA schema1;
# Connection default
# CREATE SCHEMA used to give a deadlock.
# Now we prohibit CREATE SCHEMA in LOCK TABLES mode.
CREATE SCHEMA IF NOT EXISTS schema1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
# UNLOCK TABLES so DROP SCHEMA can continue.
UNLOCK TABLES;
# Connection con2
# Connection default
#
# Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
#
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
INSERT INTO db1.t1 VALUES (1), (2);
# Connection con1
HANDLER db1.t1 OPEN;
# Connection default
# Sending:
DROP DATABASE db1;
# Connection con2
# Connection con1
CREATE DATABASE db2;
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
DROP DATABASE db2;
HANDLER t1 CLOSE;
# Connection default
# Reaping: DROP DATABASE db1
#
# Tests for increased CREATE/ALTER/DROP DATABASE concurrency with
# database name locks.
# 
DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;
# Connection default
CREATE DATABASE db1;
CREATE TABLE db1.t1 (id INT);
START TRANSACTION;
INSERT INTO db1.t1 VALUES (1);
# Connection 2
# DROP DATABASE should block due to the active transaction
# Sending:
DROP DATABASE db1;
# Connection 3
# But it should still be possible to CREATE/ALTER/DROP other databases.
CREATE DATABASE db2;
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
DROP DATABASE db2;
# Connection default
# End the transaction so DROP DATABASE db1 can continue
COMMIT;
# Connection 2
# Reaping: DROP DATABASE db1
# Connection default;
#
# Bug#21305766: DML ON A NON EXISTING TABLE DOES NOT ACQUIRE MDL ON THE SCHEMA NAME
#
# Set lock_wait_timeout, create a schema and a connection con1.
SET @start_session_value= @@session.lock_wait_timeout;
SET @@session.lock_wait_timeout= 1;
CREATE SCHEMA testdb;
connect con1, localhost, root;
# Access a non-existing table in the created schema, and park it after
# acquiring the schema while acquiring the dd::Table object.
SET DEBUG_SYNC= 'acquired_schema_while_acquiring_table SIGNAL acquired WAIT_FOR cont';
SELECT * from testdb.no_such_table;
connection default;
# From the default connection, drop the schema, then resume
# con1. Without the patch in the server code, DROP SCHEMA
# will fail with an assert due to the schema object being
# acquired by more than one thread. With the patch, we get
# a lock wait timeout for DROP since the other thread has an
# IX-lock on the schema name.
SET DEBUG_SYNC= 'now WAIT_FOR acquired';
DROP SCHEMA testdb;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC= 'now SIGNAL cont';
connection con1;
# Reaping SELECT * from testdb.no_such_table
ERROR 42S02: Table 'testdb.no_such_table' doesn't exist
disconnect con1;
connection default;
# Drop testdb and reset
DROP SCHEMA testdb;
SET @@session.lock_wait_timeout= @start_session_value;
SET DEBUG_SYNC= 'RESET';
#
# Bug #21837759: SYNCHRONIZE USAGE OF SCHEMA DD OBJECTS BY MDL ACQUISITION
#
# Do a 'USE <schema>' and park it after retrieving the schema for
# finding the default db collation. Then, from a different
# connection, drop the same schema. Without the bugfix, this
# will fail due to improper concurrent usage of the DD cache
# elements. With the bugfix, the DROP will fail with a lock
# wait timeout due to the operations now being properly synchronized
# by meta data locking.
#
# Create the schema to be used in the test.
SET @start_session_value= @@session.lock_wait_timeout;
SET @@session.lock_wait_timeout= 1;
CREATE SCHEMA testdb;
#
# Create a new connection, change schema, and stop after
# retrieving the schema object for finding the default
# collation.
connect  con1, localhost, root;
SET DEBUG_SYNC= 'acquired_schema_while_getting_collation SIGNAL acquired WAIT_FOR cont';
USE testdb;
#
# On the default connection, do a DROP schema while the
# other connection is holding the same schema object.
# This will fail with a lock wait timeout when the operations
# are properly synched by MDL. Without the synchronization,
# this will trigger an assert in the shared DD cache.
connection default;
SET DEBUG_SYNC= 'now WAIT_FOR acquired';
DROP SCHEMA testdb;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC= 'now SIGNAL cont';
#
# Reap, reset, and delete connection.
connection con1;
disconnect con1;
connection default;
SET DEBUG_SYNC= 'RESET';
DROP SCHEMA testdb;
SET @@session.lock_wait_timeout= @start_session_value;
CREATE SCHEMA s;
SHOW CREATE SCHEMA s;
Database	Create Database
s	CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
USE s;
ALTER SCHEMA s DEFAULT COLLATE= utf8_general_ci;
Warnings:
Warning	3778	'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
SHOW CREATE SCHEMA s;
Database	Create Database
s	CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */
DROP SCHEMA s;
ERROR HY000: Schema directory './s/' does not exist
DROP SCHEMA s;
CREATE SCHEMA s;
CREATE VIEW s.v AS SELECT * FROM mysql.time_zone;
DROP SCHEMA s;
CREATE SCHEMA s;
CREATE TABLE s.t (pk INTEGER PRIMARY KEY) ENGINE= InnoDB;
t.ibd
DROP SCHEMA s;
CREATE SCHEMA s;
CREATE TABLE s.t_innodb (pk INTEGER PRIMARY KEY) ENGINE= InnoDB;
ALTER TABLE s.t_innodb ADD COLUMN c INTEGER;
Got one of the listed errors
DROP SCHEMA s;
CREATE SCHEMA s;
CREATE TABLE s.t_innodb (pk INTEGER PRIMARY KEY) ENGINE= InnoDB;
DROP SCHEMA s;
CREATE SCHEMA s;
CREATE TABLE s.t_innodb (pk INTEGER PRIMARY KEY) ENGINE= InnoDB;
DROP TABLE s.t_innodb;
DROP SCHEMA s;
#
# Bug#24732194: "USE DB_NAME" AND "SELECT SCHEMA()"
#               WORK FOR NON-EXISTING DATABASES
#
USE s;
ERROR 42000: Unknown database 's'
#
# WL#7743 "New data dictionary: changes to DDL-related parts of SE API"
#
# Additional test coverage for changes in DROP DATABASE implementation.
# Check what happens when we fail to remove database directory during
# the last step of DROP DATABASE, when statement is already committed.
CREATE DATABASE db1;
CREATE FUNCTION db1.f1() RETURNS INT RETURN 0;
connect  con1, localhost, root,,;
# Acquire S lock on db1.f1(), so upcoming DROP DATABASE will get
# blocked.
BEGIN;
SELECT db1.f1();
db1.f1()
0
connection default;
# Send:
DROP DATABASE db1;
connection con1;
# Wait until the above DROP DATABASE is blocked because of S lock.
# Replace database directory with an empty file.
# Unblock DROP DATABASE by releasing S lock.
COMMIT;
disconnect con1;
connection default;
# Reap DROP DATABASE. Statement should succeed, but send warnings
# about problems with removing database directory to user ...
Warnings:
Warning	3607	Problem while dropping database. Can't remove database directory (Error dropping database (can't rmdir './db1', errno: ## - ...). Please remove it manually.
# ... and error log too. Let's check that.
Pattern "Problem while dropping database. Can't remove database directory .* Please remove it manually." found
# Clean-up.
#
# Bug#24510948: ALTER TABLE+INSERT+DROP DB HANG
#
CREATE DATABASE db1;
CREATE TABLE db1.t1(id INT, title VARCHAR(100),
FULLTEXT fidx(title), PRIMARY KEY(id));
# Con1 will attempt insert and block holding open_in_progress on share
SET DEBUG_SYNC= 'get_share_before_open SIGNAL wait_share WAIT_FOR continue_insert';
SET SESSION lock_wait_timeout= 5;
INSERT INTO db1.t1 VALUES(1, 'mysql database');
# Con2 will attempt alter and block before waiting on COND_open
SET DEBUG_SYNC= 'now WAIT_FOR wait_share';
SET DEBUG_SYNC= 'get_share_before_COND_open_wait SIGNAL wait_cond WAIT_FOR continue_alter';
ALTER TABLE db1.t1 DROP INDEX fidx;
# Con3 will wait until the other connections are waiting for signals,
# then attempt to drop schema, which requires X-MDL
SET DEBUG_SYNC= 'now WAIT_FOR wait_cond';
DROP DATABASE db1;
# Con3 is now waiting for MDL on schema. Waking the other connections
# should let them continue (before fix con1 and con2 would deadlock
# on schema MDL and LOCK_open (protecting share->open_in_progress)
# until con1 aborts with lock timeout
SET DEBUG_SYNC= 'now SIGNAL continue_insert';
SET DEBUG_SYNC= 'now SIGNAL continue_alter';
# Reaping connection con1
# Reaping connection con2
# Reaping connection con3
#
# Bug#26043994: CREATE DATABASE/DIRECTORY INCONSISTENCY
#
# Dropping a non-existing schema fails
DROP SCHEMA s1;
ERROR HY000: Can't drop database 's1'; database doesn't exist
# but drop succeeds (with a warning) when using IF EXISTS
DROP SCHEMA IF EXISTS s1;
Warnings:
Note	1008	Can't drop database 's1'; database doesn't exist
# Verify that schema can be created successfully
CREATE SCHEMA s1;
# Verify that trying to create it again fails
CREATE SCHEMA s1;
ERROR HY000: Can't create database 's1'; database exists
# but create succeds (with a warning) when using IF NOT EXITSTS
CREATE SCHEMA IF NOT EXISTS s1;
Warnings:
Note	1007	Can't create database 's1'; database exists
DROP SCHEMA s1;
# Create a directory in datadir manually
# Creating a schema with the same name as directory in datadir fails
# but with new error message
CREATE SCHEMA bogus__;
ERROR HY000: Schema directory './bogus__' already exists. This must be resolved manually (e.g. by moving the schema directory to another location).
# Creating a schema with the same name as a directory in datadir
# with IF NOT EXISTS fails.
CREATE SCHEMA IF NOT EXISTS bogus__;
ERROR HY000: Schema directory './bogus__' already exists. This must be resolved manually (e.g. by moving the schema directory to another location).
# Drop a non-existent schema with the same name as a directory in
# datadir fails.
DROP SCHEMA bogus__;
ERROR HY000: Schema 'bogus__' does not exist, but schema directory './bogus__/' was found. This must be resolved manually (e.g. by moving the schema directory to another location).
# Drop a non-existent schema with the same name as a directory in
# datadir with IF EXISTS fails.
DROP SCHEMA IF EXISTS bogus__;
ERROR HY000: Schema 'bogus__' does not exist, but schema directory './bogus__/' was found. This must be resolved manually (e.g. by moving the schema directory to another location).
CREATE SCHEMA broken;
# Remove the schema directory for an existing schema
# Drop then fails with new error message
DROP SCHEMA broken;
ERROR HY000: Schema directory './broken/' does not exist
# but drop succeeds (with warning) when adding IF EXISTS
# Suppress output since it is not stable across platforms
DROP SCHEMA IF EXISTS broken;
# Create and drop schema again to make sure nothing is left behind
CREATE SCHEMA broken;
DROP SCHEMA broken;

Bug#30344462: CHANGING DEFAULT ENCRYPTION FOR A SCHEMA WILL RESET
DEFAULT COLLATION

CREATE SCHEMA s CHARACTER SET ascii;
USE s;
SHOW CREATE SCHEMA s;
Database	Create Database
s	CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET ascii */ /*!80016 DEFAULT ENCRYPTION='N' */
SHOW VARIABLES LIKE 'collation_database';
Variable_name	Value
collation_database	ascii_general_ci
ALTER SCHEMA s ENCRYPTION = 'n';
# Without the patch, altering encryption would also
# reset the default collation, and if the schema is
# the currently used schema, the 'collation_database'
# variable would also be reset.
SHOW CREATE SCHEMA s;
Database	Create Database
s	CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET ascii */ /*!80016 DEFAULT ENCRYPTION='N' */
SHOW VARIABLES LIKE 'collation_database';
Variable_name	Value
collation_database	ascii_general_ci
DROP SCHEMA s;
#
# Bug#32248313: TABLE_CACHE_MANAGER::FREE_TABLE: ASSERTION `CACHE_EL[I]->USED_TABLES.IS_EMPTY()' FAILED.
#
CREATE SCHEMA s1;
USE s1;
CREATE TABLE t1(a INT);
HANDLER t1 OPEN;
ALTER SCHEMA s1 READ ONLY DEFAULT;
DROP table t1;
DROP SCHEMA s1;
#
# Bug #33183590: COLLATE maybe overwritten by charset if both specified in create database stmt
#
CREATE DATABASE test1 COLLATE utf8mb4_bin CHARACTER SET utf8mb4;
SHOW CREATE DATABASE test1;
Database	Create Database
test1	CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
DROP DATABASE test1;