summaryrefslogtreecommitdiff
path: root/mysql-test/main/backup_interaction.result
blob: a6fe7cf448de080a0bf99103eaac167161bab073 (plain)
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
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
#
# Check backup and FTWRL
#
flush tables with read lock;
backup stage start;
ERROR HY000: Can't execute the query because you have a conflicting read lock
unlock tables;
backup stage start;
flush tables with read lock;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
backup stage end;
#
# Check backup and FLUSH TABLES
#
flush tables;
backup stage start;
flush tables;
backup stage end;
#
# Check BACKUP STAGE under lock tables
#
create table t1 (a int);
lock table t1 write;
backup stage start;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
backup stage end;
ERROR HY000: You must start backup with "BACKUP STAGE START"
unlock tables;
lock table t1 read;
backup stage start;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
backup stage end;
ERROR HY000: You must start backup with "BACKUP STAGE START"
unlock tables;
#
# Check lock tables under BACKUP STAGE
#
backup stage start;
unlock tables;
select lock_mode from information_schema.metadata_lock_info where thread_id>0;
lock_mode
MDL_BACKUP_START
lock table t1 write;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
lock table t1 read;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
unlock tables;
backup stage end;
drop table t1;
#
# Check setting readonly under BACKUP STAGE
#
backup stage start;
set @@global.read_only=1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
backup stage end;
# also make sure going back from read-only mode is not allowed
set @@global.read_only=1;
backup stage start;
set @@global.read_only=0;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
backup stage end;
set @@global.read_only=0;
#
# Check BACKUP STAGE under read_only
#
set @@global.read_only=1;
backup stage start;
backup stage end;
set @@global.read_only=0;
#
# Check that we can't create tables during backup
#
backup stage start;
create table t1 (a int);
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
backup stage end;
# also make sure we can't write to a table during backup
create table t1(a INT);
backup stage start;
insert into t1 values(1);
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
insert delayed into t1 values(1);
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
update t1 set a=1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
delete from t1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
truncate table t1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
drop table t1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
backup stage end;
drop table t1;
#
# BACKUP STAGE performs implicit commits
#
create table t1(a int) engine=InnoDB;
begin;
insert into t1 values(1);
select lock_mode from information_schema.metadata_lock_info where thread_id>0;
lock_mode
MDL_SHARED_WRITE
backup stage start;
select lock_mode from information_schema.metadata_lock_info where thread_id>0;
lock_mode
MDL_BACKUP_START
backup stage block_commit;
commit;
backup stage end;
drop table t1;
# Ensure that BACKUP STAGE ... does AUTOCOMMIT like most DDL.
# Sideeffect:
# Show the impact of not yet committed INSERT before sequence start
# and ROLLBACK sliding through the sequence.
CREATE TABLE t1 (col1 INT) ENGINE = InnoDB;
SET AUTOCOMMIT = 0;
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
ROLLBACK;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
ROLLBACK;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
ROLLBACK;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
ROLLBACK;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
ROLLBACK;
SELECT COUNT(*) = 5 AS expect_1 FROM t1;
expect_1
1
# Show the impact of not yet committed INSERT before sequence start
# and a COMMIT sliding through the sequence.
SET AUTOCOMMIT = 0;
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
COMMIT;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
COMMIT;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
COMMIT;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
COMMIT;
BACKUP STAGE END;
#----
INSERT INTO t1 SET col1 = 1;
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
COMMIT;
SELECT COUNT(*) = 10 AS expect_1 FROM t1;
expect_1
1
DELETE FROM t1;
COMMIT;
drop table t1;
#
# CHECK: RO transaction under BACKUP STAGE is a potential deadlock
# OTOH we most probably allow them under FTWRL as well
#
CREATE TABLE t1 (col1 INT) ENGINE = InnoDB;
insert into t1 values (1);
backup stage start;
backup stage block_commit;
begin;
select * from t1;
col1
1
select lock_mode from information_schema.metadata_lock_info where thread_id>0;
lock_mode
MDL_BACKUP_WAIT_COMMIT
MDL_SHARED_READ
backup stage end;
select lock_mode from information_schema.metadata_lock_info where thread_id>0;
lock_mode
drop table t1;
#
# Check that handler are closed by backup stage block_ddl
#
create table t1 (a int, key a (a));
insert into t1 (a) values (1), (2), (3), (4), (5);
handler t1 open;
handler t1 read a prev;
a
5
backup stage start;
handler t1 read a prev;
a
4
backup stage flush;
backup stage block_ddl;
handler t1 read a prev;
a
5
backup stage block_commit;
handler t1 read a prev;
a
4
backup stage end;
handler t1 close;
drop table t1;
# Show the fate and impact of some SELECT /HANDLER ... READ
# sliding through the sequence.
CREATE TABLE t1_innodb (col1 INT) ENGINE = InnoDB;
INSERT INTO t1_innodb values (1),(2),(3);
COMMIT;
CREATE TABLE t1_myisam (col1 INT) ENGINE = MyISAM;
INSERT INTO t1_myisam values (1),(2),(3);
BACKUP STAGE START;
SELECT COUNT(*) FROM t1_innodb;
COUNT(*)
3
SELECT * FROM t1_innodb;
col1
1
2
3
HANDLER t1_innodb OPEN;
HANDLER t1_innodb READ FIRST;
col1
1
HANDLER t1_innodb CLOSE;
SELECT COUNT(*) FROM t1_myisam;
COUNT(*)
3
HANDLER t1_myisam OPEN;
HANDLER t1_myisam READ FIRST;
col1
1
HANDLER t1_myisam CLOSE;
BACKUP STAGE FLUSH;
SELECT COUNT(*) FROM t1_innodb;
COUNT(*)
3
HANDLER t1_innodb OPEN;
HANDLER t1_innodb READ FIRST;
col1
1
HANDLER t1_innodb CLOSE;
SELECT COUNT(*) FROM t1_myisam;
COUNT(*)
3
HANDLER t1_myisam OPEN;
HANDLER t1_myisam READ FIRST;
col1
1
HANDLER t1_myisam CLOSE;
BACKUP STAGE BLOCK_DDL;
SELECT COUNT(*) FROM t1_innodb;
COUNT(*)
3
HANDLER t1_innodb OPEN;
HANDLER t1_innodb READ FIRST;
col1
1
HANDLER t1_innodb CLOSE;
SELECT COUNT(*) FROM t1_myisam;
COUNT(*)
3
HANDLER t1_myisam OPEN;
HANDLER t1_myisam READ FIRST;
col1
1
HANDLER t1_myisam CLOSE;
BACKUP STAGE BLOCK_COMMIT;
SELECT COUNT(*) FROM t1_innodb;
COUNT(*)
3
HANDLER t1_innodb OPEN;
HANDLER t1_innodb READ FIRST;
col1
1
HANDLER t1_innodb CLOSE;
SELECT COUNT(*) FROM t1_myisam;
COUNT(*)
3
HANDLER t1_myisam OPEN;
HANDLER t1_myisam READ FIRST;
col1
1
HANDLER t1_myisam CLOSE;
BACKUP STAGE END;
drop table t1_innodb,t1_myisam;
# Show the fate and impact of some SET GLOBAL tx_read_only = 1/0
# sliding through the sequence.
BACKUP STAGE START;
SET GLOBAL tx_read_only = 1;
SET GLOBAL tx_read_only = 0;
BACKUP STAGE FLUSH;
SET GLOBAL tx_read_only = 1;
SET GLOBAL tx_read_only = 0;
BACKUP STAGE BLOCK_DDL;
SET GLOBAL tx_read_only = 1;
SET GLOBAL tx_read_only = 0;
BACKUP STAGE BLOCK_COMMIT;
SET GLOBAL tx_read_only = 1;
SET GLOBAL tx_read_only = 0;
BACKUP STAGE END;
# Show the fate and impact of some SET SESSION sql_log_bin = 0/1
# sliding through the sequence.
COMMIT;
SET SESSION sql_log_bin = 1;
BACKUP STAGE START;
SET SESSION sql_log_bin = 0;
SET SESSION sql_log_bin = 1;
BACKUP STAGE FLUSH;
SET SESSION sql_log_bin = 0;
SET SESSION sql_log_bin = 1;
BACKUP STAGE BLOCK_DDL;
SET SESSION sql_log_bin = 0;
SET SESSION sql_log_bin = 1;
BACKUP STAGE BLOCK_COMMIT;
SET SESSION sql_log_bin = 0;
SET SESSION sql_log_bin = 1;
BACKUP STAGE END;
#----
SET SESSION sql_log_bin = 0;
BACKUP STAGE START;
SET SESSION sql_log_bin = 1;
SET SESSION sql_log_bin = 0;
BACKUP STAGE FLUSH;
SET SESSION sql_log_bin = 1;
SET SESSION sql_log_bin = 0;
BACKUP STAGE BLOCK_DDL;
SET SESSION sql_log_bin = 1;
SET SESSION sql_log_bin = 0;
BACKUP STAGE BLOCK_COMMIT;
SET SESSION sql_log_bin = 1;
SET SESSION sql_log_bin = 0;
BACKUP STAGE END;
SET SESSION sql_log_bin = 1;
#-----------------------------------------------------------------------
# BACKUP STAGE statements are not allowed in stored routines
#-----------------------------------------------------------------------
CREATE TABLE t1 (col1 INT);
CREATE PROCEDURE p1()
BEGIN
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
END|
ERROR 0A000: BACKUP STAGE is not allowed in stored procedures
CREATE FUNCTION f1 (s CHAR(20)) RETURNS INT DETERMINISTIC
BEGIN
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
RETURN 1;
END|
ERROR 0A000: BACKUP STAGE is not allowed in stored procedures
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
BACKUP STAGE START;
BACKUP STAGE FLUSH;
BACKUP STAGE BLOCK_DDL;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
END|
ERROR 0A000: BACKUP STAGE is not allowed in stored procedures
DROP TABLE t1;
#-----------------------------------------------------------------------
# Check BACKUP status variables
#-----------------------------------------------------------------------
SET SESSION lock_wait_timeout = 1;
FLUSH STATUS;
# Show how the status variable 'Com_backup' changes after BACKUP STAGE ..
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	0
BACKUP STAGE START;
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	1
BACKUP STAGE START;
ERROR HY000: Backup stage 'START' is same or before current backup stage 'START'
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	2
BACKUP STAGE FLUSH;
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	3
BACKUP STAGE BLOCK_DDL;
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	4
BACKUP STAGE BLOCK_COMMIT;
SHOW STATUS LIKE 'Com_backup';
Variable_name	Value
Com_backup	5
BACKUP STAGE END;
# In case the backup lock is taken by the current connection than
# - DML modifying some permanent table is not allowed
CREATE TABLE t1_innodb (col1 INT) ENGINE = InnoDB;
CREATE TABLE t1_myisam (col1 INT) ENGINE = MyISAM;
BACKUP STAGE START;
SET AUTOCOMMIT = 0;
INSERT INTO t1_innodb SET col1 = 1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
SET AUTOCOMMIT = 1;
INSERT INTO t1_innodb SET col1 = 1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
INSERT INTO t1_myisam SET col1 = 1;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
# - DDL creating or renaming a permanent table or a procedure etc.
#   is not allowed.                                                               
CREATE TABLE throw_away (col1 INT) ENGINE = InnoDB;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
RENAME TABLE t1_innodb To throw_away;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
CREATE PROCEDURE p1() SELECT 13;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
CREATE PROCEDURE p1() SELECT 13;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
BACKUP STAGE END;
DROP TABLE t1_innodb;
DROP TABLE t1_myisam;
#
# Creating and modifying TEMPORARY TABLES are allowed
#
BACKUP STAGE START;
BACKUP STAGE BLOCK_DDL;
CREATE TEMPORARY TABLE tmp (col1 INT);
DROP TEMPORARY TABLE tmp;
CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE t_temporary_myisam (col1 INT) ENGINE = MyISAM;
# - DML modifying that temporary table is allowed.
INSERT INTO t_temporary_innodb SET col1 = 1;
SELECT COUNT(*) FROM t_temporary_innodb;
COUNT(*)
1
INSERT INTO t_temporary_myisam SET col1 = 1;
SELECT COUNT(*) FROM t_temporary_myisam;
COUNT(*)
1
BACKUP STAGE END;
# Show the fate and impact of some auto committed INSERT into temporary
# table sliding through the sequence.
SET AUTOCOMMIT = 1;
BACKUP STAGE START;
INSERT INTO t_temporary_innodb SET col1 = 1;
INSERT INTO t_temporary_myisam SET col1 = 1;
BACKUP STAGE FLUSH;
INSERT INTO t_temporary_innodb SET col1 = 1;
INSERT INTO t_temporary_myisam SET col1 = 1;
BACKUP STAGE BLOCK_DDL;
INSERT INTO t_temporary_innodb SET col1 = 1;
INSERT INTO t_temporary_myisam SET col1 = 1;
BACKUP STAGE BLOCK_COMMIT;
INSERT INTO t_temporary_innodb SET col1 = 1;
INSERT INTO t_temporary_myisam SET col1 = 1;
BACKUP STAGE END;
SELECT COUNT(*) FROM t_temporary_innodb;
COUNT(*)
5
# Show the fate and impact of some DROP/CREATE TEMPORARY TABLE sliding
# through the sequence.
SET AUTOCOMMIT = 1;
BACKUP STAGE START;
DROP TEMPORARY TABLE t_temporary_innodb;
CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB;
BACKUP STAGE FLUSH;
DROP TEMPORARY TABLE t_temporary_innodb;
CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB;
BACKUP STAGE BLOCK_DDL;
DROP TEMPORARY TABLE t_temporary_innodb;
CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB;
BACKUP STAGE BLOCK_COMMIT;
DROP TEMPORARY TABLE t_temporary_innodb;
CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB;
BACKUP STAGE END;
# Show that even more DDL on the temporary table is allowed.
BACKUP STAGE START;
TRUNCATE t_temporary_innodb;
ALTER TABLE t_temporary_innodb ADD COLUMN col2 INT;
ALTER TABLE t_temporary_innodb ADD KEY idx(col2);
BACKUP STAGE END;
CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, f INT);
BACKUP STAGE START;
FLUSH TABLE t1 FOR EXPORT;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
FLUSH TABLE t1 WITH READ LOCK;
ERROR HY000: Can't execute the command as you have a BACKUP STAGE active
BACKUP STAGE END;
DROP TABLE t1;