Debian Codegraph @

mysql-test/suite/innodb/t/innodb_rename_index.test

raw
1 #
2 # Test "ALTER TABLE ... RENAME INDEX" in InnoDB
3 #
4
5 let create =
6 CREATE TABLE t (
7 a INT,
8 b INT,
9 c INT,
10 d INT,
11 e INT,
12 f INT,
13 PRIMARY KEY (a),
14 INDEX i1 (b),
15 INDEX i2 (c),
16 INDEX i3 (d),
17 INDEX i4 (e)
18 ) ENGINE=INNODB;
19
20 let insert = INSERT INTO t SET a = 1;
21
22 let show_table =
23 SHOW CREATE TABLE t;
24
25 let show_sys =
26 SELECT
27 t.name AS table_name,
28 i.name AS index_name,
29 f.name AS column_name
30 FROM
31 information_schema.innodb_tables t,
32 information_schema.innodb_indexes i,
33 information_schema.innodb_fields f
34 WHERE
35 t.name LIKE '%/t' AND
36 t.table_id = i.table_id AND
37 i.index_id = f.index_id
38 ORDER BY 1, 2, 3;
39
40 let show_stats =
41 SELECT index_name, stat_description
42 FROM mysql.innodb_index_stats
43 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
44 ORDER BY 1, 2;
45
46 -- eval $create
47
48 # Add a row, so that affected rows would be nonzero for ALGORITHM=COPY.
49 # ALGORITHM=INPLACE will report 0 affected row in the result file.
50 # We will have enable_info/disable_info around every successful ALTER
51 # to enable the affected rows: output in the result file.
52 -- eval $insert
53
54 -- error ER_WRONG_NAME_FOR_INDEX
55 ALTER TABLE t RENAME INDEX i1 TO GEN_CLUST_INDEX;
56
57 # Test all combinations of ADD w, DROP x, RENAME y TO z.
58 #
59 # Use the following names for wxyz (with 1 to 4 of wxyz being the same):
60 # aaaa abcd aabb abab abba abcc acbc accb cacb cabc ccab aaab aaba abaa baaa
61 #
62 # Some cases should trivially succeed or fail. Test them in isolation:
63 # no-op: y=z (RENAME y TO y)
64 # rules out the combinations ..\(.\)\1
65 # a.k.a. aaaa aabb abcc abaa baaa
66
67 # We use the index names i1 to i4 for existing indexes abcd.
68 # Non-existing index names will be aa,bb,cc,dd.
69 # Index creation on non-existing columns will not be tested.
70
71 ALTER TABLE t RENAME INDEX i1 TO i1;
72 -- error ER_KEY_DOES_NOT_EXITS
73 ALTER TABLE t RENAME INDEX aa TO aa;
74
75 -- echo # combination: aaaa
76 # drop/add existing, null rename and drop the same
77 -- error ER_KEY_DOES_NOT_EXITS
78 ALTER TABLE t ADD INDEX i4(f), DROP INDEX i4, RENAME INDEX i4 TO i4;
79
80 -- echo # combination: aabb
81 -- error ER_CANT_DROP_FIELD_OR_KEY
82 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX i2 TO i2;
83 -- error ER_CANT_DROP_FIELD_OR_KEY
84 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX bb TO bb;
85 -- error ER_KEY_DOES_NOT_EXITS
86 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX bb TO bb;
87
88 -- enable_info
89 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i2 TO i2;
90 -- disable_info
91 -- eval $show_table
92 -- eval $show_sys
93 -- eval $show_stats
94 DROP TABLE t;
95 -- eval $create
96 -- eval $insert
97
98 -- echo # combination: abcc
99
100 -- error ER_CANT_DROP_FIELD_OR_KEY
101 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX cc TO cc;
102 -- error ER_CANT_DROP_FIELD_OR_KEY
103 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX i3 TO i3;
104 -- error ER_KEY_DOES_NOT_EXITS
105 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO cc;
106
107 # rename existing (succeeds)
108 -- enable_info
109 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i3;
110 -- disable_info
111 -- eval $show_table
112 -- eval $show_sys
113 -- eval $show_stats
114 DROP TABLE t;
115 -- eval $create
116 -- eval $insert
117
118 -- echo # combination: abaa
119
120 -- error ER_KEY_DOES_NOT_EXITS
121 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i1, RENAME INDEX aa TO aa;
122 -- error ER_DUP_KEYNAME
123 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i1;
124 -- error ER_CANT_DROP_FIELD_OR_KEY
125 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO i1;
126 -- error ER_CANT_DROP_FIELD_OR_KEY
127 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO aa;
128
129 -- echo # combination: baaa
130
131 -- error ER_KEY_DOES_NOT_EXITS
132 ALTER TABLE t ADD INDEX i2(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
133 -- error ER_KEY_DOES_NOT_EXITS
134 ALTER TABLE t ADD INDEX bb(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
135 -- error ER_CANT_DROP_FIELD_OR_KEY
136 ALTER TABLE t ADD INDEX i2(f), DROP INDEX aa, RENAME INDEX aa TO aa;
137 -- error ER_CANT_DROP_FIELD_OR_KEY
138 ALTER TABLE t ADD INDEX bb(f), DROP INDEX aa, RENAME INDEX aa TO aa;
139
140 # refuse: w=z (ADD w, RENAME y TO w)
141 # rules out the combinations \(.\)..\1
142 # a.k.a. aaaa abba cabc aaba abaa
143 # the case w=y (ADD w, RENAME w to z) may succeed, as seen below
144
145 -- error ER_KEY_DOES_NOT_EXITS
146 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX aa TO bb;
147 -- error ER_KEY_DOES_NOT_EXITS
148 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX bb TO aa;
149 -- error ER_DUP_KEYNAME
150 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX i2 TO aa;
151
152 # rename existing, add one with the same name
153 -- enable_info
154 ALTER TABLE t ADD INDEX i1(f), RENAME INDEX i1 TO bb;
155 -- disable_info
156 -- eval $show_table
157 -- eval $show_sys
158 -- eval $show_stats
159 DROP TABLE t;
160 -- eval $create
161 -- eval $insert
162
163 -- echo # combination: abba
164
165 -- error ER_KEY_DOES_NOT_EXITS
166 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i2 TO i1;
167 -- error ER_KEY_DOES_NOT_EXITS
168 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i2 TO aa;
169 -- error ER_CANT_DROP_FIELD_OR_KEY
170 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX bb TO i1;
171 -- error ER_CANT_DROP_FIELD_OR_KEY
172 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX bb TO aa;
173
174 -- echo # combination: cabc
175
176 -- error ER_DUP_KEYNAME
177 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i2 TO i3;
178 -- error ER_CANT_DROP_FIELD_OR_KEY
179 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i2 TO i3;
180 -- error ER_KEY_DOES_NOT_EXITS
181 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX bb TO i3;
182 -- error ER_CANT_DROP_FIELD_OR_KEY
183 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX bb TO i3;
184
185 -- error ER_DUP_KEYNAME
186 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX i2 TO cc;
187 -- error ER_CANT_DROP_FIELD_OR_KEY
188 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX i2 TO cc;
189 -- error ER_KEY_DOES_NOT_EXITS
190 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX bb TO cc;
191 -- error ER_CANT_DROP_FIELD_OR_KEY
192 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX bb TO cc;
193
194 # refuse: x=y (DROP x, RENAME x TO z)
195 # rules out the combinations .\(.\)\1.
196 # a.k.a. aaaa abba accb aaab baaa
197
198 # rename and drop the same
199 -- error ER_KEY_DOES_NOT_EXITS
200 ALTER TABLE t DROP INDEX i1, RENAME INDEX i1 TO bb;
201 # drop non-existing
202 -- error ER_CANT_DROP_FIELD_OR_KEY
203 ALTER TABLE t DROP INDEX aa, RENAME INDEX i2 TO aa;
204 -- error ER_CANT_DROP_FIELD_OR_KEY
205 ALTER TABLE t DROP INDEX aa, RENAME INDEX aa TO i2;
206
207 # this one will succeed (drop, replace with an existing one)
208 -- enable_info
209 ALTER TABLE t DROP INDEX i1, RENAME INDEX i4 TO i1;
210 -- disable_info
211 -- eval $show_table
212 -- eval $show_sys
213 -- eval $show_stats
214 DROP TABLE t;
215 -- eval $create
216 -- eval $insert
217
218 -- echo # combination: accb
219
220 -- error ER_KEY_DOES_NOT_EXITS
221 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO i2;
222 -- error ER_KEY_DOES_NOT_EXITS
223 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO bb;
224 -- error ER_CANT_DROP_FIELD_OR_KEY
225 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO i2;
226 -- error ER_CANT_DROP_FIELD_OR_KEY
227 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO bb;
228 -- error ER_CANT_DROP_FIELD_OR_KEY
229 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO i2;
230 -- error ER_CANT_DROP_FIELD_OR_KEY
231 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO bb;
232
233 -- echo # combination: aaab
234
235 -- error ER_KEY_DOES_NOT_EXITS
236 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
237 -- error ER_KEY_DOES_NOT_EXITS
238 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO bb;
239 -- error ER_KEY_DOES_NOT_EXITS
240 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
241 -- error ER_CANT_DROP_FIELD_OR_KEY
242 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX aa TO bb;
243
244 # Remaining combinations: abcd abab acbc cacb ccab
245
246 -- echo # combination: abcd
247
248 -- error ER_KEY_DOES_NOT_EXITS
249 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO i4;
250 -- error ER_KEY_DOES_NOT_EXITS
251 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO dd;
252 -- error ER_KEY_DOES_NOT_EXITS
253 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO i4;
254 -- error ER_KEY_DOES_NOT_EXITS
255 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO dd;
256
257 # add existing, rename to existing
258 -- error ER_DUP_KEYNAME
259 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
260 # add existing
261 -- error ER_DUP_KEYNAME
262 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
263 # rename to existing
264 -- error ER_DUP_KEYNAME
265 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
266
267 -- enable_info
268 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
269 -- disable_info
270 -- eval $show_table
271 -- eval $show_sys
272 -- eval $show_stats
273 DROP TABLE t;
274 -- eval $create
275 -- eval $insert
276
277 -- echo # combination: abab
278
279 -- enable_info
280 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i2;
281 -- disable_info
282 -- eval $show_table
283 -- eval $show_sys
284 -- eval $show_stats
285 DROP TABLE t;
286 -- eval $create
287 -- eval $insert
288
289 -- error ER_CANT_DROP_FIELD_OR_KEY
290 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO bb;
291 -- error ER_KEY_DOES_NOT_EXITS
292 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX aa TO i2;
293 -- error ER_CANT_DROP_FIELD_OR_KEY
294 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO bb;
295
296 -- echo # combination: acbc
297
298 -- error ER_CANT_DROP_FIELD_OR_KEY
299 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
300 -- error ER_CANT_DROP_FIELD_OR_KEY
301 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
302 -- error ER_CANT_DROP_FIELD_OR_KEY
303 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX bb TO cc;
304 -- error ER_CANT_DROP_FIELD_OR_KEY
305 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX bb TO cc;
306 -- error ER_KEY_DOES_NOT_EXITS
307 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX bb TO i3;
308 -- error ER_KEY_DOES_NOT_EXITS
309 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX bb TO i3;
310
311 # add existing
312 -- error ER_DUP_KEYNAME
313 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
314
315 -- enable_info
316 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
317 -- disable_info
318 -- eval $show_table
319 -- eval $show_sys
320 -- eval $show_stats
321 DROP TABLE t;
322 -- eval $create
323 -- eval $insert
324
325 -- echo # combination: cacb
326
327 -- error ER_KEY_DOES_NOT_EXITS
328 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO i2;
329 -- error ER_CANT_DROP_FIELD_OR_KEY
330 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO i2;
331 -- error ER_CANT_DROP_FIELD_OR_KEY
332 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO bb;
333 -- error ER_KEY_DOES_NOT_EXITS
334 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO bb;
335
336 -- error ER_DUP_KEYNAME
337 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO i2;
338 -- error ER_CANT_DROP_FIELD_OR_KEY
339 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO i2;
340 -- error ER_CANT_DROP_FIELD_OR_KEY
341 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO bb;
342
343 -- enable_info
344 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO bb;
345 -- disable_info
346 -- eval $show_table
347 -- eval $show_sys
348 -- eval $show_stats
349 DROP TABLE t;
350 -- eval $create
351 -- eval $insert
352
353 -- echo # combination: ccab
354
355 -- error ER_CANT_DROP_FIELD_OR_KEY
356 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO i2;
357 -- error ER_CANT_DROP_FIELD_OR_KEY
358 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO bb;
359 -- error ER_CANT_DROP_FIELD_OR_KEY
360 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO i2;
361 -- error ER_CANT_DROP_FIELD_OR_KEY
362 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO bb;
363 -- error ER_CANT_DROP_FIELD_OR_KEY
364 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO i2;
365 -- error ER_CANT_DROP_FIELD_OR_KEY
366 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO bb;
367
368 -- error ER_DUP_KEYNAME
369 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO i2;
370
371 -- enable_info
372 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO bb;
373 -- disable_info
374 -- eval $show_table
375 -- eval $show_sys
376 -- eval $show_stats
377 DROP TABLE t;
378 -- eval $create
379 -- eval $insert
380
381 # A simple successful ALTER
382 -- enable_info
383 ALTER TABLE t RENAME INDEX i1 TO x;
384 -- disable_info
385 -- eval $show_table
386 -- eval $show_sys
387 -- eval $show_stats
388 DROP TABLE t;
389 -- eval $create
390 -- eval $insert
391
392 -- error ER_DUP_KEYNAME
393 ALTER TABLE t RENAME INDEX i1 TO i2;
394
395 -- error ER_KEY_DOES_NOT_EXITS
396 ALTER TABLE t RENAME INDEX foo TO i1;
397
398 # Test ADD INDEX, RENAME INDEX
399
400 -- enable_info
401 ALTER TABLE t ADD INDEX i9 (f), RENAME INDEX i1 TO i8;
402 -- disable_info
403 -- eval $show_table
404 -- eval $show_sys
405 -- eval $show_stats
406 DROP TABLE t;
407 -- eval $create
408 -- eval $insert
409
410 -- enable_info
411 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO i9;
412 -- disable_info
413 -- eval $show_table
414 -- eval $show_sys
415 -- eval $show_stats
416 DROP TABLE t;
417 -- eval $create
418 -- eval $insert
419
420 -- error ER_DUP_KEYNAME
421 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX i1 TO foo;
422
423 # Test ADD INDEX, RENAME INDEX, DROP INDEX
424
425 -- error ER_KEY_DOES_NOT_EXITS
426 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX i1;
427
428 -- error ER_CANT_DROP_FIELD_OR_KEY
429 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX foo;
430
431 -- error ER_CANT_DROP_FIELD_OR_KEY
432 # "ALTER TABLE t ADD INDEX foo (d), DROP INDEX foo;" alone fails with the
433 # same error code, but we have that test here anyway
434 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX foo TO bar, DROP INDEX foo;
435
436 # Test RENAME INDEX, RENAME INDEX
437
438 -- error ER_DUP_KEYNAME
439 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i2 TO x;
440
441 -- error ER_KEY_DOES_NOT_EXITS
442 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO y;
443
444 -- error ER_KEY_DOES_NOT_EXITS
445 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO x;
446
447 # show that the table did not change after all the erroneous ALTERs
448 -- eval $show_table
449 -- eval $show_sys
450 -- eval $show_stats
451
452 DROP TABLE t;
453
454 # now test the rebuild case (new clustered index)
455
456 CREATE TABLE t (
457 c1 INT NOT NULL,
458 c2 INT NOT NULL,
459 c3 INT,
460 c4 INT,
461 PRIMARY KEY (c1),
462 INDEX i1 (c3),
463 INDEX i2 (c4)
464 ) ENGINE=INNODB;
465
466 INSERT INTO t SET c1=1, c2=2;
467
468 -- enable_info
469 ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (c2), RENAME INDEX i1 TO x;
470 -- disable_info
471
472 -- eval $show_table
473 -- eval $show_sys
474 -- eval $show_stats
475
476 -- enable_info
477 ALTER TABLE t RENAME INDEX i2 TO y, ROW_FORMAT=REDUNDANT;
478 -- disable_info
479
480 -- eval $show_table
481 -- eval $show_sys
482 -- eval $show_stats
483
484 DROP TABLE t;
485
486 # a case where the PK does not exist prior to the ALTER TABLE command
487
488 CREATE TABLE t (
489 c1 INT NOT NULL,
490 c2 INT,
491 c3 INT,
492 INDEX i1 (c2),
493 INDEX i2 (c3)
494 ) ENGINE=INNODB;
495
496 INSERT INTO t SET c1=1;
497
498 -- enable_info
499 ALTER TABLE t ADD PRIMARY KEY (c1), RENAME INDEX i1 TO x;
500 -- disable_info
501 -- eval $show_table
502 -- eval $show_sys
503 -- eval $show_stats
504
505 DROP TABLE t;
506
507 # Test repeated RENAMEs with alternating names
508
509 CREATE TABLE t (a INT, INDEX iiiii (a)) ENGINE=INNODB;
510 INSERT INTO t SET a=NULL;
511 -- enable_info
512 ALTER TABLE t RENAME INDEX iiiii TO i;
513 ALTER TABLE t RENAME INDEX i TO iiiii;
514 ALTER TABLE t RENAME INDEX iiiii TO i;
515 ALTER TABLE t RENAME INDEX i TO iiiii;
516 -- disable_info
517 DROP TABLE t;
518
519 # Below is a shell script to generate the full set of ALTER TABLE
520 # DROP/ADD/RENAME combinations. The generated .sql file is 3.3MB and
521 # executes in about 7 minutes.
522 #
523 ##!/bin/sh
524 #
525 #create="
526 #CREATE TABLE t (
527 # a INT,
528 # b INT,
529 # c INT,
530 # d INT,
531 # PRIMARY KEY (a),
532 # INDEX i1 (b),
533 # INDEX i2 (c)
534 #) ENGINE=INNODB;
535 #"
536 #
537 #echo "DROP TABLE IF EXISTS t;"
538 #for r in "" ", DROP PRIMARY KEY, ADD PRIMARY KEY (a)" ", ROW_FORMAT=REDUNDANT" ; do
539 # for i1 in i1 i1noexist; do
540 # for i2 in i2 i2noexist; do
541 # for i3 in i3 i3noexist; do
542 # for i4 in i4 i4noexist; do
543 # for a in $i1 $i2 $i3 $i4; do
544 # for b in $i1 $i2 $i3 $i4; do
545 # for c in $i1 $i2 $i3 $i4; do
546 # for d in $i1 $i2 $i3 $i4; do
547 # echo "$create"
548 # echo "ALTER TABLE t ADD INDEX $a (d), RENAME INDEX $b TO $c, DROP INDEX $d $r;"
549 # echo "DROP TABLE t;"
550 # done
551 # done
552 # done
553 # done
554 # done
555 # done
556 # done
557 # done
558 #done