34
36
# What happens in situation when other statement messes with
35
37
# table to be created before it is created ?
36
38
# Concurrent CREATE TABLE
37
set session debug="+d,sleep_create_select_before_create";
39
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
38
40
--send create table t1 select 1 as i;
39
41
connection addconroot1;
42
set debug_sync='now WAIT_FOR parked';
43
--send create table t1 (j char(5));
44
connection addconroot2;
46
select count(*) = 1 from information_schema.processlist
47
where state = "Waiting for table metadata lock" and
48
info = "create table t1 (j char(5))";
49
--source include/wait_condition.inc
50
set debug_sync='now SIGNAL go';
53
connection addconroot1;
41
54
--error ER_TABLE_EXISTS_ERROR
42
create table t1 (j char(5));
43
56
connection default;
45
57
show create table t1;
47
60
# Concurrent CREATE TABLE ... SELECT
61
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
48
62
--send create table t1 select 1 as i;
49
63
connection addconroot1;
64
set debug_sync='now WAIT_FOR parked';
65
--send create table t1 select 'Test' as j;
66
connection addconroot2;
67
# Wait until the above CREATE TABLE t1 is blocked due to CREATE
69
select count(*) = 1 from information_schema.processlist
70
where state = "Waiting for table metadata lock" and
71
info = "create table t1 select 'Test' as j";
72
--source include/wait_condition.inc
73
set debug_sync='now SIGNAL go';
76
connection addconroot1;
51
77
--error ER_TABLE_EXISTS_ERROR
52
create table t1 select "Test" as j;
53
79
connection default;
55
80
show create table t1;
57
83
# Concurrent CREATE TABLE LIKE
58
84
create table t3 (j char(5));
85
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
59
86
--send create table t1 select 1 as i;
60
87
connection addconroot1;
88
set debug_sync='now WAIT_FOR parked';
89
--send create table t1 like t3;
90
connection addconroot2;
91
# Wait until the above CREATE TABLE t1 is blocked due to CREATE
93
select count(*) = 1 from information_schema.processlist
94
where state = "Waiting for table metadata lock" and
95
info = "create table t1 like t3";
96
--source include/wait_condition.inc
97
set debug_sync='now SIGNAL go';
100
connection addconroot1;
62
101
--error ER_TABLE_EXISTS_ERROR
63
create table t1 like t3;
64
103
connection default;
66
104
show create table t1;
68
107
# Concurrent RENAME TABLE
108
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
69
109
--send create table t1 select 1 as i;
70
110
connection addconroot1;
111
set debug_sync='now WAIT_FOR parked';
112
--send rename table t3 to t1;
113
connection addconroot2;
114
# Wait until the above RENAME TABLE is blocked due to CREATE
116
select count(*) = 1 from information_schema.processlist
117
where state = "Waiting for table metadata lock" and
118
info = "rename table t3 to t1";
119
--source include/wait_condition.inc
120
set debug_sync='now SIGNAL go';
123
connection addconroot1;
72
124
--error ER_TABLE_EXISTS_ERROR
73
rename table t3 to t1;
74
126
connection default;
76
127
show create table t1;
78
130
# Concurrent ALTER TABLE RENAME
131
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
79
132
--send create table t1 select 1 as i;
80
133
connection addconroot1;
134
set debug_sync='now WAIT_FOR parked';
82
135
--error ER_TABLE_EXISTS_ERROR
83
136
alter table t3 rename to t1;
137
set debug_sync='now SIGNAL go';
84
138
connection default;
86
141
show create table t1;
88
144
# Concurrent ALTER TABLE RENAME which also adds column
145
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
89
146
--send create table t1 select 1 as i;
90
147
connection addconroot1;
148
set debug_sync='now WAIT_FOR parked';
92
149
--error ER_TABLE_EXISTS_ERROR
93
150
alter table t3 rename to t1, add k int;
151
set debug_sync='now SIGNAL go';
94
152
connection default;
96
154
show create table t1;
98
157
# What happens if other statement sneaks in after the table
99
158
# creation but before its opening ?
100
set session debug="-d,sleep_create_select_before_create:+d,sleep_create_select_before_open";
159
set debug_sync='create_table_select_before_open SIGNAL parked WAIT_FOR go';
101
162
# Concurrent DROP TABLE
163
set debug_sync='create_table_select_before_open SIGNAL parked WAIT_FOR go';
102
164
--send create table t1 select 1 as i;
103
165
connection addconroot1;
166
set debug_sync='now WAIT_FOR parked';
167
--send drop table t1;
168
connection addconroot2;
169
# Wait until the above DROP TABLE is blocked due to CREATE
171
select count(*) = 1 from information_schema.processlist
172
where state = "Waiting for table metadata lock" and
173
info = "drop table t1";
174
--source include/wait_condition.inc
175
set debug_sync='now SIGNAL go';
178
connection addconroot1;
108
182
# Concurrent RENAME TABLE
183
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
109
184
--send create table t1 select 1 as i;
110
185
connection addconroot1;
112
rename table t1 to t2;
186
set debug_sync='now WAIT_FOR parked';
187
--send rename table t1 to t2;
188
connection addconroot2;
189
# Wait until the above RENAME TABLE is blocked due to CREATE
191
select count(*) = 1 from information_schema.processlist
192
where state = "Waiting for table metadata lock" and
193
info = "rename table t1 to t2";
194
--source include/wait_condition.inc
195
set debug_sync='now SIGNAL go';
198
connection addconroot1;
116
203
# Concurrent SELECT
204
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
117
205
--send create table t1 select 1 as i;
118
206
connection addconroot1;
207
set debug_sync='now WAIT_FOR parked';
208
--send select * from t1;
209
connection addconroot2;
210
# Wait until the above SELECT is blocked due to CREATE
212
select count(*) = 1 from information_schema.processlist
213
where state = "Waiting for table metadata lock" and
214
info = "select * from t1";
215
--source include/wait_condition.inc
216
set debug_sync='now SIGNAL go';
219
connection addconroot1;
124
224
# Concurrent INSERT
225
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
125
226
--send create table t1 select 1 as i;
126
227
connection addconroot1;
128
insert into t1 values (2);
228
set debug_sync='now WAIT_FOR parked';
229
--send insert into t1 values (2);
230
connection addconroot2;
231
# Wait until the above INSERT is blocked due to CREATE
233
select count(*) = 1 from information_schema.processlist
234
where state = "Waiting for table metadata lock" and
235
info = "insert into t1 values (2)";
236
--source include/wait_condition.inc
237
set debug_sync='now SIGNAL go';
240
connection addconroot1;
131
243
select * from t1;
133
246
# Concurrent CREATE TRIGGER
248
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
135
249
--send create table t1 select 1 as i;
136
250
connection addconroot1;
138
create trigger t1_bi before insert on t1 for each row set @a:=1;
251
set debug_sync='now WAIT_FOR parked';
252
--send create trigger t1_bi before insert on t1 for each row set @a:=1;
253
connection addconroot2;
254
# Wait until the above CREATE TRIGGER is blocked due to CREATE TABLE
256
select count(*) = 1 from information_schema.processlist
257
where state = "Waiting for table metadata lock" and
258
info = "create trigger t1_bi before insert on t1 for each row set @a:=1";
259
--source include/wait_condition.inc
260
set debug_sync='now SIGNAL go';
263
connection addconroot1;
143
269
# Okay, now the same tests for the potential gap between open and lock
144
set session debug="-d,sleep_create_select_before_open:+d,sleep_create_select_before_lock";
270
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
145
272
# Concurrent DROP TABLE
146
273
--send create table t1 select 1 as i;
147
274
connection addconroot1;
275
set debug_sync='now WAIT_FOR parked';
276
--send drop table t1;
277
connection addconroot2;
278
# Wait until the above DROP TABLE is blocked due to CREATE TABLE
280
select count(*) = 1 from information_schema.processlist
281
where state = "Waiting for table metadata lock" and
282
info = "drop table t1";
283
--source include/wait_condition.inc
284
set debug_sync='now SIGNAL go';
287
connection addconroot1;
152
291
# Concurrent RENAME TABLE
292
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
153
293
--send create table t1 select 1 as i;
154
294
connection addconroot1;
156
rename table t1 to t2;
295
set debug_sync='now WAIT_FOR parked';
296
--send rename table t1 to t2;
297
connection addconroot2;
298
# Wait until the above RENAME TABLE is blocked due to CREATE TABLE
300
select count(*) = 1 from information_schema.processlist
301
where state = "Waiting for table metadata lock" and
302
info = "rename table t1 to t2";
303
--source include/wait_condition.inc
304
set debug_sync='now SIGNAL go';
307
connection addconroot1;
160
312
# Concurrent SELECT
313
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
161
314
--send create table t1 select 1 as i;
162
315
connection addconroot1;
316
set debug_sync='now WAIT_FOR parked';
317
--send select * from t1;
318
connection addconroot2;
319
# Wait until the above SELECT is blocked due to CREATE TABLE
321
select count(*) = 1 from information_schema.processlist
322
where state = "Waiting for table metadata lock" and
323
info = "select * from t1";
324
--source include/wait_condition.inc
325
set debug_sync='now SIGNAL go';
328
connection addconroot1;
168
333
# Concurrent INSERT
334
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
169
335
--send create table t1 select 1 as i;
170
336
connection addconroot1;
172
insert into t1 values (2);
337
set debug_sync='now WAIT_FOR parked';
338
--send insert into t1 values (2);
339
connection addconroot2;
340
# Wait until the above INSERT INTO t1 is blocked due to CREATE TABLE
342
select count(*) = 1 from information_schema.processlist
343
where state = "Waiting for table metadata lock" and
344
info = "insert into t1 values (2)";
345
--source include/wait_condition.inc
346
set debug_sync='now SIGNAL go';
349
connection addconroot1;
175
352
select * from t1;
177
355
# Concurrent CREATE TRIGGER
357
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
179
358
--send create table t1 select 1 as i;
180
359
connection addconroot1;
182
create trigger t1_bi before insert on t1 for each row set @a:=1;
360
set debug_sync='now WAIT_FOR parked';
361
--send create trigger t1_bi before insert on t1 for each row set @a:=1;
362
connection addconroot2;
363
# Wait until the above CREATE TRIGGER is blocked due to CREATE TABLE
365
select count(*) = 1 from information_schema.processlist
366
where state = "Waiting for table metadata lock" and
367
info = "create trigger t1_bi before insert on t1 for each row set @a:=1";
368
--source include/wait_condition.inc
369
set debug_sync='now SIGNAL go';
372
connection addconroot1;
187
# Some tests for case with existing table
188
set session debug="-d,sleep_create_select_before_lock:+d,sleep_create_select_before_check_if_exists";
189
create table t1 (i int);
190
378
# Concurrent DROP TABLE
379
set debug_sync='create_table_select_before_check_if_exists SIGNAL parked WAIT_FOR go';
191
380
--send create table if not exists t1 select 1 as i;
192
381
connection addconroot1;
197
# Concurrent CREATE TRIGGER
382
set debug_sync='now WAIT_FOR parked';
383
--send drop table t1;
384
connection addconroot2;
385
# Wait until the above DROP TABLE is blocked due to CREATE TABLE
387
select count(*) = 1 from information_schema.processlist
388
where state = "Waiting for table metadata lock" and
389
info = "drop table t1";
390
--source include/wait_condition.inc
391
set debug_sync='now SIGNAL go';
394
connection addconroot1;
398
# Concurrent CREATE TRIGGER
198
399
create table t1 (i int);
401
set debug_sync='create_table_select_before_check_if_exists SIGNAL parked WAIT_FOR go';
200
402
--send create table if not exists t1 select 1 as i;
201
403
connection addconroot1;
203
404
create trigger t1_bi before insert on t1 for each row set @a:=1;
204
405
connection default;
207
409
select * from t1;
209
set session debug="-d,sleep_create_select_before_check_if_exists";
212
# Test for some details of CREATE TABLE ... SELECT implementation.
214
# We check that create placeholder is handled properly if we have
215
# to reopen tables in open_tables().
216
# This test heavily relies on current implementation of name-locking/
217
# table cache so it may stop working if it changes. OTOH it such problem
218
# will serve as warning that such changes should not be done lightly.
219
create table t2 (a int);
220
create table t4 (b int);
221
connection addconroot2;
224
connection addconroot1;
225
# Create placeholder/name-lock for t3
226
--send create table t3 as select * from t4;
229
# This statement creates placeholder for t1, then opens t2,
230
# then meets name-lock for t3 and then reopens all tables
231
--send create table t1 select * from t2, t3;
233
connection addconroot2;
235
connection addconroot1;
240
show create table t1;
242
# Now similar test which proves that we really temporarily
243
# remove placeholder when we reopen tables.
244
connection addconroot2;
247
connection addconroot1;
248
# Create name-lock for t3
249
--send rename table t4 to t3;
252
# This statement creates placeholder for t1, then opens t2,
253
# then meets name-lock for t3 and then reopens all tables
254
--send create table if not exists t1 select 1 as i from t2, t3;
256
connection addconroot3;
257
# We should be able to take name-lock on table t1 as we should not have
258
# open placeholder for it at this point (otherwise it is possible to
259
# come-up with situation which will lead to deadlock, e.g. think of
260
# concurrent CREATE TABLE t1 SELECT * FROM t2 and RENAME TABLE t2 TO t1)
261
create table t5 (j int);
262
# This statement takes name-lock on t1 and therefore proves
263
# that there is no active open placeholder for it.
264
rename table t5 to t1;
265
connection addconroot2;
267
connection addconroot1;
272
show create table t1;
273
drop table t1, t2, t3;
276
412
# Tests for possible concurrency issues with CREATE TABLE ... LIKE
286
422
--disable_warnings
287
423
drop table if exists t1,t2;
288
424
--enable_warnings
425
set debug_sync='RESET';
290
427
# What happens if some statements sneak in right after we have
291
# opened source table ?
428
# acquired locks and opened source table ?
292
429
create table t1 (i int);
293
set session debug="+d,sleep_create_like_before_check_if_exists";
430
set debug_sync='create_table_like_after_open SIGNAL parked WAIT_FOR go';
294
431
# Reset binlog to have clear start
296
433
--send create table t2 like t1;
297
434
connection addconroot1;
435
set debug_sync='now WAIT_FOR parked';
299
436
# DML on source table should be allowed to run concurrently
300
437
insert into t1 values (1);
301
438
# And DDL should wait
439
--send drop table t1;
440
connection addconroot2;
442
select count(*) = 1 from information_schema.processlist
443
where state = "Waiting for table metadata lock" and
444
info = "drop table t1";
445
--source include/wait_condition.inc
446
set debug_sync='now SIGNAL go';
449
connection addconroot1;
305
452
show create table t2;
307
454
# Let us check that statements were executed/binlogged in correct order
308
455
source include/show_binlog_events.inc;
310
# Now let us check the gap between check for target table
311
# existance and copying of .frm file.
312
create table t1 (i int);
313
set session debug="-d,sleep_create_like_before_check_if_exists:+d,sleep_create_like_before_copy";
314
# It should be impossible to create target table concurrently
315
--send create table t2 like t1;
316
connection addconroot1;
318
create table if not exists t2 (j int);
321
show create table t2;
323
# And concurrent DDL on the source table should be still disallowed
325
--send create table t2 like t1;
326
connection addconroot1;
332
source include/show_binlog_events.inc;
334
# And now he gap between copying of .frm file and ha_create_table() call.
335
create table t1 (i int);
336
set session debug="-d,sleep_create_like_before_copy:+d,sleep_create_like_before_ha_create";
337
# Both DML and DDL on target table should wait till operation completes
339
--send create table t2 like t1;
340
connection addconroot1;
342
insert into t2 values (1);
346
--send create table t2 like t1;
347
connection addconroot1;
352
# Concurrent DDL on the source table still waits
353
--send create table t2 like t1;
354
connection addconroot1;
360
source include/show_binlog_events.inc;
362
# Finally we check the gap between ha_create_table() and binlogging
363
create table t1 (i int);
364
set session debug="-d,sleep_create_like_before_ha_create:+d,sleep_create_like_before_binlogging";
366
--send create table t2 like t1;
367
connection addconroot1;
369
insert into t2 values (1);
373
--send create table t2 like t1;
374
connection addconroot1;
379
--send create table t2 like t1;
380
connection addconroot1;
386
source include/show_binlog_events.inc;
388
set session debug="-d,sleep_create_like_before_binlogging";
457
# Now check the gap between table creation and binlogging
458
create table t1 (i int);
459
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
461
--send create table t2 like t1;
462
connection addconroot1;
463
set debug_sync='now WAIT_FOR parked';
464
--send insert into t2 values (1);
465
connection addconroot2;
467
select count(*) = 1 from information_schema.processlist
468
where state = "Waiting for table metadata lock" and
469
info = "insert into t2 values (1)";
470
--source include/wait_condition.inc
471
set debug_sync='now SIGNAL go';
475
connection addconroot1;
479
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
480
--send create table t2 like t1;
481
connection addconroot1;
482
set debug_sync='now WAIT_FOR parked';
483
--send drop table t2;
484
connection addconroot2;
486
select count(*) = 1 from information_schema.processlist
487
where state = "Waiting for table metadata lock" and
488
info = "drop table t2";
489
--source include/wait_condition.inc
490
set debug_sync='now SIGNAL go';
493
connection addconroot1;
496
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
497
--send create table t2 like t1;
498
connection addconroot1;
499
set debug_sync='now WAIT_FOR parked';
500
--send drop table t1;
501
connection addconroot2;
503
select count(*) = 1 from information_schema.processlist
504
where state = "Waiting for table metadata lock" and
505
info = "drop table t1";
506
--source include/wait_condition.inc
507
set debug_sync='now SIGNAL go';
510
connection addconroot1;
514
disconnect addconroot1;
515
disconnect addconroot2;
516
disconnect addconroot3;
518
set debug_sync='RESET';
519
source include/show_binlog_events.inc;
520
# Check that all connections opened by test cases in this file are really
521
# gone so execution of other tests won't be affected by their presence.
522
--source include/wait_until_count_sessions.inc