Debian Codegraph @

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

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