1SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; 2create table t1(a varchar(2) primary key) engine=innodb; 3insert into t1 values(''); 4create index t1a1 on t1(a(1)); 5affected rows: 0 6info: Records: 0 Duplicates: 0 Warnings: 0 7drop table t1; 8create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb; 9insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak'); 10commit; 11alter table t1 add index b (b), add index b (b); 12ERROR 42000: Duplicate key name 'b' 13alter table t1 add index (b,b); 14ERROR 42S21: Duplicate column name 'b' 15alter table t1 add index d2 (d); 16affected rows: 0 17info: Records: 0 Duplicates: 0 Warnings: 0 18show create table t1; 19Table Create Table 20t1 CREATE TABLE `t1` ( 21 `a` int(11) NOT NULL, 22 `b` int(11) DEFAULT NULL, 23 `c` char(10) NOT NULL, 24 `d` varchar(20) DEFAULT NULL, 25 KEY `d2` (`d`) 26) ENGINE=InnoDB DEFAULT CHARSET=latin1 27analyze table t1; 28explain select * from t1 force index(d2) order by d; 29id select_type table type possible_keys key key_len ref rows Extra 301 SIMPLE t1 index NULL d2 23 NULL 4 31select * from t1 force index (d2) order by d; 32a b c d 333 4 ad ad 342 3 ak ak 355 5 oo oo 364 4 tr tr 37alter table t1 add unique index (b); 38ERROR 23000: Duplicate entry '4' for key 'b' 39show create table t1; 40Table Create Table 41t1 CREATE TABLE `t1` ( 42 `a` int(11) NOT NULL, 43 `b` int(11) DEFAULT NULL, 44 `c` char(10) NOT NULL, 45 `d` varchar(20) DEFAULT NULL, 46 KEY `d2` (`d`) 47) ENGINE=InnoDB DEFAULT CHARSET=latin1 48alter table t1 add index (b); 49affected rows: 0 50info: Records: 0 Duplicates: 0 Warnings: 0 51show create table t1; 52Table Create Table 53t1 CREATE TABLE `t1` ( 54 `a` int(11) NOT NULL, 55 `b` int(11) DEFAULT NULL, 56 `c` char(10) NOT NULL, 57 `d` varchar(20) DEFAULT NULL, 58 KEY `d2` (`d`), 59 KEY `b` (`b`) 60) ENGINE=InnoDB DEFAULT CHARSET=latin1 61alter table t1 add unique index (c), add index (d); 62affected rows: 0 63info: Records: 0 Duplicates: 0 Warnings: 1 64Warnings: 65Note 1831 Duplicate index `d`. This is deprecated and will be disallowed in a future release 66show create table t1; 67Table Create Table 68t1 CREATE TABLE `t1` ( 69 `a` int(11) NOT NULL, 70 `b` int(11) DEFAULT NULL, 71 `c` char(10) NOT NULL, 72 `d` varchar(20) DEFAULT NULL, 73 UNIQUE KEY `c` (`c`), 74 KEY `d2` (`d`), 75 KEY `b` (`b`), 76 KEY `d` (`d`) 77) ENGINE=InnoDB DEFAULT CHARSET=latin1 78analyze table t1; 79Table Op Msg_type Msg_text 80test.t1 analyze status Engine-independent statistics collected 81test.t1 analyze status OK 82explain select * from t1 force index(c) order by c; 83id select_type table type possible_keys key key_len ref rows Extra 841 SIMPLE t1 index NULL c 10 NULL 4 85alter table t1 add primary key (a), drop index c; 86affected rows: 0 87info: Records: 0 Duplicates: 0 Warnings: 0 88show create table t1; 89Table Create Table 90t1 CREATE TABLE `t1` ( 91 `a` int(11) NOT NULL, 92 `b` int(11) DEFAULT NULL, 93 `c` char(10) NOT NULL, 94 `d` varchar(20) DEFAULT NULL, 95 PRIMARY KEY (`a`), 96 KEY `d2` (`d`), 97 KEY `b` (`b`), 98 KEY `d` (`d`) 99) ENGINE=InnoDB DEFAULT CHARSET=latin1 100affected rows: 1 101alter table t1 add primary key (c); 102ERROR 42000: Multiple primary key defined 103alter table t1 drop primary key, add primary key (b); 104ERROR 23000: Duplicate entry '4' for key 'PRIMARY' 105create unique index c on t1 (c); 106affected rows: 0 107info: Records: 0 Duplicates: 0 Warnings: 0 108show create table t1; 109Table Create Table 110t1 CREATE TABLE `t1` ( 111 `a` int(11) NOT NULL, 112 `b` int(11) DEFAULT NULL, 113 `c` char(10) NOT NULL, 114 `d` varchar(20) DEFAULT NULL, 115 PRIMARY KEY (`a`), 116 UNIQUE KEY `c` (`c`), 117 KEY `d2` (`d`), 118 KEY `b` (`b`), 119 KEY `d` (`d`) 120) ENGINE=InnoDB DEFAULT CHARSET=latin1 121analyze table t1; 122Table Op Msg_type Msg_text 123test.t1 analyze status Engine-independent statistics collected 124test.t1 analyze status OK 125explain select * from t1 force index(c) order by c; 126id select_type table type possible_keys key key_len ref rows Extra 1271 SIMPLE t1 index NULL c 10 NULL 4 128select * from t1 force index(c) order by c; 129a b c d 1303 4 ad ad 1312 3 ak ak 1325 5 oo oo 1334 4 tr tr 134alter table t1 drop index b, add index (b); 135affected rows: 0 136info: Records: 0 Duplicates: 0 Warnings: 0 137show create table t1; 138Table Create Table 139t1 CREATE TABLE `t1` ( 140 `a` int(11) NOT NULL, 141 `b` int(11) DEFAULT NULL, 142 `c` char(10) NOT NULL, 143 `d` varchar(20) DEFAULT NULL, 144 PRIMARY KEY (`a`), 145 UNIQUE KEY `c` (`c`), 146 KEY `d2` (`d`), 147 KEY `b` (`b`), 148 KEY `d` (`d`) 149) ENGINE=InnoDB DEFAULT CHARSET=latin1 150insert into t1 values(6,1,'ggg','ggg'); 151select * from t1; 152a b c d 1532 3 ak ak 1543 4 ad ad 1554 4 tr tr 1565 5 oo oo 1576 1 ggg ggg 158select * from t1 force index(b) order by b; 159a b c d 1606 1 ggg ggg 1612 3 ak ak 1623 4 ad ad 1634 4 tr tr 1645 5 oo oo 165select * from t1 force index(c) order by c; 166a b c d 1673 4 ad ad 1682 3 ak ak 1696 1 ggg ggg 1705 5 oo oo 1714 4 tr tr 172select * from t1 force index(d) order by d; 173a b c d 1743 4 ad ad 1752 3 ak ak 1766 1 ggg ggg 1775 5 oo oo 1784 4 tr tr 179analyze table t1; 180explain select * from t1 force index(b) order by b; 181id select_type table type possible_keys key key_len ref rows Extra 1821 SIMPLE t1 index NULL b 5 NULL 5 183explain select * from t1 force index(c) order by c; 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t1 index NULL c 10 NULL 5 186explain select * from t1 force index(d) order by d; 187id select_type table type possible_keys key key_len ref rows Extra 1881 SIMPLE t1 index NULL d 23 NULL 5 189show create table t1; 190Table Create Table 191t1 CREATE TABLE `t1` ( 192 `a` int(11) NOT NULL, 193 `b` int(11) DEFAULT NULL, 194 `c` char(10) NOT NULL, 195 `d` varchar(20) DEFAULT NULL, 196 PRIMARY KEY (`a`), 197 UNIQUE KEY `c` (`c`), 198 KEY `d2` (`d`), 199 KEY `b` (`b`), 200 KEY `d` (`d`) 201) ENGINE=InnoDB DEFAULT CHARSET=latin1 202drop table t1; 203create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; 204insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe'); 205commit; 206alter table t1 add index (c(2)); 207affected rows: 0 208info: Records: 0 Duplicates: 0 Warnings: 0 209show create table t1; 210Table Create Table 211t1 CREATE TABLE `t1` ( 212 `a` int(11) NOT NULL, 213 `b` int(11) DEFAULT NULL, 214 `c` char(10) DEFAULT NULL, 215 `d` varchar(20) DEFAULT NULL, 216 PRIMARY KEY (`a`), 217 KEY `c` (`c`(2)) 218) ENGINE=InnoDB DEFAULT CHARSET=latin1 219affected rows: 1 220alter table t1 add unique index (d(10)); 221affected rows: 0 222info: Records: 0 Duplicates: 0 Warnings: 0 223show create table t1; 224Table Create Table 225t1 CREATE TABLE `t1` ( 226 `a` int(11) NOT NULL, 227 `b` int(11) DEFAULT NULL, 228 `c` char(10) DEFAULT NULL, 229 `d` varchar(20) DEFAULT NULL, 230 PRIMARY KEY (`a`), 231 UNIQUE KEY `d` (`d`(10)), 232 KEY `c` (`c`(2)) 233) ENGINE=InnoDB DEFAULT CHARSET=latin1 234affected rows: 1 235insert into t1 values(5,1,'ggg','ggg'); 236analyze table t1; 237select * from t1; 238a b c d 2391 1 ab ab 2402 2 ac ac 2413 3 ad ad 2424 4 afe afe 2435 1 ggg ggg 244select * from t1 force index(c) order by c; 245a b c d 2461 1 ab ab 2472 2 ac ac 2483 3 ad ad 2494 4 afe afe 2505 1 ggg ggg 251select * from t1 force index(d) order by d; 252a b c d 2531 1 ab ab 2542 2 ac ac 2553 3 ad ad 2564 4 afe afe 2575 1 ggg ggg 258explain select * from t1 order by b; 259id select_type table type possible_keys key key_len ref rows Extra 2601 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort 261explain select * from t1 force index(c) order by c; 262id select_type table type possible_keys key key_len ref rows Extra 2631 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort 264explain select * from t1 force index(d) order by d; 265id select_type table type possible_keys key key_len ref rows Extra 2661 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort 267show create table t1; 268Table Create Table 269t1 CREATE TABLE `t1` ( 270 `a` int(11) NOT NULL, 271 `b` int(11) DEFAULT NULL, 272 `c` char(10) DEFAULT NULL, 273 `d` varchar(20) DEFAULT NULL, 274 PRIMARY KEY (`a`), 275 UNIQUE KEY `d` (`d`(10)), 276 KEY `c` (`c`(2)) 277) ENGINE=InnoDB DEFAULT CHARSET=latin1 278alter table t1 drop index d; 279affected rows: 0 280info: Records: 0 Duplicates: 0 Warnings: 0 281insert into t1 values(8,9,'fff','fff'); 282select * from t1; 283a b c d 2841 1 ab ab 2852 2 ac ac 2863 3 ad ad 2874 4 afe afe 2885 1 ggg ggg 2898 9 fff fff 290select * from t1 force index(c) order by c; 291a b c d 2921 1 ab ab 2932 2 ac ac 2943 3 ad ad 2954 4 afe afe 2968 9 fff fff 2975 1 ggg ggg 298analyze table t1; 299explain select * from t1 order by b; 300id select_type table type possible_keys key key_len ref rows Extra 3011 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 302explain select * from t1 force index(c) order by c; 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 305explain select * from t1 order by d; 306id select_type table type possible_keys key key_len ref rows Extra 3071 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 308show create table t1; 309Table Create Table 310t1 CREATE TABLE `t1` ( 311 `a` int(11) NOT NULL, 312 `b` int(11) DEFAULT NULL, 313 `c` char(10) DEFAULT NULL, 314 `d` varchar(20) DEFAULT NULL, 315 PRIMARY KEY (`a`), 316 KEY `c` (`c`(2)) 317) ENGINE=InnoDB DEFAULT CHARSET=latin1 318drop table t1; 319create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; 320insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); 321commit; 322alter table t1 add unique index (b,c); 323affected rows: 0 324info: Records: 0 Duplicates: 0 Warnings: 0 325insert into t1 values(8,9,'fff','fff'); 326select * from t1; 327a b c d 3281 1 ab ab 3292 2 ac ac 3303 2 ad ad 3314 4 afe afe 3328 9 fff fff 333select * from t1 force index(b) order by b; 334a b c d 3351 1 ab ab 3362 2 ac ac 3373 2 ad ad 3384 4 afe afe 3398 9 fff fff 340analyze table t1; 341explain select * from t1 force index(b) order by b; 342id select_type table type possible_keys key key_len ref rows Extra 3431 SIMPLE t1 index NULL b 16 NULL 5 344show create table t1; 345Table Create Table 346t1 CREATE TABLE `t1` ( 347 `a` int(11) NOT NULL, 348 `b` int(11) DEFAULT NULL, 349 `c` char(10) DEFAULT NULL, 350 `d` varchar(20) DEFAULT NULL, 351 PRIMARY KEY (`a`), 352 UNIQUE KEY `b` (`b`,`c`) 353) ENGINE=InnoDB DEFAULT CHARSET=latin1 354alter table t1 add index (b,c); 355affected rows: 0 356info: Records: 0 Duplicates: 0 Warnings: 0 357insert into t1 values(11,11,'kkk','kkk'); 358select * from t1; 359a b c d 3601 1 ab ab 3612 2 ac ac 3623 2 ad ad 3634 4 afe afe 3648 9 fff fff 36511 11 kkk kkk 366select * from t1 force index(b) order by b; 367a b c d 3681 1 ab ab 3692 2 ac ac 3703 2 ad ad 3714 4 afe afe 3728 9 fff fff 37311 11 kkk kkk 374analyze table t1; 375explain select * from t1 force index(b) order by b; 376id select_type table type possible_keys key key_len ref rows Extra 3771 SIMPLE t1 index NULL b 16 NULL 6 378show create table t1; 379Table Create Table 380t1 CREATE TABLE `t1` ( 381 `a` int(11) NOT NULL, 382 `b` int(11) DEFAULT NULL, 383 `c` char(10) DEFAULT NULL, 384 `d` varchar(20) DEFAULT NULL, 385 PRIMARY KEY (`a`), 386 UNIQUE KEY `b` (`b`,`c`), 387 KEY `b_2` (`b`,`c`) 388) ENGINE=InnoDB DEFAULT CHARSET=latin1 389alter table t1 add unique index (c,d); 390affected rows: 0 391info: Records: 0 Duplicates: 0 Warnings: 0 392insert into t1 values(13,13,'yyy','aaa'); 393select * from t1; 394a b c d 3951 1 ab ab 3962 2 ac ac 3973 2 ad ad 3984 4 afe afe 3998 9 fff fff 40011 11 kkk kkk 40113 13 yyy aaa 402select * from t1 force index(b) order by b; 403a b c d 4041 1 ab ab 4052 2 ac ac 4063 2 ad ad 4074 4 afe afe 4088 9 fff fff 40911 11 kkk kkk 41013 13 yyy aaa 411select * from t1 force index(c) order by c; 412a b c d 4131 1 ab ab 4142 2 ac ac 4153 2 ad ad 4164 4 afe afe 4178 9 fff fff 41811 11 kkk kkk 41913 13 yyy aaa 420analyze table t1; 421explain select * from t1 force index(b) order by b; 422id select_type table type possible_keys key key_len ref rows Extra 4231 SIMPLE t1 index NULL b 16 NULL 7 424explain select * from t1 force index(c) order by c; 425id select_type table type possible_keys key key_len ref rows Extra 4261 SIMPLE t1 index NULL c 34 NULL 7 427show create table t1; 428Table Create Table 429t1 CREATE TABLE `t1` ( 430 `a` int(11) NOT NULL, 431 `b` int(11) DEFAULT NULL, 432 `c` char(10) DEFAULT NULL, 433 `d` varchar(20) DEFAULT NULL, 434 PRIMARY KEY (`a`), 435 UNIQUE KEY `b` (`b`,`c`), 436 UNIQUE KEY `c` (`c`,`d`), 437 KEY `b_2` (`b`,`c`) 438) ENGINE=InnoDB DEFAULT CHARSET=latin1 439drop table t1; 440create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb; 441create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb; 442create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb; 443create table t2(a int not null, b int, c int, d int, e int, 444foreign key (b) references t1(b) on delete set null, 445foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null) 446engine = innodb; 447alter table t1 drop index b; 448ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint 449alter table t3 drop index c; 450ERROR HY000: Cannot drop index 'c': needed in a foreign key constraint 451alter table t4 drop index d; 452ERROR HY000: Cannot drop index 'd': needed in a foreign key constraint 453alter table t2 drop index b; 454ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint 455alter table t2 drop index b, drop index c, drop index d; 456ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint 457alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY; 458ERROR HY000: Cannot change column 'b': used in a foreign key constraint 't2_ibfk_1' 459set @old_sql_mode = @@sql_mode; 460set @@sql_mode = 'STRICT_TRANS_TABLES'; 461alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE; 462ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL 463set @@sql_mode = @old_sql_mode; 464SET FOREIGN_KEY_CHECKS=0; 465alter table t2 DROP COLUMN b, ALGORITHM=COPY; 466ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' 467alter table t2 DROP COLUMN b; 468ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'test/t2_ibfk_1' 469alter table t1 DROP COLUMN b, ALGORITHM=COPY; 470ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' of table `test`.`t2` 471alter table t1 DROP COLUMN b; 472ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'test/t2_ibfk_1' of table `test`.`t2` 473SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 474create unique index dc on t2 (d,c); 475affected rows: 0 476info: Records: 0 Duplicates: 0 Warnings: 0 477create index dc on t1 (b,c); 478affected rows: 0 479info: Records: 0 Duplicates: 0 Warnings: 0 480set @@sql_mode = 'STRICT_TRANS_TABLES'; 481alter table t2 add primary key (alpha), change a alpha int, 482change b beta int not null, change c charlie int not null; 483ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL 484alter table t2 add primary key (alpha), change a alpha int, 485change c charlie int not null, change d delta int not null; 486ERROR HY000: Column 'd' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_3' SET NULL 487alter table t2 add primary key (alpha), change a alpha int, 488change b beta int, modify c int not null; 489affected rows: 0 490info: Records: 0 Duplicates: 0 Warnings: 0 491set @@sql_mode = @old_sql_mode; 492insert into t1 values (1,1,1); 493insert into t3 values (1,1,1); 494insert into t4 values (1,1,1); 495insert into t2 values (1,1,1,1,1); 496commit; 497alter table t4 add constraint dc foreign key (a) references t1(a); 498affected rows: 1 499info: Records: 1 Duplicates: 0 Warnings: 0 500show create table t4; 501Table Create Table 502t4 CREATE TABLE `t4` ( 503 `a` int(11) NOT NULL, 504 `d` int(11) NOT NULL, 505 `e` int(11) DEFAULT NULL, 506 PRIMARY KEY (`a`), 507 KEY `d` (`d`), 508 CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) 509) ENGINE=InnoDB DEFAULT CHARSET=latin1 510alter table t3 add constraint dc foreign key (a) references t1(a); 511ERROR HY000: Can't create table `test`.`t3` (errno: 121 "Duplicate key on write or update") 512SET FOREIGN_KEY_CHECKS=0; 513alter table t3 add constraint dc foreign key (a) references t1(a); 514ERROR HY000: Failed to add the foreign key constraint 'test/dc' to system tables 515SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 516show create table t3; 517Table Create Table 518t3 CREATE TABLE `t3` ( 519 `a` int(11) NOT NULL, 520 `c` int(11) NOT NULL, 521 `d` int(11) DEFAULT NULL, 522 PRIMARY KEY (`a`), 523 KEY `c` (`c`) 524) ENGINE=InnoDB DEFAULT CHARSET=latin1 525alter table t2 drop index b, add index (beta); 526affected rows: 0 527info: Records: 0 Duplicates: 0 Warnings: 0 528show create table t2; 529Table Create Table 530t2 CREATE TABLE `t2` ( 531 `alpha` int(11) NOT NULL, 532 `beta` int(11) DEFAULT NULL, 533 `c` int(11) NOT NULL, 534 `d` int(11) DEFAULT NULL, 535 `e` int(11) DEFAULT NULL, 536 PRIMARY KEY (`alpha`), 537 UNIQUE KEY `dc` (`d`,`c`), 538 KEY `c` (`c`), 539 KEY `beta` (`beta`), 540 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`beta`) REFERENCES `t1` (`b`) ON DELETE SET NULL, 541 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`), 542 CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) ON UPDATE SET NULL 543) ENGINE=InnoDB DEFAULT CHARSET=latin1 544delete from t1; 545ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) 546drop index dc on t4; 547ERROR 42000: Can't DROP INDEX `dc`; check that it exists 548alter table t3 drop foreign key dc; 549ERROR 42000: Can't DROP FOREIGN KEY `dc`; check that it exists 550alter table t4 drop foreign key dc; 551affected rows: 0 552info: Records: 0 Duplicates: 0 Warnings: 0 553select * from t2; 554alpha beta c d e 5551 1 1 1 1 556delete from t1; 557select * from t2; 558alpha beta c d e 5591 NULL 1 1 1 560drop table t2,t4,t3,t1; 561create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8; 562insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); 563commit; 564alter table t1 add unique index (b); 565ERROR 23000: Duplicate entry '2' for key 'b' 566insert into t1 values(8,9,'fff','fff'); 567select * from t1; 568a b c d 5691 1 ab ab 5702 2 ac ac 5713 2 ad ad 5724 4 afe afe 5738 9 fff fff 574show create table t1; 575Table Create Table 576t1 CREATE TABLE `t1` ( 577 `a` int(11) NOT NULL, 578 `b` int(11) DEFAULT NULL, 579 `c` char(10) DEFAULT NULL, 580 `d` varchar(20) DEFAULT NULL, 581 PRIMARY KEY (`a`) 582) ENGINE=InnoDB DEFAULT CHARSET=utf8 583alter table t1 add index (b); 584insert into t1 values(10,10,'kkk','iii'); 585select * from t1; 586a b c d 5871 1 ab ab 5882 2 ac ac 5893 2 ad ad 5904 4 afe afe 5918 9 fff fff 59210 10 kkk iii 593select * from t1 force index(b) order by b; 594a b c d 5951 1 ab ab 5962 2 ac ac 5973 2 ad ad 5984 4 afe afe 5998 9 fff fff 60010 10 kkk iii 601explain select * from t1 force index(b) order by b; 602id select_type table type possible_keys key key_len ref rows Extra 6031 SIMPLE t1 index NULL b 5 NULL 6 604show create table t1; 605Table Create Table 606t1 CREATE TABLE `t1` ( 607 `a` int(11) NOT NULL, 608 `b` int(11) DEFAULT NULL, 609 `c` char(10) DEFAULT NULL, 610 `d` varchar(20) DEFAULT NULL, 611 PRIMARY KEY (`a`), 612 KEY `b` (`b`) 613) ENGINE=InnoDB DEFAULT CHARSET=utf8 614alter table t1 add unique index (c), add index (d); 615insert into t1 values(11,11,'aaa','mmm'); 616select * from t1; 617a b c d 6181 1 ab ab 6192 2 ac ac 6203 2 ad ad 6214 4 afe afe 6228 9 fff fff 62310 10 kkk iii 62411 11 aaa mmm 625select * from t1 force index(b) order by b; 626a b c d 6271 1 ab ab 6282 2 ac ac 6293 2 ad ad 6304 4 afe afe 6318 9 fff fff 63210 10 kkk iii 63311 11 aaa mmm 634select * from t1 force index(c) order by c; 635a b c d 63611 11 aaa mmm 6371 1 ab ab 6382 2 ac ac 6393 2 ad ad 6404 4 afe afe 6418 9 fff fff 64210 10 kkk iii 643select * from t1 force index(d) order by d; 644a b c d 6451 1 ab ab 6462 2 ac ac 6473 2 ad ad 6484 4 afe afe 6498 9 fff fff 65010 10 kkk iii 65111 11 aaa mmm 652explain select * from t1 force index(b) order by b; 653id select_type table type possible_keys key key_len ref rows Extra 6541 SIMPLE t1 index NULL b 5 NULL 7 655explain select * from t1 force index(c) order by c; 656id select_type table type possible_keys key key_len ref rows Extra 6571 SIMPLE t1 index NULL c 31 NULL 7 658explain select * from t1 force index(d) order by d; 659id select_type table type possible_keys key key_len ref rows Extra 6601 SIMPLE t1 index NULL d 63 NULL 7 661show create table t1; 662Table Create Table 663t1 CREATE TABLE `t1` ( 664 `a` int(11) NOT NULL, 665 `b` int(11) DEFAULT NULL, 666 `c` char(10) DEFAULT NULL, 667 `d` varchar(20) DEFAULT NULL, 668 PRIMARY KEY (`a`), 669 UNIQUE KEY `c` (`c`), 670 KEY `b` (`b`), 671 KEY `d` (`d`) 672) ENGINE=InnoDB DEFAULT CHARSET=utf8 673check table t1; 674Table Op Msg_type Msg_text 675test.t1 check status OK 676drop table t1; 677create table t1(a int not null, b int) engine = innodb; 678insert into t1 values (1,1),(1,1),(1,1),(1,1); 679alter table t1 add unique index (a); 680ERROR 23000: Duplicate entry '1' for key 'a' 681alter table t1 add unique index (b); 682ERROR 23000: Duplicate entry '1' for key 'b' 683alter table t1 add unique index (a), add unique index(b); 684ERROR 23000: Duplicate entry '1' for key 'a' 685show create table t1; 686Table Create Table 687t1 CREATE TABLE `t1` ( 688 `a` int(11) NOT NULL, 689 `b` int(11) DEFAULT NULL 690) ENGINE=InnoDB DEFAULT CHARSET=latin1 691drop table t1; 692create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb; 693alter table t1 drop index c, drop index b; 694show create table t1; 695Table Create Table 696t1 CREATE TABLE `t1` ( 697 `a` int(11) NOT NULL, 698 `c` int(11) NOT NULL, 699 `b` int(11) DEFAULT NULL, 700 PRIMARY KEY (`a`) 701) ENGINE=InnoDB DEFAULT CHARSET=latin1 702drop table t1; 703create table t1(a int not null, b int, primary key(a)) engine = innodb; 704alter table t1 add index (b); 705show create table t1; 706Table Create Table 707t1 CREATE TABLE `t1` ( 708 `a` int(11) NOT NULL, 709 `b` int(11) DEFAULT NULL, 710 PRIMARY KEY (`a`), 711 KEY `b` (`b`) 712) ENGINE=InnoDB DEFAULT CHARSET=latin1 713drop table t1; 714create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; 715insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe'); 716alter table t1 add unique index (b), add unique index (c), add unique index (d); 717ERROR 23000: Duplicate entry '4' for key 'b' 718alter table t1 add unique index (c), add unique index (b), add index (d); 719ERROR 23000: Duplicate entry 'ac' for key 'c' 720show create table t1; 721Table Create Table 722t1 CREATE TABLE `t1` ( 723 `a` int(11) NOT NULL, 724 `b` int(11) DEFAULT NULL, 725 `c` char(10) DEFAULT NULL, 726 `d` varchar(20) DEFAULT NULL, 727 PRIMARY KEY (`a`) 728) ENGINE=InnoDB DEFAULT CHARSET=latin1 729drop table t1; 730create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb; 731insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1); 732alter table t1 add unique index (b); 733affected rows: 0 734info: Records: 0 Duplicates: 0 Warnings: 0 735insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17); 736show create table t1; 737Table Create Table 738t1 CREATE TABLE `t1` ( 739 `a` int(11) NOT NULL, 740 `b` int(11) NOT NULL, 741 `c` int(11) DEFAULT NULL, 742 PRIMARY KEY (`a`), 743 UNIQUE KEY `b` (`b`), 744 KEY `c` (`c`) 745) ENGINE=InnoDB DEFAULT CHARSET=latin1 746check table t1; 747Table Op Msg_type Msg_text 748test.t1 check status OK 749analyze table t1; 750explain select * from t1 force index(c) order by c; 751id select_type table type possible_keys key key_len ref rows Extra 7521 SIMPLE t1 index NULL c 5 NULL 9 753explain select * from t1 order by a; 754id select_type table type possible_keys key key_len ref rows Extra 7551 SIMPLE t1 index NULL PRIMARY 4 NULL 9 756explain select * from t1 force index(b) order by b; 757id select_type table type possible_keys key key_len ref rows Extra 7581 SIMPLE t1 index NULL b 4 NULL 9 759select * from t1 order by a; 760a b c 7611 5 1 7622 4 2 7633 3 3 7644 2 4 7655 1 5 76610 20 20 76711 19 19 76812 18 18 76913 17 17 770select * from t1 force index(b) order by b; 771a b c 7725 1 5 7734 2 4 7743 3 3 7752 4 2 7761 5 1 77713 17 17 77812 18 18 77911 19 19 78010 20 20 781select * from t1 force index(c) order by c; 782a b c 7831 5 1 7842 4 2 7853 3 3 7864 2 4 7875 1 5 78813 17 17 78912 18 18 79011 19 19 79110 20 20 792drop table t1; 793create table t1(a int not null, b int not null) engine=innodb; 794insert into t1 values (1,1); 795alter table t1 add primary key(b); 796affected rows: 0 797info: Records: 0 Duplicates: 0 Warnings: 0 798insert into t1 values (2,2); 799show create table t1; 800Table Create Table 801t1 CREATE TABLE `t1` ( 802 `a` int(11) NOT NULL, 803 `b` int(11) NOT NULL, 804 PRIMARY KEY (`b`) 805) ENGINE=InnoDB DEFAULT CHARSET=latin1 806check table t1; 807Table Op Msg_type Msg_text 808test.t1 check status OK 809select * from t1; 810a b 8111 1 8122 2 813analyze table t1; 814Table Op Msg_type Msg_text 815test.t1 analyze status Engine-independent statistics collected 816test.t1 analyze status OK 817explain select * from t1; 818id select_type table type possible_keys key key_len ref rows Extra 8191 SIMPLE t1 ALL NULL NULL NULL NULL 2 820explain select * from t1 order by a; 821id select_type table type possible_keys key key_len ref rows Extra 8221 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort 823explain select * from t1 order by b; 824id select_type table type possible_keys key key_len ref rows Extra 8251 SIMPLE t1 index NULL PRIMARY 4 NULL 2 826checksum table t1; 827Table Checksum 828test.t1 582702641 829drop table t1; 830create table t1(a int not null) engine=innodb; 831insert into t1 values (1); 832alter table t1 add primary key(a); 833affected rows: 0 834info: Records: 0 Duplicates: 0 Warnings: 0 835insert into t1 values (2); 836show create table t1; 837Table Create Table 838t1 CREATE TABLE `t1` ( 839 `a` int(11) NOT NULL, 840 PRIMARY KEY (`a`) 841) ENGINE=InnoDB DEFAULT CHARSET=latin1 842check table t1; 843Table Op Msg_type Msg_text 844test.t1 check status OK 845commit; 846select * from t1; 847a 8481 8492 850analyze table t1; 851Table Op Msg_type Msg_text 852test.t1 analyze status Engine-independent statistics collected 853test.t1 analyze status OK 854explain select * from t1; 855id select_type table type possible_keys key key_len ref rows Extra 8561 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 857explain select * from t1 order by a; 858id select_type table type possible_keys key key_len ref rows Extra 8591 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 860drop table t1; 861SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; 862SET FOREIGN_KEY_CHECKS=0; 863CREATE TABLE t1( 864c1 BIGINT(12) NOT NULL, 865PRIMARY KEY (c1) 866) ENGINE=InnoDB DEFAULT CHARSET=latin1; 867CREATE TABLE t2( 868c1 BIGINT(16) NOT NULL, 869c2 BIGINT(12) NOT NULL, 870c3 BIGINT(12) NOT NULL, 871PRIMARY KEY (c1) 872) ENGINE=InnoDB DEFAULT CHARSET=latin1; 873ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 874FOREIGN KEY (c3) REFERENCES t1(c1); 875SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 876SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 877SHOW CREATE TABLE t2; 878Table Create Table 879t2 CREATE TABLE `t2` ( 880 `c1` bigint(16) NOT NULL, 881 `c2` bigint(12) NOT NULL, 882 `c3` bigint(12) NOT NULL, 883 PRIMARY KEY (`c1`), 884 KEY `fk_t2_ca` (`c3`), 885 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) 886) ENGINE=InnoDB DEFAULT CHARSET=latin1 887CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); 888SHOW CREATE TABLE t2; 889Table Create Table 890t2 CREATE TABLE `t2` ( 891 `c1` bigint(16) NOT NULL, 892 `c2` bigint(12) NOT NULL, 893 `c3` bigint(12) NOT NULL, 894 PRIMARY KEY (`c1`), 895 KEY `i_t2_c3_c2` (`c3`,`c2`), 896 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) 897) ENGINE=InnoDB DEFAULT CHARSET=latin1 898SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 899SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 900INSERT INTO t2 VALUES(0,0,0); 901ERROR 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`)) 902INSERT INTO t1 VALUES(0); 903INSERT INTO t2 VALUES(0,0,0); 904DROP TABLE t2; 905CREATE TABLE t2( 906c1 BIGINT(16) NOT NULL, 907c2 BIGINT(12) NOT NULL, 908c3 BIGINT(12) NOT NULL, 909PRIMARY KEY (c1,c2,c3) 910) ENGINE=InnoDB DEFAULT CHARSET=latin1; 911SET FOREIGN_KEY_CHECKS=0; 912ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 913FOREIGN KEY (c3) REFERENCES t1(c1); 914SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 915SHOW CREATE TABLE t2; 916Table Create Table 917t2 CREATE TABLE `t2` ( 918 `c1` bigint(16) NOT NULL, 919 `c2` bigint(12) NOT NULL, 920 `c3` bigint(12) NOT NULL, 921 PRIMARY KEY (`c1`,`c2`,`c3`), 922 KEY `fk_t2_ca` (`c3`), 923 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) 924) ENGINE=InnoDB DEFAULT CHARSET=latin1 925CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); 926SHOW CREATE TABLE t2; 927Table Create Table 928t2 CREATE TABLE `t2` ( 929 `c1` bigint(16) NOT NULL, 930 `c2` bigint(12) NOT NULL, 931 `c3` bigint(12) NOT NULL, 932 PRIMARY KEY (`c1`,`c2`,`c3`), 933 KEY `i_t2_c3_c2` (`c3`,`c2`), 934 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) 935) ENGINE=InnoDB DEFAULT CHARSET=latin1 936INSERT INTO t2 VALUES(0,0,1); 937ERROR 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`)) 938INSERT INTO t2 VALUES(0,0,0); 939DELETE FROM t1; 940ERROR 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`)) 941DELETE FROM t2; 942DROP TABLE t2; 943DROP TABLE t1; 944CREATE TABLE t1( 945c1 BIGINT(12) NOT NULL, 946c2 INT(4) NOT NULL, 947PRIMARY KEY (c2,c1) 948) ENGINE=InnoDB DEFAULT CHARSET=latin1; 949CREATE TABLE t2( 950c1 BIGINT(16) NOT NULL, 951c2 BIGINT(12) NOT NULL, 952c3 BIGINT(12) NOT NULL, 953PRIMARY KEY (c1) 954) ENGINE=InnoDB DEFAULT CHARSET=latin1; 955SET FOREIGN_KEY_CHECKS=0; 956ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 957FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY; 958ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 959ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 960FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1); 961ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' 962ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 963FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; 964ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 965ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 966FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); 967ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' 968ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 969FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE; 970ERROR HY000: Failed to add the foreign key constraint on table 't2'. Incorrect options in FOREIGN KEY constraint 'test/fk_t2_ca' 971ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 972FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY; 973ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 974ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL; 975affected rows: 0 976info: Records: 0 Duplicates: 0 Warnings: 0 977ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 978FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; 979ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 980ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 981FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); 982ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' 983ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca 984FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1); 985affected rows: 0 986info: Records: 0 Duplicates: 0 Warnings: 0 987SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 988affected rows: 0 989SHOW CREATE TABLE t1; 990Table Create Table 991t1 CREATE TABLE `t1` ( 992 `c1` bigint(12) NOT NULL, 993 `c2` bigint(12) NOT NULL, 994 PRIMARY KEY (`c2`,`c1`) 995) ENGINE=InnoDB DEFAULT CHARSET=latin1 996affected rows: 1 997SHOW CREATE TABLE t2; 998Table Create Table 999t2 CREATE TABLE `t2` ( 1000 `c1` bigint(16) NOT NULL, 1001 `c2` bigint(12) NOT NULL, 1002 `c3` bigint(12) NOT NULL, 1003 PRIMARY KEY (`c1`), 1004 KEY `fk_t2_ca` (`c3`,`c2`), 1005 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) 1006) ENGINE=InnoDB DEFAULT CHARSET=latin1 1007affected rows: 1 1008CREATE INDEX i_t2_c2_c1 ON t2(c2, c1); 1009affected rows: 0 1010info: Records: 0 Duplicates: 0 Warnings: 0 1011SHOW CREATE TABLE t2; 1012Table Create Table 1013t2 CREATE TABLE `t2` ( 1014 `c1` bigint(16) NOT NULL, 1015 `c2` bigint(12) NOT NULL, 1016 `c3` bigint(12) NOT NULL, 1017 PRIMARY KEY (`c1`), 1018 KEY `fk_t2_ca` (`c3`,`c2`), 1019 KEY `i_t2_c2_c1` (`c2`,`c1`), 1020 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) 1021) ENGINE=InnoDB DEFAULT CHARSET=latin1 1022affected rows: 1 1023CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2); 1024affected rows: 0 1025info: Records: 0 Duplicates: 0 Warnings: 0 1026SHOW CREATE TABLE t2; 1027Table Create Table 1028t2 CREATE TABLE `t2` ( 1029 `c1` bigint(16) NOT NULL, 1030 `c2` bigint(12) NOT NULL, 1031 `c3` bigint(12) NOT NULL, 1032 PRIMARY KEY (`c1`), 1033 KEY `fk_t2_ca` (`c3`,`c2`), 1034 KEY `i_t2_c2_c1` (`c2`,`c1`), 1035 KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`), 1036 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) 1037) ENGINE=InnoDB DEFAULT CHARSET=latin1 1038affected rows: 1 1039CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); 1040affected rows: 0 1041info: Records: 0 Duplicates: 0 Warnings: 0 1042SHOW CREATE TABLE t2; 1043Table Create Table 1044t2 CREATE TABLE `t2` ( 1045 `c1` bigint(16) NOT NULL, 1046 `c2` bigint(12) NOT NULL, 1047 `c3` bigint(12) NOT NULL, 1048 PRIMARY KEY (`c1`), 1049 KEY `i_t2_c2_c1` (`c2`,`c1`), 1050 KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`), 1051 KEY `i_t2_c3_c2` (`c3`,`c2`), 1052 CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) 1053) ENGINE=InnoDB DEFAULT CHARSET=latin1 1054affected rows: 1 1055DROP TABLE t2; 1056DROP TABLE t1; 1057connect a,localhost,root,,; 1058connect b,localhost,root,,; 1059connection a; 1060CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB; 1061INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e'); 1062CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB; 1063CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB; 1064CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB; 1065INSERT INTO t2 SELECT * FROM t1; 1066INSERT INTO t2i SELECT * FROM t1; 1067INSERT INTO t2c SELECT * FROM t1; 1068connection b; 1069BEGIN; 1070SELECT * FROM t1; 1071a b 10723 a 10733 b 10741 c 10750 d 10761 e 1077connection a; 1078SET lock_wait_timeout=1; 1079CREATE INDEX t1a ON t1(a); 1080ERROR HY000: Lock wait timeout exceeded; try restarting transaction 1081CREATE INDEX t2a ON t2(a); 1082affected rows: 0 1083info: Records: 0 Duplicates: 0 Warnings: 0 1084set @old_sql_mode = @@sql_mode; 1085set @@sql_mode = 'STRICT_TRANS_TABLES'; 1086ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE; 1087affected rows: 0 1088info: Records: 0 Duplicates: 0 Warnings: 0 1089set @@sql_mode = @old_sql_mode; 1090ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY; 1091affected rows: 5 1092info: Records: 5 Duplicates: 0 Warnings: 0 1093connection b; 1094SELECT * FROM t2i; 1095ERROR HY000: Table definition has changed, please retry transaction 1096SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; 1097ERROR HY000: Table definition has changed, please retry transaction 1098SELECT * FROM t2c; 1099ERROR HY000: Table definition has changed, please retry transaction 1100SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; 1101ERROR HY000: Table definition has changed, please retry transaction 1102SELECT * FROM t2; 1103a b 11043 a 11053 b 11061 c 11070 d 11081 e 1109SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; 1110ERROR HY000: Table definition has changed, please retry transaction 1111SELECT * FROM t2; 1112a b 11133 a 11143 b 11151 c 11160 d 11171 e 1118COMMIT; 1119SELECT * FROM t2; 1120a b 11213 a 11223 b 11231 c 11240 d 11251 e 1126SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; 1127a b 11280 d 11291 c 11301 e 11313 a 11323 b 1133SELECT * FROM t2i; 1134a b 11350 d 11361 c 11371 e 11383 a 11393 b 1140SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; 1141a b 11420 d 11431 c 11441 e 11453 a 11463 b 1147SELECT * FROM t2c; 1148a b 11490 d 11501 c 11511 e 11523 a 11533 b 1154SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; 1155a b 11560 d 11571 c 11581 e 11593 a 11603 b 1161connection default; 1162disconnect a; 1163disconnect b; 1164alter table t2 add index t2a(b); 1165ERROR 42000: Duplicate key name 't2a' 1166alter table t2 drop index t2a, add index t2a(b); 1167show create table t2; 1168Table Create Table 1169t2 CREATE TABLE `t2` ( 1170 `a` int(11) DEFAULT NULL, 1171 `b` char(1) DEFAULT NULL, 1172 KEY `t2a` (`b`) 1173) ENGINE=InnoDB DEFAULT CHARSET=latin1 1174show create table t2i; 1175Table Create Table 1176t2i CREATE TABLE `t2i` ( 1177 `a` int(11) NOT NULL, 1178 `b` char(1) NOT NULL, 1179 PRIMARY KEY (`a`,`b`), 1180 KEY `t2a` (`a`) 1181) ENGINE=InnoDB DEFAULT CHARSET=latin1 1182show create table t2c; 1183Table Create Table 1184t2c CREATE TABLE `t2c` ( 1185 `a` int(11) NOT NULL, 1186 `b` char(1) NOT NULL, 1187 PRIMARY KEY (`a`,`b`), 1188 KEY `t2a` (`a`) 1189) ENGINE=InnoDB DEFAULT CHARSET=latin1 1190DROP TABLE t1,t2,t2c,t2i; 1191CREATE TABLE t1 (c VARCHAR(1024), 1192c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL, 1193c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL, 1194c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL, 1195ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL, 1196cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL, 1197d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL, 1198d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL, 1199d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL, 1200d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL, 1201dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL, 1202UNIQUE KEY(c)) 1203ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 1204INSERT INTO t1 VALUES 1205(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''), 1206(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''); 1207ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace; 1208ERROR HY000: Index column size too large. The maximum column size is 767 bytes 1209ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy; 1210ERROR HY000: Index column size too large. The maximum column size is 767 bytes 1211SELECT COUNT(*) FROM t1; 1212COUNT(*) 12132 1214CHECK TABLE t1; 1215Table Op Msg_type Msg_text 1216test.t1 check status OK 1217DROP TABLE t1; 1218# 1219# Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH 1220# IN I_S TABLES 1221# 1222CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1; 1223SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, 1224cast(INDEX_LENGTH/@@innodb_page_size as int) I 1225FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; 1226D I 12271 0 1228ALTER TABLE t1 ADD INDEX (a); 1229affected rows: 0 1230info: Records: 0 Duplicates: 0 Warnings: 0 1231SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, 1232cast(INDEX_LENGTH/@@innodb_page_size as int) I 1233FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; 1234D I 12351 1 1236ALTER TABLE t1 ADD INDEX (b); 1237affected rows: 0 1238info: Records: 0 Duplicates: 0 Warnings: 0 1239SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, 1240cast(INDEX_LENGTH/@@innodb_page_size as int) I 1241FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; 1242D I 12431 2 1244DROP TABLE t1; 1245# 1246# Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE 1247# 1248SET GLOBAL innodb_monitor_enable = module_ddl; 1249create table t1(f1 int not null, f2 blob)engine=innodb; 1250insert into t1 values(1, repeat('a',20000)); 1251# Skip sort 1252# Reusing the same pk 1253alter table t1 force; 1254affected rows: 0 1255info: Records: 0 Duplicates: 0 Warnings: 0 1256SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1257subsystem = 'ddl' and count_reset > 0; 1258name count_reset 1259SET GLOBAL innodb_monitor_reset = module_ddl; 1260drop table t1; 1261create table t1(f1 int not null, f2 int not null, 1262primary key(f1))engine=innodb; 1263insert into t1 values(1,2), (3,4); 1264# Add Secondary index. 1265# Skip temp file usage due to small table size 1266alter table t1 add key(f2); 1267affected rows: 0 1268info: Records: 0 Duplicates: 0 Warnings: 0 1269SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1270subsystem = 'ddl' and count_reset > 0; 1271name count_reset 1272SET GLOBAL innodb_monitor_reset = module_ddl; 1273drop table t1; 1274create table t480(a serial)engine=innodb; 1275insert into t480 1276values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), 1277(),(),(),(),(),(),(),(); 1278insert into t480 select 0 from t480; 1279insert into t480 select 0 from t480; 1280insert into t480 select 0 from t480; 1281insert into t480 select 0 from t480; 1282create table t1(f1 int auto_increment not null, 1283f2 char(200) not null, f3 char(200) not null, 1284f4 char(200) not null,primary key(f1))engine=innodb; 1285insert into t1 select NULL,'aaa','bbb','ccc' from t480; 1286insert into t1 select NULL,'aaaa','bbbb','cccc' from t480; 1287insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480; 1288insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480; 1289insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480; 1290insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480; 1291select count(*) from t1; 1292count(*) 12932880 1294# Skip sort 1295# Change PK from (f1) to (f1,f2,f3,f4) 1296alter table t1 drop primary key, add primary key(f1,f2,f3,f4); 1297affected rows: 0 1298info: Records: 0 Duplicates: 0 Warnings: 0 1299SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1300subsystem = 'ddl' and count_reset > 0; 1301name count_reset 1302SET GLOBAL innodb_monitor_reset = module_ddl; 1303# Skip sort 1304# Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns) 1305alter table t1 drop primary key,add column f5 int not null, 1306add column f6 int not null,add primary key(f1,f2,f5,f6); 1307affected rows: 0 1308info: Records: 0 Duplicates: 0 Warnings: 0 1309SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1310subsystem = 'ddl' and count_reset > 0; 1311name count_reset 1312SET GLOBAL innodb_monitor_reset = module_ddl; 1313# Skip sort 1314# Change PK from (f1,f2,f5,f6) to (f1,f2,f5) 1315alter table t1 drop column f6; 1316ERROR 42000: Key column 'f6' doesn't exist in table 1317alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5); 1318affected rows: 0 1319info: Records: 0 Duplicates: 0 Warnings: 0 1320SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1321subsystem = 'ddl' and count_reset > 0; 1322name count_reset 1323SET GLOBAL innodb_monitor_reset = module_ddl; 1324# Skip sort 1325# Reusing the same PK 1326alter table t1 add column f6 int; 1327affected rows: 0 1328info: Records: 0 Duplicates: 0 Warnings: 0 1329SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1330subsystem = 'ddl' and count_reset > 0; 1331name count_reset 1332SET GLOBAL innodb_monitor_reset = module_ddl; 1333# Skip sort 1334# Reusing the same pk 1335alter table t1 drop column f6; 1336affected rows: 0 1337info: Records: 0 Duplicates: 0 Warnings: 0 1338SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1339subsystem = 'ddl' and count_reset > 0; 1340name count_reset 1341SET GLOBAL innodb_monitor_reset = module_ddl; 1342# Must sort 1343# Change PK from (f1,f2,f5) to (f1,f5) 1344alter table t1 drop column f2; 1345ERROR 42000: Key column 'f2' doesn't exist in table 1346alter table t1 drop column f2, drop primary key, add primary key(f1,f5); 1347affected rows: 0 1348info: Records: 0 Duplicates: 0 Warnings: 0 1349SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1350subsystem = 'ddl' and count_reset > 0; 1351name count_reset 1352ddl_sort_file_alter_table 2 1353SET GLOBAL innodb_monitor_reset = module_ddl; 1354# Skip sort 1355# Reusing the same pk 1356alter table t1 add column f2n int after f1, drop primary key, add 1357primary key (f1,f5,f2n); 1358affected rows: 0 1359info: Records: 0 Duplicates: 0 Warnings: 0 1360SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1361subsystem = 'ddl' and count_reset > 0; 1362name count_reset 1363SET GLOBAL innodb_monitor_reset = module_ddl; 1364# Skip sort 1365# Reusing the same pk 1366alter table t1 change f5 f2n int not null,change f2n f5 int not null, 1367add column f8 int not null; 1368affected rows: 0 1369info: Records: 0 Duplicates: 0 Warnings: 0 1370SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1371subsystem = 'ddl' and count_reset > 0; 1372name count_reset 1373SET GLOBAL innodb_monitor_reset = module_ddl; 1374# Skip sort 1375# Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n) 1376alter table t1 add column f7 int, drop primary key, 1377add primary key (f1,f5,f7,f2n); 1378affected rows: 0 1379info: Records: 0 Duplicates: 0 Warnings: 0 1380SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1381subsystem = 'ddl' and count_reset > 0; 1382name count_reset 1383ddl_sort_file_alter_table 2 1384SET GLOBAL innodb_monitor_reset = module_ddl; 1385# Skip sort 1386# Reusing the same pk 1387alter table t1 force; 1388affected rows: 0 1389info: Records: 0 Duplicates: 0 Warnings: 0 1390SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1391subsystem = 'ddl' and count_reset > 0; 1392name count_reset 1393SET GLOBAL innodb_monitor_reset = module_ddl; 1394# Skip sort 1395# Reusing the same pk 1396alter table t1 row_format=compact; 1397affected rows: 0 1398info: Records: 0 Duplicates: 0 Warnings: 0 1399SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1400subsystem = 'ddl' and count_reset > 0; 1401name count_reset 1402SET GLOBAL innodb_monitor_reset = module_ddl; 1403# Skip sort 1404# Reusing the same pk 1405alter table t1 engine=innodb; 1406affected rows: 0 1407info: Records: 0 Duplicates: 0 Warnings: 0 1408SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1409subsystem = 'ddl' and count_reset > 0; 1410name count_reset 1411SET GLOBAL innodb_monitor_reset = module_ddl; 1412# Skip sort 1413# Optimize table 1414optimize table t1; 1415Table Op Msg_type Msg_text 1416test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 1417test.t1 optimize status OK 1418affected rows: 2 1419SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1420subsystem = 'ddl' and count_reset > 0; 1421name count_reset 1422SET GLOBAL innodb_monitor_reset = module_ddl; 1423# Sort files used for adding secondary index 1424alter table t1 drop primary key, add primary key(f1,f5,f7), add index 1425i(f3); 1426affected rows: 0 1427info: Records: 0 Duplicates: 0 Warnings: 0 1428SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1429subsystem = 'ddl' and count_reset > 0; 1430name count_reset 1431SET GLOBAL innodb_monitor_reset = module_ddl; 1432# No sort files used for dropping secondary index 1433alter table t1 drop primary key, add primary key(f1,f5),drop index i; 1434affected rows: 0 1435info: Records: 0 Duplicates: 0 Warnings: 0 1436SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1437subsystem = 'ddl' and count_reset > 0; 1438name count_reset 1439SET GLOBAL innodb_monitor_reset = module_ddl; 1440# Skip sort 1441# Change PK(f1,f5) to (f1,added_columns) and drop f5 1442alter table t1 drop primary key, add primary key(f1,f12), 1443drop column f5, add column f12 int not null; 1444affected rows: 0 1445info: Records: 0 Duplicates: 0 Warnings: 0 1446SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1447subsystem = 'ddl' and count_reset > 0; 1448name count_reset 1449SET GLOBAL innodb_monitor_reset = module_ddl; 1450# Must sort 1451# Change PK(f1,f12) to (f1,existing_columns) 1452alter table t1 drop primary key, add primary key(f1,f3); 1453affected rows: 0 1454info: Records: 0 Duplicates: 0 Warnings: 0 1455SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1456subsystem = 'ddl' and count_reset > 0; 1457name count_reset 1458ddl_sort_file_alter_table 2 1459SET GLOBAL innodb_monitor_reset = module_ddl; 1460# Skip sort 1461# Change PK(f1,f3) to (f1,added_column,f3,added_column) 1462alter table t1 drop primary key, add column f3n int, 1463add column f4n int, add primary key(f1,f3n,f3,f4n); 1464affected rows: 0 1465info: Records: 0 Duplicates: 0 Warnings: 0 1466SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1467subsystem = 'ddl' and count_reset > 0; 1468name count_reset 1469SET GLOBAL innodb_monitor_reset = module_ddl; 1470# Adding Secondary index alone. 1471alter table t1 add key(f1); 1472affected rows: 0 1473info: Records: 0 Duplicates: 0 Warnings: 0 1474SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1475subsystem = 'ddl' and count_reset > 0; 1476name count_reset 1477SET GLOBAL innodb_monitor_reset = module_ddl; 1478# Must sort 1479# Change PK(f1,f3) to (existing_column,f1) 1480alter table t1 drop primary key, add primary key(f4,f1); 1481affected rows: 0 1482info: Records: 0 Duplicates: 0 Warnings: 0 1483SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1484subsystem = 'ddl' and count_reset > 0; 1485name count_reset 1486ddl_sort_file_alter_table 3 1487SET GLOBAL innodb_monitor_reset = module_ddl; 1488# Skip sort for PK. 1489# Change PK(f4,f1) to (added_columns,f4,f1) 1490# Secondary index rebuild happens 1491alter table t1 drop primary key, add column f5n int, 1492add column f6n int, add primary key(f5n,f6n,f4,f1); 1493affected rows: 0 1494info: Records: 0 Duplicates: 0 Warnings: 0 1495SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1496subsystem = 'ddl' and count_reset > 0; 1497name count_reset 1498SET GLOBAL innodb_monitor_reset = module_ddl; 1499# Skip sort for PK. 1500# Change PK(f5n,f6n,f4,f1) to 1501# (added_columns,f5n,added_column,f6n,f4,f1) 1502# Secondary index rebuild happens 1503alter table t1 drop primary key, add column f7n int, 1504add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1); 1505affected rows: 0 1506info: Records: 0 Duplicates: 0 Warnings: 0 1507SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 1508subsystem = 'ddl' and count_reset > 0; 1509name count_reset 1510SET GLOBAL innodb_monitor_reset = module_ddl; 1511SET GLOBAL innodb_monitor_disable = module_ddl; 1512select count(*) from t1; 1513count(*) 15142880 1515drop table t1; 1516SET GLOBAL innodb_monitor_reset = default; 1517SET GLOBAL innodb_monitor_enable = default; 1518SET GLOBAL innodb_monitor_disable = default; 1519# Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST 1520# WHEN SKIPPING SORT FOR CLUSTER INDEX 1521SELECT @@innodb_sort_buffer_size; 1522@@innodb_sort_buffer_size 15231048576 1524create table t1(f1 int auto_increment not null, 1525f2 char(200) not null, f3 char(200) not null, 1526f4 char(200) not null,primary key(f1,f2,f3,f4)); 1527insert into t1 select NULL,'aaa','bbb','ccc' from t480; 1528insert into t1 values(106, 'aaa','bbb','cccc'); 1529select count(*) from t1; 1530count(*) 1531481 1532# Skip sort 1533# Change PK from (f1,f2,f3,f4) to (f1,f2,f3) 1534alter table t1 drop primary key, add primary key(f1,f2,f3); 1535ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106-aaa-bbb' for key 'PRIMARY' 1536select count(*) from t1; 1537count(*) 1538481 1539drop table t1; 1540create table t1(f1 int auto_increment not null, 1541f2 char(200) not null, f3 char(200) not null, 1542f4 char(200) not null,primary key(f1,f2,f3,f4)); 1543insert into t1 select NULL,'aaa','bbb','ccc' from t480; 1544insert into t1 values(108,'aaa','bbb','cccc'); 1545select count(*) from t1; 1546count(*) 1547481 1548alter table t1 drop primary key, add primary key(f1,f2,f3); 1549ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '108-aaa-bbb' for key 'PRIMARY' 1550select count(*) from t1; 1551count(*) 1552481 1553drop table t1, t480; 1554# 1555# Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK 1556# FIELD IS CHANGED 1557# 1558create table t1(a int not null, b varchar(30) not null, 1559primary key (b(10), a)) engine = innodb; 1560insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L'); 1561select * from t1; 1562a b 15630 khdHps6UxW8Lwaoxa604oK6zkb 15641 khdHps6UxW8L 1565alter table t1 drop primary key, add primary key (b(18),a); 1566select * from t1; 1567a b 15681 khdHps6UxW8L 15690 khdHps6UxW8Lwaoxa604oK6zkb 1570drop table t1; 1571create table t1(a int not null, b varchar(30) not null, 1572primary key (b(10), a)) engine = innodb; 1573insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L'); 1574select * from t1; 1575a b 15761 khdHps6UtW8L 15770 khdHps6UxW8Lwaoxa604oK6zkb 1578alter table t1 drop primary key, add primary key (b(8),a); 1579select * from t1; 1580a b 15810 khdHps6UxW8Lwaoxa604oK6zkb 15821 khdHps6UtW8L 1583drop table t1; 1584# 1585# Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE 1586# COLUMN PRIMARY KEY 1587# 1588create table t1(f1 int not null, f2 int not null, 1589primary key (f1), unique key(f1, f2))engine=innodb; 1590insert into t1 values(1,3), (2,2); 1591alter table t1 drop column f1; 1592ERROR 42000: Key column 'f1' doesn't exist in table 1593alter table t1 drop column f1, drop primary key; 1594ERROR 42000: Key column 'f1' doesn't exist in table 1595alter table t1 drop column f1, drop key f1; 1596drop table t1; 1597create table t1(f1 int not null, f2 int not null, 1598primary key (f1), unique key(f1, f2))engine=innodb; 1599insert into t1 values(1,3), (2,2); 1600alter table t1 drop primary key, lock=none; 1601alter table t1 drop index f1, lock=none; 1602ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED 1603drop table t1; 1604# 1605# BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP 1606# LAST COLUMN OF OLD PK 1607# 1608create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1609insert into t1 values(1,1,2),(1,2,1); 1610alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none; 1611drop table t1; 1612create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1613insert into t1 values(1,1,2),(1,2,1); 1614alter table t1 drop o1, drop o2, add primary key(o3), lock=none; 1615drop table t1; 1616create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1617insert into t1 values(1,1,2),(1,2,1); 1618alter table t1 drop primary key, add primary key(o1,o3), lock=none; 1619drop table t1; 1620create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1621insert into t1 values(1,1,2),(1,2,1); 1622alter table t1 drop primary key, add primary key(o3), lock=none; 1623drop table t1; 1624create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1625insert into t1 values(1,1,2),(1,2,1); 1626alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none; 1627drop table t1; 1628create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1629insert into t1 values(1,1,2),(2,2,1); 1630alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none; 1631drop table t1; 1632create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1633insert into t1 values(1,2,2),(2,1,1); 1634alter table t1 drop primary key, add primary key(o2, o1), lock=none; 1635drop table t1; 1636create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1637insert into t1 values(1,2,2),(2,1,1); 1638alter table t1 drop primary key, add primary key(o2), lock=none; 1639drop table t1; 1640create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1641insert into t1 values(1,2,2),(2,1,1); 1642alter table t1 drop primary key, add primary key(o2,o3), lock=none; 1643drop table t1; 1644create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb; 1645insert into t1 values(1,1,2),(2,1,1); 1646alter table t1 drop primary key, add primary key(o2,o3), lock=none; 1647drop table t1; 1648create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1649insert into t1 values(1,1,2),(1,2,1); 1650alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; 1651drop table t1; 1652create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; 1653insert into t1 values(1,2,2),(2,1,1); 1654alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none; 1655drop table t1; 1656create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1657insert into t1 values(1,1,2),(1,2,1); 1658alter table t1 drop primary key, add primary key(o1,o3), lock=none; 1659drop table t1; 1660create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1661insert into t1 values(1,2,2),(2,1,1); 1662alter table t1 drop o1, lock=none; 1663ERROR 42000: Key column 'o1' doesn't exist in table 1664alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none; 1665drop table t1; 1666create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1667insert into t1 values(1,1,2),(1,2,1); 1668alter table t1 drop o2, lock=none; 1669ERROR 42000: Key column 'o2' doesn't exist in table 1670alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none; 1671drop table t1; 1672create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1673insert into t1 values(1,2,2),(2,1,1); 1674alter table t1 drop o1, drop o2, lock=none; 1675ERROR 42000: Key column 'o2' doesn't exist in table 1676alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none; 1677drop table t1; 1678create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; 1679insert into t1 values('abd', 1, 1), ('abc', 2, 2); 1680alter table t1 drop primary key, add primary key(o1(3), o2), lock=none; 1681drop table t1; 1682create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; 1683insert into t1 values('abd', 1, 1), ('abc', 2, 2); 1684alter table t1 drop primary key, add primary key(o1, o2), lock=none; 1685drop table t1; 1686create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; 1687insert into t1 values('abd', 1, 1), ('abc', 2, 2); 1688alter table t1 drop primary key, add primary key(o1(3), o3), lock=none; 1689drop table t1; 1690create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; 1691insert into t1 values('abd', 1, 1), ('abc', 2, 2); 1692alter table t1 drop primary key, add primary key(o1, o3), lock=none; 1693drop table t1; 1694create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb; 1695insert into t1 values('abc', 2, 1), ('abd', 1, 2); 1696alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; 1697drop table t1; 1698create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb; 1699insert into t1 values('abc', 2, 1), ('abd', 1, 2); 1700alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; 1701drop table t1; 1702create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb; 1703insert into t1 values('abc', 2, 2), ('abd', 1, 1); 1704alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; 1705drop table t1; 1706create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb; 1707insert into t1 values('abc', 2, 2), ('abd', 1, 1); 1708alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; 1709drop table t1; 1710create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb; 1711insert into t1 values(1, 'abd', 1), (1, 'abc', 2); 1712alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; 1713drop table t1; 1714create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb; 1715insert into t1 values(1, 'abd', 1), (1, 'abc', 2); 1716alter table t1 drop primary key, add primary key(o1,o2), lock=none; 1717drop table t1; 1718create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; 1719insert into t1 values('abc', 'acd'), ('abd', 'abd'); 1720alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none; 1721drop table t1; 1722create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; 1723insert into t1 values('abc', 'acd'), ('abd', 'abd'); 1724alter table t1 drop primary key, add primary key(o1(2),o2), lock=none; 1725drop table t1; 1726create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; 1727insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1728alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none; 1729drop table t1; 1730create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; 1731insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1732alter table t1 drop primary key, add primary key(o2,o1), lock=none; 1733drop table t1; 1734create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; 1735insert into t1 values('abd'), ('acd'); 1736alter table t1 drop primary key, add primary key(o1(3)), lock=none; 1737drop table t1; 1738create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; 1739insert into t1 values('abd'), ('acd'); 1740alter table t1 drop primary key, add primary key(o1), lock=none; 1741drop table t1; 1742create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; 1743insert into t1 values('abd'), ('acd'); 1744alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none; 1745drop table t1; 1746create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; 1747insert into t1 values('abd'), ('acd'); 1748alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none; 1749drop table t1; 1750create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; 1751insert into t1 values('abd', 1), ('acd', 2); 1752alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none; 1753drop table t1; 1754create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; 1755insert into t1 values('abd', 1), ('acd', 2); 1756alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none; 1757drop table t1; 1758create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb; 1759insert into t1 values('abd'), ('acd'); 1760alter table t1 drop primary key, add primary key(o1(2)), lock=none; 1761drop table t1; 1762create table t1(o1 varchar(10), primary key(o1)) engine = innodb; 1763insert into t1 values('abd'), ('acd'); 1764alter table t1 drop primary key, add primary key(o1(2)), lock=none; 1765drop table t1; 1766create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; 1767insert into t1 values('abd', 1), ('acd', 2); 1768alter table t1 drop primary key, add primary key(o1(2)), lock=none; 1769drop table t1; 1770create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; 1771insert into t1 values('abd', 1), ('acd', 2); 1772alter table t1 drop primary key, add primary key(o1(2)), lock=none; 1773drop table t1; 1774create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; 1775insert into t1 values('abd', 1), ('acd', 2); 1776alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; 1777drop table t1; 1778create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; 1779insert into t1 values('abd', 1), ('acd', 2); 1780alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; 1781drop table t1; 1782create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; 1783insert into t1 values('abd', 1), ('acd', 2); 1784alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none; 1785drop table t1; 1786create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; 1787insert into t1 values('abd', 1), ('acd', 2); 1788alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; 1789drop table t1; 1790create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb; 1791insert into t1 values(1,'abd'), (2,'acd'); 1792alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; 1793drop table t1; 1794create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb; 1795insert into t1 values(1,'abd'), (2,'acd'); 1796alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; 1797drop table t1; 1798create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; 1799insert into t1 values(1, 'abd'), (2, 'acd'); 1800alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; 1801drop table t1; 1802create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; 1803insert into t1 values(1, 'abd'), (2, 'acd'); 1804alter table t1 drop primary key, add primary key(o1,o2), lock=none; 1805drop table t1; 1806create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb; 1807insert into t1 values(1, 'abd', 1), (2, 'acd', 2); 1808alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; 1809drop table t1; 1810create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb; 1811insert into t1 values(1, 'abd', 1), (2, 'acd', 2); 1812alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; 1813drop table t1; 1814create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; 1815insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1816alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none; 1817drop table t1; 1818create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; 1819insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1820alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; 1821drop table t1; 1822create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb; 1823insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1824alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none; 1825drop table t1; 1826create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb; 1827insert into t1 values('abd', 'acd'), ('acd', 'abd'); 1828alter table t1 drop primary key, add primary key(o1,o2), lock=none; 1829drop table t1; 1830create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb; 1831insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); 1832alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none; 1833drop table t1; 1834create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb; 1835insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); 1836alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; 1837drop table t1; 1838create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb; 1839insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); 1840alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none; 1841drop table t1; 1842create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb; 1843insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); 1844alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none; 1845drop table t1; 1846create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1847insert into t1 values(1,1),(2,2); 1848alter table t1 drop primary key, add primary key(o1,o2), lock=none; 1849drop table t1; 1850create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1851insert into t1 values(1,1),(2,2); 1852alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; 1853drop table t1; 1854create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1855insert into t1 values(1,1),(2,2); 1856alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none; 1857drop table t1; 1858create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1859insert into t1 values(1,1),(2,2); 1860alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none; 1861drop table t1; 1862create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1863insert into t1 values(1,1),(2,2); 1864alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none; 1865drop table t1; 1866create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1867insert into t1 values(1,1),(2,2); 1868alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none; 1869drop table t1; 1870create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1871insert into t1 values(1,1),(2,2); 1872alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; 1873drop table t1; 1874create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1875insert into t1 values(1,1),(2,2); 1876alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; 1877drop table t1; 1878create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; 1879insert into t1 values(1,1),(2,2); 1880alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none; 1881drop table t1; 1882create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; 1883insert into t1 values(1,1,2),(2,2,1); 1884alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; 1885drop table t1; 1886create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; 1887insert into t1 values(1,1,2),(2,2,1); 1888alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; 1889drop table t1; 1890create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1891insert into t1 values(1,1,2),(2,2,1); 1892alter table t1 drop primary key, add primary key(o1,o2), lock=none; 1893drop table t1; 1894create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb; 1895insert into t1 values(1,1,2,2),(2,2,1,1); 1896alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none; 1897drop table t1; 1898create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1899insert into t1 values(1,1,2),(2,2,1); 1900alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; 1901drop table t1; 1902create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1903insert into t1 values(1,1,2),(2,2,1); 1904alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; 1905drop table t1; 1906create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; 1907insert into t1 values(1,1,2),(2,2,1); 1908alter table t1 drop primary key, add primary key(o1), lock=none; 1909drop table t1; 1910create table t1(o1 int, o2 int, primary key(o1,o2)) engine = innodb; 1911insert into t1 values(1,1),(2,1); 1912alter table t1 drop primary key, add column a int unique auto_increment, 1913add primary key(o1,o2,a), algorithm=inplace; 1914drop table t1; 1915# 1916# MDEV-15325 Incomplete validation of missing tablespace during recovery 1917# 1918CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB; 1919CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB; 1920# Kill the server 1921# Wrong space_id in a dirty file and a missing file 1922# restart 1923SELECT * FROM INFORMATION_SCHEMA.ENGINES 1924WHERE engine = 'innodb' 1925AND support IN ('YES', 'DEFAULT', 'ENABLED'); 1926ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS 1927# Restore t1 and t2 1928# restart 1929SELECT * FROM t1; 1930f1 1931SELECT * FROM t2; 1932f1 1933DROP TABLE t1, t2; 1934# 1935# MDEV-18186 assertion failure on missing InnoDB index 1936# 1937CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB; 1938DROP TABLE t; 1939CREATE TABLE t (a INT) ENGINE=INNODB; 1940SHOW CREATE TABLE t; 1941Table Create Table 1942t CREATE TABLE `t` ( 1943 `a` int(11) DEFAULT NULL, 1944 KEY `i1` (`a`) 1945) ENGINE=InnoDB DEFAULT CHARSET=latin1 1946Warnings: 1947Warning 1082 InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB 1948Warning 1082 InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB 1949DROP TABLE t; 1950