Debian Codegraph @

mysql-test/suite/innodb/r/innodb_rename_index.result

raw
1 CREATE TABLE t (
2 a INT,
3 b INT,
4 c INT,
5 d INT,
6 e INT,
7 f INT,
8 PRIMARY KEY (a),
9 INDEX i1 (b),
10 INDEX i2 (c),
11 INDEX i3 (d),
12 INDEX i4 (e)
13 ) ENGINE=INNODB;
14 INSERT INTO t SET a = 1;
15 ALTER TABLE t RENAME INDEX i1 TO GEN_CLUST_INDEX;
16 ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX'
17 ALTER TABLE t RENAME INDEX i1 TO i1;
18 ALTER TABLE t RENAME INDEX aa TO aa;
19 ERROR 42000: Key 'aa' doesn't exist in table 't'
20 # combination: aaaa
21 ALTER TABLE t ADD INDEX i4(f), DROP INDEX i4, RENAME INDEX i4 TO i4;
22 ERROR 42000: Key 'i4' doesn't exist in table 't'
23 # combination: aabb
24 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX i2 TO i2;
25 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
26 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX bb TO bb;
27 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
28 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX bb TO bb;
29 ERROR 42000: Key 'bb' doesn't exist in table 't'
30 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i2 TO i2;
31 affected rows: 0
32 info: Records: 0 Duplicates: 0 Warnings: 0
33 SHOW CREATE TABLE t;
34 Table Create Table
35 t CREATE TABLE `t` (
36 `a` int(11) NOT NULL,
37 `b` int(11) DEFAULT NULL,
38 `c` int(11) DEFAULT NULL,
39 `d` int(11) DEFAULT NULL,
40 `e` int(11) DEFAULT NULL,
41 `f` int(11) DEFAULT NULL,
42 PRIMARY KEY (`a`),
43 KEY `i2` (`c`),
44 KEY `i3` (`d`),
45 KEY `i4` (`e`),
46 KEY `i1` (`f`)
47 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
48 SELECT
49 t.name AS table_name,
50 i.name AS index_name,
51 f.name AS column_name
52 FROM
53 information_schema.innodb_sys_tables t,
54 information_schema.innodb_sys_indexes i,
55 information_schema.innodb_sys_fields f
56 WHERE
57 t.name LIKE '%/t' AND
58 t.table_id = i.table_id AND
59 i.index_id = f.index_id
60 ORDER BY 1, 2, 3;
61 table_name index_name column_name
62 test/t i1 f
63 test/t i2 c
64 test/t i3 d
65 test/t i4 e
66 test/t PRIMARY a
67 DROP TABLE t;
68 CREATE TABLE t (
69 a INT,
70 b INT,
71 c INT,
72 d INT,
73 e INT,
74 f INT,
75 PRIMARY KEY (a),
76 INDEX i1 (b),
77 INDEX i2 (c),
78 INDEX i3 (d),
79 INDEX i4 (e)
80 ) ENGINE=INNODB;
81 INSERT INTO t SET a = 1;
82 # combination: abcc
83 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX cc TO cc;
84 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
85 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX i3 TO i3;
86 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
87 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO cc;
88 ERROR 42000: Key 'cc' doesn't exist in table 't'
89 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i3;
90 affected rows: 0
91 info: Records: 0 Duplicates: 0 Warnings: 0
92 SHOW CREATE TABLE t;
93 Table Create Table
94 t CREATE TABLE `t` (
95 `a` int(11) NOT NULL,
96 `b` int(11) DEFAULT NULL,
97 `c` int(11) DEFAULT NULL,
98 `d` int(11) DEFAULT NULL,
99 `e` int(11) DEFAULT NULL,
100 `f` int(11) DEFAULT NULL,
101 PRIMARY KEY (`a`),
102 KEY `i1` (`b`),
103 KEY `i3` (`d`),
104 KEY `i4` (`e`),
105 KEY `aa` (`f`)
106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
107 SELECT
108 t.name AS table_name,
109 i.name AS index_name,
110 f.name AS column_name
111 FROM
112 information_schema.innodb_sys_tables t,
113 information_schema.innodb_sys_indexes i,
114 information_schema.innodb_sys_fields f
115 WHERE
116 t.name LIKE '%/t' AND
117 t.table_id = i.table_id AND
118 i.index_id = f.index_id
119 ORDER BY 1, 2, 3;
120 table_name index_name column_name
121 test/t aa f
122 test/t i1 b
123 test/t i3 d
124 test/t i4 e
125 test/t PRIMARY a
126 DROP TABLE t;
127 CREATE TABLE t (
128 a INT,
129 b INT,
130 c INT,
131 d INT,
132 e INT,
133 f INT,
134 PRIMARY KEY (a),
135 INDEX i1 (b),
136 INDEX i2 (c),
137 INDEX i3 (d),
138 INDEX i4 (e)
139 ) ENGINE=INNODB;
140 INSERT INTO t SET a = 1;
141 # combination: abaa
142 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i1, RENAME INDEX aa TO aa;
143 ERROR 42000: Key 'aa' doesn't exist in table 't'
144 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i1;
145 ERROR 42000: Duplicate key name 'i1'
146 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO i1;
147 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
148 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO aa;
149 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
150 # combination: baaa
151 ALTER TABLE t ADD INDEX i2(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
152 ERROR 42000: Key 'i1' doesn't exist in table 't'
153 ALTER TABLE t ADD INDEX bb(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
154 ERROR 42000: Key 'i1' doesn't exist in table 't'
155 ALTER TABLE t ADD INDEX i2(f), DROP INDEX aa, RENAME INDEX aa TO aa;
156 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
157 ALTER TABLE t ADD INDEX bb(f), DROP INDEX aa, RENAME INDEX aa TO aa;
158 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
159 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX aa TO bb;
160 ERROR 42000: Key 'aa' doesn't exist in table 't'
161 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX bb TO aa;
162 ERROR 42000: Key 'bb' doesn't exist in table 't'
163 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX i2 TO aa;
164 ERROR 42000: Duplicate key name 'aa'
165 ALTER TABLE t ADD INDEX i1(f), RENAME INDEX i1 TO bb;
166 affected rows: 0
167 info: Records: 0 Duplicates: 0 Warnings: 0
168 SHOW CREATE TABLE t;
169 Table Create Table
170 t CREATE TABLE `t` (
171 `a` int(11) NOT NULL,
172 `b` int(11) DEFAULT NULL,
173 `c` int(11) DEFAULT NULL,
174 `d` int(11) DEFAULT NULL,
175 `e` int(11) DEFAULT NULL,
176 `f` int(11) DEFAULT NULL,
177 PRIMARY KEY (`a`),
178 KEY `bb` (`b`),
179 KEY `i2` (`c`),
180 KEY `i3` (`d`),
181 KEY `i4` (`e`),
182 KEY `i1` (`f`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
184 SELECT
185 t.name AS table_name,
186 i.name AS index_name,
187 f.name AS column_name
188 FROM
189 information_schema.innodb_sys_tables t,
190 information_schema.innodb_sys_indexes i,
191 information_schema.innodb_sys_fields f
192 WHERE
193 t.name LIKE '%/t' AND
194 t.table_id = i.table_id AND
195 i.index_id = f.index_id
196 ORDER BY 1, 2, 3;
197 table_name index_name column_name
198 test/t bb b
199 test/t i1 f
200 test/t i2 c
201 test/t i3 d
202 test/t i4 e
203 test/t PRIMARY a
204 DROP TABLE t;
205 CREATE TABLE t (
206 a INT,
207 b INT,
208 c INT,
209 d INT,
210 e INT,
211 f INT,
212 PRIMARY KEY (a),
213 INDEX i1 (b),
214 INDEX i2 (c),
215 INDEX i3 (d),
216 INDEX i4 (e)
217 ) ENGINE=INNODB;
218 INSERT INTO t SET a = 1;
219 # combination: abba
220 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i2 TO i1;
221 ERROR 42000: Key 'i2' doesn't exist in table 't'
222 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i2 TO aa;
223 ERROR 42000: Key 'i2' doesn't exist in table 't'
224 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX bb TO i1;
225 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
226 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX bb TO aa;
227 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
228 # combination: cabc
229 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i2 TO i3;
230 ERROR 42000: Duplicate key name 'i3'
231 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i2 TO i3;
232 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
233 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX bb TO i3;
234 ERROR 42000: Key 'bb' doesn't exist in table 't'
235 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX bb TO i3;
236 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
237 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX i2 TO cc;
238 ERROR 42000: Duplicate key name 'cc'
239 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX i2 TO cc;
240 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
241 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX bb TO cc;
242 ERROR 42000: Key 'bb' doesn't exist in table 't'
243 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX bb TO cc;
244 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
245 ALTER TABLE t DROP INDEX i1, RENAME INDEX i1 TO bb;
246 ERROR 42000: Key 'i1' doesn't exist in table 't'
247 ALTER TABLE t DROP INDEX aa, RENAME INDEX i2 TO aa;
248 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
249 ALTER TABLE t DROP INDEX aa, RENAME INDEX aa TO i2;
250 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
251 ALTER TABLE t DROP INDEX i1, RENAME INDEX i4 TO i1;
252 affected rows: 0
253 info: Records: 0 Duplicates: 0 Warnings: 0
254 SHOW CREATE TABLE t;
255 Table Create Table
256 t CREATE TABLE `t` (
257 `a` int(11) NOT NULL,
258 `b` int(11) DEFAULT NULL,
259 `c` int(11) DEFAULT NULL,
260 `d` int(11) DEFAULT NULL,
261 `e` int(11) DEFAULT NULL,
262 `f` int(11) DEFAULT NULL,
263 PRIMARY KEY (`a`),
264 KEY `i2` (`c`),
265 KEY `i3` (`d`),
266 KEY `i1` (`e`)
267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
268 SELECT
269 t.name AS table_name,
270 i.name AS index_name,
271 f.name AS column_name
272 FROM
273 information_schema.innodb_sys_tables t,
274 information_schema.innodb_sys_indexes i,
275 information_schema.innodb_sys_fields f
276 WHERE
277 t.name LIKE '%/t' AND
278 t.table_id = i.table_id AND
279 i.index_id = f.index_id
280 ORDER BY 1, 2, 3;
281 table_name index_name column_name
282 test/t i1 e
283 test/t i2 c
284 test/t i3 d
285 test/t PRIMARY a
286 DROP TABLE t;
287 CREATE TABLE t (
288 a INT,
289 b INT,
290 c INT,
291 d INT,
292 e INT,
293 f INT,
294 PRIMARY KEY (a),
295 INDEX i1 (b),
296 INDEX i2 (c),
297 INDEX i3 (d),
298 INDEX i4 (e)
299 ) ENGINE=INNODB;
300 INSERT INTO t SET a = 1;
301 # combination: accb
302 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO i2;
303 ERROR 42000: Key 'i3' doesn't exist in table 't'
304 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO bb;
305 ERROR 42000: Key 'i3' doesn't exist in table 't'
306 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO i2;
307 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
308 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO bb;
309 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
310 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO i2;
311 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
312 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO bb;
313 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
314 # combination: aaab
315 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
316 ERROR 42000: Key 'i1' doesn't exist in table 't'
317 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO bb;
318 ERROR 42000: Key 'i1' doesn't exist in table 't'
319 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
320 ERROR 42000: Key 'i1' doesn't exist in table 't'
321 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX aa TO bb;
322 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
323 # combination: abcd
324 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO i4;
325 ERROR 42000: Key 'cc' doesn't exist in table 't'
326 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO dd;
327 ERROR 42000: Key 'cc' doesn't exist in table 't'
328 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO i4;
329 ERROR 42000: Key 'cc' doesn't exist in table 't'
330 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO dd;
331 ERROR 42000: Key 'cc' doesn't exist in table 't'
332 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
333 ERROR 42000: Duplicate key name 'i4'
334 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
335 ERROR 42000: Duplicate key name 'i1'
336 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
337 ERROR 42000: Duplicate key name 'i4'
338 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
339 affected rows: 0
340 info: Records: 0 Duplicates: 0 Warnings: 0
341 SHOW CREATE TABLE t;
342 Table Create Table
343 t CREATE TABLE `t` (
344 `a` int(11) NOT NULL,
345 `b` int(11) DEFAULT NULL,
346 `c` int(11) DEFAULT NULL,
347 `d` int(11) DEFAULT NULL,
348 `e` int(11) DEFAULT NULL,
349 `f` int(11) DEFAULT NULL,
350 PRIMARY KEY (`a`),
351 KEY `i1` (`b`),
352 KEY `dd` (`d`),
353 KEY `i4` (`e`),
354 KEY `aa` (`f`)
355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
356 SELECT
357 t.name AS table_name,
358 i.name AS index_name,
359 f.name AS column_name
360 FROM
361 information_schema.innodb_sys_tables t,
362 information_schema.innodb_sys_indexes i,
363 information_schema.innodb_sys_fields f
364 WHERE
365 t.name LIKE '%/t' AND
366 t.table_id = i.table_id AND
367 i.index_id = f.index_id
368 ORDER BY 1, 2, 3;
369 table_name index_name column_name
370 test/t aa f
371 test/t dd d
372 test/t i1 b
373 test/t i4 e
374 test/t PRIMARY a
375 DROP TABLE t;
376 CREATE TABLE t (
377 a INT,
378 b INT,
379 c INT,
380 d INT,
381 e INT,
382 f INT,
383 PRIMARY KEY (a),
384 INDEX i1 (b),
385 INDEX i2 (c),
386 INDEX i3 (d),
387 INDEX i4 (e)
388 ) ENGINE=INNODB;
389 INSERT INTO t SET a = 1;
390 # combination: abab
391 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i2;
392 affected rows: 0
393 info: Records: 0 Duplicates: 0 Warnings: 0
394 SHOW CREATE TABLE t;
395 Table Create Table
396 t CREATE TABLE `t` (
397 `a` int(11) NOT NULL,
398 `b` int(11) DEFAULT NULL,
399 `c` int(11) DEFAULT NULL,
400 `d` int(11) DEFAULT NULL,
401 `e` int(11) DEFAULT NULL,
402 `f` int(11) DEFAULT NULL,
403 PRIMARY KEY (`a`),
404 KEY `i2` (`b`),
405 KEY `i3` (`d`),
406 KEY `i4` (`e`),
407 KEY `i1` (`f`)
408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
409 SELECT
410 t.name AS table_name,
411 i.name AS index_name,
412 f.name AS column_name
413 FROM
414 information_schema.innodb_sys_tables t,
415 information_schema.innodb_sys_indexes i,
416 information_schema.innodb_sys_fields f
417 WHERE
418 t.name LIKE '%/t' AND
419 t.table_id = i.table_id AND
420 i.index_id = f.index_id
421 ORDER BY 1, 2, 3;
422 table_name index_name column_name
423 test/t i1 f
424 test/t i2 b
425 test/t i3 d
426 test/t i4 e
427 test/t PRIMARY a
428 DROP TABLE t;
429 CREATE TABLE t (
430 a INT,
431 b INT,
432 c INT,
433 d INT,
434 e INT,
435 f INT,
436 PRIMARY KEY (a),
437 INDEX i1 (b),
438 INDEX i2 (c),
439 INDEX i3 (d),
440 INDEX i4 (e)
441 ) ENGINE=INNODB;
442 INSERT INTO t SET a = 1;
443 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO bb;
444 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
445 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX aa TO i2;
446 ERROR 42000: Key 'aa' doesn't exist in table 't'
447 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO bb;
448 ERROR 42000: Can't DROP INDEX `bb`; check that it exists
449 # combination: acbc
450 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
451 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
452 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
453 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
454 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX bb TO cc;
455 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
456 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX bb TO cc;
457 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
458 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX bb TO i3;
459 ERROR 42000: Key 'bb' doesn't exist in table 't'
460 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX bb TO i3;
461 ERROR 42000: Key 'bb' doesn't exist in table 't'
462 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
463 ERROR 42000: Duplicate key name 'i1'
464 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
465 affected rows: 0
466 info: Records: 0 Duplicates: 0 Warnings: 0
467 SHOW CREATE TABLE t;
468 Table Create Table
469 t CREATE TABLE `t` (
470 `a` int(11) NOT NULL,
471 `b` int(11) DEFAULT NULL,
472 `c` int(11) DEFAULT NULL,
473 `d` int(11) DEFAULT NULL,
474 `e` int(11) DEFAULT NULL,
475 `f` int(11) DEFAULT NULL,
476 PRIMARY KEY (`a`),
477 KEY `i1` (`b`),
478 KEY `i3` (`c`),
479 KEY `i4` (`e`),
480 KEY `aa` (`f`)
481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
482 SELECT
483 t.name AS table_name,
484 i.name AS index_name,
485 f.name AS column_name
486 FROM
487 information_schema.innodb_sys_tables t,
488 information_schema.innodb_sys_indexes i,
489 information_schema.innodb_sys_fields f
490 WHERE
491 t.name LIKE '%/t' AND
492 t.table_id = i.table_id AND
493 i.index_id = f.index_id
494 ORDER BY 1, 2, 3;
495 table_name index_name column_name
496 test/t aa f
497 test/t i1 b
498 test/t i3 c
499 test/t i4 e
500 test/t PRIMARY a
501 DROP TABLE t;
502 CREATE TABLE t (
503 a INT,
504 b INT,
505 c INT,
506 d INT,
507 e INT,
508 f INT,
509 PRIMARY KEY (a),
510 INDEX i1 (b),
511 INDEX i2 (c),
512 INDEX i3 (d),
513 INDEX i4 (e)
514 ) ENGINE=INNODB;
515 INSERT INTO t SET a = 1;
516 # combination: cacb
517 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO i2;
518 ERROR 42000: Key 'cc' doesn't exist in table 't'
519 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO i2;
520 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
521 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO bb;
522 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
523 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO bb;
524 ERROR 42000: Key 'cc' doesn't exist in table 't'
525 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO i2;
526 ERROR 42000: Duplicate key name 'i2'
527 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO i2;
528 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
529 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO bb;
530 ERROR 42000: Can't DROP INDEX `aa`; check that it exists
531 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO bb;
532 affected rows: 0
533 info: Records: 0 Duplicates: 0 Warnings: 0
534 SHOW CREATE TABLE t;
535 Table Create Table
536 t CREATE TABLE `t` (
537 `a` int(11) NOT NULL,
538 `b` int(11) DEFAULT NULL,
539 `c` int(11) DEFAULT NULL,
540 `d` int(11) DEFAULT NULL,
541 `e` int(11) DEFAULT NULL,
542 `f` int(11) DEFAULT NULL,
543 PRIMARY KEY (`a`),
544 KEY `i2` (`c`),
545 KEY `bb` (`d`),
546 KEY `i4` (`e`),
547 KEY `i3` (`f`)
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
549 SELECT
550 t.name AS table_name,
551 i.name AS index_name,
552 f.name AS column_name
553 FROM
554 information_schema.innodb_sys_tables t,
555 information_schema.innodb_sys_indexes i,
556 information_schema.innodb_sys_fields f
557 WHERE
558 t.name LIKE '%/t' AND
559 t.table_id = i.table_id AND
560 i.index_id = f.index_id
561 ORDER BY 1, 2, 3;
562 table_name index_name column_name
563 test/t bb d
564 test/t i2 c
565 test/t i3 f
566 test/t i4 e
567 test/t PRIMARY a
568 DROP TABLE t;
569 CREATE TABLE t (
570 a INT,
571 b INT,
572 c INT,
573 d INT,
574 e INT,
575 f INT,
576 PRIMARY KEY (a),
577 INDEX i1 (b),
578 INDEX i2 (c),
579 INDEX i3 (d),
580 INDEX i4 (e)
581 ) ENGINE=INNODB;
582 INSERT INTO t SET a = 1;
583 # combination: ccab
584 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO i2;
585 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
586 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO bb;
587 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
588 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO i2;
589 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
590 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO bb;
591 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
592 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO i2;
593 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
594 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO bb;
595 ERROR 42000: Can't DROP INDEX `cc`; check that it exists
596 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO i2;
597 ERROR 42000: Duplicate key name 'i2'
598 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO bb;
599 affected rows: 0
600 info: Records: 0 Duplicates: 0 Warnings: 0
601 SHOW CREATE TABLE t;
602 Table Create Table
603 t CREATE TABLE `t` (
604 `a` int(11) NOT NULL,
605 `b` int(11) DEFAULT NULL,
606 `c` int(11) DEFAULT NULL,
607 `d` int(11) DEFAULT NULL,
608 `e` int(11) DEFAULT NULL,
609 `f` int(11) DEFAULT NULL,
610 PRIMARY KEY (`a`),
611 KEY `bb` (`b`),
612 KEY `i2` (`c`),
613 KEY `i4` (`e`),
614 KEY `i3` (`f`)
615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
616 SELECT
617 t.name AS table_name,
618 i.name AS index_name,
619 f.name AS column_name
620 FROM
621 information_schema.innodb_sys_tables t,
622 information_schema.innodb_sys_indexes i,
623 information_schema.innodb_sys_fields f
624 WHERE
625 t.name LIKE '%/t' AND
626 t.table_id = i.table_id AND
627 i.index_id = f.index_id
628 ORDER BY 1, 2, 3;
629 table_name index_name column_name
630 test/t bb b
631 test/t i2 c
632 test/t i3 f
633 test/t i4 e
634 test/t PRIMARY a
635 DROP TABLE t;
636 CREATE TABLE t (
637 a INT,
638 b INT,
639 c INT,
640 d INT,
641 e INT,
642 f INT,
643 PRIMARY KEY (a),
644 INDEX i1 (b),
645 INDEX i2 (c),
646 INDEX i3 (d),
647 INDEX i4 (e)
648 ) ENGINE=INNODB;
649 INSERT INTO t SET a = 1;
650 ALTER TABLE t RENAME INDEX i1 TO x;
651 affected rows: 0
652 info: Records: 0 Duplicates: 0 Warnings: 0
653 SHOW CREATE TABLE t;
654 Table Create Table
655 t CREATE TABLE `t` (
656 `a` int(11) NOT NULL,
657 `b` int(11) DEFAULT NULL,
658 `c` int(11) DEFAULT NULL,
659 `d` int(11) DEFAULT NULL,
660 `e` int(11) DEFAULT NULL,
661 `f` int(11) DEFAULT NULL,
662 PRIMARY KEY (`a`),
663 KEY `x` (`b`),
664 KEY `i2` (`c`),
665 KEY `i3` (`d`),
666 KEY `i4` (`e`)
667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
668 SELECT
669 t.name AS table_name,
670 i.name AS index_name,
671 f.name AS column_name
672 FROM
673 information_schema.innodb_sys_tables t,
674 information_schema.innodb_sys_indexes i,
675 information_schema.innodb_sys_fields f
676 WHERE
677 t.name LIKE '%/t' AND
678 t.table_id = i.table_id AND
679 i.index_id = f.index_id
680 ORDER BY 1, 2, 3;
681 table_name index_name column_name
682 test/t i2 c
683 test/t i3 d
684 test/t i4 e
685 test/t PRIMARY a
686 test/t x b
687 DROP TABLE t;
688 CREATE TABLE t (
689 a INT,
690 b INT,
691 c INT,
692 d INT,
693 e INT,
694 f INT,
695 PRIMARY KEY (a),
696 INDEX i1 (b),
697 INDEX i2 (c),
698 INDEX i3 (d),
699 INDEX i4 (e)
700 ) ENGINE=INNODB;
701 INSERT INTO t SET a = 1;
702 ALTER TABLE t RENAME INDEX i1 TO i2;
703 ERROR 42000: Duplicate key name 'i2'
704 ALTER TABLE t RENAME INDEX foo TO i1;
705 ERROR 42000: Key 'foo' doesn't exist in table 't'
706 ALTER TABLE t ADD INDEX i9 (f), RENAME INDEX i1 TO i8;
707 affected rows: 0
708 info: Records: 0 Duplicates: 0 Warnings: 0
709 SHOW CREATE TABLE t;
710 Table Create Table
711 t CREATE TABLE `t` (
712 `a` int(11) NOT NULL,
713 `b` int(11) DEFAULT NULL,
714 `c` int(11) DEFAULT NULL,
715 `d` int(11) DEFAULT NULL,
716 `e` int(11) DEFAULT NULL,
717 `f` int(11) DEFAULT NULL,
718 PRIMARY KEY (`a`),
719 KEY `i8` (`b`),
720 KEY `i2` (`c`),
721 KEY `i3` (`d`),
722 KEY `i4` (`e`),
723 KEY `i9` (`f`)
724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
725 SELECT
726 t.name AS table_name,
727 i.name AS index_name,
728 f.name AS column_name
729 FROM
730 information_schema.innodb_sys_tables t,
731 information_schema.innodb_sys_indexes i,
732 information_schema.innodb_sys_fields f
733 WHERE
734 t.name LIKE '%/t' AND
735 t.table_id = i.table_id AND
736 i.index_id = f.index_id
737 ORDER BY 1, 2, 3;
738 table_name index_name column_name
739 test/t i2 c
740 test/t i3 d
741 test/t i4 e
742 test/t i8 b
743 test/t i9 f
744 test/t PRIMARY a
745 DROP TABLE t;
746 CREATE TABLE t (
747 a INT,
748 b INT,
749 c INT,
750 d INT,
751 e INT,
752 f INT,
753 PRIMARY KEY (a),
754 INDEX i1 (b),
755 INDEX i2 (c),
756 INDEX i3 (d),
757 INDEX i4 (e)
758 ) ENGINE=INNODB;
759 INSERT INTO t SET a = 1;
760 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO i9;
761 affected rows: 0
762 info: Records: 0 Duplicates: 0 Warnings: 0
763 SHOW CREATE TABLE t;
764 Table Create Table
765 t CREATE TABLE `t` (
766 `a` int(11) NOT NULL,
767 `b` int(11) DEFAULT NULL,
768 `c` int(11) DEFAULT NULL,
769 `d` int(11) DEFAULT NULL,
770 `e` int(11) DEFAULT NULL,
771 `f` int(11) DEFAULT NULL,
772 PRIMARY KEY (`a`),
773 KEY `i9` (`b`),
774 KEY `i2` (`c`),
775 KEY `i3` (`d`),
776 KEY `i4` (`e`),
777 KEY `i1` (`f`)
778 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
779 SELECT
780 t.name AS table_name,
781 i.name AS index_name,
782 f.name AS column_name
783 FROM
784 information_schema.innodb_sys_tables t,
785 information_schema.innodb_sys_indexes i,
786 information_schema.innodb_sys_fields f
787 WHERE
788 t.name LIKE '%/t' AND
789 t.table_id = i.table_id AND
790 i.index_id = f.index_id
791 ORDER BY 1, 2, 3;
792 table_name index_name column_name
793 test/t i1 f
794 test/t i2 c
795 test/t i3 d
796 test/t i4 e
797 test/t i9 b
798 test/t PRIMARY a
799 DROP TABLE t;
800 CREATE TABLE t (
801 a INT,
802 b INT,
803 c INT,
804 d INT,
805 e INT,
806 f INT,
807 PRIMARY KEY (a),
808 INDEX i1 (b),
809 INDEX i2 (c),
810 INDEX i3 (d),
811 INDEX i4 (e)
812 ) ENGINE=INNODB;
813 INSERT INTO t SET a = 1;
814 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX i1 TO foo;
815 ERROR 42000: Duplicate key name 'foo'
816 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX i1;
817 ERROR 42000: Key 'i1' doesn't exist in table 't'
818 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX foo;
819 ERROR 42000: Can't DROP INDEX `foo`; check that it exists
820 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX foo TO bar, DROP INDEX foo;
821 ERROR 42000: Can't DROP INDEX `foo`; check that it exists
822 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i2 TO x;
823 ERROR 42000: Duplicate key name 'x'
824 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO y;
825 ERROR 42000: Key 'i1' doesn't exist in table 't'
826 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO x;
827 ERROR 42000: Key 'i1' doesn't exist in table 't'
828 SHOW CREATE TABLE t;
829 Table Create Table
830 t CREATE TABLE `t` (
831 `a` int(11) NOT NULL,
832 `b` int(11) DEFAULT NULL,
833 `c` int(11) DEFAULT NULL,
834 `d` int(11) DEFAULT NULL,
835 `e` int(11) DEFAULT NULL,
836 `f` int(11) DEFAULT NULL,
837 PRIMARY KEY (`a`),
838 KEY `i1` (`b`),
839 KEY `i2` (`c`),
840 KEY `i3` (`d`),
841 KEY `i4` (`e`)
842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
843 SELECT
844 t.name AS table_name,
845 i.name AS index_name,
846 f.name AS column_name
847 FROM
848 information_schema.innodb_sys_tables t,
849 information_schema.innodb_sys_indexes i,
850 information_schema.innodb_sys_fields f
851 WHERE
852 t.name LIKE '%/t' AND
853 t.table_id = i.table_id AND
854 i.index_id = f.index_id
855 ORDER BY 1, 2, 3;
856 table_name index_name column_name
857 test/t i1 b
858 test/t i2 c
859 test/t i3 d
860 test/t i4 e
861 test/t PRIMARY a
862 DROP TABLE t;
863 CREATE TABLE t (
864 c1 INT NOT NULL,
865 c2 INT NOT NULL,
866 c3 INT,
867 c4 INT,
868 PRIMARY KEY (c1),
869 INDEX i1 (c3),
870 INDEX i2 (c4)
871 ) ENGINE=INNODB;
872 INSERT INTO t SET c1=1, c2=2;
873 ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (c2), RENAME INDEX i1 TO x;
874 affected rows: 0
875 info: Records: 0 Duplicates: 0 Warnings: 0
876 SHOW CREATE TABLE t;
877 Table Create Table
878 t CREATE TABLE `t` (
879 `c1` int(11) NOT NULL,
880 `c2` int(11) NOT NULL,
881 `c3` int(11) DEFAULT NULL,
882 `c4` int(11) DEFAULT NULL,
883 PRIMARY KEY (`c2`),
884 KEY `x` (`c3`),
885 KEY `i2` (`c4`)
886 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
887 SELECT
888 t.name AS table_name,
889 i.name AS index_name,
890 f.name AS column_name
891 FROM
892 information_schema.innodb_sys_tables t,
893 information_schema.innodb_sys_indexes i,
894 information_schema.innodb_sys_fields f
895 WHERE
896 t.name LIKE '%/t' AND
897 t.table_id = i.table_id AND
898 i.index_id = f.index_id
899 ORDER BY 1, 2, 3;
900 table_name index_name column_name
901 test/t i2 c4
902 test/t PRIMARY c2
903 test/t x c3
904 ALTER TABLE t RENAME INDEX i2 TO y, ROW_FORMAT=REDUNDANT;
905 affected rows: 0
906 info: Records: 0 Duplicates: 0 Warnings: 0
907 SHOW CREATE TABLE t;
908 Table Create Table
909 t CREATE TABLE `t` (
910 `c1` int(11) NOT NULL,
911 `c2` int(11) NOT NULL,
912 `c3` int(11) DEFAULT NULL,
913 `c4` int(11) DEFAULT NULL,
914 PRIMARY KEY (`c2`),
915 KEY `x` (`c3`),
916 KEY `y` (`c4`)
917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT
918 SELECT
919 t.name AS table_name,
920 i.name AS index_name,
921 f.name AS column_name
922 FROM
923 information_schema.innodb_sys_tables t,
924 information_schema.innodb_sys_indexes i,
925 information_schema.innodb_sys_fields f
926 WHERE
927 t.name LIKE '%/t' AND
928 t.table_id = i.table_id AND
929 i.index_id = f.index_id
930 ORDER BY 1, 2, 3;
931 table_name index_name column_name
932 test/t PRIMARY c2
933 test/t x c3
934 test/t y c4
935 DROP TABLE t;
936 CREATE TABLE t (
937 c1 INT NOT NULL,
938 c2 INT,
939 c3 INT,
940 INDEX i1 (c2),
941 INDEX i2 (c3)
942 ) ENGINE=INNODB;
943 INSERT INTO t SET c1=1;
944 ALTER TABLE t ADD PRIMARY KEY (c1), RENAME INDEX i1 TO x;
945 affected rows: 0
946 info: Records: 0 Duplicates: 0 Warnings: 0
947 SHOW CREATE TABLE t;
948 Table Create Table
949 t CREATE TABLE `t` (
950 `c1` int(11) NOT NULL,
951 `c2` int(11) DEFAULT NULL,
952 `c3` int(11) DEFAULT NULL,
953 PRIMARY KEY (`c1`),
954 KEY `x` (`c2`),
955 KEY `i2` (`c3`)
956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
957 SELECT
958 t.name AS table_name,
959 i.name AS index_name,
960 f.name AS column_name
961 FROM
962 information_schema.innodb_sys_tables t,
963 information_schema.innodb_sys_indexes i,
964 information_schema.innodb_sys_fields f
965 WHERE
966 t.name LIKE '%/t' AND
967 t.table_id = i.table_id AND
968 i.index_id = f.index_id
969 ORDER BY 1, 2, 3;
970 table_name index_name column_name
971 test/t i2 c3
972 test/t PRIMARY c1
973 test/t x c2
974 DROP TABLE t;
975 CREATE TABLE t (a INT, INDEX iiiii (a)) ENGINE=INNODB;
976 INSERT INTO t SET a=NULL;
977 ALTER TABLE t RENAME INDEX iiiii TO i;
978 affected rows: 0
979 info: Records: 0 Duplicates: 0 Warnings: 0
980 ALTER TABLE t RENAME INDEX i TO iiiii;
981 affected rows: 0
982 info: Records: 0 Duplicates: 0 Warnings: 0
983 ALTER TABLE t RENAME INDEX iiiii TO i;
984 affected rows: 0
985 info: Records: 0 Duplicates: 0 Warnings: 0
986 ALTER TABLE t RENAME INDEX i TO iiiii;
987 affected rows: 0
988 info: Records: 0 Duplicates: 0 Warnings: 0
989 DROP TABLE t;