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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
|
########################################################################
# Tests BACKUP STAGE locking
########################################################################
--source include/have_innodb.inc
--source include/have_metadata_lock_info.inc
--source include/not_embedded.inc
--source include/no_view_protocol.inc
--echo #
--echo # Testing which locks we get from all stages
--echo #
let $mdl= LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
--source ../suite/innodb/include/wait_all_purged.inc
BACKUP STAGE START;
eval SELECT $mdl;
BACKUP STAGE FLUSH;
eval SELECT $mdl;
BACKUP STAGE BLOCK_DDL;
eval SELECT $mdl;
BACKUP STAGE BLOCK_COMMIT;
eval SELECT $mdl;
BACKUP STAGE END;
eval SELECT $mdl;
--echo #
--echo # testing BACKUP STAGE LOCK's
--echo #
# Following connections are used in a few of the following tests
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection default;
--echo #
--echo # testing if BACKUP STAGE FLUSH causes deadlocks with ALTER TABLE
--echo #
create table t1 (a int) stats_persistent= 0, engine=innodb;
--source ../suite/innodb/include/wait_all_purged.inc
connection con2;
backup stage start;
connection default;
start transaction;
# Acquires MDL lock
insert into t1 values (1);
connection con1;
# Waits on MDL
--send alter table t1 add column (j int), algorithm copy, lock shared
connection con2;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock";
--source include/wait_condition.inc
backup stage flush;
eval SELECT $mdl;
#
# Do first test with max_statement_time, other tests later are done with
# lock_wait_timeout. This is mostly to ensure that both methods works
#
--error ER_STATEMENT_TIMEOUT
SET STATEMENT max_statement_time=1 FOR backup stage block_ddl;
--send backup stage block_ddl
connection default;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
--source include/wait_condition.inc
commit;
# The following select works because alter table is waiting for DDL lock
SELECT * FROM t1;
--error ER_LOCK_WAIT_TIMEOUT
SET STATEMENT lock_wait_timeout=0 FOR INSERT INTO t1 values (2);
--send INSERT INTO t1 values (2,0);
connection con2;
--reap # BLOCK_DDL
backup stage end;
connection con1;
--reap # ALTER TABLE
connection default;
--reap # INSERT
select * from t1;
drop table t1;
--echo # Test with inline alter table, which doesn't block block_commit
create table t1 (a int) engine=innodb;
start transaction;
# Acquires MDL lock
insert into t1 values (1);
connection con1;
# Waits on MDL
--send alter table t1 add column (j int)
connection con2;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock";
--source include/wait_condition.inc
backup stage start;
backup stage flush;
eval SELECT $mdl;
backup stage block_ddl;
backup stage block_commit;
connection default;
SELECT * FROM t1;
--send commit
connection con2;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
backup stage end;
connection con1;
--reap # ALTER TABLE
connection default;
--reap # commit
drop table t1;
--echo #
--echo # testing if BACKUP STAGE FLUSH causes deadlocks with DROP TABLE
--echo #
create table t1 (a int)stats_persistent=0, engine=innodb;
--source ../suite/innodb/include/wait_all_purged.inc
start transaction;
# Acquires MDL lock
insert into t1 values (1);
connection con1;
# Waits on MDL
--error ER_LOCK_WAIT_TIMEOUT
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
--send DROP TABLE t1
connection con2;
backup stage start;
backup stage flush;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock";
--source include/wait_condition.inc
--error ER_LOCK_WAIT_TIMEOUT
SET STATEMENT lock_wait_timeout=0 FOR SELECT * FROM t1;
backup stage block_ddl;
eval SELECT $mdl;
backup stage end;
connection default;
commit;
connection con1;
--reap # DROP TABLE
connection default;
--echo #
--echo # Check if backup stage block_dll + concurrent drop table blocks select
--echo #
create table t1 (a int) engine=innodb;
--source ../suite/innodb/include/wait_all_purged.inc
backup stage start;
backup stage block_ddl;
connection con1;
--send DROP TABLE t1
connection con2;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
--source include/wait_condition.inc
connection con2;
eval SELECT $mdl;
# Check that select's are not blocked
SELECT * FROM t1;
connection default;
backup stage end;
connection con1;
--reap
connection default;
--echo #
--echo # Check if backup stage block_dll overrides ddl lock for drop table
--echo #
create table t1 (a int) engine=innodb;
start transaction;
# Acquires MDL lock
insert into t1 values (1);
connection con1;
# Waits on MDL
--error ER_LOCK_WAIT_TIMEOUT
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
--send DROP TABLE t1
connection con2;
backup stage start;
backup stage flush;
backup stage block_ddl;
connection default;
commit;
connection con2;
backup stage end;
connection con1;
--reap # DROP TABLE
connection default;
--echo #
--echo # Check if BACKUP STAGE BLOCK_COMMIT blocks commit
--echo #
create table t1 (a int) engine=innodb;
start transaction;
# Acquires MDL lock
insert into t1 values (1);
connection con1;
backup stage start;
backup stage block_commit;
connection default;
--send commit
connection con1;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
backup stage end;
connection default;
--reap # commit
select * from t1;
drop table t1;
--echo #
--echo # Check if BACKUP STAGE BLOCK_DDL blocks create view
--echo #
create table t1 (a int) engine=innodb;
connection con1;
backup stage start;
backup stage block_ddl;
connection default;
--send create view v1 as select * from t1;
connection con1;
--sleep 2
select count(*) = 1 from information_schema.processlist;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for backup lock";
backup stage end;
connection default;
--reap # create
drop table t1;
drop view v1;
#
# End of tests using con1 and con2
#
disconnect con1;
disconnect con2;
--echo #
--echo # Test backup stage and flush tables
--echo #
BACKUP STAGE START ;
BACKUP STAGE BLOCK_DDL ;
FLUSH TABLES;
CREATE TEMPORARY TABLE t12345678_tmp (col1 INT);
drop table t12345678_tmp;
BACKUP STAGE END;
--echo #
--echo # Test BACKUP STAGES with lock timeouts
--echo #
SET GLOBAL lock_wait_timeout=0;
CREATE TABLE t_permanent_innodb (col1 INT) ENGINE = InnoDB;
CREATE TABLE t_permanent_myisam (col1 INT) ENGINE = MyISAM;
CREATE TABLE t_permanent_aria (col1 INT) ENGINE = Aria transactional=1;
CREATE TABLE t_permanent_aria2 (col1 INT) ENGINE = Aria transactional=0;
INSERT INTO t_permanent_innodb SET col1 = 1;
INSERT INTO t_permanent_myisam SET col1 = 1;
INSERT INTO t_permanent_aria SET col1 = 1;
INSERT INTO t_permanent_aria2 SET col1 = 1;
CREATE TABLE t_con1_innodb (col1 INT) ENGINE = InnoDB;
CREATE TABLE t_con1_myisam (col1 INT) ENGINE = MyISAM;
--connect(con1,localhost,root,,)
--connection default
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
--connection con1
SET AUTOCOMMIT = 1;
# These should work as values are not changed
UPDATE t_permanent_aria SET col1 = 1;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_innodb SET col1 = 1;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_innodb SET col1 = 8;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_myisam SET col1 = 8;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_aria SET col1 = 8;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_aria2 SET col1 = 8;
select * from t_permanent_innodb;
select * from t_permanent_myisam;
select * from t_permanent_aria;
select * from t_permanent_aria2;
SET AUTOCOMMIT = 0;
UPDATE t_permanent_innodb SET col1 = 9;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_aria SET col1 = 9;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_myisam SET col1 = 9;
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t_permanent_aria2 SET col1 = 9;
--error ER_LOCK_WAIT_TIMEOUT
DROP TABLE t_con1_innodb;
--error ER_LOCK_WAIT_TIMEOUT
DROP TABLE t_con1_myisam;
--connection default
BACKUP STAGE END;
select * from t_permanent_innodb;
select * from t_permanent_myisam;
select * from t_permanent_aria;
select * from t_permanent_aria2;
DROP TABLE t_permanent_myisam, t_permanent_innodb, t_permanent_aria, t_permanent_aria2;
DROP TABLE t_con1_innodb, t_con1_myisam;
--disconnect con1
set global lock_wait_timeout=default;
|
