314 lines
12 KiB
Plaintext
314 lines
12 KiB
Plaintext
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 'utf8_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 utf8 */ /*!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;
|