path: root/mysql-test/r/mdl_sync.result

SET DEBUG_SYNC= 'RESET';
drop table if exists t1,t2,t3;
create table t1 (i int);
create table t2 (i int);
connection: default
lock tables t2 read;
connection: con1
set debug_sync='mdl_upgrade_shared_lock_to_exclusive SIGNAL parked WAIT_FOR go';
alter table t1 rename t3;
connection: default
set debug_sync= 'now WAIT_FOR parked';
connection: con2
set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go';
drop table t1,t2;
connection: con1
connection: default
unlock tables;
connection: con2
ERROR 42S02: Unknown table 't1'
drop table t3;
SET DEBUG_SYNC= 'RESET';
#
# Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
# on INSERT + CREATE TRIGGER".
#
drop tables if exists t1, t2, t3, t4, t5;
# Let us simulate scenario in which we open some tables from extended
# part of prelocking set but then encounter conflicting metadata lock,
# so have to back-off and wait for it to go away.
create table t1 (i int);
create table t2 (j int);
create table t3 (k int);
create table t4 (l int);
create trigger t1_bi before insert on t1 for each row
insert into t2 values (new.i);
create trigger t2_bi before insert on t2 for each row
insert into t3 values (new.j);
#
# Switching to connection 'con1root'.
lock tables t4 read;
#
# Switching to connection 'con2root'.
# Send :
rename table t3 to t5, t4 to t3;;
#
# Switching to connection 'default'.
# Wait until the above RENAME TABLE adds pending requests for exclusive
# metadata lock on its tables and blocks due to 't4' being used by LOCK
# TABLES.
# Send :
insert into t1 values (1);;
#
# Switching to connection 'con1root'.
# Wait until INSERT statement waits due to encountering pending
# exclusive metadata lock on 't3'.
unlock tables;
#
# Switching to connection 'con2root'.
# Reap RENAME TABLE.
#
# Switching to connection 'default'.
# Reap INSERT.
# Clean-up.
drop tables t1, t2, t3, t5;
#
# Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table
#
DROP TABLE IF EXISTS t1;
set @save_log_output=@@global.log_output;
set global log_output=file;
#
# Test 1: CREATE TABLE
#
# Connection 2 
# Start insert on the not-yet existing table
# Wait after taking the MDL lock
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
INSERT INTO t1 VALUES(1,"def");
# Connection 1
SET DEBUG_SYNC= 'now WAIT_FOR locked';
# Now INSERT has a MDL on the non-existent table t1.
#
# Continue the INSERT once CREATE waits for exclusive lock
SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
# Try to create that table.
CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1));
# Connection 2
# Insert fails
ERROR 42S02: Table 'test.t1' doesn't exist
# Connection 1
SET DEBUG_SYNC= 'RESET';
SHOW TABLES;
Tables_in_test
t1
DROP TABLE IF EXISTS t1;
#
# Test 2: CREATE TABLE LIKE
#
CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1));
# Connection 2 
# Start insert on the not-yet existing table
# Wait after taking the MDL
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
INSERT INTO t1 VALUES(1,"def");
# Connection 1
SET DEBUG_SYNC= 'now WAIT_FOR locked';
# Now INSERT has a MDL on the non-existent table t1.
#
# Continue the INSERT once CREATE waits for exclusive lock
SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish';
# Try to create that table.
CREATE TABLE t1 LIKE t2;
# Connection 2
# Insert fails
ERROR 42S02: Table 'test.t1' doesn't exist
# Connection 1
SET DEBUG_SYNC= 'RESET';
SHOW TABLES;
Tables_in_test
t1
t2
DROP TABLE t2;
DROP TABLE IF EXISTS t1;
set global log_output=@save_log_output;
#
# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
#             FOR UPDATE"
#
drop tables if exists t1, t2;
create table t1 (i int);
# Let us check that we won't deadlock if during filling
# of I_S table we encounter conflicting metadata lock
# which owner is in its turn waiting for our connection.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# First let us check that SHOW FIELDS/DESCRIBE doesn't
# gets blocked and emits and error.
show fields from t2;
ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
# Now test for I_S query which reads only .FRMs.
#
# Query below should only emit a warning.
select column_name from information_schema.columns
where table_schema='test' and table_name='t2';
column_name
Warnings:
Warning	1652	Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
# Finally, test for I_S query which does full-blown table open.
#
# Query below should not be blocked. Warning message should be
# stored in the 'table_comment' column.
select table_name, table_type, auto_increment, table_comment
from information_schema.tables where table_schema='test' and table_name='t2';
table_name	table_type	auto_increment	table_comment
t2	BASE TABLE	NULL	Table 'test'.'t2' was skipped since its definition is being modified by concurre
# Switching to connection 'default'.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
drop table t2;
#
# Let us also check that queries to I_S wait for conflicting metadata
# locks to go away instead of skipping table with a warning in cases
# when deadlock is not possible. This is a nice thing from compatibility
# and ease of use points of view.
#
# We check same three queries to I_S in this new situation.
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Let us check that SHOW FIELDS/DESCRIBE gets blocked.
# Sending:
show fields from t2;;
# Switching to connection 'con46044_2'.
# Wait until SHOW FIELDS gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SHOW FIELDS ...
Field	Type	Null	Key	Default	Extra
i	int(11)	YES		NULL	
drop table t2;
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Check that I_S query which reads only .FRMs gets blocked.
# Sending:
select column_name from information_schema.columns where table_schema='test' and table_name='t2';;
# Switching to connection 'con46044_2'.
# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS  gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS
column_name
i
drop table t2;
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Finally, check that I_S query which does full-blown table open
# also gets blocked.
# Sending:
select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';;
# Switching to connection 'con46044_2'.
# Wait until SELECT ... FROM I_S.TABLES gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SELECT ... FROM I_S.TABLES
table_name	table_type	auto_increment	table_comment
t2	BASE TABLE	NULL	
drop table t2;
# Switching to connection 'default'.
# Clean-up.
drop table t1;
#
# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
#                      and DML".
#
drop tables if exists t1;
create table t1 (i int);
# Switching to connection 'con46673'.
begin;
insert into t1 values (1);
# Switching to connection 'default'.
# Statement below should not get blocked. And if after some
# changes to code it is there should not be a deadlock between
# it and transaction from connection 'con46673'.
flush tables with read lock;
unlock tables;
# Switching to connection 'con46673'.
delete from t1 where i = 1;
commit;
# Switching to connection 'default'.
# Clean-up
drop table t1;
#
# Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks 
#           against concurrent CREATE PROCEDURE
#
# Test 1: CREATE PROCEDURE
# Connection 1
# Start CREATE PROCEDURE and open mysql.proc
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
CREATE PROCEDURE p1() SELECT 1;
# Connection 2
SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
# Check that FLUSH must wait to get the GRL
# and let CREATE PROCEDURE continue
SET DEBUG_SYNC= 'wait_lock_global_read_lock SIGNAL grlwait';
FLUSH TABLES WITH READ LOCK;
# Connection 1
# Connection 2
UNLOCK TABLES;
# Connection 1
SET DEBUG_SYNC= 'RESET';
# Test 2: DROP PROCEDURE
# Start DROP PROCEDURE and open tables
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
DROP PROCEDURE p1;
# Connection 2
SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
# Check that FLUSH must wait to get the GRL
# and let DROP PROCEDURE continue
SET DEBUG_SYNC= 'wait_lock_global_read_lock SIGNAL grlwait';
FLUSH TABLES WITH READ LOCK;
# Connection 1
# Connection 2
UNLOCK TABLES;
# Connection 1
SET DEBUG_SYNC= 'RESET';