You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
107 lines
3.6 KiB
107 lines
3.6 KiB
#
|
|
# WL#9451 -- Backup Log
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
connect con1, localhost, root,,;
|
|
SET lock_wait_timeout= 1;
|
|
SET autocommit= 0;
|
|
connection default;
|
|
LOCK INSTANCE FOR BACKUP;
|
|
connection con1;
|
|
# Test case 1: Check that attempt to run DDL statement leads to
|
|
# emission of error ER_LOCK_WAIT_TIMEOUT since execution
|
|
# of the statement was blocked by LOCK INSTANCE issued
|
|
# from connection default.
|
|
CREATE TABLE t2 (a INT);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
# Test case 2: Check that DML statement is executed successfully
|
|
# when LOCK INSTANCE was acquired from another connection.
|
|
INSERT INTO t1 VALUES (100);
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
100
|
|
# Test case 3: Make attempt to execute a DDL statement after LOCK INSTANCE was issued
|
|
# and check that DDL is executed successfully as soon as UNLOCK INSTANCE was issued
|
|
SET lock_wait_timeout= 10000000;
|
|
CREATE TABLE t3 (a INT);
|
|
connection default;
|
|
UNLOCK INSTANCE;
|
|
connection con1;
|
|
# Reap result of CREATE TABLE t3
|
|
# Check that the table t3 was created
|
|
DESCRIBE t3;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
# Test case 4: Check that several statements LOCK INSTANCE FOR BACKUP
|
|
# can be issued from different connections.
|
|
LOCK INSTANCE FOR BACKUP;
|
|
connect con2, localhost, root,,;
|
|
# It is expected that second execution of LOCK INSTANCE FOR BACKUP
|
|
# will be successful.
|
|
LOCK INSTANCE FOR BACKUP;
|
|
# Then switch to the connection default
|
|
# and try to execute the statement CREATE TABLE t2.
|
|
# It is expected that processing of the statement will be suspended
|
|
# until connections con1 and con2 release Backup Lock.
|
|
connection default;
|
|
CREATE TABLE t2 (a INT);
|
|
connection con1;
|
|
# Show that default connection is waiting until Backup Lock be released
|
|
SELECT info, state FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = default_con_id;
|
|
info state
|
|
CREATE TABLE t2 (a INT) Waiting for backup lock
|
|
UNLOCK INSTANCE;
|
|
connection con2;
|
|
# Show that default connection is still waiting until Backup Lock be released
|
|
# by connection con2
|
|
SELECT info, state FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = default_con_id;
|
|
info state
|
|
CREATE TABLE t2 (a INT) Waiting for backup lock
|
|
UNLOCK INSTANCE;
|
|
# Waiting until connection default acquire Backup Lock and resume execution
|
|
connection default;
|
|
# Reap CREAT TABLE t2
|
|
# Check that the table t2 was created after Backup Lock had been released
|
|
DESCRIBE t2;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
# Test case 5: Check that Backup Lock independent from
|
|
# FLUSH TABLES <table list> WITH READ LOCK
|
|
# Case 5.1: Check that FLUSH TABLES <table list> WITH READ LOCK following with
|
|
# LOCK INSTANCE FOR BACKUP are executed successfully.
|
|
FLUSH TABLES t1 WITH READ LOCK;
|
|
LOCK INSTANCE FOR BACKUP;
|
|
UNLOCK INSTANCE;
|
|
UNLOCK TABLES;
|
|
# Case 5.2: Check that LOCK INSTANCE FOR BACKUP following with
|
|
# FLUSH TABLES <table list> WITH READ LOCK are executed successfully.
|
|
LOCK INSTANCE FOR BACKUP;
|
|
FLUSH TABLES t1 WITH READ LOCK;
|
|
UNLOCK TABLES;
|
|
UNLOCK INSTANCE;
|
|
connection con1;
|
|
disconnect con1;
|
|
connection con2;
|
|
disconnect con2;
|
|
connection default;
|
|
# Test case 6: check that a user without granted BACKUP_ADMIN privilege
|
|
# failed to acquire Backup Lock.
|
|
CREATE USER u1;
|
|
connect con1, localhost, u1,,;
|
|
LOCK INSTANCE FOR BACKUP;
|
|
ERROR 42000: Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
|
|
disconnect con1;
|
|
connection default;
|
|
DROP USER u1;
|
|
DROP TABLE t1, t2, t3;
|
|
#
|
|
# Bug26665851 - USING FLUSH TABLES WITH READ LOCK AND LOCK INSTANCE LEADS TO A CRASH
|
|
#
|
|
LOCK INSTANCE FOR BACKUP;
|
|
CREATE TABLE t1 (a INT);
|
|
# Without the patch the following statement leads to a crash for debug build
|
|
FLUSH TABLES WITH READ LOCK;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
UNLOCK INSTANCE;
|
|
|