Debian Codegraph @

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

raw
1 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
2 create table t1(a varchar(2) primary key) engine=innodb;
3 insert into t1 values('');
4 create index t1a1 on t1(a(1));
5 affected rows: 0
6 info: Records: 0 Duplicates: 0 Warnings: 0
7 drop table t1;
8 create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
9 insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
10 commit;
11 alter table t1 add index b (b), add index b (b);
12 ERROR 42000: Duplicate key name 'b'
13 alter table t1 add index (b,b);
14 ERROR 42S21: Duplicate column name 'b'
15 alter table t1 add index d2 (d);
16 affected rows: 0
17 info: Records: 0 Duplicates: 0 Warnings: 0
18 show create table t1;
19 Table Create Table
20 t1 CREATE TABLE `t1` (
21 `a` int NOT NULL,
22 `b` int DEFAULT NULL,
23 `c` char(10) NOT NULL,
24 `d` varchar(20) DEFAULT NULL,
25 KEY `d2` (`d`)
26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
27 analyze table t1;
28 explain select * from t1 force index(d2) order by d;
29 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
30 1 SIMPLE t1 NULL index NULL d2 83 NULL 4 100.00 NULL
31 Warnings:
32 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d2`) order by `test`.`t1`.`d`
33 select * from t1 force index (d2) order by d;
34 a b c d
35 3 4 ad ad
36 2 3 ak ak
37 5 5 oo oo
38 4 4 tr tr
39 alter table t1 add unique index (b);
40 ERROR 23000: Duplicate entry '4' for key 't1.b'
41 show create table t1;
42 Table Create Table
43 t1 CREATE TABLE `t1` (
44 `a` int NOT NULL,
45 `b` int DEFAULT NULL,
46 `c` char(10) NOT NULL,
47 `d` varchar(20) DEFAULT NULL,
48 KEY `d2` (`d`)
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
50 alter table t1 add index (b);
51 affected rows: 0
52 info: Records: 0 Duplicates: 0 Warnings: 0
53 show create table t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `a` int NOT NULL,
57 `b` int DEFAULT NULL,
58 `c` char(10) NOT NULL,
59 `d` varchar(20) DEFAULT NULL,
60 KEY `d2` (`d`),
61 KEY `b` (`b`)
62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
63 alter table t1 add unique index (c), add index (d);
64 affected rows: 0
65 info: Records: 0 Duplicates: 0 Warnings: 1
66 Warnings:
67 Warning 1831 Duplicate index 'd' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
68 show create table t1;
69 Table Create Table
70 t1 CREATE TABLE `t1` (
71 `a` int NOT NULL,
72 `b` int DEFAULT NULL,
73 `c` char(10) NOT NULL,
74 `d` varchar(20) DEFAULT NULL,
75 UNIQUE KEY `c` (`c`),
76 KEY `d2` (`d`),
77 KEY `b` (`b`),
78 KEY `d` (`d`)
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
80 analyze table t1;
81 Table Op Msg_type Msg_text
82 test.t1 analyze status OK
83 explain select * from t1 force index(c) order by c;
84 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
85 1 SIMPLE t1 NULL index NULL c 40 NULL 4 100.00 NULL
86 Warnings:
87 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
88 alter table t1 add primary key (a), drop index c;
89 affected rows: 0
90 info: Records: 0 Duplicates: 0 Warnings: 0
91 show create table t1;
92 Table Create Table
93 t1 CREATE TABLE `t1` (
94 `a` int NOT NULL,
95 `b` int DEFAULT NULL,
96 `c` char(10) NOT NULL,
97 `d` varchar(20) DEFAULT NULL,
98 PRIMARY KEY (`a`),
99 KEY `d2` (`d`),
100 KEY `b` (`b`),
101 KEY `d` (`d`)
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
103 affected rows: 1
104 alter table t1 add primary key (c);
105 ERROR 42000: Multiple primary key defined
106 alter table t1 drop primary key, add primary key (b);
107 ERROR 23000: Duplicate entry '4' for key 't1.PRIMARY'
108 create unique index c on t1 (c);
109 affected rows: 0
110 info: Records: 0 Duplicates: 0 Warnings: 0
111 show create table t1;
112 Table Create Table
113 t1 CREATE TABLE `t1` (
114 `a` int NOT NULL,
115 `b` int DEFAULT NULL,
116 `c` char(10) NOT NULL,
117 `d` varchar(20) DEFAULT NULL,
118 PRIMARY KEY (`a`),
119 UNIQUE KEY `c` (`c`),
120 KEY `d2` (`d`),
121 KEY `b` (`b`),
122 KEY `d` (`d`)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
124 analyze table t1;
125 Table Op Msg_type Msg_text
126 test.t1 analyze status OK
127 explain select * from t1 force index(c) order by c;
128 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
129 1 SIMPLE t1 NULL index NULL c 40 NULL 4 100.00 NULL
130 Warnings:
131 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
132 select * from t1 force index(c) order by c;
133 a b c d
134 3 4 ad ad
135 2 3 ak ak
136 5 5 oo oo
137 4 4 tr tr
138 alter table t1 drop index b, add index (b);
139 affected rows: 0
140 info: Records: 0 Duplicates: 0 Warnings: 0
141 show create table t1;
142 Table Create Table
143 t1 CREATE TABLE `t1` (
144 `a` int NOT NULL,
145 `b` int DEFAULT NULL,
146 `c` char(10) NOT NULL,
147 `d` varchar(20) DEFAULT NULL,
148 PRIMARY KEY (`a`),
149 UNIQUE KEY `c` (`c`),
150 KEY `d2` (`d`),
151 KEY `b` (`b`),
152 KEY `d` (`d`)
153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
154 insert into t1 values(6,1,'ggg','ggg');
155 select * from t1;
156 a b c d
157 2 3 ak ak
158 3 4 ad ad
159 4 4 tr tr
160 5 5 oo oo
161 6 1 ggg ggg
162 select * from t1 force index(b) order by b;
163 a b c d
164 6 1 ggg ggg
165 2 3 ak ak
166 3 4 ad ad
167 4 4 tr tr
168 5 5 oo oo
169 select * from t1 force index(c) order by c;
170 a b c d
171 3 4 ad ad
172 2 3 ak ak
173 6 1 ggg ggg
174 5 5 oo oo
175 4 4 tr tr
176 select * from t1 force index(d) order by d;
177 a b c d
178 3 4 ad ad
179 2 3 ak ak
180 6 1 ggg ggg
181 5 5 oo oo
182 4 4 tr tr
183 analyze table t1;
184 explain select * from t1 force index(b) order by b;
185 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
186 1 SIMPLE t1 NULL index NULL b 5 NULL 5 100.00 NULL
187 Warnings:
188 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
189 explain select * from t1 force index(c) order by c;
190 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
191 1 SIMPLE t1 NULL index NULL c 40 NULL 5 100.00 NULL
192 Warnings:
193 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
194 explain select * from t1 force index(d) order by d;
195 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
196 1 SIMPLE t1 NULL index NULL d 83 NULL 5 100.00 NULL
197 Warnings:
198 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d`
199 show create table t1;
200 Table Create Table
201 t1 CREATE TABLE `t1` (
202 `a` int NOT NULL,
203 `b` int DEFAULT NULL,
204 `c` char(10) NOT NULL,
205 `d` varchar(20) DEFAULT NULL,
206 PRIMARY KEY (`a`),
207 UNIQUE KEY `c` (`c`),
208 KEY `d2` (`d`),
209 KEY `b` (`b`),
210 KEY `d` (`d`)
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
212 drop table t1;
213 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
214 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
215 commit;
216 alter table t1 add index (c(2));
217 affected rows: 0
218 info: Records: 0 Duplicates: 0 Warnings: 0
219 show create table t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `a` int NOT NULL,
223 `b` int DEFAULT NULL,
224 `c` char(10) DEFAULT NULL,
225 `d` varchar(20) DEFAULT NULL,
226 PRIMARY KEY (`a`),
227 KEY `c` (`c`(2))
228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 affected rows: 1
230 alter table t1 add unique index (d(10));
231 affected rows: 0
232 info: Records: 0 Duplicates: 0 Warnings: 0
233 show create table t1;
234 Table Create Table
235 t1 CREATE TABLE `t1` (
236 `a` int NOT NULL,
237 `b` int DEFAULT NULL,
238 `c` char(10) DEFAULT NULL,
239 `d` varchar(20) DEFAULT NULL,
240 PRIMARY KEY (`a`),
241 UNIQUE KEY `d` (`d`(10)),
242 KEY `c` (`c`(2))
243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
244 affected rows: 1
245 insert into t1 values(5,1,'ggg','ggg');
246 analyze table t1;
247 select * from t1;
248 a b c d
249 1 1 ab ab
250 2 2 ac ac
251 3 3 ad ad
252 4 4 afe afe
253 5 1 ggg ggg
254 select * from t1 force index(c) order by c;
255 a b c d
256 1 1 ab ab
257 2 2 ac ac
258 3 3 ad ad
259 4 4 afe afe
260 5 1 ggg ggg
261 select * from t1 force index(d) order by d;
262 a b c d
263 1 1 ab ab
264 2 2 ac ac
265 3 3 ad ad
266 4 4 afe afe
267 5 1 ggg ggg
268 explain select * from t1 order by b;
269 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
270 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort
271 Warnings:
272 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b`
273 explain select * from t1 force index(c) order by c;
274 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
275 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort
276 Warnings:
277 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
278 explain select * from t1 force index(d) order by d;
279 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
280 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort
281 Warnings:
282 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d`
283 show create table t1;
284 Table Create Table
285 t1 CREATE TABLE `t1` (
286 `a` int NOT NULL,
287 `b` int DEFAULT NULL,
288 `c` char(10) DEFAULT NULL,
289 `d` varchar(20) DEFAULT NULL,
290 PRIMARY KEY (`a`),
291 UNIQUE KEY `d` (`d`(10)),
292 KEY `c` (`c`(2))
293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
294 alter table t1 drop index d;
295 affected rows: 0
296 info: Records: 0 Duplicates: 0 Warnings: 0
297 insert into t1 values(8,9,'fff','fff');
298 select * from t1;
299 a b c d
300 1 1 ab ab
301 2 2 ac ac
302 3 3 ad ad
303 4 4 afe afe
304 5 1 ggg ggg
305 8 9 fff fff
306 select * from t1 force index(c) order by c;
307 a b c d
308 1 1 ab ab
309 2 2 ac ac
310 3 3 ad ad
311 4 4 afe afe
312 8 9 fff fff
313 5 1 ggg ggg
314 analyze table t1;
315 explain select * from t1 order by b;
316 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
317 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort
318 Warnings:
319 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b`
320 explain select * from t1 force index(c) order by c;
321 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
322 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort
323 Warnings:
324 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
325 explain select * from t1 order by d;
326 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
327 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort
328 Warnings:
329 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`d`
330 show create table t1;
331 Table Create Table
332 t1 CREATE TABLE `t1` (
333 `a` int NOT NULL,
334 `b` int DEFAULT NULL,
335 `c` char(10) DEFAULT NULL,
336 `d` varchar(20) DEFAULT NULL,
337 PRIMARY KEY (`a`),
338 KEY `c` (`c`(2))
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
340 drop table t1;
341 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
342 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
343 commit;
344 alter table t1 add unique index (b,c);
345 affected rows: 0
346 info: Records: 0 Duplicates: 0 Warnings: 0
347 insert into t1 values(8,9,'fff','fff');
348 select * from t1;
349 a b c d
350 1 1 ab ab
351 2 2 ac ac
352 3 2 ad ad
353 4 4 afe afe
354 8 9 fff fff
355 select * from t1 force index(b) order by b;
356 a b c d
357 1 1 ab ab
358 2 2 ac ac
359 3 2 ad ad
360 4 4 afe afe
361 8 9 fff fff
362 analyze table t1;
363 explain select * from t1 force index(b) order by b;
364 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
365 1 SIMPLE t1 NULL index NULL b 46 NULL 5 100.00 NULL
366 Warnings:
367 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
368 show create table t1;
369 Table Create Table
370 t1 CREATE TABLE `t1` (
371 `a` int NOT NULL,
372 `b` int DEFAULT NULL,
373 `c` char(10) DEFAULT NULL,
374 `d` varchar(20) DEFAULT NULL,
375 PRIMARY KEY (`a`),
376 UNIQUE KEY `b` (`b`,`c`)
377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
378 alter table t1 add index (b,c);
379 affected rows: 0
380 info: Records: 0 Duplicates: 0 Warnings: 0
381 insert into t1 values(11,11,'kkk','kkk');
382 select * from t1;
383 a b c d
384 1 1 ab ab
385 2 2 ac ac
386 3 2 ad ad
387 4 4 afe afe
388 8 9 fff fff
389 11 11 kkk kkk
390 select * from t1 force index(b) order by b;
391 a b c d
392 1 1 ab ab
393 2 2 ac ac
394 3 2 ad ad
395 4 4 afe afe
396 8 9 fff fff
397 11 11 kkk kkk
398 analyze table t1;
399 explain select * from t1 force index(b) order by b;
400 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
401 1 SIMPLE t1 NULL index NULL b 46 NULL 6 100.00 NULL
402 Warnings:
403 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
404 show create table t1;
405 Table Create Table
406 t1 CREATE TABLE `t1` (
407 `a` int NOT NULL,
408 `b` int DEFAULT NULL,
409 `c` char(10) DEFAULT NULL,
410 `d` varchar(20) DEFAULT NULL,
411 PRIMARY KEY (`a`),
412 UNIQUE KEY `b` (`b`,`c`),
413 KEY `b_2` (`b`,`c`)
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
415 alter table t1 add unique index (c,d);
416 affected rows: 0
417 info: Records: 0 Duplicates: 0 Warnings: 0
418 insert into t1 values(13,13,'yyy','aaa');
419 select * from t1;
420 a b c d
421 1 1 ab ab
422 2 2 ac ac
423 3 2 ad ad
424 4 4 afe afe
425 8 9 fff fff
426 11 11 kkk kkk
427 13 13 yyy aaa
428 select * from t1 force index(b) order by b;
429 a b c d
430 1 1 ab ab
431 2 2 ac ac
432 3 2 ad ad
433 4 4 afe afe
434 8 9 fff fff
435 11 11 kkk kkk
436 13 13 yyy aaa
437 select * from t1 force index(c) order by c;
438 a b c d
439 1 1 ab ab
440 2 2 ac ac
441 3 2 ad ad
442 4 4 afe afe
443 8 9 fff fff
444 11 11 kkk kkk
445 13 13 yyy aaa
446 analyze table t1;
447 explain select * from t1 force index(b) order by b;
448 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
449 1 SIMPLE t1 NULL index NULL b 46 NULL 7 100.00 NULL
450 Warnings:
451 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
452 explain select * from t1 force index(c) order by c;
453 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
454 1 SIMPLE t1 NULL index NULL c 124 NULL 7 100.00 NULL
455 Warnings:
456 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
457 show create table t1;
458 Table Create Table
459 t1 CREATE TABLE `t1` (
460 `a` int NOT NULL,
461 `b` int DEFAULT NULL,
462 `c` char(10) DEFAULT NULL,
463 `d` varchar(20) DEFAULT NULL,
464 PRIMARY KEY (`a`),
465 UNIQUE KEY `b` (`b`,`c`),
466 UNIQUE KEY `c` (`c`,`d`),
467 KEY `b_2` (`b`,`c`)
468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
469 drop table t1;
470 create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
471 create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
472 create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
473 create table t2(a int not null, b int, c int, d int, e int,
474 foreign key (b) references t1(b) on delete set null,
475 foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
476 engine = innodb;
477 alter table t1 drop index b;
478 ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
479 alter table t3 drop index c;
480 ERROR HY000: Cannot drop index 'c': needed in a foreign key constraint
481 alter table t4 drop index d;
482 ERROR HY000: Cannot drop index 'd': needed in a foreign key constraint
483 alter table t2 drop index b;
484 ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
485 alter table t2 drop index b, drop index c, drop index d;
486 ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
487 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
488 ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
489 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
490 ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
491 SET FOREIGN_KEY_CHECKS=0;
492 alter table t2 DROP COLUMN b, ALGORITHM=COPY;
493 ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1'
494 alter table t2 DROP COLUMN b;
495 ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1'
496 alter table t1 DROP COLUMN b, ALGORITHM=COPY;
497 ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' of table 't2'
498 alter table t1 DROP COLUMN b;
499 ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' of table 't2'
500 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
501 create unique index dc on t2 (d,c);
502 affected rows: 0
503 info: Records: 0 Duplicates: 0 Warnings: 0
504 create index dc on t1 (b,c);
505 affected rows: 0
506 info: Records: 0 Duplicates: 0 Warnings: 0
507 alter table t2 add primary key (alpha), change a alpha int,
508 change b beta int not null, change c charlie int not null;
509 ERROR HY000: Column 'beta' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
510 alter table t2 add primary key (alpha), change a alpha int,
511 change c charlie int not null, change d delta int not null;
512 ERROR HY000: Column 'delta' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_3' SET NULL
513 alter table t2 add primary key (alpha), change a alpha int,
514 change b beta int, modify c int not null;
515 affected rows: 0
516 info: Records: 0 Duplicates: 0 Warnings: 0
517 insert into t1 values (1,1,1);
518 insert into t3 values (1,1,1);
519 insert into t4 values (1,1,1);
520 insert into t2 values (1,1,1,1,1);
521 commit;
522 alter table t4 add constraint dc foreign key (a) references t1(a);
523 affected rows: 1
524 info: Records: 1 Duplicates: 0 Warnings: 0
525 show create table t4;
526 Table Create Table
527 t4 CREATE TABLE `t4` (
528 `a` int NOT NULL,
529 `d` int NOT NULL,
530 `e` int DEFAULT NULL,
531 PRIMARY KEY (`a`),
532 KEY `d` (`d`),
533 CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
535 alter table t3 add constraint dc foreign key (a) references t1(a);
536 ERROR HY000: Duplicate foreign key constraint name 'dc'
537 SET FOREIGN_KEY_CHECKS=0;
538 alter table t3 add constraint dc foreign key (a) references t1(a);
539 ERROR HY000: Duplicate foreign key constraint name 'dc'
540 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
541 show create table t3;
542 Table Create Table
543 t3 CREATE TABLE `t3` (
544 `a` int NOT NULL,
545 `c` int NOT NULL,
546 `d` int DEFAULT NULL,
547 PRIMARY KEY (`a`),
548 KEY `c` (`c`)
549 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
550 alter table t2 drop index b, add index (beta);
551 affected rows: 0
552 info: Records: 0 Duplicates: 0 Warnings: 0
553 show create table t2;
554 Table Create Table
555 t2 CREATE TABLE `t2` (
556 `alpha` int NOT NULL,
557 `beta` int DEFAULT NULL,
558 `c` int NOT NULL,
559 `d` int DEFAULT NULL,
560 `e` int DEFAULT NULL,
561 PRIMARY KEY (`alpha`),
562 UNIQUE KEY `dc` (`d`,`c`),
563 KEY `c` (`c`),
564 KEY `beta` (`beta`),
565 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`beta`) REFERENCES `t1` (`b`) ON DELETE SET NULL,
566 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`),
567 CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) ON UPDATE SET NULL
568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
569 delete from t1;
570 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
571 drop index dc on t4;
572 ERROR 42000: Can't DROP 'dc'; check that column/key exists
573 alter table t3 drop foreign key dc;
574 ERROR 42000: Can't DROP 'dc'; check that column/key exists
575 alter table t4 drop foreign key dc;
576 affected rows: 0
577 info: Records: 0 Duplicates: 0 Warnings: 0
578 select * from t2;
579 alpha beta c d e
580 1 1 1 1 1
581 delete from t1;
582 select * from t2;
583 alpha beta c d e
584 1 NULL 1 1 1
585 drop table t2,t4,t3,t1;
586 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8;
587 Warnings:
588 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
589 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
590 commit;
591 alter table t1 add unique index (b);
592 ERROR 23000: Duplicate entry '2' for key 't1.b'
593 insert into t1 values(8,9,'fff','fff');
594 select * from t1;
595 a b c d
596 1 1 ab ab
597 2 2 ac ac
598 3 2 ad ad
599 4 4 afe afe
600 8 9 fff fff
601 show create table t1;
602 Table Create Table
603 t1 CREATE TABLE `t1` (
604 `a` int NOT NULL,
605 `b` int DEFAULT NULL,
606 `c` char(10) DEFAULT NULL,
607 `d` varchar(20) DEFAULT NULL,
608 PRIMARY KEY (`a`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
610 alter table t1 add index (b);
611 insert into t1 values(10,10,'kkk','iii');
612 select * from t1;
613 a b c d
614 1 1 ab ab
615 2 2 ac ac
616 3 2 ad ad
617 4 4 afe afe
618 8 9 fff fff
619 10 10 kkk iii
620 select * from t1 force index(b) order by b;
621 a b c d
622 1 1 ab ab
623 2 2 ac ac
624 3 2 ad ad
625 4 4 afe afe
626 8 9 fff fff
627 10 10 kkk iii
628 explain select * from t1 force index(b) order by b;
629 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
630 1 SIMPLE t1 NULL index NULL b 5 NULL 6 100.00 NULL
631 Warnings:
632 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
633 show create table t1;
634 Table Create Table
635 t1 CREATE TABLE `t1` (
636 `a` int NOT NULL,
637 `b` int DEFAULT NULL,
638 `c` char(10) DEFAULT NULL,
639 `d` varchar(20) DEFAULT NULL,
640 PRIMARY KEY (`a`),
641 KEY `b` (`b`)
642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
643 alter table t1 add unique index (c), add index (d);
644 insert into t1 values(11,11,'aaa','mmm');
645 select * from t1;
646 a b c d
647 1 1 ab ab
648 2 2 ac ac
649 3 2 ad ad
650 4 4 afe afe
651 8 9 fff fff
652 10 10 kkk iii
653 11 11 aaa mmm
654 select * from t1 force index(b) order by b;
655 a b c d
656 1 1 ab ab
657 2 2 ac ac
658 3 2 ad ad
659 4 4 afe afe
660 8 9 fff fff
661 10 10 kkk iii
662 11 11 aaa mmm
663 select * from t1 force index(c) order by c;
664 a b c d
665 11 11 aaa mmm
666 1 1 ab ab
667 2 2 ac ac
668 3 2 ad ad
669 4 4 afe afe
670 8 9 fff fff
671 10 10 kkk iii
672 select * from t1 force index(d) order by d;
673 a b c d
674 1 1 ab ab
675 2 2 ac ac
676 3 2 ad ad
677 4 4 afe afe
678 8 9 fff fff
679 10 10 kkk iii
680 11 11 aaa mmm
681 explain select * from t1 force index(b) order by b;
682 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
683 1 SIMPLE t1 NULL index NULL b 5 NULL 7 100.00 NULL
684 Warnings:
685 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
686 explain select * from t1 force index(c) order by c;
687 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
688 1 SIMPLE t1 NULL index NULL c 31 NULL 7 100.00 NULL
689 Warnings:
690 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
691 explain select * from t1 force index(d) order by d;
692 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
693 1 SIMPLE t1 NULL index NULL d 63 NULL 7 100.00 NULL
694 Warnings:
695 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d`
696 show create table t1;
697 Table Create Table
698 t1 CREATE TABLE `t1` (
699 `a` int NOT NULL,
700 `b` int DEFAULT NULL,
701 `c` char(10) DEFAULT NULL,
702 `d` varchar(20) DEFAULT NULL,
703 PRIMARY KEY (`a`),
704 UNIQUE KEY `c` (`c`),
705 KEY `b` (`b`),
706 KEY `d` (`d`)
707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
708 check table t1;
709 Table Op Msg_type Msg_text
710 test.t1 check status OK
711 drop table t1;
712 create table t1(a int not null, b int) engine = innodb;
713 insert into t1 values (1,1),(1,1),(1,1),(1,1);
714 alter table t1 add unique index (a);
715 ERROR 23000: Duplicate entry '1' for key 't1.a'
716 alter table t1 add unique index (b);
717 ERROR 23000: Duplicate entry '1' for key 't1.b'
718 alter table t1 add unique index (a), add unique index(b);
719 ERROR 23000: Duplicate entry '1' for key 't1.a'
720 show create table t1;
721 Table Create Table
722 t1 CREATE TABLE `t1` (
723 `a` int NOT NULL,
724 `b` int DEFAULT NULL
725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
726 drop table t1;
727 create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
728 alter table t1 drop index c, drop index b;
729 show create table t1;
730 Table Create Table
731 t1 CREATE TABLE `t1` (
732 `a` int NOT NULL,
733 `c` int NOT NULL,
734 `b` int DEFAULT NULL,
735 PRIMARY KEY (`a`)
736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
737 drop table t1;
738 create table t1(a int not null, b int, primary key(a)) engine = innodb;
739 alter table t1 add index (b);
740 show create table t1;
741 Table Create Table
742 t1 CREATE TABLE `t1` (
743 `a` int NOT NULL,
744 `b` int DEFAULT NULL,
745 PRIMARY KEY (`a`),
746 KEY `b` (`b`)
747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
748 drop table t1;
749 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
750 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
751 alter table t1 add unique index (b), add unique index (c), add unique index (d);
752 ERROR 23000: Duplicate entry '4' for key 't1.b'
753 alter table t1 add unique index (c), add unique index (b), add index (d);
754 ERROR 23000: Duplicate entry 'ac' for key 't1.c'
755 show create table t1;
756 Table Create Table
757 t1 CREATE TABLE `t1` (
758 `a` int NOT NULL,
759 `b` int DEFAULT NULL,
760 `c` char(10) DEFAULT NULL,
761 `d` varchar(20) DEFAULT NULL,
762 PRIMARY KEY (`a`)
763 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
764 drop table t1;
765 create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
766 insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
767 alter table t1 add unique index (b);
768 affected rows: 0
769 info: Records: 0 Duplicates: 0 Warnings: 0
770 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
771 show create table t1;
772 Table Create Table
773 t1 CREATE TABLE `t1` (
774 `a` int NOT NULL,
775 `b` int NOT NULL,
776 `c` int DEFAULT NULL,
777 PRIMARY KEY (`a`),
778 UNIQUE KEY `b` (`b`),
779 KEY `c` (`c`)
780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
781 check table t1;
782 Table Op Msg_type Msg_text
783 test.t1 check status OK
784 analyze table t1;
785 explain select * from t1 force index(c) order by c;
786 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
787 1 SIMPLE t1 NULL index NULL c 5 NULL 9 100.00 NULL
788 Warnings:
789 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
790 explain select * from t1 order by a;
791 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
792 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 9 100.00 NULL
793 Warnings:
794 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`
795 explain select * from t1 force index(b) order by b;
796 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
797 1 SIMPLE t1 NULL index NULL b 4 NULL 9 100.00 NULL
798 Warnings:
799 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
800 select * from t1 order by a;
801 a b c
802 1 5 1
803 2 4 2
804 3 3 3
805 4 2 4
806 5 1 5
807 10 20 20
808 11 19 19
809 12 18 18
810 13 17 17
811 select * from t1 force index(b) order by b;
812 a b c
813 5 1 5
814 4 2 4
815 3 3 3
816 2 4 2
817 1 5 1
818 13 17 17
819 12 18 18
820 11 19 19
821 10 20 20
822 select * from t1 force index(c) order by c;
823 a b c
824 1 5 1
825 2 4 2
826 3 3 3
827 4 2 4
828 5 1 5
829 13 17 17
830 12 18 18
831 11 19 19
832 10 20 20
833 drop table t1;
834 create table t1(a int not null, b int not null) engine=innodb;
835 insert into t1 values (1,1);
836 alter table t1 add primary key(b);
837 affected rows: 0
838 info: Records: 0 Duplicates: 0 Warnings: 0
839 insert into t1 values (2,2);
840 show create table t1;
841 Table Create Table
842 t1 CREATE TABLE `t1` (
843 `a` int NOT NULL,
844 `b` int NOT NULL,
845 PRIMARY KEY (`b`)
846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
847 check table t1;
848 Table Op Msg_type Msg_text
849 test.t1 check status OK
850 select * from t1;
851 a b
852 1 1
853 2 2
854 analyze table t1;
855 Table Op Msg_type Msg_text
856 test.t1 analyze status OK
857 explain select * from t1;
858 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
859 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
860 Warnings:
861 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
862 explain select * from t1 order by a;
863 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
864 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
865 Warnings:
866 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`
867 explain select * from t1 order by b;
868 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
869 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 NULL
870 Warnings:
871 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b`
872 checksum table t1;
873 Table Checksum
874 test.t1 582702641
875 drop table t1;
876 create table t1(a int not null) engine=innodb;
877 insert into t1 values (1);
878 alter table t1 add primary key(a);
879 affected rows: 0
880 info: Records: 0 Duplicates: 0 Warnings: 0
881 insert into t1 values (2);
882 show create table t1;
883 Table Create Table
884 t1 CREATE TABLE `t1` (
885 `a` int NOT NULL,
886 PRIMARY KEY (`a`)
887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
888 check table t1;
889 Table Op Msg_type Msg_text
890 test.t1 check status OK
891 commit;
892 select * from t1;
893 a
894 1
895 2
896 analyze table t1;
897 Table Op Msg_type Msg_text
898 test.t1 analyze status OK
899 explain select * from t1;
900 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
901 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
902 Warnings:
903 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
904 explain select * from t1 order by a;
905 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
906 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
907 Warnings:
908 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a`
909 drop table t1;
910 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
911 SET FOREIGN_KEY_CHECKS=0;
912 CREATE TABLE t1(
913 c1 BIGINT(12) NOT NULL,
914 PRIMARY KEY (c1)
915 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
916 Warnings:
917 Warning 1681 Integer display width is deprecated and will be removed in a future release.
918 CREATE TABLE t2(
919 c1 BIGINT(16) NOT NULL,
920 c2 BIGINT(12) NOT NULL,
921 c3 BIGINT(12) NOT NULL,
922 PRIMARY KEY (c1)
923 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
924 Warnings:
925 Warning 1681 Integer display width is deprecated and will be removed in a future release.
926 Warning 1681 Integer display width is deprecated and will be removed in a future release.
927 Warning 1681 Integer display width is deprecated and will be removed in a future release.
928 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
929 FOREIGN KEY (c3) REFERENCES t1(c1);
930 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
931 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
932 SHOW CREATE TABLE t2;
933 Table Create Table
934 t2 CREATE TABLE `t2` (
935 `c1` bigint NOT NULL,
936 `c2` bigint NOT NULL,
937 `c3` bigint NOT NULL,
938 PRIMARY KEY (`c1`),
939 KEY `fk_t2_ca` (`c3`),
940 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
941 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
942 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
943 SHOW CREATE TABLE t2;
944 Table Create Table
945 t2 CREATE TABLE `t2` (
946 `c1` bigint NOT NULL,
947 `c2` bigint NOT NULL,
948 `c3` bigint NOT NULL,
949 PRIMARY KEY (`c1`),
950 KEY `i_t2_c3_c2` (`c3`,`c2`),
951 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
952 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
953 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
954 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
955 INSERT INTO t2 VALUES(0,0,0);
956 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
957 INSERT INTO t1 VALUES(0);
958 INSERT INTO t2 VALUES(0,0,0);
959 DROP TABLE t2;
960 CREATE TABLE t2(
961 c1 BIGINT(16) NOT NULL,
962 c2 BIGINT(12) NOT NULL,
963 c3 BIGINT(12) NOT NULL,
964 PRIMARY KEY (c1,c2,c3)
965 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
966 Warnings:
967 Warning 1681 Integer display width is deprecated and will be removed in a future release.
968 Warning 1681 Integer display width is deprecated and will be removed in a future release.
969 Warning 1681 Integer display width is deprecated and will be removed in a future release.
970 SET FOREIGN_KEY_CHECKS=0;
971 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
972 FOREIGN KEY (c3) REFERENCES t1(c1);
973 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
974 SHOW CREATE TABLE t2;
975 Table Create Table
976 t2 CREATE TABLE `t2` (
977 `c1` bigint NOT NULL,
978 `c2` bigint NOT NULL,
979 `c3` bigint NOT NULL,
980 PRIMARY KEY (`c1`,`c2`,`c3`),
981 KEY `fk_t2_ca` (`c3`),
982 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
983 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
984 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
985 SHOW CREATE TABLE t2;
986 Table Create Table
987 t2 CREATE TABLE `t2` (
988 `c1` bigint NOT NULL,
989 `c2` bigint NOT NULL,
990 `c3` bigint NOT NULL,
991 PRIMARY KEY (`c1`,`c2`,`c3`),
992 KEY `i_t2_c3_c2` (`c3`,`c2`),
993 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
994 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
995 INSERT INTO t2 VALUES(0,0,1);
996 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
997 INSERT INTO t2 VALUES(0,0,0);
998 DELETE FROM t1;
999 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`))
1000 DELETE FROM t2;
1001 DROP TABLE t2;
1002 DROP TABLE t1;
1003 CREATE TABLE t1(
1004 c1 BIGINT(12) NOT NULL,
1005 c2 INT(4) NOT NULL,
1006 PRIMARY KEY (c2,c1)
1007 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1008 Warnings:
1009 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1010 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1011 CREATE TABLE t2(
1012 c1 BIGINT(16) NOT NULL,
1013 c2 BIGINT(12) NOT NULL,
1014 c3 BIGINT(12) NOT NULL,
1015 PRIMARY KEY (c1)
1016 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1017 Warnings:
1018 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1019 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1020 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1021 SET FOREIGN_KEY_CHECKS=0;
1022 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1023 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
1024 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
1025 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1026 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
1027 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
1028 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1029 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
1030 ERROR HY000: Referencing column 'c2' and referenced column 'c2' in foreign key constraint 'fk_t2_ca' are incompatible.
1031 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1032 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
1033 ERROR HY000: Referencing column 'c2' and referenced column 'c2' in foreign key constraint 'fk_t2_ca' are incompatible.
1034 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1035 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;
1036 ERROR HY000: Referencing column 'c3' and referenced column 'c2' in foreign key constraint 'fk_t2_ca' are incompatible.
1037 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1038 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
1039 ERROR HY000: Referencing column 'c3' and referenced column 'c2' in foreign key constraint 'fk_t2_ca' are incompatible.
1040 ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
1041 affected rows: 0
1042 info: Records: 0 Duplicates: 0 Warnings: 1
1043 Warnings:
1044 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1045 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1046 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
1047 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
1048 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1049 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
1050 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
1051 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
1052 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
1053 affected rows: 0
1054 info: Records: 0 Duplicates: 0 Warnings: 0
1055 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
1056 affected rows: 0
1057 SHOW CREATE TABLE t1;
1058 Table Create Table
1059 t1 CREATE TABLE `t1` (
1060 `c1` bigint NOT NULL,
1061 `c2` bigint NOT NULL,
1062 PRIMARY KEY (`c2`,`c1`)
1063 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1064 affected rows: 1
1065 SHOW CREATE TABLE t2;
1066 Table Create Table
1067 t2 CREATE TABLE `t2` (
1068 `c1` bigint NOT NULL,
1069 `c2` bigint NOT NULL,
1070 `c3` bigint NOT NULL,
1071 PRIMARY KEY (`c1`),
1072 KEY `fk_t2_ca` (`c3`,`c2`),
1073 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
1074 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1075 affected rows: 1
1076 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
1077 affected rows: 0
1078 info: Records: 0 Duplicates: 0 Warnings: 0
1079 SHOW CREATE TABLE t2;
1080 Table Create Table
1081 t2 CREATE TABLE `t2` (
1082 `c1` bigint NOT NULL,
1083 `c2` bigint NOT NULL,
1084 `c3` bigint NOT NULL,
1085 PRIMARY KEY (`c1`),
1086 KEY `fk_t2_ca` (`c3`,`c2`),
1087 KEY `i_t2_c2_c1` (`c2`,`c1`),
1088 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
1089 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1090 affected rows: 1
1091 CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
1092 affected rows: 0
1093 info: Records: 0 Duplicates: 0 Warnings: 0
1094 SHOW CREATE TABLE t2;
1095 Table Create Table
1096 t2 CREATE TABLE `t2` (
1097 `c1` bigint NOT NULL,
1098 `c2` bigint NOT NULL,
1099 `c3` bigint NOT NULL,
1100 PRIMARY KEY (`c1`),
1101 KEY `fk_t2_ca` (`c3`,`c2`),
1102 KEY `i_t2_c2_c1` (`c2`,`c1`),
1103 KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
1104 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
1105 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1106 affected rows: 1
1107 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
1108 affected rows: 0
1109 info: Records: 0 Duplicates: 0 Warnings: 0
1110 SHOW CREATE TABLE t2;
1111 Table Create Table
1112 t2 CREATE TABLE `t2` (
1113 `c1` bigint NOT NULL,
1114 `c2` bigint NOT NULL,
1115 `c3` bigint NOT NULL,
1116 PRIMARY KEY (`c1`),
1117 KEY `i_t2_c2_c1` (`c2`,`c1`),
1118 KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
1119 KEY `i_t2_c3_c2` (`c3`,`c2`),
1120 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
1121 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1122 affected rows: 1
1123 DROP TABLE t2;
1124 DROP TABLE t1;
1125 CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
1126 INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
1127 CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
1128 CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
1129 CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
1130 INSERT INTO t2 SELECT * FROM t1;
1131 INSERT INTO t2i SELECT * FROM t1;
1132 INSERT INTO t2c SELECT * FROM t1;
1133 BEGIN;
1134 SELECT * FROM t1;
1135 a b
1136 3 a
1137 3 b
1138 1 c
1139 0 d
1140 1 e
1141 SET lock_wait_timeout=1;
1142 CREATE INDEX t1a ON t1(a);
1143 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1144 CREATE INDEX t2a ON t2(a);
1145 affected rows: 0
1146 info: Records: 0 Duplicates: 0 Warnings: 0
1147 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
1148 affected rows: 0
1149 info: Records: 0 Duplicates: 0 Warnings: 0
1150 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
1151 affected rows: 5
1152 info: Records: 5 Duplicates: 0 Warnings: 0
1153 SELECT * FROM t2i;
1154 ERROR HY000: Table definition has changed, please retry transaction
1155 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
1156 ERROR HY000: Table definition has changed, please retry transaction
1157 SELECT * FROM t2c;
1158 ERROR HY000: Table definition has changed, please retry transaction
1159 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
1160 ERROR HY000: Table definition has changed, please retry transaction
1161 SELECT * FROM t2;
1162 a b
1163 3 a
1164 3 b
1165 1 c
1166 0 d
1167 1 e
1168 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
1169 ERROR HY000: Table definition has changed, please retry transaction
1170 SELECT * FROM t2;
1171 a b
1172 3 a
1173 3 b
1174 1 c
1175 0 d
1176 1 e
1177 COMMIT;
1178 SELECT * FROM t2;
1179 a b
1180 3 a
1181 3 b
1182 1 c
1183 0 d
1184 1 e
1185 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
1186 a b
1187 0 d
1188 1 c
1189 1 e
1190 3 a
1191 3 b
1192 SELECT * FROM t2i;
1193 a b
1194 0 d
1195 1 c
1196 1 e
1197 3 a
1198 3 b
1199 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
1200 a b
1201 0 d
1202 1 c
1203 1 e
1204 3 a
1205 3 b
1206 SELECT * FROM t2c;
1207 a b
1208 0 d
1209 1 c
1210 1 e
1211 3 a
1212 3 b
1213 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
1214 a b
1215 0 d
1216 1 c
1217 1 e
1218 3 a
1219 3 b
1220 alter table t2 add index t2a(b);
1221 ERROR 42000: Duplicate key name 't2a'
1222 alter table t2 drop index t2a, add index t2a(b);
1223 show create table t2;
1224 Table Create Table
1225 t2 CREATE TABLE `t2` (
1226 `a` int DEFAULT NULL,
1227 `b` char(1) DEFAULT NULL,
1228 KEY `t2a` (`b`)
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1230 show create table t2i;
1231 Table Create Table
1232 t2i CREATE TABLE `t2i` (
1233 `a` int NOT NULL,
1234 `b` char(1) NOT NULL,
1235 PRIMARY KEY (`a`,`b`),
1236 KEY `t2a` (`a`)
1237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1238 show create table t2c;
1239 Table Create Table
1240 t2c CREATE TABLE `t2c` (
1241 `a` int NOT NULL,
1242 `b` char(1) NOT NULL,
1243 PRIMARY KEY (`a`,`b`),
1244 KEY `t2a` (`a`)
1245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1246 DROP TABLE t1,t2,t2c,t2i;
1247 #
1248 # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
1249 #
1250 SET GLOBAL innodb_monitor_enable = module_ddl;
1251 create table t1(f1 int not null, f2 blob)engine=innodb;
1252 insert into t1 values(1, repeat('a',20000));
1253 # Skip sort
1254 # Reusing the same pk
1255 alter table t1 force;
1256 affected rows: 0
1257 info: Records: 0 Duplicates: 0 Warnings: 0
1258 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1259 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1260 name count_reset
1261 SET GLOBAL innodb_monitor_reset = module_ddl;
1262 drop table t1;
1263 create table t1(f1 int not null, f2 int not null,
1264 primary key(f1))engine=innodb;
1265 insert into t1 values(1,2), (3,4);
1266 # Add Secondary index.
1267 # Skip temp file usage due to small table size
1268 alter table t1 add key(f2);
1269 affected rows: 0
1270 info: Records: 0 Duplicates: 0 Warnings: 0
1271 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1272 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1273 name count_reset
1274 SET GLOBAL innodb_monitor_reset = module_ddl;
1275 drop table t1;
1276 create table t480(a serial)engine=innodb;
1277 insert into t480
1278 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
1279 (),(),(),(),(),(),(),();
1280 insert into t480 select 0 from t480;
1281 insert into t480 select 0 from t480;
1282 insert into t480 select 0 from t480;
1283 insert into t480 select 0 from t480;
1284 create table t1(f1 int auto_increment not null,
1285 f2 char(200) not null, f3 char(200) not null,
1286 f4 char(200) not null,primary key(f1))engine=innodb charset latin1;
1287 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
1288 insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
1289 insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
1290 insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
1291 insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
1292 insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
1293 select count(*) from t1;
1294 count(*)
1295 2880
1296 # Skip sort
1297 # Change PK from (f1) to (f1,f2,f3,f4)
1298 alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
1299 affected rows: 0
1300 info: Records: 0 Duplicates: 0 Warnings: 0
1301 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1302 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1303 name count_reset
1304 SET GLOBAL innodb_monitor_reset = module_ddl;
1305 # Skip sort
1306 # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
1307 alter table t1 drop primary key,add column f5 int not null,
1308 add column f6 int not null,add primary key(f1,f2,f5,f6);
1309 affected rows: 0
1310 info: Records: 0 Duplicates: 0 Warnings: 0
1311 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1312 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1313 name count_reset
1314 SET GLOBAL innodb_monitor_reset = module_ddl;
1315 # Skip sort
1316 # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
1317 alter table t1 drop column f6;
1318 affected rows: 0
1319 info: Records: 0 Duplicates: 0 Warnings: 0
1320 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1321 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1322 name count_reset
1323 SET GLOBAL innodb_monitor_reset = module_ddl;
1324 # Skip sort
1325 # Reusing the same PK
1326 alter table t1 add column f6 int;
1327 affected rows: 0
1328 info: Records: 0 Duplicates: 0 Warnings: 0
1329 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1330 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1331 name count_reset
1332 SET GLOBAL innodb_monitor_reset = module_ddl;
1333 # Skip sort
1334 # Reusing the same pk
1335 alter table t1 drop column f6;
1336 affected rows: 0
1337 info: Records: 0 Duplicates: 0 Warnings: 0
1338 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1339 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1340 name count_reset
1341 SET GLOBAL innodb_monitor_reset = module_ddl;
1342 # Must sort
1343 # Change PK from (f1,f2,f5) to (f1,f5)
1344 alter table t1 drop column f2;
1345 affected rows: 0
1346 info: Records: 0 Duplicates: 0 Warnings: 0
1347 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1348 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1349 name count_reset
1350 SET GLOBAL innodb_monitor_reset = module_ddl;
1351 # Skip sort
1352 # Reusing the same pk
1353 alter table t1 add column f2n int after f1, drop primary key, add
1354 primary key (f1,f5,f2n);
1355 affected rows: 0
1356 info: Records: 0 Duplicates: 0 Warnings: 0
1357 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1358 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1359 name count_reset
1360 SET GLOBAL innodb_monitor_reset = module_ddl;
1361 # Skip sort
1362 # Reusing the same pk
1363 alter table t1 change f5 f2n int not null,change f2n f5 int not null,
1364 add column f8 int not null;
1365 affected rows: 0
1366 info: Records: 0 Duplicates: 0 Warnings: 0
1367 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1368 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1369 name count_reset
1370 SET GLOBAL innodb_monitor_reset = module_ddl;
1371 # Skip sort
1372 # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
1373 alter table t1 add column f7 int, drop primary key,
1374 add primary key (f1,f5,f7,f2n);
1375 affected rows: 0
1376 info: Records: 0 Duplicates: 0 Warnings: 0
1377 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1378 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1379 name count_reset
1380 SET GLOBAL innodb_monitor_reset = module_ddl;
1381 # Skip sort
1382 # Reusing the same pk
1383 alter table t1 force;
1384 affected rows: 0
1385 info: Records: 0 Duplicates: 0 Warnings: 0
1386 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1387 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1388 name count_reset
1389 SET GLOBAL innodb_monitor_reset = module_ddl;
1390 # Skip sort
1391 # Reusing the same pk
1392 alter table t1 row_format=compact;
1393 affected rows: 0
1394 info: Records: 0 Duplicates: 0 Warnings: 0
1395 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1396 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1397 name count_reset
1398 SET GLOBAL innodb_monitor_reset = module_ddl;
1399 # Skip sort
1400 # Reusing the same pk
1401 alter table t1 engine=innodb;
1402 affected rows: 0
1403 info: Records: 0 Duplicates: 0 Warnings: 0
1404 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1405 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1406 name count_reset
1407 SET GLOBAL innodb_monitor_reset = module_ddl;
1408 # Skip sort
1409 # Optimize table
1410 optimize table t1;
1411 Table Op Msg_type Msg_text
1412 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
1413 test.t1 optimize status OK
1414 affected rows: 2
1415 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1416 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1417 name count_reset
1418 SET GLOBAL innodb_monitor_reset = module_ddl;
1419 # Sort files used for adding secondary index
1420 alter table t1 drop primary key, add primary key(f1,f5,f7), add index
1421 i(f3);
1422 affected rows: 0
1423 info: Records: 0 Duplicates: 0 Warnings: 0
1424 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1425 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1426 name count_reset
1427 SET GLOBAL innodb_monitor_reset = module_ddl;
1428 # No sort files used for dropping secondary index
1429 alter table t1 drop primary key, add primary key(f1,f5),drop index i;
1430 affected rows: 0
1431 info: Records: 0 Duplicates: 0 Warnings: 0
1432 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1433 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1434 name count_reset
1435 SET GLOBAL innodb_monitor_reset = module_ddl;
1436 # Skip sort
1437 # Change PK(f1,f5) to (f1,added_columns) and drop f5
1438 alter table t1 drop primary key, add primary key(f1,f12),
1439 drop column f5, add column f12 int not null;
1440 affected rows: 0
1441 info: Records: 0 Duplicates: 0 Warnings: 0
1442 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1443 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1444 name count_reset
1445 SET GLOBAL innodb_monitor_reset = module_ddl;
1446 # Must sort
1447 # Change PK(f1,f12) to (f1,existing_columns)
1448 alter table t1 drop primary key, add primary key(f1,f3);
1449 affected rows: 0
1450 info: Records: 0 Duplicates: 0 Warnings: 0
1451 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1452 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1453 name count_reset
1454 SET GLOBAL innodb_monitor_reset = module_ddl;
1455 # Skip sort
1456 # Change PK(f1,f3) to (f1,added_column,f3,added_column)
1457 alter table t1 drop primary key, add column f3n int,
1458 add column f4n int, add primary key(f1,f3n,f3,f4n);
1459 affected rows: 0
1460 info: Records: 0 Duplicates: 0 Warnings: 0
1461 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1462 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1463 name count_reset
1464 SET GLOBAL innodb_monitor_reset = module_ddl;
1465 # Adding Secondary index alone.
1466 alter table t1 add key(f1);
1467 affected rows: 0
1468 info: Records: 0 Duplicates: 0 Warnings: 0
1469 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1470 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1471 name count_reset
1472 SET GLOBAL innodb_monitor_reset = module_ddl;
1473 # Must sort
1474 # Change PK(f1,f3) to (existing_column,f1)
1475 alter table t1 drop primary key, add primary key(f4,f1);
1476 affected rows: 0
1477 info: Records: 0 Duplicates: 0 Warnings: 0
1478 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1479 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1480 name count_reset
1481 SET GLOBAL innodb_monitor_reset = module_ddl;
1482 # Skip sort for PK.
1483 # Change PK(f4,f1) to (added_columns,f4,f1)
1484 # Secondary index rebuild happens
1485 alter table t1 drop primary key, add column f5n int,
1486 add column f6n int, add primary key(f5n,f6n,f4,f1);
1487 affected rows: 0
1488 info: Records: 0 Duplicates: 0 Warnings: 0
1489 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1490 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1491 name count_reset
1492 SET GLOBAL innodb_monitor_reset = module_ddl;
1493 # Skip sort for PK.
1494 # Change PK(f5n,f6n,f4,f1) to
1495 # (added_columns,f5n,added_column,f6n,f4,f1)
1496 # Secondary index rebuild happens
1497 alter table t1 drop primary key, add column f7n int,
1498 add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
1499 affected rows: 0
1500 info: Records: 0 Duplicates: 0 Warnings: 0
1501 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
1502 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
1503 name count_reset
1504 SET GLOBAL innodb_monitor_reset = module_ddl;
1505 SET GLOBAL innodb_monitor_disable = module_ddl;
1506 select count(*) from t1;
1507 count(*)
1508 2880
1509 drop table t1;
1510 SET GLOBAL innodb_monitor_reset = default;
1511 SET GLOBAL innodb_monitor_enable = default;
1512 SET GLOBAL innodb_monitor_disable = default;
1513 # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
1514 # WHEN SKIPPING SORT FOR CLUSTER INDEX
1515 SELECT @@innodb_sort_buffer_size;
1516 @@innodb_sort_buffer_size
1517 65536
1518 create table t1(f1 int auto_increment not null,
1519 f2 char(200) not null, f3 char(200) not null,
1520 f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1;
1521 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
1522 insert into t1 values(106, 'aaa','bbb','cccc');
1523 select count(*) from t1;
1524 count(*)
1525 481
1526 # Skip sort
1527 # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
1528 alter table t1 drop primary key, add primary key(f1,f2,f3);
1529 ERROR 23000: Duplicate entry '106-aaa-bbb' for key 't1.PRIMARY'
1530 select count(*) from t1;
1531 count(*)
1532 481
1533 drop table t1;
1534 create table t1(f1 int auto_increment not null,
1535 f2 char(200) not null, f3 char(200) not null,
1536 f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1;
1537 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
1538 insert into t1 values(108,'aaa','bbb','cccc');
1539 select count(*) from t1;
1540 count(*)
1541 481
1542 alter table t1 drop primary key, add primary key(f1,f2,f3);
1543 ERROR 23000: Duplicate entry '108-aaa-bbb' for key 't1.PRIMARY'
1544 select count(*) from t1;
1545 count(*)
1546 481
1547 drop table t1, t480;
1548 #
1549 # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
1550 # FIELD IS CHANGED
1551 #
1552 create table t1(a int not null, b varchar(30) not null,
1553 primary key (b(10), a)) engine = innodb;
1554 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
1555 select * from t1;
1556 a b
1557 0 khdHps6UxW8Lwaoxa604oK6zkb
1558 1 khdHps6UxW8L
1559 alter table t1 drop primary key, add primary key (b(18),a);
1560 select * from t1;
1561 a b
1562 1 khdHps6UxW8L
1563 0 khdHps6UxW8Lwaoxa604oK6zkb
1564 drop table t1;
1565 create table t1(a int not null, b varchar(30) not null,
1566 primary key (b(10), a)) engine = innodb;
1567 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
1568 select * from t1;
1569 a b
1570 1 khdHps6UtW8L
1571 0 khdHps6UxW8Lwaoxa604oK6zkb
1572 alter table t1 drop primary key, add primary key (b(8),a);
1573 select * from t1;
1574 a b
1575 0 khdHps6UxW8Lwaoxa604oK6zkb
1576 1 khdHps6UtW8L
1577 drop table t1;
1578 #
1579 # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE
1580 # COLUMN PRIMARY KEY
1581 #
1582 create table t1(f1 int not null, f2 int not null,
1583 primary key (f1), unique key(f1, f2))engine=innodb;
1584 insert into t1 values(1,3), (2,2);
1585 alter table t1 drop column f1;
1586 drop table t1;
1587 create table t1(f1 int not null, f2 int not null,
1588 primary key (f1), unique key(f1, f2))engine=innodb;
1589 insert into t1 values(1,3), (2,2);
1590 alter table t1 drop primary key, lock=none;
1591 ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.
1592 drop table t1;
1593 #
1594 # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
1595 # LAST COLUMN OF OLD PK
1596 #
1597 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1598 insert into t1 values(1,1,2),(1,2,1);
1599 alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
1600 drop table t1;
1601 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1602 insert into t1 values(1,1,2),(1,2,1);
1603 alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
1604 drop table t1;
1605 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1606 insert into t1 values(1,1,2),(1,2,1);
1607 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
1608 drop table t1;
1609 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1610 insert into t1 values(1,1,2),(1,2,1);
1611 alter table t1 drop primary key, add primary key(o3), lock=none;
1612 drop table t1;
1613 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1614 insert into t1 values(1,1,2),(1,2,1);
1615 alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
1616 drop table t1;
1617 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1618 insert into t1 values(1,1,2),(2,2,1);
1619 alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
1620 drop table t1;
1621 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1622 insert into t1 values(1,2,2),(2,1,1);
1623 alter table t1 drop primary key, add primary key(o2, o1), lock=none;
1624 drop table t1;
1625 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1626 insert into t1 values(1,2,2),(2,1,1);
1627 alter table t1 drop primary key, add primary key(o2), lock=none;
1628 drop table t1;
1629 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1630 insert into t1 values(1,2,2),(2,1,1);
1631 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
1632 drop table t1;
1633 create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
1634 insert into t1 values(1,1,2),(2,1,1);
1635 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
1636 drop table t1;
1637 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1638 insert into t1 values(1,1,2),(1,2,1);
1639 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
1640 drop table t1;
1641 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1642 insert into t1 values(1,2,2),(2,1,1);
1643 alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
1644 drop table t1;
1645 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1646 insert into t1 values(1,1,2),(1,2,1);
1647 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
1648 drop table t1;
1649 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1650 insert into t1 values(1,2,2),(2,1,1);
1651 alter table t1 drop o1, lock=none;
1652 drop table t1;
1653 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1654 insert into t1 values(1,1,2),(1,2,1);
1655 alter table t1 drop o2, lock=none;
1656 drop table t1;
1657 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1658 insert into t1 values(1,2,2),(2,1,1);
1659 alter table t1 drop o1, drop o2, lock=none;
1660 drop table t1;
1661 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1662 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1663 alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
1664 drop table t1;
1665 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1666 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1667 alter table t1 drop primary key, add primary key(o1, o2), lock=none;
1668 drop table t1;
1669 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1670 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1671 alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
1672 drop table t1;
1673 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1674 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1675 alter table t1 drop primary key, add primary key(o1, o3), lock=none;
1676 drop table t1;
1677 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
1678 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
1679 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
1680 drop table t1;
1681 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
1682 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
1683 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
1684 drop table t1;
1685 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
1686 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
1687 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
1688 drop table t1;
1689 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
1690 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
1691 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
1692 drop table t1;
1693 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
1694 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
1695 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
1696 drop table t1;
1697 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
1698 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
1699 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1700 drop table t1;
1701 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1702 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1703 alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
1704 drop table t1;
1705 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1706 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1707 alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
1708 drop table t1;
1709 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1710 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1711 alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
1712 drop table t1;
1713 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1714 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1715 alter table t1 drop primary key, add primary key(o2,o1), lock=none;
1716 drop table t1;
1717 CREATE TABLE t1(a INT NOT NULL, b INT, PRIMARY KEY (a ASC)) ENGINE=INNODB;
1718 SHOW CREATE TABLE t1;
1719 Table Create Table
1720 t1 CREATE TABLE `t1` (
1721 `a` int NOT NULL,
1722 `b` int DEFAULT NULL,
1723 PRIMARY KEY (`a`)
1724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1725 INSERT INTO t1 VALUES(5,5), (4,4), (3,4), (2,3);
1726 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a DESC);
1727 CHECK TABLE t1;
1728 Table Op Msg_type Msg_text
1729 test.t1 check status OK
1730 SELECT * FROM t1;
1731 a b
1732 5 5
1733 4 4
1734 3 4
1735 2 3
1736 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a ASC);
1737 CHECK TABLE t1;
1738 Table Op Msg_type Msg_text
1739 test.t1 check status OK
1740 SELECT * FROM t1;
1741 a b
1742 2 3
1743 3 4
1744 4 4
1745 5 5
1746 DROP TABLE t1;
1747 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
1748 insert into t1 values('abd'), ('acd');
1749 alter table t1 drop primary key, add primary key(o1(3)), lock=none;
1750 drop table t1;
1751 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
1752 insert into t1 values('abd'), ('acd');
1753 alter table t1 drop primary key, add primary key(o1), lock=none;
1754 drop table t1;
1755 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
1756 insert into t1 values('abd'), ('acd');
1757 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
1758 drop table t1;
1759 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
1760 insert into t1 values('abd'), ('acd');
1761 alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
1762 drop table t1;
1763 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
1764 insert into t1 values('abd', 1), ('acd', 2);
1765 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
1766 drop table t1;
1767 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
1768 insert into t1 values('abd', 1), ('acd', 2);
1769 alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
1770 drop table t1;
1771 create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
1772 insert into t1 values('abd'), ('acd');
1773 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
1774 drop table t1;
1775 create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
1776 insert into t1 values('abd'), ('acd');
1777 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
1778 drop table t1;
1779 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
1780 insert into t1 values('abd', 1), ('acd', 2);
1781 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
1782 drop table t1;
1783 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
1784 insert into t1 values('abd', 1), ('acd', 2);
1785 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
1786 drop table t1;
1787 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
1788 insert into t1 values('abd', 1), ('acd', 2);
1789 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
1790 drop table t1;
1791 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
1792 insert into t1 values('abd', 1), ('acd', 2);
1793 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
1794 drop table t1;
1795 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
1796 insert into t1 values('abd', 1), ('acd', 2);
1797 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
1798 drop table t1;
1799 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
1800 insert into t1 values('abd', 1), ('acd', 2);
1801 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
1802 drop table t1;
1803 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
1804 insert into t1 values(1,'abd'), (2,'acd');
1805 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
1806 drop table t1;
1807 create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
1808 insert into t1 values(1,'abd'), (2,'acd');
1809 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
1810 drop table t1;
1811 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
1812 insert into t1 values(1, 'abd'), (2, 'acd');
1813 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
1814 drop table t1;
1815 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
1816 insert into t1 values(1, 'abd'), (2, 'acd');
1817 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1818 drop table t1;
1819 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
1820 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
1821 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
1822 drop table t1;
1823 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
1824 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
1825 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
1826 drop table t1;
1827 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1828 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1829 alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
1830 drop table t1;
1831 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1832 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1833 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
1834 drop table t1;
1835 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
1836 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1837 alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
1838 drop table t1;
1839 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
1840 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1841 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1842 drop table t1;
1843 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
1844 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
1845 alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
1846 drop table t1;
1847 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
1848 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
1849 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
1850 drop table t1;
1851 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
1852 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
1853 alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
1854 drop table t1;
1855 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
1856 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
1857 alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
1858 drop table t1;
1859 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1860 insert into t1 values(1,1),(2,2);
1861 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1862 drop table t1;
1863 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1864 insert into t1 values(1,1),(2,2);
1865 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
1866 drop table t1;
1867 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1868 insert into t1 values(1,1),(2,2);
1869 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
1870 drop table t1;
1871 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1872 insert into t1 values(1,1),(2,2);
1873 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
1874 drop table t1;
1875 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1876 insert into t1 values(1,1),(2,2);
1877 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
1878 drop table t1;
1879 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1880 insert into t1 values(1,1),(2,2);
1881 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
1882 drop table t1;
1883 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1884 insert into t1 values(1,1),(2,2);
1885 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
1886 drop table t1;
1887 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1888 insert into t1 values(1,1),(2,2);
1889 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
1890 drop table t1;
1891 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
1892 insert into t1 values(1,1),(2,2);
1893 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
1894 drop table t1;
1895 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
1896 insert into t1 values(1,1,2),(2,2,1);
1897 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
1898 drop table t1;
1899 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
1900 insert into t1 values(1,1,2),(2,2,1);
1901 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
1902 drop table t1;
1903 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1904 insert into t1 values(1,1,2),(2,2,1);
1905 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1906 drop table t1;
1907 create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
1908 insert into t1 values(1,1,2,2),(2,2,1,1);
1909 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
1910 drop table t1;
1911 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1912 insert into t1 values(1,1,2),(2,2,1);
1913 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
1914 drop table t1;
1915 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1916 insert into t1 values(1,1,2),(2,2,1);
1917 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
1918 drop table t1;
1919 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1920 insert into t1 values(1,1,2),(2,2,1);
1921 alter table t1 drop primary key, add primary key(o1), lock=none;
1922 drop table t1;
1923 CREATE TABLE t1(o1 int, o2 int, o3 int, PRIMARY KEY(o1), KEY(o2)) ENGINE=INNODB;
1924 INSERT INTO t1 VALUES(1,1,2),(2,2,1);
1925 EXPLAIN SELECT * FROM t1 ORDER BY o2;
1926 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1927 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
1928 Warnings:
1929 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
1930 SELECT * FROM t1 ORDER BY o2;
1931 o1 o2 o3
1932 1 1 2
1933 2 2 1
1934 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 DESC);
1935 CHECK TABLE t1;
1936 Table Op Msg_type Msg_text
1937 test.t1 check status OK
1938 SELECT * FROM t1;
1939 o1 o2 o3
1940 1 1 2
1941 2 2 1
1942 EXPLAIN SELECT * FROM t1 ORDER BY o2;
1943 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1944 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
1945 Warnings:
1946 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
1947 SELECT * FROM t1 ORDER BY o2;
1948 o1 o2 o3
1949 1 1 2
1950 2 2 1
1951 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 ASC);
1952 CHECK TABLE t1;
1953 Table Op Msg_type Msg_text
1954 test.t1 check status OK
1955 SELECT * FROM t1;
1956 o1 o2 o3
1957 1 1 2
1958 2 2 1
1959 EXPLAIN SELECT * FROM t1 ORDER BY o2;
1960 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1961 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
1962 Warnings:
1963 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
1964 SELECT * FROM t1 ORDER BY o2;
1965 o1 o2 o3
1966 1 1 2
1967 2 2 1
1968 DROP TABLE t1;