Debian Codegraph @

mysql-test/suite/ndb/t/ndb_use_dropped_index.test

raw
1 --source include/have_ndb.inc
2 --result_format 2
3
4 # Bug#28584066 MYSQLD CANNOT HANDLE MISSING INDICES IN NDB
5
6 create table t1(
7 a int not null primary key, # intrinsic PK plus PRIMARY ordered index
8 b int not null,
9 c int,
10 d int not null,
11 unique(b) using hash, # unique index only
12 index(c), # ordered index only
13 unique(d) # ordered and unique indexes
14 ) engine=ndb;
15
16 insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
17
18 ## Select on the PRIMARY ordered index
19 select a, count(*) from t1 group by a order by a;
20
21 ## You can make an index invisible, and then visible again
22 select c, count(1) from t1 group by c order by c;
23 EXPLAIN select c, count(1) from t1 group by c order by c;
24 ALTER TABLE t1 ALTER INDEX c invisible;
25 EXPLAIN select c, count(1) from t1 group by c order by c;
26 ALTER TABLE t1 ALTER INDEX c visible;
27
28 ##########################################
29 ## Use ndb_drop_index to drop PRIMARY
30 --exec $NDB_DROP_INDEX -d test t1 PRIMARY >> $NDB_TOOLS_OUTPUT
31
32 ## First query after drop fails with stale metadata
33 --error ER_TABLE_DEF_CHANGED
34 select * from t1 where a > 3;
35
36 ## Second time succeeds. No warnings. (Uses pushed condition, not an index)
37 select * from t1 where a > 3;
38
39 ## A query trying to use the index fails and gives warning
40 --error ER_NOT_KEYFILE
41 select a, count(1) from t1 group by a;
42 show warnings;
43
44 ## Add a column. This runs a DD-vs-NDB metadata check on the altered
45 ## table which previously would have failed. The new column has both
46 ## ordered and unique indexes.
47 ALTER TABLE t1 ADD e int;
48 ALTER TABLE t1 ADD unique(e);
49 insert into t1 values (5,5,5,5,5);
50 UPDATE t1 set e = d;
51 --sorted_result
52 SELECT * FROM t1;
53
54 ##########################################
55 ## Use ndb_drop_index to drop the unique index on b.
56 --exec $NDB_DROP_INDEX -d test t1 b\$unique >> $NDB_TOOLS_OUTPUT
57
58 ## First attempt to use dropped index fails with stale metadata
59 --error ER_TABLE_DEF_CHANGED
60 SELECT * FROM t1 WHERE b = 3;
61
62 ## Second attempt fails with bad index.
63 --error ER_NOT_KEYFILE
64 SELECT * FROM t1 WHERE b = 3;
65 show warnings;
66
67 # This test results in an error message in the server log:
68 # [ERROR] [MY-010211] [Server] Got error 126 when reading table './test/t1'
69 call mtr.add_suppression(".*Got error 126 when reading table '.*t1'");
70
71 ## Attempt MRR read via dropped uk index
72 --error ER_NOT_KEYFILE
73 select * from t1 where b in (2,4,6,8);
74 show warnings;
75
76 ## Attempt join with dropped uk lookup as root, pk lookup as child
77 --error ER_NOT_KEYFILE
78 select * from t1 x join t1 y on x.a = y.c where y.b=5;
79 show warnings;
80
81 ## Attempt join with pk lookup as root, dropped uk lookup as child
82 --error ER_NOT_KEYFILE
83 select * from t1 x join t1 y on y.b = x.c where x.a=3;
84 show warnings;
85
86 ## Attempt update ignoring duplicates on dropped uk index
87 --error ER_NOT_KEYFILE
88 update ignore t1 set b=1 where a=4;
89 show warnings;
90
91 ## Attempt insert on duplicate key update of dropped uk index
92 --error ER_NOT_KEYFILE
93 insert into t1 values (10,4,4,4,4) on duplicate key update b=b+1;
94 show warnings;
95
96 ## Attempt insert which must check for duplicates on dropped
97 ## uk index to ignore errors
98 --error ER_NOT_KEYFILE
99 insert ignore into t1 values (10,4,4,4,4);
100 show warnings;
101
102 ##########################################
103 ## Use ndb_drop_index to drop the index on c.
104 --exec $NDB_DROP_INDEX -d test t1 c >> $NDB_TOOLS_OUTPUT
105
106 ## Stale metadata
107 --error ER_TABLE_DEF_CHANGED
108 select c, count(1) from t1 group by c order by c;
109
110 ## Use of index fails
111 --error ER_NOT_KEYFILE
112 select c, count(1) from t1 group by c order by c;
113
114 ## Make the dropped index invisible. Then all tests will succeed.
115 ALTER TABLE t1 ALTER INDEX c invisible;
116 --sorted_result
117 SELECT * FROM t1 WHERE c = 3;
118 select c, count(1) from t1 group by c order by c;
119
120 ## Drop the index in MySQL
121 ALTER TABLE t1 DROP INDEX c;
122
123 ## Then rebuild it in MySQL
124 ALTER TABLE t1 add index(c);
125
126 ## And test once more
127 select c, count(1) from t1 group by c order by c;
128
129 ##########################################
130 ## Use ndb_drop_index to drop d$unique
131 ## d will be downgraded from UNIQUE_ORDERED_INDEX to ORDERED_INDEX
132 --exec $NDB_DROP_INDEX -d test t1 d\$unique >> $NDB_TOOLS_OUTPUT
133
134 ## Table scan succeeds.
135 --sorted_result
136 SELECT * FROM t1;
137
138 ## But, try to use the index? Stale metadata.
139 --error ER_TABLE_DEF_CHANGED
140 SELECT * FROM t1 where d = 3;
141
142 ## Then it succeeds (the ordered index is still available).
143 SELECT * FROM t1 where d = 3;
144
145 ## How did it do it? It used the ordered index.
146 EXPLAIN SELECT * FROM t1 where d = 3;
147
148 ## Let's corrupt the unique index while it's not online!
149 UPDATE t1 set d = 14;
150
151 ## ... and then fix it.
152 UPDATE t1 set d = a;
153
154
155 ##########################################
156 ## Use ndb_drop_index to drop the ordered index on e.
157 ## e will be downgraded from UNIQUE_ORDERED_INDEX to UNIQUE_INDEX
158 --exec $NDB_DROP_INDEX -d test t1 e >> $NDB_TOOLS_OUTPUT
159
160 ## Table scan succeeds.
161 --sorted_result
162 SELECT * FROM t1;
163
164 ## First attempt to use dropped index fails with stale metadata
165 --error ER_TABLE_DEF_CHANGED
166 SELECT * FROM t1 where e > 3;
167
168 ## Second attempt succeeds.
169 SELECT * FROM t1 where e > 3 order by e;
170
171 ## A query trying to use the index fails and gives warning
172 --error ER_NOT_KEYFILE
173 select 3, count(*) from t1 group by e order by e;
174 show warnings;
175
176 ##########################################
177 ## A small set of tests on a table with some two-part indexes
178
179 create table t2 (
180 a int not null,
181 b int not null,
182 c int,
183 primary key (a,b), # primary key ordered index on two columns
184 index(b,c) # ordered index on two columns
185 ) engine=ndb;
186
187 insert into t2 values (1,1,null), (2,1,0), (3,1,1), (1,2,1), (1,3,null);
188
189 select * from t2 where a > 2;
190 --exec $NDB_DROP_INDEX -d test t2 PRIMARY >> $NDB_TOOLS_OUTPUT
191 --error ER_TABLE_DEF_CHANGED
192 select * from t2 where a > 2;
193 select * from t2 where a > 2;
194 --exec $NDB_DROP_INDEX -d test t2 b >> $NDB_TOOLS_OUTPUT
195 --error ER_TABLE_DEF_CHANGED
196 select * from t2 where b = 2;
197 --error ER_NOT_KEYFILE
198 select * from t2 where b = 2;
199
200 # This test results in an error message in the server log:
201 # [ERROR] [MY-010211] [Server] Got error 126 when reading table './test/t2'
202 call mtr.add_suppression(".*Got error 126 when reading table '.*t2'");
203
204 ## All done
205 DROP TABLE t1;
206 DROP TABLE t2;
207 --remove_file $NDB_TOOLS_OUTPUT
208