Debian Codegraph @

mysql-test/suite/ndb/r/ndb_use_dropped_index.result

raw
1 result_format: 2
2 create table t1(
3 a int not null primary key, # intrinsic PK plus PRIMARY ordered index
4 b int not null,
5 c int,
6 d int not null,
7 unique(b) using hash, # unique index only
8 index(c), # ordered index only
9 unique(d) # ordered and unique indexes
10 ) engine=ndb;
11
12 insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
13
14 ## Select on the PRIMARY ordered index
15 select a, count(*) from t1 group by a order by a;
16 a count(*)
17 1 1
18 2 1
19 3 1
20 4 1
21
22 ## You can make an index invisible, and then visible again
23 select c, count(1) from t1 group by c order by c;
24 c count(1)
25 1 1
26 2 1
27 3 1
28 4 1
29 EXPLAIN select c, count(1) from t1 group by c order by c;
30 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
31 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c c 5 NULL 4 100.00 NULL
32 Warnings:
33 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c`,count(1) AS `count(1)` from `test`.`t1` group by `test`.`t1`.`c` order by `test`.`t1`.`c`
34 ALTER TABLE t1 ALTER INDEX c invisible;
35 EXPLAIN select c, count(1) from t1 group by c order by c;
36 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
37 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
38 Warnings:
39 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c`,count(1) AS `count(1)` from `test`.`t1` group by `test`.`t1`.`c` order by `test`.`t1`.`c`
40 ALTER TABLE t1 ALTER INDEX c visible;
41
42 ##########################################
43 ## Use ndb_drop_index to drop PRIMARY
44 ## First query after drop fails with stale metadata
45 select * from t1 where a > 3;
46 ERROR HY000: Table definition has changed, please retry transaction
47
48 ## Second time succeeds. No warnings. (Uses pushed condition, not an index)
49 select * from t1 where a > 3;
50 a b c d
51 4 4 4 4
52
53 ## A query trying to use the index fails and gives warning
54 select a, count(1) from t1 group by a;
55 ERROR HY000: Incorrect key file for table 't1'; try to repair it
56 show warnings;
57 Level Code Message
58 Warning 1034 Index PRIMARY is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX PRIMARY INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
59 Error 1034 Incorrect key file for table 't1'; try to repair it
60
61 ## Add a column. This runs a DD-vs-NDB metadata check on the altered
62 ## table which previously would have failed. The new column has both
63 ## ordered and unique indexes.
64 ALTER TABLE t1 ADD e int;
65 Warnings:
66 Warning 1478 Converted FIXED field 'e' to DYNAMIC to enable online ADD COLUMN
67 ALTER TABLE t1 ADD unique(e);
68 insert into t1 values (5,5,5,5,5);
69 UPDATE t1 set e = d;
70 SELECT * FROM t1;
71 a b c d e
72 1 1 1 1 1
73 2 2 2 2 2
74 3 3 3 3 3
75 4 4 4 4 4
76 5 5 5 5 5
77
78 ##########################################
79 ## Use ndb_drop_index to drop the unique index on b.
80 ## First attempt to use dropped index fails with stale metadata
81 SELECT * FROM t1 WHERE b = 3;
82 ERROR HY000: Table definition has changed, please retry transaction
83
84 ## Second attempt fails with bad index.
85 SELECT * FROM t1 WHERE b = 3;
86 ERROR HY000: Incorrect key file for table 't1'; try to repair it
87 show warnings;
88 Level Code Message
89 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
90 Error 1034 Incorrect key file for table 't1'; try to repair it
91
92 call mtr.add_suppression(".*Got error 126 when reading table '.*t1'");
93
94 ## Attempt MRR read via dropped uk index
95 select * from t1 where b in (2,4,6,8);
96 ERROR HY000: Incorrect key file for table 't1'; try to repair it
97 show warnings;
98 Level Code Message
99 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
100 Error 1034 Incorrect key file for table 't1'; try to repair it
101
102 ## Attempt join with dropped uk lookup as root, pk lookup as child
103 select * from t1 x join t1 y on x.a = y.c where y.b=5;
104 ERROR HY000: Incorrect key file for table 't1'; try to repair it
105 show warnings;
106 Level Code Message
107 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
108 Error 1034 Incorrect key file for table 't1'; try to repair it
109
110 ## Attempt join with pk lookup as root, dropped uk lookup as child
111 select * from t1 x join t1 y on y.b = x.c where x.a=3;
112 ERROR HY000: Incorrect key file for table 't1'; try to repair it
113 show warnings;
114 Level Code Message
115 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
116 Error 1034 Incorrect key file for table 't1'; try to repair it
117
118 ## Attempt update ignoring duplicates on dropped uk index
119 update ignore t1 set b=1 where a=4;
120 ERROR HY000: Incorrect key file for table 't1'; try to repair it
121 show warnings;
122 Level Code Message
123 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
124 Error 1034 Incorrect key file for table 't1'; try to repair it
125
126 ## Attempt insert on duplicate key update of dropped uk index
127 insert into t1 values (10,4,4,4,4) on duplicate key update b=b+1;
128 ERROR HY000: Incorrect key file for table 't1'; try to repair it
129 show warnings;
130 Level Code Message
131 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
132 Error 1034 Incorrect key file for table 't1'; try to repair it
133
134 ## Attempt insert which must check for duplicates on dropped
135 ## uk index to ignore errors
136 insert ignore into t1 values (10,4,4,4,4);
137 ERROR HY000: Incorrect key file for table 't1'; try to repair it
138 show warnings;
139 Level Code Message
140 Warning 1034 Index b is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX b INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
141 Error 1034 Incorrect key file for table 't1'; try to repair it
142
143 ##########################################
144 ## Use ndb_drop_index to drop the index on c.
145 ## Stale metadata
146 select c, count(1) from t1 group by c order by c;
147 ERROR HY000: Table definition has changed, please retry transaction
148
149 ## Use of index fails
150 select c, count(1) from t1 group by c order by c;
151 ERROR HY000: Incorrect key file for table 't1'; try to repair it
152
153 ## Make the dropped index invisible. Then all tests will succeed.
154 ALTER TABLE t1 ALTER INDEX c invisible;
155 SELECT * FROM t1 WHERE c = 3;
156 a b c d e
157 3 3 3 3 3
158 select c, count(1) from t1 group by c order by c;
159 c count(1)
160 1 1
161 2 1
162 3 1
163 4 1
164 5 1
165
166 ## Drop the index in MySQL
167 ALTER TABLE t1 DROP INDEX c;
168
169 ## Then rebuild it in MySQL
170 ALTER TABLE t1 add index(c);
171
172 ## And test once more
173 select c, count(1) from t1 group by c order by c;
174 c count(1)
175 1 1
176 2 1
177 3 1
178 4 1
179 5 1
180
181 ##########################################
182 ## Use ndb_drop_index to drop d$unique
183 ## d will be downgraded from UNIQUE_ORDERED_INDEX to ORDERED_INDEX
184 ## Table scan succeeds.
185 SELECT * FROM t1;
186 a b c d e
187 1 1 1 1 1
188 2 2 2 2 2
189 3 3 3 3 3
190 4 4 4 4 4
191 5 5 5 5 5
192
193 ## But, try to use the index? Stale metadata.
194 SELECT * FROM t1 where d = 3;
195 ERROR HY000: Table definition has changed, please retry transaction
196
197 ## Then it succeeds (the ordered index is still available).
198 SELECT * FROM t1 where d = 3;
199 a b c d e
200 3 3 3 3 3
201
202 ## How did it do it? It used the ordered index.
203 EXPLAIN SELECT * FROM t1 where d = 3;
204 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
205 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref d d 4 const 1 100.00 NULL
206 Warnings:
207 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`e` AS `e` from `test`.`t1` where (`test`.`t1`.`d` = 3)
208
209 ## Let's corrupt the unique index while it's not online!
210 UPDATE t1 set d = 14;
211
212 ## ... and then fix it.
213 UPDATE t1 set d = a;
214
215 ##########################################
216 ## Use ndb_drop_index to drop the ordered index on e.
217 ## e will be downgraded from UNIQUE_ORDERED_INDEX to UNIQUE_INDEX
218 ## Table scan succeeds.
219 SELECT * FROM t1;
220 a b c d e
221 1 1 1 1 1
222 2 2 2 2 2
223 3 3 3 3 3
224 4 4 4 4 4
225 5 5 5 5 5
226
227 ## First attempt to use dropped index fails with stale metadata
228 SELECT * FROM t1 where e > 3;
229 ERROR HY000: Table definition has changed, please retry transaction
230
231 ## Second attempt succeeds.
232 SELECT * FROM t1 where e > 3 order by e;
233 a b c d e
234 4 4 4 4 4
235 5 5 5 5 5
236
237 ## A query trying to use the index fails and gives warning
238 select 3, count(*) from t1 group by e order by e;
239 ERROR HY000: Incorrect key file for table 't1'; try to repair it
240 show warnings;
241 Level Code Message
242 Warning 1034 Index e is not available in NDB. Use "ALTER TABLE t1 ALTER INDEX e INVISIBLE" to prevent MySQL from attempting to access it, or use "ndb_restore --rebuild-indexes" to rebuild it.
243 Error 1034 Incorrect key file for table 't1'; try to repair it
244
245 ##########################################
246 ## A small set of tests on a table with some two-part indexes
247 create table t2 (
248 a int not null,
249 b int not null,
250 c int,
251 primary key (a,b), # primary key ordered index on two columns
252 index(b,c) # ordered index on two columns
253 ) engine=ndb;
254
255 insert into t2 values (1,1,null), (2,1,0), (3,1,1), (1,2,1), (1,3,null);
256
257 select * from t2 where a > 2;
258 a b c
259 3 1 1
260 select * from t2 where a > 2;
261 ERROR HY000: Table definition has changed, please retry transaction
262 select * from t2 where a > 2;
263 a b c
264 3 1 1
265 select * from t2 where b = 2;
266 ERROR HY000: Table definition has changed, please retry transaction
267 select * from t2 where b = 2;
268 ERROR HY000: Incorrect key file for table 't2'; try to repair it
269
270 call mtr.add_suppression(".*Got error 126 when reading table '.*t2'");
271
272 ## All done
273 DROP TABLE t1;
274 DROP TABLE t2;