summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb/r/innodb-index.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-index.result')
-rw-r--r--mysql-test/suite/innodb/r/innodb-index.result1997
1 files changed, 1997 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result
new file mode 100644
index 00000000..a76837b9
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-index.result
@@ -0,0 +1,1997 @@
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
+create table t1(a varchar(2) primary key) engine=innodb;
+insert into t1 values('');
+create index t1a1 on t1(a(1));
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+drop table t1;
+create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
+insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
+commit;
+alter table t1 add index b (b), add index b (b);
+ERROR 42000: Duplicate key name 'b'
+alter table t1 add index (b,b);
+ERROR 42S21: Duplicate column name 'b'
+alter table t1 add index d2 (d);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+analyze table t1;
+explain select * from t1 force index(d2) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d2 23 NULL 4
+select * from t1 force index (d2) order by d;
+a b c d
+3 4 ad ad
+2 3 ak ak
+5 5 oo oo
+4 4 tr tr
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '4' for key 'b'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t1 add index (b);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ KEY `d2` (`d`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t1 add unique index (c), add index (d);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 1
+Warnings:
+Note 1831 Duplicate index `d`. This is deprecated and will be disallowed in a future release
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 4
+alter table t1 add primary key (a), drop index c;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+alter table t1 add primary key (c);
+ERROR 42000: Multiple primary key defined
+alter table t1 drop primary key, add primary key (b);
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
+create unique index c on t1 (c);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 4
+select * from t1 force index(c) order by c;
+a b c d
+3 4 ad ad
+2 3 ak ak
+5 5 oo oo
+4 4 tr tr
+alter table t1 drop index b, add index (b);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+insert into t1 values(6,1,'ggg','ggg');
+select * from t1;
+a b c d
+2 3 ak ak
+3 4 ad ad
+4 4 tr tr
+5 5 oo oo
+6 1 ggg ggg
+select * from t1 force index(b) order by b;
+a b c d
+6 1 ggg ggg
+2 3 ak ak
+3 4 ad ad
+4 4 tr tr
+5 5 oo oo
+select * from t1 force index(c) order by c;
+a b c d
+3 4 ad ad
+2 3 ak ak
+6 1 ggg ggg
+5 5 oo oo
+4 4 tr tr
+select * from t1 force index(d) order by d;
+a b c d
+3 4 ad ad
+2 3 ak ak
+6 1 ggg ggg
+5 5 oo oo
+4 4 tr tr
+analyze table t1;
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 5
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 10 NULL 5
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d 23 NULL 5
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) NOT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `d2` (`d`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add index (c(2));
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+alter table t1 add unique index (d(10));
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `d` (`d`(10)),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+insert into t1 values(5,1,'ggg','ggg');
+analyze table t1;
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+select * from t1 force index(d) order by d;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `d` (`d`(10)),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t1 drop index d;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+5 1 ggg ggg
+8 9 fff fff
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 3 ad ad
+4 4 afe afe
+8 9 fff fff
+5 1 ggg ggg
+analyze table t1;
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+explain select * from t1 order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`(2))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b,c);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+analyze table t1;
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 5
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t1 add index (b,c);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values(11,11,'kkk','kkk');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+analyze table t1;
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 6
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`),
+ KEY `b_2` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t1 add unique index (c,d);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values(13,13,'yyy','aaa');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+select * from t1 force index(c) order by c;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+11 11 kkk kkk
+13 13 yyy aaa
+analyze table t1;
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 16 NULL 7
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 34 NULL 7
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`,`c`),
+ UNIQUE KEY `c` (`c`,`d`),
+ KEY `b_2` (`b`,`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
+create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
+create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
+create table t2(a int not null, b int, c int, d int, e int,
+foreign key (b) references t1(b) on delete set null,
+foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
+engine = innodb;
+alter table t1 drop index b;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+alter table t3 drop index c;
+ERROR HY000: Cannot drop index 'c': needed in a foreign key constraint
+alter table t4 drop index d;
+ERROR HY000: Cannot drop index 'd': needed in a foreign key constraint
+alter table t2 drop index b;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+alter table t2 drop index b, drop index c, drop index d;
+ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
+alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
+ERROR HY000: Cannot change column 'b': used in a foreign key constraint 't2_ibfk_1'
+set @old_sql_mode = @@sql_mode;
+set @@sql_mode = 'STRICT_TRANS_TABLES';
+alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
+ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL
+set @@sql_mode = @old_sql_mode;
+SET FOREIGN_KEY_CHECKS=0;
+alter table t2 DROP COLUMN b, ALGORITHM=COPY;
+ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1'
+alter table t2 DROP COLUMN b;
+ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'test/t2_ibfk_1'
+alter table t1 DROP COLUMN b, ALGORITHM=COPY;
+ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' of table `test`.`t2`
+alter table t1 DROP COLUMN b;
+ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'test/t2_ibfk_1' of table `test`.`t2`
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+create unique index dc on t2 (d,c);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+create index dc on t1 (b,c);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+set @@sql_mode = 'STRICT_TRANS_TABLES';
+alter table t2 add primary key (alpha), change a alpha int,
+change b beta int not null, change c charlie int not null;
+ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL
+alter table t2 add primary key (alpha), change a alpha int,
+change c charlie int not null, change d delta int not null;
+ERROR HY000: Column 'd' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_3' SET NULL
+alter table t2 add primary key (alpha), change a alpha int,
+change b beta int, modify c int not null;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+set @@sql_mode = @old_sql_mode;
+insert into t1 values (1,1,1);
+insert into t3 values (1,1,1);
+insert into t4 values (1,1,1);
+insert into t2 values (1,1,1,1,1);
+commit;
+alter table t4 add constraint dc foreign key (a) references t1(a);
+affected rows: 1
+info: Records: 1 Duplicates: 0 Warnings: 0
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL,
+ `d` int(11) NOT NULL,
+ `e` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `d` (`d`),
+ CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t3 add constraint dc foreign key (a) references t1(a);
+ERROR HY000: Can't create table `test`.`t3` (errno: 121 "Duplicate key on write or update")
+SET FOREIGN_KEY_CHECKS=0;
+alter table t3 add constraint dc foreign key (a) references t1(a);
+ERROR HY000: Failed to add the foreign key constraint 'test/dc' to system tables
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ `d` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `c` (`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table t2 drop index b, add index (beta);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `alpha` int(11) NOT NULL,
+ `beta` int(11) DEFAULT NULL,
+ `c` int(11) NOT NULL,
+ `d` int(11) DEFAULT NULL,
+ `e` int(11) DEFAULT NULL,
+ PRIMARY KEY (`alpha`),
+ UNIQUE KEY `dc` (`d`,`c`),
+ KEY `c` (`c`),
+ KEY `beta` (`beta`),
+ CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`beta`) REFERENCES `t1` (`b`) ON DELETE SET NULL,
+ CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`),
+ CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) ON UPDATE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+delete from t1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
+drop index dc on t4;
+ERROR 42000: Can't DROP INDEX `dc`; check that it exists
+alter table t3 drop foreign key dc;
+ERROR 42000: Can't DROP FOREIGN KEY `dc`; check that it exists
+alter table t4 drop foreign key dc;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+select * from t2;
+alpha beta c d e
+1 1 1 1 1
+delete from t1;
+select * from t2;
+alpha beta c d e
+1 NULL 1 1 1
+drop table t2,t4,t3,t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
+commit;
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '2' for key 'b'
+insert into t1 values(8,9,'fff','fff');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
+alter table t1 add index (b);
+insert into t1 values(10,10,'kkk','iii');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 6
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
+alter table t1 add unique index (c), add index (d);
+insert into t1 values(11,11,'aaa','mmm');
+select * from t1;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(b) order by b;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+select * from t1 force index(c) order by c;
+a b c d
+11 11 aaa mmm
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+select * from t1 force index(d) order by d;
+a b c d
+1 1 ab ab
+2 2 ac ac
+3 2 ad ad
+4 4 afe afe
+8 9 fff fff
+10 10 kkk iii
+11 11 aaa mmm
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 7
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 31 NULL 7
+explain select * from t1 force index(d) order by d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL d 63 NULL 7
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`),
+ KEY `b` (`b`),
+ KEY `d` (`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
+create table t1(a int not null, b int) engine = innodb;
+insert into t1 values (1,1),(1,1),(1,1),(1,1);
+alter table t1 add unique index (a);
+ERROR 23000: Duplicate entry '1' for key 'a'
+alter table t1 add unique index (b);
+ERROR 23000: Duplicate entry '1' for key 'b'
+alter table t1 add unique index (a), add unique index(b);
+ERROR 23000: Duplicate entry '1' for key 'a'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
+alter table t1 drop index c, drop index b;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `c` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int, primary key(a)) engine = innodb;
+alter table t1 add index (b);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
+insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
+alter table t1 add unique index (b), add unique index (c), add unique index (d);
+ERROR 23000: Duplicate entry '4' for key 'b'
+alter table t1 add unique index (c), add unique index (b), add index (d);
+ERROR 23000: Duplicate entry 'ac' for key 'c'
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
+ `d` varchar(20) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
+insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
+alter table t1 add unique index (b);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `b` (`b`),
+ KEY `c` (`c`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+analyze table t1;
+explain select * from t1 force index(c) order by c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 5 NULL 9
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 9
+explain select * from t1 force index(b) order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 4 NULL 9
+select * from t1 order by a;
+a b c
+1 5 1
+2 4 2
+3 3 3
+4 2 4
+5 1 5
+10 20 20
+11 19 19
+12 18 18
+13 17 17
+select * from t1 force index(b) order by b;
+a b c
+5 1 5
+4 2 4
+3 3 3
+2 4 2
+1 5 1
+13 17 17
+12 18 18
+11 19 19
+10 20 20
+select * from t1 force index(c) order by c;
+a b c
+1 5 1
+2 4 2
+3 3 3
+4 2 4
+5 1 5
+13 17 17
+12 18 18
+11 19 19
+10 20 20
+drop table t1;
+create table t1(a int not null, b int not null) engine=innodb;
+insert into t1 values (1,1);
+alter table t1 add primary key(b);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values (2,2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ PRIMARY KEY (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+select * from t1;
+a b
+1 1
+2 2
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+explain select * from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort
+explain select * from t1 order by b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
+checksum table t1;
+Table Checksum
+test.t1 582702641
+drop table t1;
+create table t1(a int not null) engine=innodb;
+insert into t1 values (1);
+alter table t1 add primary key(a);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+insert into t1 values (2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+commit;
+select * from t1;
+a
+1
+2
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+explain select * from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+explain select * from t1 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+drop table t1;
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET FOREIGN_KEY_CHECKS=0;
+CREATE TABLE t1(
+c1 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3) REFERENCES t1(c1);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+INSERT INTO t2 VALUES(0,0,0);
+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`))
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0,0,0);
+DROP TABLE t2;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1,c2,c3)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3) REFERENCES t1(c1);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`,`c2`,`c3`),
+ KEY `fk_t2_ca` (`c3`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`,`c2`,`c3`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t2 VALUES(0,0,1);
+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`))
+INSERT INTO t2 VALUES(0,0,0);
+DELETE FROM t1;
+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`))
+DELETE FROM t2;
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1(
+c1 BIGINT(12) NOT NULL,
+c2 INT(4) NOT NULL,
+PRIMARY KEY (c2,c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2(
+c1 BIGINT(16) NOT NULL,
+c2 BIGINT(12) NOT NULL,
+c3 BIGINT(12) NOT NULL,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
+ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
+ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;
+ERROR HY000: Failed to add the foreign key constraint on table 't2'. Incorrect options in FOREIGN KEY constraint 'test/fk_t2_ca'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
+ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
+ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+affected rows: 0
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` bigint(12) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ PRIMARY KEY (`c2`,`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `fk_t2_ca` (`c3`,`c2`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` bigint(16) NOT NULL,
+ `c2` bigint(12) NOT NULL,
+ `c3` bigint(12) NOT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `i_t2_c2_c1` (`c2`,`c1`),
+ KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
+ KEY `i_t2_c3_c2` (`c3`,`c2`),
+ CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+affected rows: 1
+DROP TABLE t2;
+DROP TABLE t1;
+connect a,localhost,root,,;
+connect b,localhost,root,,;
+connection a;
+CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
+CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
+CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
+CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2i SELECT * FROM t1;
+INSERT INTO t2c SELECT * FROM t1;
+connection b;
+BEGIN;
+SELECT * FROM t1;
+a b
+3 a
+3 b
+1 c
+0 d
+1 e
+connection a;
+SET lock_wait_timeout=1;
+CREATE INDEX t1a ON t1(a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE INDEX t2a ON t2(a);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+set @old_sql_mode = @@sql_mode;
+set @@sql_mode = 'STRICT_TRANS_TABLES';
+ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+set @@sql_mode = @old_sql_mode;
+ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
+affected rows: 5
+info: Records: 5 Duplicates: 0 Warnings: 0
+connection b;
+SELECT * FROM t2i;
+ERROR HY000: Table definition has changed, please retry transaction
+SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
+ERROR HY000: Table definition has changed, please retry transaction
+SELECT * FROM t2c;
+ERROR HY000: Table definition has changed, please retry transaction
+SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
+ERROR HY000: Table definition has changed, please retry transaction
+SELECT * FROM t2;
+a b
+3 a
+3 b
+1 c
+0 d
+1 e
+SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
+ERROR HY000: Table definition has changed, please retry transaction
+SELECT * FROM t2;
+a b
+3 a
+3 b
+1 c
+0 d
+1 e
+COMMIT;
+SELECT * FROM t2;
+a b
+3 a
+3 b
+1 c
+0 d
+1 e
+SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
+a b
+0 d
+1 c
+1 e
+3 a
+3 b
+SELECT * FROM t2i;
+a b
+0 d
+1 c
+1 e
+3 a
+3 b
+SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
+a b
+0 d
+1 c
+1 e
+3 a
+3 b
+SELECT * FROM t2c;
+a b
+0 d
+1 c
+1 e
+3 a
+3 b
+SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
+a b
+0 d
+1 c
+1 e
+3 a
+3 b
+connection default;
+disconnect a;
+disconnect b;
+alter table t2 add index t2a(b);
+ERROR 42000: Duplicate key name 't2a'
+alter table t2 drop index t2a, add index t2a(b);
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) DEFAULT NULL,
+ `b` char(1) DEFAULT NULL,
+ KEY `t2a` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+show create table t2i;
+Table Create Table
+t2i CREATE TABLE `t2i` (
+ `a` int(11) NOT NULL,
+ `b` char(1) NOT NULL,
+ PRIMARY KEY (`a`,`b`),
+ KEY `t2a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+show create table t2c;
+Table Create Table
+t2c CREATE TABLE `t2c` (
+ `a` int(11) NOT NULL,
+ `b` char(1) NOT NULL,
+ PRIMARY KEY (`a`,`b`),
+ KEY `t2a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1,t2,t2c,t2i;
+CREATE TABLE t1 (c VARCHAR(1024),
+c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL,
+c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL,
+c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL,
+ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL,
+cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL,
+d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL,
+d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL,
+d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL,
+d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL,
+dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL,
+UNIQUE KEY(c))
+ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
+INSERT INTO t1 VALUES
+(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''),
+(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace;
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy;
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+2
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+DROP TABLE t1;
+#
+# Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH
+# IN I_S TABLES
+#
+CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1;
+SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
+cast(INDEX_LENGTH/@@innodb_page_size as int) I
+FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+D I
+1 0
+ALTER TABLE t1 ADD INDEX (a);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
+cast(INDEX_LENGTH/@@innodb_page_size as int) I
+FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+D I
+1 1
+ALTER TABLE t1 ADD INDEX (b);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
+cast(INDEX_LENGTH/@@innodb_page_size as int) I
+FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+D I
+1 2
+DROP TABLE t1;
+#
+# Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
+#
+SET GLOBAL innodb_monitor_enable = module_ddl;
+create table t1(f1 int not null, f2 blob)engine=innodb;
+insert into t1 values(1, repeat('a',20000));
+# Skip sort
+# Reusing the same pk
+alter table t1 force;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+create table t1(f1 int not null, f2 int not null,
+primary key(f1))engine=innodb;
+insert into t1 values(1,2), (3,4);
+# Add Secondary index.
+# Skip temp file usage due to small table size
+alter table t1 add key(f2);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+drop table t1;
+create table t480(a serial)engine=innodb;
+insert into t480
+values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
+(),(),(),(),(),(),(),();
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+insert into t480 select 0 from t480;
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1))engine=innodb;
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
+insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
+insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
+insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
+insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
+select count(*) from t1;
+count(*)
+2880
+# Skip sort
+# Change PK from (f1) to (f1,f2,f3,f4)
+alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
+alter table t1 drop primary key,add column f5 int not null,
+add column f6 int not null,add primary key(f1,f2,f5,f6);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
+alter table t1 drop column f6;
+ERROR 42000: Key column 'f6' doesn't exist in table
+alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same PK
+alter table t1 add column f6 int;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 drop column f6;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK from (f1,f2,f5) to (f1,f5)
+alter table t1 drop column f2;
+ERROR 42000: Key column 'f2' doesn't exist in table
+alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+ddl_sort_file_alter_table 2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 add column f2n int after f1, drop primary key, add
+primary key (f1,f5,f2n);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 change f5 f2n int not null,change f2n f5 int not null,
+add column f8 int not null;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
+alter table t1 add column f7 int, drop primary key,
+add primary key (f1,f5,f7,f2n);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+ddl_sort_file_alter_table 2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 force;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 row_format=compact;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Reusing the same pk
+alter table t1 engine=innodb;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Optimize table
+optimize table t1;
+Table Op Msg_type Msg_text
+test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
+test.t1 optimize status OK
+affected rows: 2
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Sort files used for adding secondary index
+alter table t1 drop primary key, add primary key(f1,f5,f7), add index
+i(f3);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# No sort files used for dropping secondary index
+alter table t1 drop primary key, add primary key(f1,f5),drop index i;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK(f1,f5) to (f1,added_columns) and drop f5
+alter table t1 drop primary key, add primary key(f1,f12),
+drop column f5, add column f12 int not null;
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK(f1,f12) to (f1,existing_columns)
+alter table t1 drop primary key, add primary key(f1,f3);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+ddl_sort_file_alter_table 2
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort
+# Change PK(f1,f3) to (f1,added_column,f3,added_column)
+alter table t1 drop primary key, add column f3n int,
+add column f4n int, add primary key(f1,f3n,f3,f4n);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Adding Secondary index alone.
+alter table t1 add key(f1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Must sort
+# Change PK(f1,f3) to (existing_column,f1)
+alter table t1 drop primary key, add primary key(f4,f1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+ddl_sort_file_alter_table 3
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort for PK.
+# Change PK(f4,f1) to (added_columns,f4,f1)
+# Secondary index rebuild happens
+alter table t1 drop primary key, add column f5n int,
+add column f6n int, add primary key(f5n,f6n,f4,f1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+# Skip sort for PK.
+# Change PK(f5n,f6n,f4,f1) to
+# (added_columns,f5n,added_column,f6n,f4,f1)
+# Secondary index rebuild happens
+alter table t1 drop primary key, add column f7n int,
+add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
+SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
+subsystem = 'ddl' and count_reset > 0;
+name count_reset
+SET GLOBAL innodb_monitor_reset = module_ddl;
+SET GLOBAL innodb_monitor_disable = module_ddl;
+select count(*) from t1;
+count(*)
+2880
+drop table t1;
+SET GLOBAL innodb_monitor_reset = default;
+SET GLOBAL innodb_monitor_enable = default;
+SET GLOBAL innodb_monitor_disable = default;
+# Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
+# WHEN SKIPPING SORT FOR CLUSTER INDEX
+SELECT @@innodb_sort_buffer_size;
+@@innodb_sort_buffer_size
+1048576
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(106, 'aaa','bbb','cccc');
+select count(*) from t1;
+count(*)
+481
+# Skip sort
+# Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106-aaa-bbb' for key 'PRIMARY'
+select count(*) from t1;
+count(*)
+481
+drop table t1;
+create table t1(f1 int auto_increment not null,
+f2 char(200) not null, f3 char(200) not null,
+f4 char(200) not null,primary key(f1,f2,f3,f4));
+insert into t1 select NULL,'aaa','bbb','ccc' from t480;
+insert into t1 values(108,'aaa','bbb','cccc');
+select count(*) from t1;
+count(*)
+481
+alter table t1 drop primary key, add primary key(f1,f2,f3);
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '108-aaa-bbb' for key 'PRIMARY'
+select count(*) from t1;
+count(*)
+481
+drop table t1, t480;
+#
+# Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
+# FIELD IS CHANGED
+#
+create table t1(a int not null, b varchar(30) not null,
+primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
+select * from t1;
+a b
+0 khdHps6UxW8Lwaoxa604oK6zkb
+1 khdHps6UxW8L
+alter table t1 drop primary key, add primary key (b(18),a);
+select * from t1;
+a b
+1 khdHps6UxW8L
+0 khdHps6UxW8Lwaoxa604oK6zkb
+drop table t1;
+create table t1(a int not null, b varchar(30) not null,
+primary key (b(10), a)) engine = innodb;
+insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
+select * from t1;
+a b
+1 khdHps6UtW8L
+0 khdHps6UxW8Lwaoxa604oK6zkb
+alter table t1 drop primary key, add primary key (b(8),a);
+select * from t1;
+a b
+0 khdHps6UxW8Lwaoxa604oK6zkb
+1 khdHps6UtW8L
+drop table t1;
+#
+# Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE
+# COLUMN PRIMARY KEY
+#
+create table t1(f1 int not null, f2 int not null,
+primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+alter table t1 drop column f1;
+ERROR 42000: Key column 'f1' doesn't exist in table
+alter table t1 drop column f1, drop primary key;
+ERROR 42000: Key column 'f1' doesn't exist in table
+alter table t1 drop column f1, drop key f1;
+drop table t1;
+create table t1(f1 int not null, f2 int not null,
+primary key (f1), unique key(f1, f2))engine=innodb;
+insert into t1 values(1,3), (2,2);
+alter table t1 drop primary key, lock=none;
+alter table t1 drop index f1, lock=none;
+ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED
+drop table t1;
+#
+# BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
+# LAST COLUMN OF OLD PK
+#
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2, o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop o1, lock=none;
+ERROR 42000: Key column 'o1' doesn't exist in table
+alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(1,2,1);
+alter table t1 drop o2, lock=none;
+ERROR 42000: Key column 'o2' doesn't exist in table
+alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,2,2),(2,1,1);
+alter table t1 drop o1, drop o2, lock=none;
+ERROR 42000: Key column 'o2' doesn't exist in table
+alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
+insert into t1 values('abd', 1, 1), ('abc', 2, 2);
+alter table t1 drop primary key, add primary key(o1, o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 1), ('abd', 1, 2);
+alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abc', 2, 2), ('abd', 1, 1);
+alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abc', 'acd'), ('abd', 'abd');
+alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o2,o1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
+insert into t1 values('abd'), ('acd');
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 drop primary key, add primary key(o1(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
+insert into t1 values('abd', 1), ('acd', 2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,'abd'), (2,'acd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values(1, 'abd'), (2, 'acd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
+insert into t1 values('abd', 'acd'), ('acd', 'abd');
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
+insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
+alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1),(2,2);
+alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2,2),(2,2,1,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
+insert into t1 values(1,1,2),(2,2,1);
+alter table t1 drop primary key, add primary key(o1), lock=none;
+drop table t1;
+create table t1(o1 int, o2 int, primary key(o1,o2)) engine = innodb;
+insert into t1 values(1,1),(2,1);
+alter table t1 drop primary key, add column a int unique auto_increment,
+add primary key(o1,o2,a), algorithm=inplace;
+drop table t1;
+#
+# MDEV-15325 Incomplete validation of missing tablespace during recovery
+#
+CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB;
+CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB;
+# Kill the server
+# Wrong space_id in a dirty file and a missing file
+# restart
+SELECT * FROM INFORMATION_SCHEMA.ENGINES
+WHERE engine = 'innodb'
+AND support IN ('YES', 'DEFAULT', 'ENABLED');
+ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
+# Restore t1 and t2
+# restart
+SELECT * FROM t1;
+f1
+SELECT * FROM t2;
+f1
+DROP TABLE t1, t2;
+#
+# MDEV-18186 assertion failure on missing InnoDB index
+#
+CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
+DROP TABLE t;
+CREATE TABLE t (a INT) ENGINE=INNODB;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` int(11) DEFAULT NULL,
+ KEY `i1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+Warnings:
+Warning 1082 InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB
+Warning 1082 InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB
+DROP TABLE t;
+#
+# MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column
+#
+CREATE TABLE t1 (a VARCHAR(8), PRIMARY KEY(a DESC)) ENGINE=InnoDB;
+ALTER TABLE t1 RENAME COLUMN a TO b, ALGORITHM=INPLACE;
+SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
+SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
+NAME
+b
+DROP TABLE t1;
+#
+# MDEV-27432 ASC/DESC primary and unique keys cause index
+# inconsistency between InnoDB and server
+#
+CREATE TABLE t1 (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD PRIMARY KEY (id), ALGORITHM=INPLACE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `id` (`id` DESC)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+#
+# MDEV-27445 Index inconsistency and assertion failure after
+# renaming virtual column with DESC key
+#
+CREATE TABLE t1(a INT, b INT AS (a), KEY(a, b DESC)) ENGINE=InnoDB;
+ALTER TABLE t1 RENAME COLUMN IF EXISTS b TO v;
+ALTER TABLE t1 FORCE;
+DROP TABLE t1;
+#
+# MDEV-27592 DESC primary index fails to set wide format
+# while renaming the column
+#
+CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(8), b INT, KEY(a DESC,b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'foo',10);
+ALTER TABLE t1 RENAME COLUMN b TO c, ALGORITHM=INPLACE;
+SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
+SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id ORDER BY INDEX_ID DESC LIMIT 1;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
+NAME
+a
+c
+DROP TABLE t1;
+# End of 10.8 tests
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-18 13:46:43 +0000