Debian Codegraph @

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

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