Skip to content

Commit 958add0

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#15 DDL FAST FAIL
Summary: -------- The feature provides a way to set lock wait timeout for ddl statements. Usage: ----- alter table t [wait [n]|no_wait] add f int, drop table [wait [n]|no_wait], etc. no_wait: the query not wait for locks, return immediately, and print "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction;" wait [n]: n is the wait time in second, after wait n seconds for locks, return and print errors;
1 parent ac2f202 commit 958add0

File tree

5 files changed

+428
-17
lines changed

5 files changed

+428
-17
lines changed

‎mysql-test/suite/rds/r/perf_ddl_fast_fail_issue15.result‎

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
1+
set autocommit= 0;
2+
create table test.t1(id int, name varchar(100)) engine=innodb;
3+
create index t1_ind_1 on test.t1(id);
4+
insert into test.t1 values(1, 'test');
5+
create table test.t2(id int, name varchar(100)) engine=myisam;
6+
create index t2_ind_1 on test.t2(id);
7+
insert into test.t2 values(1, 'test');
8+
commit;
9+
select * from test.t1;
10+
id name
11+
1 test
12+
select * from test.t2;
13+
id name
14+
1 test
15+
alter table test.t1 no_wait add extra varchar(10);
16+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
17+
alter table test.t1 no_wait rename to test.r1;
18+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
19+
alter table test.t1 no_wait add primary key(id);
20+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
21+
alter table test.t2 no_wait add extra varchar(10);
22+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
23+
alter table test.t2 no_wait rename to test.r2;
24+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
25+
alter table test.t2 no_wait add primary key(id);
26+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
27+
drop table test.t1 no_wait;
28+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
29+
truncate table test.t1 no_wait;
30+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
31+
optimize table test.t1 no_wait;
32+
Table Op Msg_type Msg_text
33+
test.t1 optimize Error Lock wait timeout exceeded; try restarting transaction
34+
test.t1 optimize status Operation failed
35+
rename table test.t1 no_wait to test.r1;
36+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
37+
drop table test.t2 no_wait;
38+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
39+
truncate table test.t2 no_wait;
40+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
41+
optimize table test.t2 no_wait;
42+
Table Op Msg_type Msg_text
43+
test.t2 optimize Error Lock wait timeout exceeded; try restarting transaction
44+
test.t2 optimize status Operation failed
45+
rename table test.t2 no_wait to test.r2;
46+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
47+
drop index t1_ind_1 on test.t1 no_wait;
48+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
49+
create index t1_ind_2 on test.t1(id) no_wait;
50+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
51+
drop index t2_ind_1 on test.t2 no_wait;
52+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
53+
create index t2_ind_2 on test.t2(id) no_wait;
54+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
55+
create fulltext index t2_ind_3 on test.t2(name) no_wait;
56+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
57+
alter table test.t1 wait 1 add extra varchar(10);
58+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
59+
alter table test.t1 wait 1 rename to test.r1;
60+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
61+
alter table test.t1 wait 1 add primary key(id);
62+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
63+
alter table test.t2 wait 1 add extra varchar(10);
64+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
65+
alter table test.t2 wait 1 rename to test.r2;
66+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
67+
alter table test.t2 wait 1 add primary key(id);
68+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
69+
drop table test.t1 wait 1;
70+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
71+
truncate table test.t1 wait 1;
72+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
73+
optimize table test.t1 wait 1;
74+
Table Op Msg_type Msg_text
75+
test.t1 optimize Error Lock wait timeout exceeded; try restarting transaction
76+
test.t1 optimize status Operation failed
77+
rename table test.t1 wait 1 to test.r1;
78+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
79+
drop table test.t2 wait 1;
80+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
81+
truncate table test.t2 wait 1;
82+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
83+
optimize table test.t2 wait 1;
84+
Table Op Msg_type Msg_text
85+
test.t2 optimize Error Lock wait timeout exceeded; try restarting transaction
86+
test.t2 optimize status Operation failed
87+
rename table test.t2 wait 1 to test.r2;
88+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
89+
drop index t1_ind_1 on test.t1 wait 1;
90+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
91+
create index t1_ind_2 on test.t1(id) wait 1;
92+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
93+
drop index t2_ind_1 on test.t2 wait 1;
94+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
95+
create index t2_ind_2 on test.t2(id) wait 1;
96+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
97+
create fulltext index t2_ind_3 on test.t2(name) wait 1;
98+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
99+
commit;
100+
alter table test.t1 no_wait add extra varchar(10);
101+
alter table test.t1 no_wait rename to test.r1;
102+
alter table test.r1 no_wait rename to test.t1;
103+
alter table test.t1 no_wait add primary key(id);
104+
alter table test.t2 no_wait add extra varchar(10);
105+
alter table test.t2 no_wait rename to test.r2;
106+
alter table test.r2 no_wait rename to test.t2;
107+
alter table test.t2 no_wait add primary key(id);
108+
truncate table test.t1 no_wait;
109+
optimize table test.t1 no_wait;
110+
Table Op Msg_type Msg_text
111+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
112+
test.t1 optimize status OK
113+
rename table test.t1 no_wait to test.r1;
114+
rename table test.r1 no_wait to test.t1;
115+
truncate table test.t2 no_wait;
116+
optimize table test.t2 no_wait;
117+
Table Op Msg_type Msg_text
118+
test.t2 optimize status Table is already up to date
119+
rename table test.t2 no_wait to test.r2;
120+
rename table test.r2 no_wait to test.t2;
121+
drop index t1_ind_1 on test.t1 no_wait;
122+
create index t1_ind_2 on test.t1(id) no_wait;
123+
drop index t2_ind_1 on test.t2 no_wait;
124+
create index t2_ind_2 on test.t2(id) no_wait;
125+
create fulltext index t2_ind_3 on test.t2(name) no_wait;
126+
drop table test.t1 no_wait;
127+
drop table test.t2 no_wait;
128+
create table test.t1 (id int, name varchar(100)) engine=innodb;
129+
create index t1_ind_1 on test.t1(id);
130+
insert into test.t1 values (1, 'test');
131+
create table test.t2 (id int, name varchar(100)) engine=myisam;
132+
create index t2_ind_1 on test.t2(id);
133+
insert into test.t2 values(1, 'test');
134+
commit;
135+
alter table test.t1 wait 1 add extra varchar(10);
136+
alter table test.t1 wait 1 rename to test.r1;
137+
alter table test.r1 wait 1 rename to test.t1;
138+
alter table test.t1 wait 1 add primary key(id);
139+
alter table test.t2 wait 1 add extra varchar(10);
140+
alter table test.t2 wait 1 rename to test.r2;
141+
alter table test.r2 wait 1 rename to test.t2;
142+
alter table test.t2 wait 1 add primary key(id);
143+
truncate table test.t1 wait 1;
144+
optimize table test.t1 wait 1;
145+
Table Op Msg_type Msg_text
146+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
147+
test.t1 optimize status OK
148+
rename table test.t1 wait 1 to test.r1;
149+
rename table test.r1 wait 1 to test.t1;
150+
truncate table test.t2 wait 1;
151+
optimize table test.t2 wait 1;
152+
Table Op Msg_type Msg_text
153+
test.t2 optimize status Table is already up to date
154+
rename table test.t2 wait 1 to test.r2;
155+
rename table test.r2 wait 1 to test.t2;
156+
drop index t1_ind_1 on test.t1 wait 1;
157+
create index t1_ind_2 on test.t1(id) wait 1;
158+
drop index t2_ind_1 on test.t2 wait 1;
159+
create index t2_ind_2 on test.t2(id) wait 1;
160+
create fulltext index t2_ind_3 on test.t2(name) wait 1;
161+
drop table test.t1 wait 1;
162+
drop table test.t2 wait 1;
163+
create table test.t1 (a int);
164+
begin;
165+
insert into test.t1 values (1);
166+
alter table test.t1 wait 2 add index (a);
167+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
168+
alter table test.t1 no_wait add index (a);
169+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
170+
commit;
171+
drop table test.t1;

‎mysql-test/suite/rds/t/perf_ddl_fast_fail_issue15.test‎

Lines changed: 207 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,207 @@
1+
connect(conn1, localhost, root,,);
2+
connect(conn2, localhost, root,,);
3+
4+
# initialize the environment
5+
connection conn1;
6+
set autocommit= 0;
7+
create table test.t1(id int, name varchar(100)) engine=innodb;
8+
create index t1_ind_1 on test.t1(id);
9+
insert into test.t1 values(1, 'test');
10+
11+
create table test.t2(id int, name varchar(100)) engine=myisam;
12+
create index t2_ind_1 on test.t2(id);
13+
insert into test.t2 values(1, 'test');
14+
commit;
15+
16+
select * from test.t1;
17+
select * from test.t2;
18+
19+
# failed since of mdl lock fast fail
20+
# no_wait
21+
connection conn2;
22+
--error ER_LOCK_WAIT_TIMEOUT
23+
alter table test.t1 no_wait add extra varchar(10);
24+
--error ER_LOCK_WAIT_TIMEOUT
25+
alter table test.t1 no_wait rename to test.r1;
26+
--error ER_LOCK_WAIT_TIMEOUT
27+
alter table test.t1 no_wait add primary key(id);
28+
29+
--error ER_LOCK_WAIT_TIMEOUT
30+
alter table test.t2 no_wait add extra varchar(10);
31+
--error ER_LOCK_WAIT_TIMEOUT
32+
alter table test.t2 no_wait rename to test.r2;
33+
--error ER_LOCK_WAIT_TIMEOUT
34+
alter table test.t2 no_wait add primary key(id);
35+
36+
--error ER_LOCK_WAIT_TIMEOUT
37+
drop table test.t1 no_wait;
38+
--error ER_LOCK_WAIT_TIMEOUT
39+
truncate table test.t1 no_wait;
40+
optimize table test.t1 no_wait;
41+
--error ER_LOCK_WAIT_TIMEOUT
42+
rename table test.t1 no_wait to test.r1;
43+
44+
--error ER_LOCK_WAIT_TIMEOUT
45+
drop table test.t2 no_wait;
46+
--error ER_LOCK_WAIT_TIMEOUT
47+
truncate table test.t2 no_wait;
48+
optimize table test.t2 no_wait;
49+
--error ER_LOCK_WAIT_TIMEOUT
50+
rename table test.t2 no_wait to test.r2;
51+
52+
--error ER_LOCK_WAIT_TIMEOUT
53+
drop index t1_ind_1 on test.t1 no_wait;
54+
--error ER_LOCK_WAIT_TIMEOUT
55+
create index t1_ind_2 on test.t1(id) no_wait;
56+
57+
--error ER_LOCK_WAIT_TIMEOUT
58+
drop index t2_ind_1 on test.t2 no_wait;
59+
--error ER_LOCK_WAIT_TIMEOUT
60+
create index t2_ind_2 on test.t2(id) no_wait;
61+
62+
--error ER_LOCK_WAIT_TIMEOUT
63+
create fulltext index t2_ind_3 on test.t2(name) no_wait;
64+
65+
# failed since of mdl lock timeout
66+
# wait 1
67+
--error ER_LOCK_WAIT_TIMEOUT
68+
alter table test.t1 wait 1 add extra varchar(10);
69+
--error ER_LOCK_WAIT_TIMEOUT
70+
alter table test.t1 wait 1 rename to test.r1;
71+
--error ER_LOCK_WAIT_TIMEOUT
72+
alter table test.t1 wait 1 add primary key(id);
73+
74+
--error ER_LOCK_WAIT_TIMEOUT
75+
alter table test.t2 wait 1 add extra varchar(10);
76+
--error ER_LOCK_WAIT_TIMEOUT
77+
alter table test.t2 wait 1 rename to test.r2;
78+
--error ER_LOCK_WAIT_TIMEOUT
79+
alter table test.t2 wait 1 add primary key(id);
80+
81+
--error ER_LOCK_WAIT_TIMEOUT
82+
drop table test.t1 wait 1;
83+
--error ER_LOCK_WAIT_TIMEOUT
84+
truncate table test.t1 wait 1;
85+
optimize table test.t1 wait 1;
86+
--error ER_LOCK_WAIT_TIMEOUT
87+
rename table test.t1 wait 1 to test.r1;
88+
89+
--error ER_LOCK_WAIT_TIMEOUT
90+
drop table test.t2 wait 1;
91+
--error ER_LOCK_WAIT_TIMEOUT
92+
truncate table test.t2 wait 1;
93+
optimize table test.t2 wait 1;
94+
--error ER_LOCK_WAIT_TIMEOUT
95+
rename table test.t2 wait 1 to test.r2;
96+
97+
--error ER_LOCK_WAIT_TIMEOUT
98+
drop index t1_ind_1 on test.t1 wait 1;
99+
--error ER_LOCK_WAIT_TIMEOUT
100+
create index t1_ind_2 on test.t1(id) wait 1;
101+
102+
--error ER_LOCK_WAIT_TIMEOUT
103+
drop index t2_ind_1 on test.t2 wait 1;
104+
--error ER_LOCK_WAIT_TIMEOUT
105+
create index t2_ind_2 on test.t2(id) wait 1;
106+
107+
--error ER_LOCK_WAIT_TIMEOUT
108+
create fulltext index t2_ind_3 on test.t2(name) wait 1;
109+
110+
# release thd mdl table lock
111+
connection conn1;
112+
commit;
113+
114+
# success
115+
# no_wait
116+
connection conn2;
117+
118+
alter table test.t1 no_wait add extra varchar(10);
119+
alter table test.t1 no_wait rename to test.r1;
120+
alter table test.r1 no_wait rename to test.t1;
121+
alter table test.t1 no_wait add primary key(id);
122+
123+
alter table test.t2 no_wait add extra varchar(10);
124+
alter table test.t2 no_wait rename to test.r2;
125+
alter table test.r2 no_wait rename to test.t2;
126+
alter table test.t2 no_wait add primary key(id);
127+
128+
truncate table test.t1 no_wait;
129+
optimize table test.t1 no_wait;
130+
rename table test.t1 no_wait to test.r1;
131+
rename table test.r1 no_wait to test.t1;
132+
133+
truncate table test.t2 no_wait;
134+
optimize table test.t2 no_wait;
135+
rename table test.t2 no_wait to test.r2;
136+
rename table test.r2 no_wait to test.t2;
137+
138+
drop index t1_ind_1 on test.t1 no_wait;
139+
create index t1_ind_2 on test.t1(id) no_wait;
140+
141+
drop index t2_ind_1 on test.t2 no_wait;
142+
create index t2_ind_2 on test.t2(id) no_wait;
143+
144+
create fulltext index t2_ind_3 on test.t2(name) no_wait;
145+
146+
drop table test.t1 no_wait;
147+
drop table test.t2 no_wait;
148+
149+
# reinitiate the environment
150+
connection conn1;
151+
create table test.t1 (id int, name varchar(100)) engine=innodb;
152+
create index t1_ind_1 on test.t1(id);
153+
insert into test.t1 values (1, 'test');
154+
155+
create table test.t2 (id int, name varchar(100)) engine=myisam;
156+
create index t2_ind_1 on test.t2(id);
157+
insert into test.t2 values(1, 'test');
158+
commit;
159+
160+
# success
161+
# wait 1
162+
connection conn2;
163+
alter table test.t1 wait 1 add extra varchar(10);
164+
alter table test.t1 wait 1 rename to test.r1;
165+
alter table test.r1 wait 1 rename to test.t1;
166+
alter table test.t1 wait 1 add primary key(id);
167+
168+
alter table test.t2 wait 1 add extra varchar(10);
169+
alter table test.t2 wait 1 rename to test.r2;
170+
alter table test.r2 wait 1 rename to test.t2;
171+
alter table test.t2 wait 1 add primary key(id);
172+
173+
truncate table test.t1 wait 1;
174+
optimize table test.t1 wait 1;
175+
rename table test.t1 wait 1 to test.r1;
176+
rename table test.r1 wait 1 to test.t1;
177+
178+
truncate table test.t2 wait 1;
179+
optimize table test.t2 wait 1;
180+
rename table test.t2 wait 1 to test.r2;
181+
rename table test.r2 wait 1 to test.t2;
182+
183+
drop index t1_ind_1 on test.t1 wait 1;
184+
create index t1_ind_2 on test.t1(id) wait 1;
185+
186+
drop index t2_ind_1 on test.t2 wait 1;
187+
create index t2_ind_2 on test.t2(id) wait 1;
188+
189+
create fulltext index t2_ind_3 on test.t2(name) wait 1;
190+
191+
drop table test.t1 wait 1;
192+
drop table test.t2 wait 1;
193+
194+
connection conn1;
195+
create table test.t1 (a int);
196+
begin;
197+
insert into test.t1 values (1);
198+
199+
connection conn2;
200+
--error ER_LOCK_WAIT_TIMEOUT
201+
alter table test.t1 wait 2 add index (a);
202+
--error ER_LOCK_WAIT_TIMEOUT
203+
alter table test.t1 no_wait add index (a);
204+
205+
connection conn1;
206+
commit;
207+
drop table test.t1;

0 commit comments

Comments
 (0)