2 # Tests for various concurrency-related aspects of ALTER TABLE implemetation
4 # This test takes rather long time so let us run it only in --big-test mode
5 --source include/big_test.inc
6 # We are using some debug-only features in this test
7 --source include/have_debug.inc
8 # Also we are using SBR to check that statements are executed
10 --source include/have_binlog_format_mixed_or_statement.inc
13 # Test for Bug#25044 ALTER TABLE ... ENABLE KEYS acquires global
14 # 'opening tables' lock
16 # ALTER TABLE ... ENABLE KEYS should not acquire LOCK_open mutex for
17 # the whole its duration as it prevents other queries from execution.
19 drop table if exists t1, t2;
21 connect (addconroot, localhost, root,,);
23 create table t1 (n1 int, n2 int, n3 int,
27 create table t2 (i int);
29 # Starting from 5.1 we have runtime settable @@debug variable,
30 # which can be used for introducing delays at certain points of
31 # statement execution, so we don't need many rows in 't1' to make
32 # this test repeatable.
33 alter table t1 disable keys;
35 insert into t1 values (RAND()*1000, RAND()*1000, RAND()*1000);
38 # Later we use binlog to check the order in which statements are
39 # executed so let us reset it first.
41 set session debug="+d,sleep_alter_enable_indexes";
42 --send alter table t1 enable keys;
43 connection addconroot;
45 # This statement should not be blocked by in-flight ALTER and therefore
46 # should be executed and written to binlog before ALTER TABLE ... ENABLE KEYS
48 insert into t2 values (1);
49 # And this should wait until the end of ALTER TABLE ... ENABLE KEYS.
50 insert into t1 values (1, 1, 1);
53 set session debug="-d,sleep_alter_enable_indexes";
54 # Check that statements were executed/binlogged in correct order.
55 source include/show_binlog_events.inc;
59 disconnect addconroot;
62 --echo End of 5.0 tests
65 # Additional coverage for the main ALTER TABLE case
67 # We should be sure that table being altered is properly
68 # locked during statement execution and in particular that
69 # no DDL or DML statement can sneak in and get access to
70 # the table when real operation has already taken place
71 # but this fact has not been noted in binary log yet.
73 drop table if exists t1, t2, t3;
75 create table t1 (i int);
76 # We are going to check that statements are logged in correct order
78 set session debug="+d,sleep_alter_before_main_binlog";
79 --send alter table t1 change i c char(10) default 'Test1';
80 connect (addconroot, localhost, root,,);
81 connection addconroot;
83 insert into t1 values ();
87 --send alter table t1 change c vc varchar(100) default 'Test2';
88 connection addconroot;
90 rename table t1 to t2;
94 # And now tests for ALTER TABLE with RENAME clause. In this
95 # case target table name should be properly locked as well.
96 create table t1 (i int);
97 --send alter table t1 change i c char(10) default 'Test3', rename to t2;
98 connection addconroot;
100 insert into t2 values ();
104 --send alter table t2 change c vc varchar(100) default 'Test2', rename to t1;
105 connection addconroot;
107 rename table t1 to t3;
110 disconnect addconroot;
112 set session debug="-d,sleep_alter_before_main_binlog";
114 # Check that all statements were logged in correct order
115 source include/show_binlog_events.inc;
118 --echo End of 5.1 tests