path: root/mysql-test/suite/innodb/r/innodb-index.result

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