1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
#
# WL#9451 -- Backup Lock
#
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 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
Warnings:
Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
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
Warnings:
Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
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 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;
#
# Bug#30226264 - flush tables with read lock fails with deadlock found
#
CREATE DATABASE db1;
use db1;
CREATE TABLE t1 (a INT);
connect con1, localhost, root,,;
use db1;
connection default;
LOCK INSTANCE FOR BACKUP;
connection con1;
ALTER DATABASE db1 CHARACTER SET=latin1;
connection default;
# Without the patch the following statement would failed with
# ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;
connection con1;
# Reap result of ALTER DATABASE db1
clean up
DROP DATABASE db1;
disconnect con1;
connection default;
|
