2
# Test file for InnoDB tests that require the debug sync facility
4
--source include/have_innodb.inc
5
--source include/have_debug.inc
6
--source include/have_debug_sync.inc
7
# Save the initial number of concurrent sessions.
8
--source include/count_sessions.inc
12
--echo # Bug 42074 concurrent optimize table and
13
--echo # alter table = Assertion failed: thd->is_error()
17
DROP TABLE IF EXISTS t1;
20
--echo # Create InnoDB table
21
CREATE TABLE t1 (id INT) engine=innodb;
22
connect (con2, localhost, root);
25
--echo # Start optimizing table
27
SET DEBUG_SYNC='ha_admin_try_alter SIGNAL optimize_started WAIT_FOR table_altered';
28
--send OPTIMIZE TABLE t1
31
--echo # Change table to engine=memory
33
SET DEBUG_SYNC='now WAIT_FOR optimize_started';
34
ALTER TABLE t1 engine=memory;
35
SET DEBUG_SYNC='now SIGNAL table_altered';
38
--echo # Complete optimization
44
SET DEBUG_SYNC='RESET';
48
--echo # Bug#47459 Assertion in Diagnostics_area::set_eof_status on
49
--echo # OPTIMIZE TABLE
53
DROP TABLE IF EXISTS t1;
56
connect (con1, localhost, root);
59
CREATE TABLE t1(a INT) ENGINE= InnoDB;
61
--echo # Connection con1
63
SET DEBUG_SYNC= "ha_admin_open_ltable SIGNAL opening WAIT_FOR dropped";
65
--send OPTIMIZE TABLE t1
67
--echo # Connection default
69
SET DEBUG_SYNC= "now WAIT_FOR opening";
71
SET DEBUG_SYNC= "now SIGNAL dropped";
73
--echo # Connection con1
75
--echo # Reaping: OPTIMIZE TABLE t1
78
--echo # Connection default
81
SET DEBUG_SYNC= "RESET";
85
--echo # Bug#53757 assert in mysql_truncate_by_delete
89
DROP TABLE IF EXISTS t1, t2;
92
CREATE TABLE t1(a INT) Engine=InnoDB;
93
CREATE TABLE t2(id INT);
94
INSERT INTO t1 VALUES (1), (2);
96
connect (con1, localhost, root);
97
INSERT INTO t2 VALUES(connection_id());
98
SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed";
99
--echo # Sending: (not reaped since connection is killed later)
103
SET DEBUG_SYNC= "now WAIT_FOR opening";
104
SELECT ((@id := id) - id) FROM t2;
106
SET DEBUG_SYNC= "now SIGNAL killed";
109
--source include/wait_until_count_sessions.inc
110
SET DEBUG_SYNC= "RESET";
114
--echo # Bug#58933 Assertion `thd- >is_error()' fails on shutdown with ongoing
115
--echo # OPTIMIZE TABLE
119
DROP TABLE IF EXISTS t1;
122
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
123
INSERT INTO t1 VALUES (1), (2);
125
--echo # Connection con1
126
connect (con1,localhost,root);
127
let $ID= `SELECT connection_id()`;
128
SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL waiting WAIT_FOR killed';
130
--send OPTIMIZE TABLE t1
132
--echo # Connection default
134
SET DEBUG_SYNC= 'now WAIT_FOR waiting';
135
--replace_result $ID ID
137
SET DEBUG_SYNC= 'now SIGNAL killed';
139
--echo # Connection con1
141
--echo # Reaping: OPTIMIZE TABLE t1
144
--echo # Connection default
147
SET DEBUG_SYNC= 'RESET';
152
--echo # Bug#42230 during add index, cannot do queries on storage engines
153
--echo # that implement add_index
157
DROP DATABASE IF EXISTS db1;
158
DROP TABLE IF EXISTS t1;
161
connect(con1,localhost,root);
162
connect(con2,localhost,root);
164
--echo # Test 1: Secondary index, should not block reads (original test case).
166
--echo # Connection default
169
CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb;
170
INSERT INTO db1.t1(value) VALUES (1), (2);
171
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
173
--send ALTER TABLE db1.t1 ADD INDEX(value)
175
--echo # Connection con1
177
SET DEBUG_SYNC= "now WAIT_FOR manage";
178
# Neither of these two statements should be blocked
181
SET DEBUG_SYNC= "now SIGNAL query";
183
--echo # Connection default
185
--echo # Reaping: ALTER TABLE db1.t1 ADD INDEX(value)
189
--echo # Test 2: Primary index (implicit), should block writes.
191
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
192
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
194
--send ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED
196
--echo # Connection con1
198
SET DEBUG_SYNC= "now WAIT_FOR manage";
202
--send UPDATE t1 SET a=NULL
204
--echo # Connection con2
206
--echo # Waiting for SELECT to be blocked by the metadata lock on t1
207
let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
208
WHERE state= 'Waiting for table metadata lock'
209
AND info='UPDATE t1 SET a=NULL';
210
--source include/wait_condition.inc
211
SET DEBUG_SYNC= "now SIGNAL query";
213
--echo # Connection default
215
--echo # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a)
218
--echo # Connection con1
220
--echo # Reaping: UPDATE t1 SET a=NULL
223
--echo # Test 3: Primary index (explicit), should block writes.
225
--echo # Connection default
227
ALTER TABLE t1 DROP INDEX a;
228
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
230
--send ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED
232
--echo # Connection con1
234
SET DEBUG_SYNC= "now WAIT_FOR manage";
237
--send UPDATE t1 SET a=NULL
239
--echo # Connection con2
241
--echo # Waiting for SELECT to be blocked by the metadata lock on t1
242
let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
243
WHERE state= 'Waiting for table metadata lock'
244
AND info='UPDATE t1 SET a=NULL';
245
--source include/wait_condition.inc
246
SET DEBUG_SYNC= "now SIGNAL query";
248
--echo # Connection default
250
--echo # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
253
--echo # Connection con1
255
--echo # Reaping: UPDATE t1 SET a=NULL
258
--echo # Test 4: Secondary unique index, should not block reads.
260
--echo # Connection default
262
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
264
--send ALTER TABLE t1 ADD UNIQUE (b)
266
--echo # Connection con1
268
SET DEBUG_SYNC= "now WAIT_FOR manage";
270
SET DEBUG_SYNC= "now SIGNAL query";
272
--echo # Connection default
274
--echo # Reaping: ALTER TABLE t1 ADD UNIQUE (b)
279
SET DEBUG_SYNC= "RESET";
284
--echo # Bug#11853126 RE-ENABLE CONCURRENT READS WHILE CREATING SECONDARY INDEX
289
DROP TABLE IF EXISTS t1;
292
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
293
INSERT INTO t1 VALUES (1, 12345), (2, 23456);
295
--echo # Connection con1
296
--connect (con1,localhost,root)
297
SET SESSION debug= "+d,alter_table_rollback_new_index";
298
--error ER_UNKNOWN_ERROR
299
ALTER TABLE t1 ADD PRIMARY KEY(a);
302
--echo # Connection default
310
--echo # Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA
314
DROP TABLE IF EXISTS t1;
315
DROP DATABASE IF EXISTS db1;
318
CREATE TABLE t1(a int) engine=InnoDB;
321
connect(con1, localhost, root);
322
connect(con2, localhost, root);
324
--echo # Connection con1
326
SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue';
328
--send ALTER TABLE t1 RENAME db1.t1
330
--echo # Connection con2
332
SET DEBUG_SYNC= 'now WAIT_FOR locked';
333
--echo # DROP DATABASE db1 should now be blocked by ALTER TABLE
335
--send DROP DATABASE db1
337
--echo # Connection default
339
--echo # Check that DROP DATABASE is blocked by IX lock on db1
341
SELECT COUNT(*) = 1 FROM information_schema.processlist
342
WHERE state = "Waiting for schema metadata lock" and
343
info = "DROP DATABASE db1";
344
--source include/wait_condition.inc
345
--echo # Resume ALTER TABLE
346
SET DEBUG_SYNC= 'now SIGNAL continue';
348
--echo # Connection con1
350
--echo # Reaping: ALTER TABLE t1 RENAME db1.t1;
353
--echo # Connection con2
355
--echo # Reaping: DROP DATABASE db1
358
--echo # Connection default;
360
SET DEBUG_SYNC= 'RESET';
366
--echo # WL#5534 Online ALTER, Phase 1
369
--echo # Multi thread tests.
370
--echo # See alter_table.test for single thread tests.
373
DROP TABLE IF EXISTS t1;
376
CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB;
377
INSERT INTO t1 VALUES (1,1), (2,2);
378
SET DEBUG_SYNC= 'RESET';
379
connect (con1, localhost, root);
380
SET SESSION lock_wait_timeout= 1;
383
--echo # 1: In-place + writes blocked.
386
--echo # Connection default
388
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
389
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
390
SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3';
391
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4';
393
--send ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED
395
--echo # Connection con1;
397
SET DEBUG_SYNC= 'now WAIT_FOR opened';
398
--echo # At this point, neither reads nor writes should be blocked.
400
INSERT INTO t1 VALUES (3,3);
402
SET DEBUG_SYNC= 'now SIGNAL continue1';
403
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
404
--echo # Now both reads and writes should be blocked
405
--error ER_LOCK_WAIT_TIMEOUT
407
--error ER_LOCK_WAIT_TIMEOUT
408
INSERT INTO t1 VALUES (4,4);
410
SET DEBUG_SYNC= 'now SIGNAL continue2';
411
SET DEBUG_SYNC= 'now WAIT_FOR beforecommit';
412
--echo # Still both reads and writes should be blocked.
413
--error ER_LOCK_WAIT_TIMEOUT
415
--error ER_LOCK_WAIT_TIMEOUT
416
INSERT INTO t1 VALUES (5,5);
418
SET DEBUG_SYNC= 'now SIGNAL continue3';
419
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
421
--error ER_LOCK_WAIT_TIMEOUT
423
--error ER_LOCK_WAIT_TIMEOUT
424
INSERT INTO t1 VALUES (6,6);
426
SET DEBUG_SYNC= 'now SIGNAL continue4';
427
--echo # Connection default
429
--echo # Reaping ALTER TABLE ...
431
SET DEBUG_SYNC= 'RESET';
432
DELETE FROM t1 WHERE a= 3;
435
--echo # 2: Copy + writes blocked.
438
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
439
SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
440
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3';
442
--send ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED
444
--echo # Connection con1;
446
SET DEBUG_SYNC= 'now WAIT_FOR opened';
447
--echo # At this point, neither reads nor writes should be blocked.
449
INSERT INTO t1 VALUES (3,3);
451
SET DEBUG_SYNC= 'now SIGNAL continue1';
452
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
453
--echo # Now writes should be blocked, reads still allowed.
455
--error ER_LOCK_WAIT_TIMEOUT
456
INSERT INTO t1 VALUES (4,4);
458
SET DEBUG_SYNC= 'now SIGNAL continue2';
459
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
460
--echo # Now both reads and writes should be blocked.
461
--error ER_LOCK_WAIT_TIMEOUT
462
SELECT * FROM t1 limit 1;
463
--error ER_LOCK_WAIT_TIMEOUT
464
INSERT INTO t1 VALUES (5,5);
466
SET DEBUG_SYNC= 'now SIGNAL continue3';
467
--echo # Connection default
469
--echo # Reaping ALTER TABLE ...
471
SET DEBUG_SYNC= 'RESET';
472
DELETE FROM t1 WHERE a= 3;
475
--echo # 3: In-place + writes allowed.
478
--echo # TODO: Enable this test once WL#5526 is pushed
481
--echo # Connection default
483
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
484
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
485
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue3';
486
SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue4';
487
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue5';
489
--send ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= NONE
491
--echo # Connection con1;
493
SET DEBUG_SYNC= 'now WAIT_FOR opened';
494
--echo # At this point, neither reads nor writes should be blocked.
496
INSERT INTO t1 VALUES (3,3);
498
SET DEBUG_SYNC= 'now SIGNAL continue1';
499
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
500
--echo # Now writes should be blocked, reads still allowed.
502
--error ER_LOCK_WAIT_TIMEOUT
503
INSERT INTO t1 VALUES (4,4);
505
SET DEBUG_SYNC= 'now SIGNAL continue2';
506
SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
507
--echo # Now writes should be allowed again.
509
INSERT INTO t1 VALUES (5,5);
511
SET DEBUG_SYNC= 'now SIGNAL continue3';
512
SET DEBUG_SYNC= 'now WAIT_FOR beforecommit';
513
--echo # Now both reads and writes should be blocked.
514
--error ER_LOCK_WAIT_TIMEOUT
516
--error ER_LOCK_WAIT_TIMEOUT
517
INSERT INTO t1 VALUES (6,6);
519
SET DEBUG_SYNC= 'now SIGNAL continue4';
520
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
522
--error ER_LOCK_WAIT_TIMEOUT
524
--error ER_LOCK_WAIT_TIMEOUT
525
INSERT INTO t1 VALUES (7,7);
527
SET DEBUG_SYNC= 'now SIGNAL continue5';
528
--echo # Connection default
530
--echo # Reaping ALTER TABLE ...
532
SET DEBUG_SYNC= 'RESET';
533
DELETE FROM t1 WHERE a= 3 OR a= 4;
535
--echo # TODO: Enable this test once WL#5526 is pushed
539
--echo # 4: In-place + reads and writes blocked.
542
--echo # Connection default
544
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
545
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
546
SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3';
547
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4';
549
--send ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE
551
--echo # Connection con1;
553
SET DEBUG_SYNC= 'now WAIT_FOR opened';
554
--echo # At this point, neither reads nor writes should be blocked.
556
INSERT INTO t1 VALUES (3,3);
558
SET DEBUG_SYNC= 'now SIGNAL continue1';
559
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
560
--echo # Now both reads and writes should be blocked.
561
--error ER_LOCK_WAIT_TIMEOUT
563
--error ER_LOCK_WAIT_TIMEOUT
564
INSERT INTO t1 VALUES (4,4);
566
SET DEBUG_SYNC= 'now SIGNAL continue2';
567
SET DEBUG_SYNC= 'now WAIT_FOR beforecommit';
569
--error ER_LOCK_WAIT_TIMEOUT
571
--error ER_LOCK_WAIT_TIMEOUT
572
INSERT INTO t1 VALUES (5,5);
574
SET DEBUG_SYNC= 'now SIGNAL continue3';
575
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
577
--error ER_LOCK_WAIT_TIMEOUT
579
--error ER_LOCK_WAIT_TIMEOUT
580
INSERT INTO t1 VALUES (6,6);
582
SET DEBUG_SYNC= 'now SIGNAL continue4';
583
--echo # Connection default
585
--echo # Reaping ALTER TABLE ...
587
SET DEBUG_SYNC= 'RESET';
592
SET DEBUG_SYNC= 'RESET';
595
# Check that all connections opened by test cases in this file are really
596
# gone so execution of other tests won't be affected by their presence.
597
--source include/wait_until_count_sessions.inc