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 'aa'; check that column/key exists
26 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX bb TO bb;
27 ERROR 42000: Can't DROP 'aa'; check that column/key 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 NOT NULL,
37 `b` int DEFAULT NULL,
38 `c` int DEFAULT NULL,
39 `d` int DEFAULT NULL,
40 `e` int DEFAULT NULL,
41 `f` int 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_0900_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_tables t,
54 information_schema.innodb_indexes i,
55 information_schema.innodb_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 SELECT index_name, stat_description
68 FROM mysql.innodb_index_stats
69 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
70 ORDER BY 1, 2;
71 index_name stat_description
72 PRIMARY a
73 i1 f
74 i1 f,a
75 i2 c
76 i2 c,a
77 i3 d
78 i3 d,a
79 i4 e
80 i4 e,a
81 DROP TABLE t;
82 CREATE TABLE t (
83 a INT,
84 b INT,
85 c INT,
86 d INT,
87 e INT,
88 f INT,
89 PRIMARY KEY (a),
90 INDEX i1 (b),
91 INDEX i2 (c),
92 INDEX i3 (d),
93 INDEX i4 (e)
94 ) ENGINE=INNODB;
95 INSERT INTO t SET a = 1;
96 # combination: abcc
97 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX cc TO cc;
98 ERROR 42000: Can't DROP 'bb'; check that column/key exists
99 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX i3 TO i3;
100 ERROR 42000: Can't DROP 'bb'; check that column/key exists
101 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO cc;
102 ERROR 42000: Key 'cc' doesn't exist in table 't'
103 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i3;
104 affected rows: 0
105 info: Records: 0 Duplicates: 0 Warnings: 0
106 SHOW CREATE TABLE t;
107 Table Create Table
108 t CREATE TABLE `t` (
109 `a` int NOT NULL,
110 `b` int DEFAULT NULL,
111 `c` int DEFAULT NULL,
112 `d` int DEFAULT NULL,
113 `e` int DEFAULT NULL,
114 `f` int DEFAULT NULL,
115 PRIMARY KEY (`a`),
116 KEY `i1` (`b`),
117 KEY `i3` (`d`),
118 KEY `i4` (`e`),
119 KEY `aa` (`f`)
120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
121 SELECT
122 t.name AS table_name,
123 i.name AS index_name,
124 f.name AS column_name
125 FROM
126 information_schema.innodb_tables t,
127 information_schema.innodb_indexes i,
128 information_schema.innodb_fields f
129 WHERE
130 t.name LIKE '%/t' AND
131 t.table_id = i.table_id AND
132 i.index_id = f.index_id
133 ORDER BY 1, 2, 3;
134 table_name index_name column_name
135 test/t aa f
136 test/t i1 b
137 test/t i3 d
138 test/t i4 e
139 test/t PRIMARY a
140 SELECT index_name, stat_description
141 FROM mysql.innodb_index_stats
142 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
143 ORDER BY 1, 2;
144 index_name stat_description
145 PRIMARY a
146 aa f
147 aa f,a
148 i1 b
149 i1 b,a
150 i3 d
151 i3 d,a
152 i4 e
153 i4 e,a
154 DROP TABLE t;
155 CREATE TABLE t (
156 a INT,
157 b INT,
158 c INT,
159 d INT,
160 e INT,
161 f INT,
162 PRIMARY KEY (a),
163 INDEX i1 (b),
164 INDEX i2 (c),
165 INDEX i3 (d),
166 INDEX i4 (e)
167 ) ENGINE=INNODB;
168 INSERT INTO t SET a = 1;
169 # combination: abaa
170 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i1, RENAME INDEX aa TO aa;
171 ERROR 42000: Key 'aa' doesn't exist in table 't'
172 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i1;
173 ERROR 42000: Duplicate key name 'i1'
174 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO i1;
175 ERROR 42000: Can't DROP 'bb'; check that column/key exists
176 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO aa;
177 ERROR 42000: Can't DROP 'bb'; check that column/key exists
178 # combination: baaa
179 ALTER TABLE t ADD INDEX i2(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
180 ERROR 42000: Key 'i1' doesn't exist in table 't'
181 ALTER TABLE t ADD INDEX bb(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
182 ERROR 42000: Key 'i1' doesn't exist in table 't'
183 ALTER TABLE t ADD INDEX i2(f), DROP INDEX aa, RENAME INDEX aa TO aa;
184 ERROR 42000: Can't DROP 'aa'; check that column/key exists
185 ALTER TABLE t ADD INDEX bb(f), DROP INDEX aa, RENAME INDEX aa TO aa;
186 ERROR 42000: Can't DROP 'aa'; check that column/key exists
187 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX aa TO bb;
188 ERROR 42000: Key 'aa' doesn't exist in table 't'
189 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX bb TO aa;
190 ERROR 42000: Key 'bb' doesn't exist in table 't'
191 ALTER TABLE t ADD INDEX aa(f), RENAME INDEX i2 TO aa;
192 ERROR 42000: Duplicate key name 'aa'
193 ALTER TABLE t ADD INDEX i1(f), RENAME INDEX i1 TO bb;
194 affected rows: 0
195 info: Records: 0 Duplicates: 0 Warnings: 0
196 SHOW CREATE TABLE t;
197 Table Create Table
198 t CREATE TABLE `t` (
199 `a` int NOT NULL,
200 `b` int DEFAULT NULL,
201 `c` int DEFAULT NULL,
202 `d` int DEFAULT NULL,
203 `e` int DEFAULT NULL,
204 `f` int DEFAULT NULL,
205 PRIMARY KEY (`a`),
206 KEY `bb` (`b`),
207 KEY `i2` (`c`),
208 KEY `i3` (`d`),
209 KEY `i4` (`e`),
210 KEY `i1` (`f`)
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
212 SELECT
213 t.name AS table_name,
214 i.name AS index_name,
215 f.name AS column_name
216 FROM
217 information_schema.innodb_tables t,
218 information_schema.innodb_indexes i,
219 information_schema.innodb_fields f
220 WHERE
221 t.name LIKE '%/t' AND
222 t.table_id = i.table_id AND
223 i.index_id = f.index_id
224 ORDER BY 1, 2, 3;
225 table_name index_name column_name
226 test/t bb b
227 test/t i1 f
228 test/t i2 c
229 test/t i3 d
230 test/t i4 e
231 test/t PRIMARY a
232 SELECT index_name, stat_description
233 FROM mysql.innodb_index_stats
234 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
235 ORDER BY 1, 2;
236 index_name stat_description
237 PRIMARY a
238 bb b
239 bb b,a
240 i1 f
241 i1 f,a
242 i2 c
243 i2 c,a
244 i3 d
245 i3 d,a
246 i4 e
247 i4 e,a
248 DROP TABLE t;
249 CREATE TABLE t (
250 a INT,
251 b INT,
252 c INT,
253 d INT,
254 e INT,
255 f INT,
256 PRIMARY KEY (a),
257 INDEX i1 (b),
258 INDEX i2 (c),
259 INDEX i3 (d),
260 INDEX i4 (e)
261 ) ENGINE=INNODB;
262 INSERT INTO t SET a = 1;
263 # combination: abba
264 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i2 TO i1;
265 ERROR 42000: Key 'i2' doesn't exist in table 't'
266 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i2 TO aa;
267 ERROR 42000: Key 'i2' doesn't exist in table 't'
268 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX bb TO i1;
269 ERROR 42000: Can't DROP 'bb'; check that column/key exists
270 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX bb TO aa;
271 ERROR 42000: Can't DROP 'bb'; check that column/key exists
272 # combination: cabc
273 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i2 TO i3;
274 ERROR 42000: Duplicate key name 'i3'
275 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i2 TO i3;
276 ERROR 42000: Can't DROP 'aa'; check that column/key exists
277 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX bb TO i3;
278 ERROR 42000: Key 'bb' doesn't exist in table 't'
279 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX bb TO i3;
280 ERROR 42000: Can't DROP 'aa'; check that column/key exists
281 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX i2 TO cc;
282 ERROR 42000: Duplicate key name 'cc'
283 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX i2 TO cc;
284 ERROR 42000: Can't DROP 'aa'; check that column/key exists
285 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX bb TO cc;
286 ERROR 42000: Key 'bb' doesn't exist in table 't'
287 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX bb TO cc;
288 ERROR 42000: Can't DROP 'aa'; check that column/key exists
289 ALTER TABLE t DROP INDEX i1, RENAME INDEX i1 TO bb;
290 ERROR 42000: Key 'i1' doesn't exist in table 't'
291 ALTER TABLE t DROP INDEX aa, RENAME INDEX i2 TO aa;
292 ERROR 42000: Can't DROP 'aa'; check that column/key exists
293 ALTER TABLE t DROP INDEX aa, RENAME INDEX aa TO i2;
294 ERROR 42000: Can't DROP 'aa'; check that column/key exists
295 ALTER TABLE t DROP INDEX i1, RENAME INDEX i4 TO i1;
296 affected rows: 0
297 info: Records: 0 Duplicates: 0 Warnings: 0
298 SHOW CREATE TABLE t;
299 Table Create Table
300 t CREATE TABLE `t` (
301 `a` int NOT NULL,
302 `b` int DEFAULT NULL,
303 `c` int DEFAULT NULL,
304 `d` int DEFAULT NULL,
305 `e` int DEFAULT NULL,
306 `f` int DEFAULT NULL,
307 PRIMARY KEY (`a`),
308 KEY `i2` (`c`),
309 KEY `i3` (`d`),
310 KEY `i1` (`e`)
311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
312 SELECT
313 t.name AS table_name,
314 i.name AS index_name,
315 f.name AS column_name
316 FROM
317 information_schema.innodb_tables t,
318 information_schema.innodb_indexes i,
319 information_schema.innodb_fields f
320 WHERE
321 t.name LIKE '%/t' AND
322 t.table_id = i.table_id AND
323 i.index_id = f.index_id
324 ORDER BY 1, 2, 3;
325 table_name index_name column_name
326 test/t i1 e
327 test/t i2 c
328 test/t i3 d
329 test/t PRIMARY a
330 SELECT index_name, stat_description
331 FROM mysql.innodb_index_stats
332 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
333 ORDER BY 1, 2;
334 index_name stat_description
335 PRIMARY a
336 i1 e
337 i1 e,a
338 i2 c
339 i2 c,a
340 i3 d
341 i3 d,a
342 DROP TABLE t;
343 CREATE TABLE t (
344 a INT,
345 b INT,
346 c INT,
347 d INT,
348 e INT,
349 f INT,
350 PRIMARY KEY (a),
351 INDEX i1 (b),
352 INDEX i2 (c),
353 INDEX i3 (d),
354 INDEX i4 (e)
355 ) ENGINE=INNODB;
356 INSERT INTO t SET a = 1;
357 # combination: accb
358 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO i2;
359 ERROR 42000: Key 'i3' doesn't exist in table 't'
360 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO bb;
361 ERROR 42000: Key 'i3' doesn't exist in table 't'
362 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO i2;
363 ERROR 42000: Can't DROP 'cc'; check that column/key exists
364 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO bb;
365 ERROR 42000: Can't DROP 'cc'; check that column/key exists
366 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO i2;
367 ERROR 42000: Can't DROP 'cc'; check that column/key exists
368 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO bb;
369 ERROR 42000: Can't DROP 'cc'; check that column/key exists
370 # combination: aaab
371 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
372 ERROR 42000: Key 'i1' doesn't exist in table 't'
373 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO bb;
374 ERROR 42000: Key 'i1' doesn't exist in table 't'
375 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
376 ERROR 42000: Key 'i1' doesn't exist in table 't'
377 ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX aa TO bb;
378 ERROR 42000: Can't DROP 'aa'; check that column/key exists
379 # combination: abcd
380 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO i4;
381 ERROR 42000: Key 'cc' doesn't exist in table 't'
382 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO dd;
383 ERROR 42000: Key 'cc' doesn't exist in table 't'
384 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO i4;
385 ERROR 42000: Key 'cc' doesn't exist in table 't'
386 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO dd;
387 ERROR 42000: Key 'cc' doesn't exist in table 't'
388 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
389 ERROR 42000: Duplicate key name 'i4'
390 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
391 ERROR 42000: Duplicate key name 'i1'
392 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
393 ERROR 42000: Duplicate key name 'i4'
394 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
395 affected rows: 0
396 info: Records: 0 Duplicates: 0 Warnings: 0
397 SHOW CREATE TABLE t;
398 Table Create Table
399 t CREATE TABLE `t` (
400 `a` int NOT NULL,
401 `b` int DEFAULT NULL,
402 `c` int DEFAULT NULL,
403 `d` int DEFAULT NULL,
404 `e` int DEFAULT NULL,
405 `f` int DEFAULT NULL,
406 PRIMARY KEY (`a`),
407 KEY `i1` (`b`),
408 KEY `dd` (`d`),
409 KEY `i4` (`e`),
410 KEY `aa` (`f`)
411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
412 SELECT
413 t.name AS table_name,
414 i.name AS index_name,
415 f.name AS column_name
416 FROM
417 information_schema.innodb_tables t,
418 information_schema.innodb_indexes i,
419 information_schema.innodb_fields f
420 WHERE
421 t.name LIKE '%/t' AND
422 t.table_id = i.table_id AND
423 i.index_id = f.index_id
424 ORDER BY 1, 2, 3;
425 table_name index_name column_name
426 test/t aa f
427 test/t dd d
428 test/t i1 b
429 test/t i4 e
430 test/t PRIMARY a
431 SELECT index_name, stat_description
432 FROM mysql.innodb_index_stats
433 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
434 ORDER BY 1, 2;
435 index_name stat_description
436 PRIMARY a
437 aa f
438 aa f,a
439 dd d
440 dd d,a
441 i1 b
442 i1 b,a
443 i4 e
444 i4 e,a
445 DROP TABLE t;
446 CREATE TABLE t (
447 a INT,
448 b INT,
449 c INT,
450 d INT,
451 e INT,
452 f INT,
453 PRIMARY KEY (a),
454 INDEX i1 (b),
455 INDEX i2 (c),
456 INDEX i3 (d),
457 INDEX i4 (e)
458 ) ENGINE=INNODB;
459 INSERT INTO t SET a = 1;
460 # combination: abab
461 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i2;
462 affected rows: 0
463 info: Records: 0 Duplicates: 0 Warnings: 0
464 SHOW CREATE TABLE t;
465 Table Create Table
466 t CREATE TABLE `t` (
467 `a` int NOT NULL,
468 `b` int DEFAULT NULL,
469 `c` int DEFAULT NULL,
470 `d` int DEFAULT NULL,
471 `e` int DEFAULT NULL,
472 `f` int DEFAULT NULL,
473 PRIMARY KEY (`a`),
474 KEY `i2` (`b`),
475 KEY `i3` (`d`),
476 KEY `i4` (`e`),
477 KEY `i1` (`f`)
478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
479 SELECT
480 t.name AS table_name,
481 i.name AS index_name,
482 f.name AS column_name
483 FROM
484 information_schema.innodb_tables t,
485 information_schema.innodb_indexes i,
486 information_schema.innodb_fields f
487 WHERE
488 t.name LIKE '%/t' AND
489 t.table_id = i.table_id AND
490 i.index_id = f.index_id
491 ORDER BY 1, 2, 3;
492 table_name index_name column_name
493 test/t i1 f
494 test/t i2 b
495 test/t i3 d
496 test/t i4 e
497 test/t PRIMARY a
498 SELECT index_name, stat_description
499 FROM mysql.innodb_index_stats
500 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
501 ORDER BY 1, 2;
502 index_name stat_description
503 PRIMARY a
504 i1 f
505 i1 f,a
506 i2 b
507 i2 b,a
508 i3 d
509 i3 d,a
510 i4 e
511 i4 e,a
512 DROP TABLE t;
513 CREATE TABLE t (
514 a INT,
515 b INT,
516 c INT,
517 d INT,
518 e INT,
519 f INT,
520 PRIMARY KEY (a),
521 INDEX i1 (b),
522 INDEX i2 (c),
523 INDEX i3 (d),
524 INDEX i4 (e)
525 ) ENGINE=INNODB;
526 INSERT INTO t SET a = 1;
527 ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO bb;
528 ERROR 42000: Can't DROP 'bb'; check that column/key exists
529 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX aa TO i2;
530 ERROR 42000: Key 'aa' doesn't exist in table 't'
531 ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO bb;
532 ERROR 42000: Can't DROP 'bb'; check that column/key exists
533 # combination: acbc
534 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
535 ERROR 42000: Can't DROP 'cc'; check that column/key exists
536 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
537 ERROR 42000: Can't DROP 'cc'; check that column/key exists
538 ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX bb TO cc;
539 ERROR 42000: Can't DROP 'cc'; check that column/key exists
540 ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX bb TO cc;
541 ERROR 42000: Can't DROP 'cc'; check that column/key exists
542 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX bb TO i3;
543 ERROR 42000: Key 'bb' doesn't exist in table 't'
544 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX bb TO i3;
545 ERROR 42000: Key 'bb' doesn't exist in table 't'
546 ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
547 ERROR 42000: Duplicate key name 'i1'
548 ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
549 affected rows: 0
550 info: Records: 0 Duplicates: 0 Warnings: 0
551 SHOW CREATE TABLE t;
552 Table Create Table
553 t CREATE TABLE `t` (
554 `a` int NOT NULL,
555 `b` int DEFAULT NULL,
556 `c` int DEFAULT NULL,
557 `d` int DEFAULT NULL,
558 `e` int DEFAULT NULL,
559 `f` int DEFAULT NULL,
560 PRIMARY KEY (`a`),
561 KEY `i1` (`b`),
562 KEY `i3` (`c`),
563 KEY `i4` (`e`),
564 KEY `aa` (`f`)
565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
566 SELECT
567 t.name AS table_name,
568 i.name AS index_name,
569 f.name AS column_name
570 FROM
571 information_schema.innodb_tables t,
572 information_schema.innodb_indexes i,
573 information_schema.innodb_fields f
574 WHERE
575 t.name LIKE '%/t' AND
576 t.table_id = i.table_id AND
577 i.index_id = f.index_id
578 ORDER BY 1, 2, 3;
579 table_name index_name column_name
580 test/t aa f
581 test/t i1 b
582 test/t i3 c
583 test/t i4 e
584 test/t PRIMARY a
585 SELECT index_name, stat_description
586 FROM mysql.innodb_index_stats
587 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
588 ORDER BY 1, 2;
589 index_name stat_description
590 PRIMARY a
591 aa f
592 aa f,a
593 i1 b
594 i1 b,a
595 i3 c
596 i3 c,a
597 i4 e
598 i4 e,a
599 DROP TABLE t;
600 CREATE TABLE t (
601 a INT,
602 b INT,
603 c INT,
604 d INT,
605 e INT,
606 f INT,
607 PRIMARY KEY (a),
608 INDEX i1 (b),
609 INDEX i2 (c),
610 INDEX i3 (d),
611 INDEX i4 (e)
612 ) ENGINE=INNODB;
613 INSERT INTO t SET a = 1;
614 # combination: cacb
615 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO i2;
616 ERROR 42000: Key 'cc' doesn't exist in table 't'
617 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO i2;
618 ERROR 42000: Can't DROP 'aa'; check that column/key exists
619 ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO bb;
620 ERROR 42000: Can't DROP 'aa'; check that column/key exists
621 ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO bb;
622 ERROR 42000: Key 'cc' doesn't exist in table 't'
623 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO i2;
624 ERROR 42000: Duplicate key name 'i2'
625 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO i2;
626 ERROR 42000: Can't DROP 'aa'; check that column/key exists
627 ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO bb;
628 ERROR 42000: Can't DROP 'aa'; check that column/key exists
629 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO bb;
630 affected rows: 0
631 info: Records: 0 Duplicates: 0 Warnings: 0
632 SHOW CREATE TABLE t;
633 Table Create Table
634 t CREATE TABLE `t` (
635 `a` int NOT NULL,
636 `b` int DEFAULT NULL,
637 `c` int DEFAULT NULL,
638 `d` int DEFAULT NULL,
639 `e` int DEFAULT NULL,
640 `f` int DEFAULT NULL,
641 PRIMARY KEY (`a`),
642 KEY `i2` (`c`),
643 KEY `bb` (`d`),
644 KEY `i4` (`e`),
645 KEY `i3` (`f`)
646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
647 SELECT
648 t.name AS table_name,
649 i.name AS index_name,
650 f.name AS column_name
651 FROM
652 information_schema.innodb_tables t,
653 information_schema.innodb_indexes i,
654 information_schema.innodb_fields f
655 WHERE
656 t.name LIKE '%/t' AND
657 t.table_id = i.table_id AND
658 i.index_id = f.index_id
659 ORDER BY 1, 2, 3;
660 table_name index_name column_name
661 test/t bb d
662 test/t i2 c
663 test/t i3 f
664 test/t i4 e
665 test/t PRIMARY a
666 SELECT index_name, stat_description
667 FROM mysql.innodb_index_stats
668 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
669 ORDER BY 1, 2;
670 index_name stat_description
671 PRIMARY a
672 bb d
673 bb d,a
674 i2 c
675 i2 c,a
676 i3 f
677 i3 f,a
678 i4 e
679 i4 e,a
680 DROP TABLE t;
681 CREATE TABLE t (
682 a INT,
683 b INT,
684 c INT,
685 d INT,
686 e INT,
687 f INT,
688 PRIMARY KEY (a),
689 INDEX i1 (b),
690 INDEX i2 (c),
691 INDEX i3 (d),
692 INDEX i4 (e)
693 ) ENGINE=INNODB;
694 INSERT INTO t SET a = 1;
695 # combination: ccab
696 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO i2;
697 ERROR 42000: Can't DROP 'cc'; check that column/key exists
698 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO bb;
699 ERROR 42000: Can't DROP 'cc'; check that column/key exists
700 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO i2;
701 ERROR 42000: Can't DROP 'cc'; check that column/key exists
702 ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO bb;
703 ERROR 42000: Can't DROP 'cc'; check that column/key exists
704 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO i2;
705 ERROR 42000: Can't DROP 'cc'; check that column/key exists
706 ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO bb;
707 ERROR 42000: Can't DROP 'cc'; check that column/key exists
708 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO i2;
709 ERROR 42000: Duplicate key name 'i2'
710 ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO bb;
711 affected rows: 0
712 info: Records: 0 Duplicates: 0 Warnings: 0
713 SHOW CREATE TABLE t;
714 Table Create Table
715 t CREATE TABLE `t` (
716 `a` int NOT NULL,
717 `b` int DEFAULT NULL,
718 `c` int DEFAULT NULL,
719 `d` int DEFAULT NULL,
720 `e` int DEFAULT NULL,
721 `f` int DEFAULT NULL,
722 PRIMARY KEY (`a`),
723 KEY `bb` (`b`),
724 KEY `i2` (`c`),
725 KEY `i4` (`e`),
726 KEY `i3` (`f`)
727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
728 SELECT
729 t.name AS table_name,
730 i.name AS index_name,
731 f.name AS column_name
732 FROM
733 information_schema.innodb_tables t,
734 information_schema.innodb_indexes i,
735 information_schema.innodb_fields f
736 WHERE
737 t.name LIKE '%/t' AND
738 t.table_id = i.table_id AND
739 i.index_id = f.index_id
740 ORDER BY 1, 2, 3;
741 table_name index_name column_name
742 test/t bb b
743 test/t i2 c
744 test/t i3 f
745 test/t i4 e
746 test/t PRIMARY a
747 SELECT index_name, stat_description
748 FROM mysql.innodb_index_stats
749 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
750 ORDER BY 1, 2;
751 index_name stat_description
752 PRIMARY a
753 bb b
754 bb b,a
755 i2 c
756 i2 c,a
757 i3 f
758 i3 f,a
759 i4 e
760 i4 e,a
761 DROP TABLE t;
762 CREATE TABLE t (
763 a INT,
764 b INT,
765 c INT,
766 d INT,
767 e INT,
768 f INT,
769 PRIMARY KEY (a),
770 INDEX i1 (b),
771 INDEX i2 (c),
772 INDEX i3 (d),
773 INDEX i4 (e)
774 ) ENGINE=INNODB;
775 INSERT INTO t SET a = 1;
776 ALTER TABLE t RENAME INDEX i1 TO x;
777 affected rows: 0
778 info: Records: 0 Duplicates: 0 Warnings: 0
779 SHOW CREATE TABLE t;
780 Table Create Table
781 t CREATE TABLE `t` (
782 `a` int NOT NULL,
783 `b` int DEFAULT NULL,
784 `c` int DEFAULT NULL,
785 `d` int DEFAULT NULL,
786 `e` int DEFAULT NULL,
787 `f` int DEFAULT NULL,
788 PRIMARY KEY (`a`),
789 KEY `x` (`b`),
790 KEY `i2` (`c`),
791 KEY `i3` (`d`),
792 KEY `i4` (`e`)
793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
794 SELECT
795 t.name AS table_name,
796 i.name AS index_name,
797 f.name AS column_name
798 FROM
799 information_schema.innodb_tables t,
800 information_schema.innodb_indexes i,
801 information_schema.innodb_fields f
802 WHERE
803 t.name LIKE '%/t' AND
804 t.table_id = i.table_id AND
805 i.index_id = f.index_id
806 ORDER BY 1, 2, 3;
807 table_name index_name column_name
808 test/t i2 c
809 test/t i3 d
810 test/t i4 e
811 test/t PRIMARY a
812 test/t x b
813 SELECT index_name, stat_description
814 FROM mysql.innodb_index_stats
815 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
816 ORDER BY 1, 2;
817 index_name stat_description
818 PRIMARY a
819 i2 c
820 i2 c,a
821 i3 d
822 i3 d,a
823 i4 e
824 i4 e,a
825 x b
826 x b,a
827 DROP TABLE t;
828 CREATE TABLE t (
829 a INT,
830 b INT,
831 c INT,
832 d INT,
833 e INT,
834 f INT,
835 PRIMARY KEY (a),
836 INDEX i1 (b),
837 INDEX i2 (c),
838 INDEX i3 (d),
839 INDEX i4 (e)
840 ) ENGINE=INNODB;
841 INSERT INTO t SET a = 1;
842 ALTER TABLE t RENAME INDEX i1 TO i2;
843 ERROR 42000: Duplicate key name 'i2'
844 ALTER TABLE t RENAME INDEX foo TO i1;
845 ERROR 42000: Key 'foo' doesn't exist in table 't'
846 ALTER TABLE t ADD INDEX i9 (f), RENAME INDEX i1 TO i8;
847 affected rows: 0
848 info: Records: 0 Duplicates: 0 Warnings: 0
849 SHOW CREATE TABLE t;
850 Table Create Table
851 t CREATE TABLE `t` (
852 `a` int NOT NULL,
853 `b` int DEFAULT NULL,
854 `c` int DEFAULT NULL,
855 `d` int DEFAULT NULL,
856 `e` int DEFAULT NULL,
857 `f` int DEFAULT NULL,
858 PRIMARY KEY (`a`),
859 KEY `i8` (`b`),
860 KEY `i2` (`c`),
861 KEY `i3` (`d`),
862 KEY `i4` (`e`),
863 KEY `i9` (`f`)
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
865 SELECT
866 t.name AS table_name,
867 i.name AS index_name,
868 f.name AS column_name
869 FROM
870 information_schema.innodb_tables t,
871 information_schema.innodb_indexes i,
872 information_schema.innodb_fields f
873 WHERE
874 t.name LIKE '%/t' AND
875 t.table_id = i.table_id AND
876 i.index_id = f.index_id
877 ORDER BY 1, 2, 3;
878 table_name index_name column_name
879 test/t i2 c
880 test/t i3 d
881 test/t i4 e
882 test/t i8 b
883 test/t i9 f
884 test/t PRIMARY a
885 SELECT index_name, stat_description
886 FROM mysql.innodb_index_stats
887 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
888 ORDER BY 1, 2;
889 index_name stat_description
890 PRIMARY a
891 i2 c
892 i2 c,a
893 i3 d
894 i3 d,a
895 i4 e
896 i4 e,a
897 i8 b
898 i8 b,a
899 i9 f
900 i9 f,a
901 DROP TABLE t;
902 CREATE TABLE t (
903 a INT,
904 b INT,
905 c INT,
906 d INT,
907 e INT,
908 f INT,
909 PRIMARY KEY (a),
910 INDEX i1 (b),
911 INDEX i2 (c),
912 INDEX i3 (d),
913 INDEX i4 (e)
914 ) ENGINE=INNODB;
915 INSERT INTO t SET a = 1;
916 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO i9;
917 affected rows: 0
918 info: Records: 0 Duplicates: 0 Warnings: 0
919 SHOW CREATE TABLE t;
920 Table Create Table
921 t CREATE TABLE `t` (
922 `a` int NOT NULL,
923 `b` int DEFAULT NULL,
924 `c` int DEFAULT NULL,
925 `d` int DEFAULT NULL,
926 `e` int DEFAULT NULL,
927 `f` int DEFAULT NULL,
928 PRIMARY KEY (`a`),
929 KEY `i9` (`b`),
930 KEY `i2` (`c`),
931 KEY `i3` (`d`),
932 KEY `i4` (`e`),
933 KEY `i1` (`f`)
934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
935 SELECT
936 t.name AS table_name,
937 i.name AS index_name,
938 f.name AS column_name
939 FROM
940 information_schema.innodb_tables t,
941 information_schema.innodb_indexes i,
942 information_schema.innodb_fields f
943 WHERE
944 t.name LIKE '%/t' AND
945 t.table_id = i.table_id AND
946 i.index_id = f.index_id
947 ORDER BY 1, 2, 3;
948 table_name index_name column_name
949 test/t i1 f
950 test/t i2 c
951 test/t i3 d
952 test/t i4 e
953 test/t i9 b
954 test/t PRIMARY a
955 SELECT index_name, stat_description
956 FROM mysql.innodb_index_stats
957 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
958 ORDER BY 1, 2;
959 index_name stat_description
960 PRIMARY a
961 i1 f
962 i1 f,a
963 i2 c
964 i2 c,a
965 i3 d
966 i3 d,a
967 i4 e
968 i4 e,a
969 i9 b
970 i9 b,a
971 DROP TABLE t;
972 CREATE TABLE t (
973 a INT,
974 b INT,
975 c INT,
976 d INT,
977 e INT,
978 f INT,
979 PRIMARY KEY (a),
980 INDEX i1 (b),
981 INDEX i2 (c),
982 INDEX i3 (d),
983 INDEX i4 (e)
984 ) ENGINE=INNODB;
985 INSERT INTO t SET a = 1;
986 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX i1 TO foo;
987 ERROR 42000: Duplicate key name 'foo'
988 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX i1;
989 ERROR 42000: Key 'i1' doesn't exist in table 't'
990 ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX foo;
991 ERROR 42000: Can't DROP 'foo'; check that column/key exists
992 ALTER TABLE t ADD INDEX foo (f), RENAME INDEX foo TO bar, DROP INDEX foo;
993 ERROR 42000: Can't DROP 'foo'; check that column/key exists
994 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i2 TO x;
995 ERROR 42000: Duplicate key name 'x'
996 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO y;
997 ERROR 42000: Key 'i1' doesn't exist in table 't'
998 ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO x;
999 ERROR 42000: Key 'i1' doesn't exist in table 't'
1000 SHOW CREATE TABLE t;
1001 Table Create Table
1002 t CREATE TABLE `t` (
1003 `a` int NOT NULL,
1004 `b` int DEFAULT NULL,
1005 `c` int DEFAULT NULL,
1006 `d` int DEFAULT NULL,
1007 `e` int DEFAULT NULL,
1008 `f` int DEFAULT NULL,
1009 PRIMARY KEY (`a`),
1010 KEY `i1` (`b`),
1011 KEY `i2` (`c`),
1012 KEY `i3` (`d`),
1013 KEY `i4` (`e`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1015 SELECT
1016 t.name AS table_name,
1017 i.name AS index_name,
1018 f.name AS column_name
1019 FROM
1020 information_schema.innodb_tables t,
1021 information_schema.innodb_indexes i,
1022 information_schema.innodb_fields f
1023 WHERE
1024 t.name LIKE '%/t' AND
1025 t.table_id = i.table_id AND
1026 i.index_id = f.index_id
1027 ORDER BY 1, 2, 3;
1028 table_name index_name column_name
1029 test/t i1 b
1030 test/t i2 c
1031 test/t i3 d
1032 test/t i4 e
1033 test/t PRIMARY a
1034 SELECT index_name, stat_description
1035 FROM mysql.innodb_index_stats
1036 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
1037 ORDER BY 1, 2;
1038 index_name stat_description
1039 PRIMARY a
1040 i1 b
1041 i1 b,a
1042 i2 c
1043 i2 c,a
1044 i3 d
1045 i3 d,a
1046 i4 e
1047 i4 e,a
1048 DROP TABLE t;
1049 CREATE TABLE t (
1050 c1 INT NOT NULL,
1051 c2 INT NOT NULL,
1052 c3 INT,
1053 c4 INT,
1054 PRIMARY KEY (c1),
1055 INDEX i1 (c3),
1056 INDEX i2 (c4)
1057 ) ENGINE=INNODB;
1058 INSERT INTO t SET c1=1, c2=2;
1059 ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (c2), RENAME INDEX i1 TO x;
1060 affected rows: 0
1061 info: Records: 0 Duplicates: 0 Warnings: 0
1062 SHOW CREATE TABLE t;
1063 Table Create Table
1064 t CREATE TABLE `t` (
1065 `c1` int NOT NULL,
1066 `c2` int NOT NULL,
1067 `c3` int DEFAULT NULL,
1068 `c4` int DEFAULT NULL,
1069 PRIMARY KEY (`c2`),
1070 KEY `x` (`c3`),
1071 KEY `i2` (`c4`)
1072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1073 SELECT
1074 t.name AS table_name,
1075 i.name AS index_name,
1076 f.name AS column_name
1077 FROM
1078 information_schema.innodb_tables t,
1079 information_schema.innodb_indexes i,
1080 information_schema.innodb_fields f
1081 WHERE
1082 t.name LIKE '%/t' AND
1083 t.table_id = i.table_id AND
1084 i.index_id = f.index_id
1085 ORDER BY 1, 2, 3;
1086 table_name index_name column_name
1087 test/t i2 c4
1088 test/t PRIMARY c2
1089 test/t x c3
1090 SELECT index_name, stat_description
1091 FROM mysql.innodb_index_stats
1092 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
1093 ORDER BY 1, 2;
1094 index_name stat_description
1095 PRIMARY c2
1096 i2 c4
1097 i2 c4,c2
1098 x c3
1099 x c3,c2
1100 ALTER TABLE t RENAME INDEX i2 TO y, ROW_FORMAT=REDUNDANT;
1101 affected rows: 0
1102 info: Records: 0 Duplicates: 0 Warnings: 0
1103 SHOW CREATE TABLE t;
1104 Table Create Table
1105 t CREATE TABLE `t` (
1106 `c1` int NOT NULL,
1107 `c2` int NOT NULL,
1108 `c3` int DEFAULT NULL,
1109 `c4` int DEFAULT NULL,
1110 PRIMARY KEY (`c2`),
1111 KEY `x` (`c3`),
1112 KEY `y` (`c4`)
1113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1114 SELECT
1115 t.name AS table_name,
1116 i.name AS index_name,
1117 f.name AS column_name
1118 FROM
1119 information_schema.innodb_tables t,
1120 information_schema.innodb_indexes i,
1121 information_schema.innodb_fields f
1122 WHERE
1123 t.name LIKE '%/t' AND
1124 t.table_id = i.table_id AND
1125 i.index_id = f.index_id
1126 ORDER BY 1, 2, 3;
1127 table_name index_name column_name
1128 test/t PRIMARY c2
1129 test/t x c3
1130 test/t y c4
1131 SELECT index_name, stat_description
1132 FROM mysql.innodb_index_stats
1133 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
1134 ORDER BY 1, 2;
1135 index_name stat_description
1136 PRIMARY c2
1137 x c3
1138 x c3,c2
1139 y c4
1140 y c4,c2
1141 DROP TABLE t;
1142 CREATE TABLE t (
1143 c1 INT NOT NULL,
1144 c2 INT,
1145 c3 INT,
1146 INDEX i1 (c2),
1147 INDEX i2 (c3)
1148 ) ENGINE=INNODB;
1149 INSERT INTO t SET c1=1;
1150 ALTER TABLE t ADD PRIMARY KEY (c1), RENAME INDEX i1 TO x;
1151 affected rows: 0
1152 info: Records: 0 Duplicates: 0 Warnings: 0
1153 SHOW CREATE TABLE t;
1154 Table Create Table
1155 t CREATE TABLE `t` (
1156 `c1` int NOT NULL,
1157 `c2` int DEFAULT NULL,
1158 `c3` int DEFAULT NULL,
1159 PRIMARY KEY (`c1`),
1160 KEY `x` (`c2`),
1161 KEY `i2` (`c3`)
1162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1163 SELECT
1164 t.name AS table_name,
1165 i.name AS index_name,
1166 f.name AS column_name
1167 FROM
1168 information_schema.innodb_tables t,
1169 information_schema.innodb_indexes i,
1170 information_schema.innodb_fields f
1171 WHERE
1172 t.name LIKE '%/t' AND
1173 t.table_id = i.table_id AND
1174 i.index_id = f.index_id
1175 ORDER BY 1, 2, 3;
1176 table_name index_name column_name
1177 test/t i2 c3
1178 test/t PRIMARY c1
1179 test/t x c2
1180 SELECT index_name, stat_description
1181 FROM mysql.innodb_index_stats
1182 WHERE table_name = 't' AND stat_name LIKE 'n_diff%'
1183 ORDER BY 1, 2;
1184 index_name stat_description
1185 PRIMARY c1
1186 i2 c3
1187 i2 c3,c1
1188 x c2
1189 x c2,c1
1190 DROP TABLE t;
1191 CREATE TABLE t (a INT, INDEX iiiii (a)) ENGINE=INNODB;
1192 INSERT INTO t SET a=NULL;
1193 ALTER TABLE t RENAME INDEX iiiii TO i;
1194 affected rows: 0
1195 info: Records: 0 Duplicates: 0 Warnings: 0
1196 ALTER TABLE t RENAME INDEX i TO iiiii;
1197 affected rows: 0
1198 info: Records: 0 Duplicates: 0 Warnings: 0
1199 ALTER TABLE t RENAME INDEX iiiii TO i;
1200 affected rows: 0
1201 info: Records: 0 Duplicates: 0 Warnings: 0
1202 ALTER TABLE t RENAME INDEX i TO iiiii;
1203 affected rows: 0
1204 info: Records: 0 Duplicates: 0 Warnings: 0
1205 DROP TABLE t;