/
ddl_high_priority.inc
174 lines (124 loc) · 4.18 KB
/
ddl_high_priority.inc
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
###############################################################################
# Common test file for high priority DDL
###############################################################################
create user test_user1@localhost;
grant all on test to test_user1@localhost;
create user test_user2@localhost;
grant all on test to test_user2@localhost;
# Default values
--let $con_block = con1
--let $con_kill = default
--let $should_kill = 1
--let $recreate_table = 1
--let $throw_error = 1
##
## killing conflicting shared locks by alter table
##
--let $blocking_sql = lock tables t1 read;
--let $cmd = alter table t1 modify i bigint;
--let $high_priority_cmd = alter high_priority table t1 modify i bigint;
--source include/ddl_high_priority_module.inc
##
## killing conflicting shared lock in a transaction
## transaction will rollback
##
--let $blocking_sql = begin; insert into t1 values (4); select i from t1;
--let $cmd = alter table t1 rename t1_new;
--let $high_priority_cmd = alter high_priority table t1 rename t1_new;
--source include/ddl_high_priority_module.inc
select * from t1_new;
drop table t1_new;
##
## simulate conflicting DDL which will not be killed
##
# Simulate conflicting DDL
# This will hold MDL_SHARED_NO_READ_WRITE, which may be upgraded to exclusive
# locks to run DDLs like ALTER TABLE
# the upgradable/exclusive lock should not be killed
--let $should_kill = 0
--let $blocking_sql = lock tables t1 write;
--let $cmd = drop table t1;
--let $high_priority_cmd = drop high_priority table t1;
--source include/ddl_high_priority_module.inc
# restore $should_kill
--let $should_kill = 1
##
## killing conflicting transaction by drop table DDL
##
--let $blocking_sql = lock tables t1 read; begin; insert into t1 values (4);
--let $cmd = drop table t1;
--let $high_priority_cmd = drop high_priority table t1;
--source include/ddl_high_priority_module.inc
##
## no effect for regular users
##
connect (con2,localhost,test_user2,,test,,);
# $con_kill is regular user
--let $con_kill = con2
--let $should_kill = 0
--let $blocking_sql = lock tables t1 read;
--let $cmd = alter table t1 modify i bigint;
--let $high_priority_cmd = alter high_priority table t1 modify i bigint;
--source include/ddl_high_priority_module.inc
disconnect con2;
# restore $con_kill
--let $con_kill = default
# restore $should_kill
--let $should_kill = 1
##
## create/drop index
##
# create index
--let $blocking_sql = lock tables t1 read;
--let $cmd = create index idx1 on t1 (i);
--let $high_priority_cmd = create high_priority index idx1 on t1 (i);
--source include/ddl_high_priority_module.inc
# drop index (use the previously created table)
--let $recreate_table = 0
--let $cmd = drop index idx1 on t1;
--let $high_priority_cmd = drop high_priority index idx1 on t1;
--source include/ddl_high_priority_module.inc
# restore $recreate_table
--let $recreate_table = 1
##
## high_priority truncate table
##
--let $blocking_sql = lock tables t1 read;
--let $cmd = truncate t1;
--let $high_priority_cmd = truncate high_priority t1;
--source include/ddl_high_priority_module.inc
##
## high_priority create/drop trigger
##
--let $blocking_sql = lock tables t1 read;
--let $cmd = create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;
--let $high_priority_cmd = create high_priority trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;
--source include/ddl_high_priority_module.inc
# drop trigger (use the previously created table)
--let $recreate_table = 0
--let $cmd = drop trigger ins_sum;
--let $high_priority_cmd = drop high_priority trigger ins_sum;
--source include/ddl_high_priority_module.inc
# restore $recreate_table
--let $recreate_table = 1
##
## high_priority optimize table
##
## "optimize table" doesn't throw errors. It catches all errors, and
## returns a result set in a table
##
--let $throw_error = 0
--let $blocking_sql = lock tables t1 read;
--let $cmd = optimize table t1;
--let $high_priority_cmd = optimize high_priority table t1;
--source include/ddl_high_priority_module.inc
# restore throw_error
--let $throw_error = 1
##
## clean up
##
drop user test_user1@localhost;
drop user test_user2@localhost;
--disable_warnings
drop table if exists t1;
--enable_warnings