Skip to content

Commit fc7e697

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#34 Support force drop table
Summary: -------- Usually the meta data is not consistent between server layer and InnoDB layer. So we supply a new syntax to clear the meta data. Usage: ------ 1. DROP TABLE FORCE t1; 2. ALTER TABLE t1 DROP PARTITION force p2;
1 parent 3d58918 commit fc7e697

File tree

16 files changed

+675
-14
lines changed

16 files changed

+675
-14
lines changed

‎mysql-test/r/force_drop_issue178.result‎

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
CALL mtr.add_suppression("already exists though the corresponding table");
2+
CALL mtr.add_suppression("returned OS error 71");
3+
CALL mtr.add_suppression("Cannot create file");
4+
CALL mtr.add_suppression("Error number 17 means");
5+
CALL mtr.add_suppression("Maybe it doesn't exist in innodb");
6+
CALL mtr.add_suppression("Could not find a valid tablespace file");
7+
CALL mtr.add_suppression("nnoDB: Tablespace open failed for");
8+
CALL mtr.add_suppression("InnoDB: Failed to find tablespace");
9+
create database zwx;
10+
use zwx;
11+
grant all on zwx.* to 'nosuper'@'%';
12+
drop table force t1;
13+
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
14+
create table t1 (a int) engine=innodb;
15+
rm zwx/t1.ibd
16+
drop table t1;
17+
create table t1 (a int) engine=innodb;
18+
rm zwx/t1.frm
19+
drop table t1;
20+
ERROR 42S02: Unknown table 'zwx.t1'
21+
create table t1 (a int) engine=innodb;
22+
ERROR HY000: Tablespace for table '`zwx`.`t1`' exists. Please DISCARD the tablespace before IMPORT.
23+
drop table force t1;
24+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
25+
create table t1 (a int) engine=innodb;
26+
drop table t1;
27+
create table t1 (a int, key(a)) engine=innodb;
28+
create table t2 (b int, foreign key(b) references t1(a), key(b)) engine=innodb;
29+
drop table t1;
30+
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
31+
rm zwx/t1.frm
32+
drop table t1;
33+
ERROR 42S02: Unknown table 'zwx.t1'
34+
CALL mtr.add_suppression("because of foreign key constraint");
35+
drop table force t1;
36+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
37+
drop table t2;
38+
drop table force t1;
39+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
40+
create table t1 (a int) engine=innodb;
41+
drop table t1;
42+
drop table force t1;
43+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
44+
create table t1 (a int) partition by list (a)
45+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3));
46+
rm zwx/t1.frm
47+
drop table t1;
48+
ERROR 42S02: Unknown table 'zwx.t1'
49+
CALL mtr.add_suppression("does not exist in the InnoDB internal");
50+
drop table force t1;
51+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
52+
drop table force t1;
53+
ERROR 42S02: Unknown table 'zwx.t1'. Force engine layer to clean these tables.'
54+
create table t1 (a int) engine = innodb partition by list (a)
55+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in(4));
56+
rm zwx/t1#P#p2.ibd
57+
rm zwx/t1#P#p3.ibd
58+
create table t2 (a int, b int, c int, primary key(a), key(b)) engine = innodb
59+
partition by list (a) (partition p1 values in (1), partition p2 values in (2),
60+
partition p3 values in (3), partition p4 values in(4));
61+
rm zwx/t2#P#p1.ibd
62+
create table t3 (a int, b int, c int, primary key(a), key(b)) engine = innodb
63+
partition by list (a) (partition p1 values in (1), partition p2 values in (2),
64+
partition p3 values in (3), partition p4 values in(4));
65+
rm zwx/t3#P#p4.ibd
66+
create table t4 (a int) engine =myisam partition by list (a)
67+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in(4));
68+
rm zwx/t4#P#p2.MYD
69+
rm zwx/t4#P#p3.ibd
70+
create table t6 (a int primary key, b int) engine = innodb partition by range columns(a)
71+
(partition p1 values less than (10),
72+
partition p2 values less than (100),
73+
partition p3 values less than (1000),
74+
partition p4 values less than (10000),
75+
partition p5 values less than (MAXVALUE));
76+
rm zwx/t6#P#p1.IBD
77+
rm zwx/t6#P#p3.IBD
78+
rm zwx/t6#P#p5.IBD
79+
create table t7(a int , b int) engine = innodb partition by hash(a) partitions 5;
80+
rm zwx/t7#P#p1.IBD
81+
rm zwx/t7#P#p3.IBD
82+
rm zwx/t7#P#p4.IBD
83+
create table t8(a int , b int) engine = innodb partition by linear hash(a) partitions 5;
84+
rm zwx/t8#P#p1.IBD
85+
rm zwx/t8#P#p3.IBD
86+
rm zwx/t8#P#p4.IBD
87+
create table t9 (a int , b int ) engine=innodb partition by key(a) partitions 5;
88+
rm zwx/t9#P#p1.IBD
89+
rm zwx/t9#P#p3.IBD
90+
rm zwx/t9#P#p4.IBD
91+
create table t10 (a int, b int) engine = innodb
92+
partition by range(a) subpartition by hash(b)
93+
( PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1),
94+
PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2, SUBPARTITION s3),
95+
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4,SUBPARTITION s5));
96+
rm zwx/t10#P#p0#SP#s0.ibd
97+
rm zwx/t10#P#p2#SP#s5.ibd
98+
# Stop server
99+
use zwx;
100+
alter table t1 drop partition p2;
101+
ERROR 42S02: Table 'zwx.t1' doesn't exist
102+
alter table t1 drop partition p3;
103+
ERROR 42S02: Table 'zwx.t1' doesn't exist
104+
alter table t1 drop partition force p2, p3;
105+
ERROR 28000: Access denied for user 'nosuper'@'%' (using password: NO)
106+
alter table t1 drop partition force p2, p3;
107+
Warnings:
108+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t1#P#p2'
109+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t1#P#p3'
110+
alter table t1 add partition (partition p2 values in (2));
111+
alter table t2 drop partition force p1;
112+
Warnings:
113+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t2#P#p1'
114+
alter table t3 drop partition force p4;
115+
Warnings:
116+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t3#P#p4'
117+
alter table t6 drop partition force p1,p3,p5;
118+
Warnings:
119+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t6#P#p1'
120+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t6#P#p3'
121+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t6#P#p5'
122+
alter table t7 drop partition p1,p3,p5;
123+
ERROR 42S02: Table 'zwx.t7' doesn't exist
124+
alter table t7 drop partition force p1,p3,p5;
125+
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
126+
alter table t8 drop partition force p1,p3,p5;
127+
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
128+
alter table t9 drop partition force p1,p3,p5;
129+
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
130+
alter table t10 drop partition force p0, p2;
131+
Warnings:
132+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t10#P#p0#SP#s0'
133+
Warning 1812 InnoDB: Tablespace is missing for table 'zwx/t10#P#p2#SP#s5'
134+
alter table t4 drop partition p2;
135+
ERROR HY000: File './zwx/t4#P#p2.MYD' not found (Errcode: 2 - No such file or directory)
136+
alter table t4 drop partition p3;
137+
ERROR HY000: File './zwx/t4#P#p2.MYD' not found (Errcode: 2 - No such file or directory)
138+
alter table t4 drop partition force p2, p3;
139+
ERROR HY000: File './zwx/t4#P#p2.MYD' not found (Errcode: 2 - No such file or directory)
140+
alter table t4 add partition (partition p2 values in (2));
141+
drop database zwx;
142+
drop user nosuper;

‎mysql-test/t/force_drop_issue178-master.opt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
--log-bin --binlog_format=row

‎mysql-test/t/force_drop_issue178.test‎

Lines changed: 244 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,244 @@
1+
--source include/have_innodb.inc
2+
3+
CALL mtr.add_suppression("already exists though the corresponding table");
4+
CALL mtr.add_suppression("returned OS error 71");
5+
CALL mtr.add_suppression("Cannot create file");
6+
CALL mtr.add_suppression("Error number 17 means");
7+
CALL mtr.add_suppression("Maybe it doesn't exist in innodb");
8+
CALL mtr.add_suppression("Could not find a valid tablespace file");
9+
CALL mtr.add_suppression("nnoDB: Tablespace open failed for");
10+
CALL mtr.add_suppression("InnoDB: Failed to find tablespace");
11+
12+
create database zwx;
13+
use zwx;
14+
15+
#Only allow supper user to do FORCE DROP.
16+
grant all on zwx.* to 'nosuper'@'%';
17+
18+
connect(no_super, localhost, nosuper,,);
19+
20+
connection no_super;
21+
--error 1227
22+
drop table force t1;
23+
24+
connection default;
25+
26+
let $MYSQLD_DATADIR= `select @@datadir`;
27+
28+
create table t1 (a int) engine=innodb;
29+
30+
--echo rm zwx/t1.ibd
31+
--remove_file $MYSQLD_DATADIR/zwx/t1.ibd
32+
33+
drop table t1;
34+
35+
create table t1 (a int) engine=innodb;
36+
37+
--echo rm zwx/t1.frm
38+
--remove_file $MYSQLD_DATADIR/zwx/t1.frm
39+
40+
--error 1051
41+
drop table t1;
42+
43+
--error 1813
44+
create table t1 (a int) engine=innodb;
45+
46+
--error 1051
47+
drop table force t1;
48+
49+
create table t1 (a int) engine=innodb;
50+
51+
drop table t1;
52+
53+
create table t1 (a int, key(a)) engine=innodb;
54+
create table t2 (b int, foreign key(b) references t1(a), key(b)) engine=innodb;
55+
56+
--error 1217
57+
drop table t1;
58+
59+
--echo rm zwx/t1.frm
60+
--remove_file $MYSQLD_DATADIR/zwx/t1.frm
61+
62+
--error 1051
63+
drop table t1;
64+
65+
CALL mtr.add_suppression("because of foreign key constraint");
66+
67+
--error 1051
68+
drop table force t1;
69+
70+
drop table t2;
71+
72+
--error 1051
73+
drop table force t1;
74+
75+
create table t1 (a int) engine=innodb;
76+
77+
drop table t1;
78+
--error 1051
79+
drop table force t1;
80+
81+
create table t1 (a int) partition by list (a)
82+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3));
83+
84+
--echo rm zwx/t1.frm
85+
--remove_file $MYSQLD_DATADIR/zwx/t1.frm
86+
87+
--error 1051
88+
drop table t1;
89+
90+
CALL mtr.add_suppression("does not exist in the InnoDB internal");
91+
92+
--error 1051
93+
drop table force t1;
94+
95+
--error 1051
96+
drop table force t1;
97+
98+
#Test alter...drop partition force..
99+
#Test innodb engine
100+
create table t1 (a int) engine = innodb partition by list (a)
101+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in(4));
102+
103+
--echo rm zwx/t1#P#p2.ibd
104+
--remove_file $MYSQLD_DATADIR/zwx/t1#P#p2.ibd
105+
--echo rm zwx/t1#P#p3.ibd
106+
--remove_file $MYSQLD_DATADIR/zwx/t1#P#p3.ibd
107+
108+
create table t2 (a int, b int, c int, primary key(a), key(b)) engine = innodb
109+
partition by list (a) (partition p1 values in (1), partition p2 values in (2),
110+
partition p3 values in (3), partition p4 values in(4));
111+
112+
#remove first ibd
113+
--echo rm zwx/t2#P#p1.ibd
114+
--remove_file $MYSQLD_DATADIR/zwx/t2#P#p1.ibd
115+
116+
create table t3 (a int, b int, c int, primary key(a), key(b)) engine = innodb
117+
partition by list (a) (partition p1 values in (1), partition p2 values in (2),
118+
partition p3 values in (3), partition p4 values in(4));
119+
120+
#remove last ibd
121+
--echo rm zwx/t3#P#p4.ibd
122+
--remove_file $MYSQLD_DATADIR/zwx/t3#P#p4.ibd
123+
124+
# Test mysiam engine
125+
create table t4 (a int) engine =myisam partition by list (a)
126+
(partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in(4));
127+
128+
--echo rm zwx/t4#P#p2.MYD
129+
--remove_file $MYSQLD_DATADIR/zwx/t4#P#p2.MYD
130+
--echo rm zwx/t4#P#p3.ibd
131+
--remove_file $MYSQLD_DATADIR/zwx/t4#P#p3.MYD
132+
133+
#Test range partition
134+
create table t6 (a int primary key, b int) engine = innodb partition by range columns(a)
135+
(partition p1 values less than (10),
136+
partition p2 values less than (100),
137+
partition p3 values less than (1000),
138+
partition p4 values less than (10000),
139+
partition p5 values less than (MAXVALUE));
140+
141+
#remove ibd file
142+
--echo rm zwx/t6#P#p1.IBD
143+
--remove_file $MYSQLD_DATADIR/zwx/t6#P#p1.ibd
144+
--echo rm zwx/t6#P#p3.IBD
145+
--remove_file $MYSQLD_DATADIR/zwx/t6#P#p3.ibd
146+
--echo rm zwx/t6#P#p5.IBD
147+
--remove_file $MYSQLD_DATADIR/zwx/t6#P#p5.ibd
148+
149+
#Test hash partition
150+
create table t7(a int , b int) engine = innodb partition by hash(a) partitions 5;
151+
#remove ibd file
152+
--echo rm zwx/t7#P#p1.IBD
153+
--remove_file $MYSQLD_DATADIR/zwx/t7#P#p1.ibd
154+
--echo rm zwx/t7#P#p3.IBD
155+
--remove_file $MYSQLD_DATADIR/zwx/t7#P#p3.ibd
156+
--echo rm zwx/t7#P#p4.IBD
157+
--remove_file $MYSQLD_DATADIR/zwx/t7#P#p4.ibd
158+
159+
#linear partition
160+
create table t8(a int , b int) engine = innodb partition by linear hash(a) partitions 5;
161+
#remove ibd file
162+
--echo rm zwx/t8#P#p1.IBD
163+
--remove_file $MYSQLD_DATADIR/zwx/t8#P#p1.ibd
164+
--echo rm zwx/t8#P#p3.IBD
165+
--remove_file $MYSQLD_DATADIR/zwx/t8#P#p3.ibd
166+
--echo rm zwx/t8#P#p4.IBD
167+
--remove_file $MYSQLD_DATADIR/zwx/t8#P#p4.ibd
168+
169+
#Test KEY partition
170+
create table t9 (a int , b int ) engine=innodb partition by key(a) partitions 5;
171+
#remove ibd file
172+
--echo rm zwx/t9#P#p1.IBD
173+
--remove_file $MYSQLD_DATADIR/zwx/t9#P#p1.ibd
174+
--echo rm zwx/t9#P#p3.IBD
175+
--remove_file $MYSQLD_DATADIR/zwx/t9#P#p3.ibd
176+
--echo rm zwx/t9#P#p4.IBD
177+
--remove_file $MYSQLD_DATADIR/zwx/t9#P#p4.ibd
178+
179+
#Test Sub-partition
180+
create table t10 (a int, b int) engine = innodb
181+
partition by range(a) subpartition by hash(b)
182+
( PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1),
183+
PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2, SUBPARTITION s3),
184+
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4,SUBPARTITION s5));
185+
#remove ibd file
186+
--echo rm zwx/t10#P#p0#SP#s0.ibd
187+
--remove_file $MYSQLD_DATADIR/zwx/t10#P#p0#SP#s0.ibd
188+
--echo rm zwx/t10#P#p2#SP#s5.ibd
189+
--remove_file $MYSQLD_DATADIR/zwx/t10#P#p2#SP#s5.ibd
190+
191+
disconnect no_super;
192+
193+
#restart the server
194+
--echo # Stop server
195+
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
196+
--send_shutdown
197+
--source include/wait_until_disconnected.inc
198+
--exec echo "restart: " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
199+
--enable_reconnect
200+
--source include/wait_until_connected_again.inc
201+
202+
use zwx;
203+
--error 1146
204+
alter table t1 drop partition p2;
205+
--error 1146
206+
alter table t1 drop partition p3;
207+
208+
connect(no_super, localhost, nosuper,,);
209+
connection no_super;
210+
--error 1045
211+
alter table t1 drop partition force p2, p3;
212+
213+
connection default;
214+
alter table t1 drop partition force p2, p3;
215+
alter table t1 add partition (partition p2 values in (2));
216+
217+
alter table t2 drop partition force p1;
218+
alter table t3 drop partition force p4;
219+
alter table t6 drop partition force p1,p3,p5;
220+
221+
--error 1146
222+
alter table t7 drop partition p1,p3,p5;
223+
#DROP PARTITION can only be used on RANGE/LIST partitions
224+
--error 1512
225+
alter table t7 drop partition force p1,p3,p5;
226+
--error 1512
227+
alter table t8 drop partition force p1,p3,p5;
228+
--error 1512
229+
alter table t9 drop partition force p1,p3,p5;
230+
alter table t10 drop partition force p0, p2;
231+
232+
--error 29
233+
alter table t4 drop partition p2;
234+
--error 29
235+
alter table t4 drop partition p3;
236+
237+
--error 29
238+
alter table t4 drop partition force p2, p3;
239+
alter table t4 add partition (partition p2 values in (2));
240+
241+
#cleanup
242+
disconnect no_super;
243+
drop database zwx;
244+
drop user nosuper;

0 commit comments

Comments
 (0)