diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-index.test | 1234 |
1 files changed, 1234 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test new file mode 100644 index 00000000..c80e3e6c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -0,0 +1,1234 @@ +-- source include/have_innodb.inc +# Embedded server tests do not support restarting. +-- source include/not_embedded.inc + +let $MYSQLD_DATADIR= `select @@datadir`; + +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; + +# Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD +create table t1(a varchar(2) primary key) engine=innodb; +insert into t1 values(''); +--enable_info +create index t1a1 on t1(a(1)); +--disable_info +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; +--error ER_DUP_KEYNAME +alter table t1 add index b (b), add index b (b); +--error ER_DUP_FIELDNAME +alter table t1 add index (b,b); +--enable_info +alter table t1 add index d2 (d); +--disable_info +show create table t1; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(d2) order by d; +select * from t1 force index (d2) order by d; +--error ER_DUP_ENTRY +alter table t1 add unique index (b); +show create table t1; +--enable_info +alter table t1 add index (b); +--disable_info +show create table t1; + +--enable_info +alter table t1 add unique index (c), add index (d); +--disable_info +show create table t1; +analyze table t1; +explain select * from t1 force index(c) order by c; +--enable_info +alter table t1 add primary key (a), drop index c; +show create table t1; +--error ER_MULTIPLE_PRI_KEY +alter table t1 add primary key (c); +--error ER_DUP_ENTRY +alter table t1 drop primary key, add primary key (b); +create unique index c on t1 (c); +--disable_info +show create table t1; +analyze table t1; +explain select * from t1 force index(c) order by c; +select * from t1 force index(c) order by c; +--enable_info +alter table t1 drop index b, add index (b); +--disable_info +show create table t1; +insert into t1 values(6,1,'ggg','ggg'); +select * from t1; +select * from t1 force index(b) order by b; +select * from t1 force index(c) order by c; +select * from t1 force index(d) order by d; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(b) order by b; +explain select * from t1 force index(c) order by c; +explain select * from t1 force index(d) order by d; +show create table t1; +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; +--enable_info +alter table t1 add index (c(2)); +show create table t1; +alter table t1 add unique index (d(10)); +show create table t1; +--disable_info +insert into t1 values(5,1,'ggg','ggg'); +-- disable_result_log +analyze table t1; +-- enable_result_log +select * from t1; +select * from t1 force index(c) order by c; +select * from t1 force index(d) order by d; +explain select * from t1 order by b; +explain select * from t1 force index(c) order by c; +explain select * from t1 force index(d) order by d; +show create table t1; +--enable_info +alter table t1 drop index d; +--disable_info +insert into t1 values(8,9,'fff','fff'); +select * from t1; +select * from t1 force index(c) order by c; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 order by b; +explain select * from t1 force index(c) order by c; +explain select * from t1 order by d; +show create table t1; +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; +--enable_info +alter table t1 add unique index (b,c); +--disable_info +insert into t1 values(8,9,'fff','fff'); +select * from t1; +select * from t1 force index(b) order by b; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(b) order by b; +show create table t1; +--enable_info +alter table t1 add index (b,c); +--disable_info +insert into t1 values(11,11,'kkk','kkk'); +select * from t1; +select * from t1 force index(b) order by b; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(b) order by b; +show create table t1; +--enable_info +alter table t1 add unique index (c,d); +--disable_info +insert into t1 values(13,13,'yyy','aaa'); +select * from t1; +select * from t1 force index(b) order by b; +select * from t1 force index(c) order by c; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(b) order by b; +explain select * from t1 force index(c) order by c; +show create table t1; +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; +--error ER_DROP_INDEX_FK +alter table t1 drop index b; +--error ER_DROP_INDEX_FK +alter table t3 drop index c; +--error ER_DROP_INDEX_FK +alter table t4 drop index d; +--error ER_DROP_INDEX_FK +alter table t2 drop index b; +--error ER_DROP_INDEX_FK +alter table t2 drop index b, drop index c, drop index d; +--error ER_FK_COLUMN_CANNOT_CHANGE +alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY; +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +set @old_sql_mode = @@sql_mode; +set @@sql_mode = 'STRICT_TRANS_TABLES'; +--error ER_FK_COLUMN_NOT_NULL +alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE; +set @@sql_mode = @old_sql_mode; + +SET FOREIGN_KEY_CHECKS=0; +--error ER_FK_COLUMN_CANNOT_DROP +alter table t2 DROP COLUMN b, ALGORITHM=COPY; +--error ER_FK_COLUMN_CANNOT_DROP +alter table t2 DROP COLUMN b; +--error ER_FK_COLUMN_CANNOT_DROP_CHILD +alter table t1 DROP COLUMN b, ALGORITHM=COPY; +--error ER_FK_COLUMN_CANNOT_DROP_CHILD +alter table t1 DROP COLUMN b; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; + +--enable_info +# Apparently, the following makes mysql_alter_table() drop index d. +create unique index dc on t2 (d,c); +create index dc on t1 (b,c); +# This should preserve the foreign key constraints. +--disable_info +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +# And adding a PRIMARY KEY will also add NOT NULL implicitly! +set @@sql_mode = 'STRICT_TRANS_TABLES'; +--enable_info +--error ER_FK_COLUMN_NOT_NULL +alter table t2 add primary key (alpha), change a alpha int, +change b beta int not null, change c charlie int not null; +--error ER_FK_COLUMN_NOT_NULL +alter table t2 add primary key (alpha), change a alpha int, +change c charlie int not null, change d delta int not null; +alter table t2 add primary key (alpha), change a alpha int, +change b beta int, modify c int not null; +--disable_info +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; +--enable_info +alter table t4 add constraint dc foreign key (a) references t1(a); +--disable_info +show create table t4; +# mysqltest first does replace_regex, then replace_result +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' +# a foreign key 'test/dc' already exists +--error ER_CANT_CREATE_TABLE +alter table t3 add constraint dc foreign key (a) references t1(a); +SET FOREIGN_KEY_CHECKS=0; +--error ER_FK_FAIL_ADD_SYSTEM +alter table t3 add constraint dc foreign key (a) references t1(a); +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +show create table t3; +--enable_info +alter table t2 drop index b, add index (beta); +--disable_info +show create table t2; +--error ER_ROW_IS_REFERENCED_2 +delete from t1; +--error ER_CANT_DROP_FIELD_OR_KEY +drop index dc on t4; +--enable_info +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t3 drop foreign key dc; +alter table t4 drop foreign key dc; +--disable_info +select * from t2; +delete from t1; +select * from t2; + +drop table t2,t4,t3,t1; + +-- let charset = utf8 +-- source include/innodb-index.inc + +create table t1(a int not null, b int) engine = innodb; +insert into t1 values (1,1),(1,1),(1,1),(1,1); +--error ER_DUP_ENTRY +alter table t1 add unique index (a); +--error ER_DUP_ENTRY +alter table t1 add unique index (b); +--error ER_DUP_ENTRY +alter table t1 add unique index (a), add unique index(b); +show create table t1; +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; +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; +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'); +--error ER_DUP_ENTRY +alter table t1 add unique index (b), add unique index (c), add unique index (d); +--error ER_DUP_ENTRY +alter table t1 add unique index (c), add unique index (b), add index (d); +show create table t1; +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); +--enable_info +alter table t1 add unique index (b); +--disable_info +insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17); +show create table t1; +check table t1; +-- disable_result_log +analyze table t1; +-- enable_result_log +explain select * from t1 force index(c) order by c; +explain select * from t1 order by a; +explain select * from t1 force index(b) order by b; +select * from t1 order by a; +select * from t1 force index(b) order by b; +select * from t1 force index(c) order by c; +drop table t1; + +create table t1(a int not null, b int not null) engine=innodb; +insert into t1 values (1,1); +--enable_info +alter table t1 add primary key(b); +--disable_info +insert into t1 values (2,2); +show create table t1; +check table t1; +select * from t1; +analyze table t1; +explain select * from t1; +explain select * from t1 order by a; +explain select * from t1 order by b; +checksum table t1; +drop table t1; + +create table t1(a int not null) engine=innodb; +insert into t1 values (1); +--enable_info +alter table t1 add primary key(a); +--disable_info +insert into t1 values (2); +show create table t1; +check table t1; +commit; +select * from t1; +analyze table t1; +explain select * from t1; +explain select * from t1 order by a; +drop table t1; + +# +# Test to check whether CREATE INDEX handles implicit foreign key +# constraint modifications (Issue #70, Bug #38786) +# +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; + +CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); + +SHOW CREATE TABLE t2; + +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; + +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO t2 VALUES(0,0,0); +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; + +CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); + +SHOW CREATE TABLE t2; +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO t2 VALUES(0,0,1); +INSERT INTO t2 VALUES(0,0,0); +--error ER_ROW_IS_REFERENCED_2 +DELETE FROM t1; +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; +--enable_info + +# mysqltest first does replace_regex, then replace_result +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' +--error ER_CANT_CREATE_TABLE +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY; +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1); +# mysqltest first does replace_regex, then replace_result +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' +--error ER_CANT_CREATE_TABLE +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); + +--error ER_FK_INCORRECT_OPTION +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE; + +# mysqltest first does replace_regex, then replace_result +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' +--error ER_CANT_CREATE_TABLE +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY; + +ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL; +# mysqltest first does replace_regex, then replace_result +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' +--error ER_CANT_CREATE_TABLE +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); + +ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca + FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1); + +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; + +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +CREATE INDEX i_t2_c2_c1 ON t2(c2, c1); +SHOW CREATE TABLE t2; +CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2); +SHOW CREATE TABLE t2; +CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); +SHOW CREATE TABLE t2; +--disable_info + +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; +# This acquires a MDL lock on t1 until commit. +SELECT * FROM t1; +connection a; +# This times out before of the MDL lock held by connection b. +SET lock_wait_timeout=1; +--error ER_LOCK_WAIT_TIMEOUT +CREATE INDEX t1a ON t1(a); +--enable_info +CREATE INDEX t2a ON t2(a); +--disable_info +set @old_sql_mode = @@sql_mode; +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +# And adding a PRIMARY KEY will also add NOT NULL implicitly! +set @@sql_mode = 'STRICT_TRANS_TABLES'; +--enable_info +ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE; +--disable_info +set @@sql_mode = @old_sql_mode; +--enable_info +ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY; +--disable_info +connection b; +# t2i and t2c are too new for this transaction, because they were rebuilt +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2i; +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2c; +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; +# In t2, only the new index t2a is too new for this transaction. +SELECT * FROM t2; +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; +SELECT * FROM t2; +COMMIT; +# For a new transaction, all of t2, t2i, t2c are accessible. +SELECT * FROM t2; +SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; +SELECT * FROM t2i; +SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; +SELECT * FROM t2c; +SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; +connection default; +disconnect a; +disconnect b; + +--error ER_DUP_KEYNAME +alter table t2 add index t2a(b); +alter table t2 drop index t2a, add index t2a(b); +show create table t2; +show create table t2i; +show create table t2c; + +--disable_info + +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'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''); +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace; +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH +--echo # IN I_S TABLES +--echo # +let $i_s_query=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'; + +CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1; +eval $i_s_query; +--enable_info +ALTER TABLE t1 ADD INDEX (a); +--disable_info +eval $i_s_query; +--enable_info +ALTER TABLE t1 ADD INDEX (b); +--disable_info +eval $i_s_query; +DROP TABLE t1; + +--echo # +--echo # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE +--echo # + +SET GLOBAL innodb_monitor_enable = module_ddl; +let $innodb_metrics_select= +SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE +subsystem = 'ddl' and count_reset > 0; + +# Table with Blob data. +create table t1(f1 int not null, f2 blob)engine=innodb; +insert into t1 values(1, repeat('a',20000)); +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 force; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; +drop table t1; + +# Table with small data. +create table t1(f1 int not null, f2 int not null, +primary key(f1))engine=innodb; +insert into t1 values(1,2), (3,4); +--echo # Add Secondary index. +--echo # Skip temp file usage due to small table size +--enable_info +alter table t1 add key(f2); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; +drop table t1; + +# Table with large data which is greater than sort buffer +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; + +--echo # Skip sort +--echo # Change PK from (f1) to (f1,f2,f3,f4) +--enable_info +alter table t1 drop primary key, add primary key(f1,f2,f3,f4); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + + +# Temp file not used during clustered index rebuild +# for the following alter table commands. + +--echo # Skip sort +--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns) +--enable_info +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); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5) +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop column f6; +--enable_info +alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same PK +--enable_info +alter table t1 add column f6 int; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 drop column f6; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Must sort +--echo # Change PK from (f1,f2,f5) to (f1,f5) +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop column f2; +--enable_info +alter table t1 drop column f2, drop primary key, add primary key(f1,f5); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 add column f2n int after f1, drop primary key, add +primary key (f1,f5,f2n); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 change f5 f2n int not null,change f2n f5 int not null, +add column f8 int not null; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n) +--enable_info +alter table t1 add column f7 int, drop primary key, +add primary key (f1,f5,f7,f2n); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 force; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 row_format=compact; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Reusing the same pk +--enable_info +alter table t1 engine=innodb; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Optimize table +--enable_info +optimize table t1; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Sort files used for adding secondary index +--enable_info +alter table t1 drop primary key, add primary key(f1,f5,f7), add index +i(f3); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # No sort files used for dropping secondary index +--enable_info +alter table t1 drop primary key, add primary key(f1,f5),drop index i; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Change PK(f1,f5) to (f1,added_columns) and drop f5 +--enable_info +alter table t1 drop primary key, add primary key(f1,f12), +drop column f5, add column f12 int not null; +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Must sort +--echo # Change PK(f1,f12) to (f1,existing_columns) +--enable_info +alter table t1 drop primary key, add primary key(f1,f3); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort +--echo # Change PK(f1,f3) to (f1,added_column,f3,added_column) +--enable_info +alter table t1 drop primary key, add column f3n int, +add column f4n int, add primary key(f1,f3n,f3,f4n); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Adding Secondary index alone. +--enable_info +alter table t1 add key(f1); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Must sort +--echo # Change PK(f1,f3) to (existing_column,f1) +--enable_info +alter table t1 drop primary key, add primary key(f4,f1); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort for PK. +--echo # Change PK(f4,f1) to (added_columns,f4,f1) +--echo # Secondary index rebuild happens +--enable_info +alter table t1 drop primary key, add column f5n int, +add column f6n int, add primary key(f5n,f6n,f4,f1); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +--echo # Skip sort for PK. +--echo # Change PK(f5n,f6n,f4,f1) to +--echo # (added_columns,f5n,added_column,f6n,f4,f1) +--echo # Secondary index rebuild happens +--enable_info +alter table t1 drop primary key, add column f7n int, +add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1); +--disable_info +eval $innodb_metrics_select; +SET GLOBAL innodb_monitor_reset = module_ddl; + +SET GLOBAL innodb_monitor_disable = module_ddl; +select count(*) from t1; +drop table t1; +--disable_warnings +SET GLOBAL innodb_monitor_reset = default; +SET GLOBAL innodb_monitor_enable = default; +SET GLOBAL innodb_monitor_disable = default; +--enable_warnings + +--echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST +--echo # WHEN SKIPPING SORT FOR CLUSTER INDEX + +# last mtuple in previous buffer and first mtuple in next buffer +# are equal. +SELECT @@innodb_sort_buffer_size; +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; +--echo # Skip sort +--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3) +--error ER_DUP_ENTRY +alter table t1 drop primary key, add primary key(f1,f2,f3); +select count(*) from t1; +drop table t1; + +# Duplicates exist with in the buffer +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; +--error ER_DUP_ENTRY +alter table t1 drop primary key, add primary key(f1,f2,f3); +select count(*) from t1; +drop table t1, t480; + +--echo # +--echo # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK +--echo # FIELD IS CHANGED +--echo # + +# Prefix length changes for the varchar column. +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; +alter table t1 drop primary key, add primary key (b(18),a); +select * from t1; +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; +alter table t1 drop primary key, add primary key (b(8),a); +select * from t1; +drop table t1; + + +--echo # +--echo # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE +--echo # COLUMN PRIMARY KEY +--echo # + +# Drop primary key column. +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); +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop column f1; +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop column f1, drop primary key; +# DROP PRIMARY KEY is implied for a single-column PRIMARY KEY +alter table t1 drop column f1, drop key f1; +drop table t1; + +# Drop Primary key when lock is none. +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; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t1 drop index f1, lock=none; +drop table t1; + +--echo # +--echo # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP +--echo # LAST COLUMN OF OLD PK +--echo # + +# no skip sort cases +# pk(o1,o2) to pk(o1,o3), drop o2 +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; + +# pk(o1,o2) to pk(o3), drop o1, o2 +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; + +# pk(o1,o2) to pk(o1,o3) +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; + +# pk(o1,o2) to pk(o3) +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; + +# pk(o1,o2) to pk(n1,o3) +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; + +# pk(o1,o2) to pk(o3,n1) +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; + +# pk(o1,o2) to pk(o2,o1) +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; + +# pk(o1,o2) to pk(o2) +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; + +# pk(o1,o2) to pk(o2,o3) +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; + +# pk(o2,o1) to pk(o2,o3) +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; + +# pk(o1,o2) to pk(o1,o3,o2) +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; + +# pk(o1,o2) to pk(o3,o1,o2) +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; + +# pk(o1,o2,o3) to pk(o1,o3) +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; + +# pk(o1,o2,o3) to pk(o2,o3) by drop o1 +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); +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop o1, lock=none; +alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o3) by drop o2 +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); +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop o2, lock=none; +alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o3) by drop o1,o2 +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); +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 drop o1, drop o2, lock=none; +alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none; +drop table t1; + +# no skip sort for prefix change +# pk(o1(2),o2) to pk(o1(3),o2) +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; + +# pk(o1(2),o2) to pk(o1,o2) +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; + +# pk(o1(2),o2) to pk(o1(3),o3) +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; + +# pk(o1(2),o2) to pk(o1,o3) +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; + +# pk(o1(3),o2) to pk(o1(2),o2) +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; + +# pk(o1,o2) to pk(o1(2),o2) +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; + +# pk(o1(3),o2) to pk(o1(2),o3) +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; + +# pk(o1,o2) to pk(o1(2),o3) +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; + +# pk(o1,o2(2),o3) to pk(o1,o2(3)) +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; + +# pk(o1,o2(2),o3) to pk(o1,o2) +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; + +# pk(o1(3),o2(3)) to pk(o1(2),o2(3)) +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; + +# pk(o1,o2) to pk(o1(2),o2) +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; + +# pk(o1(3),o2(3)) to pk(o2(3),o1(3)) +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; + +# pk(o1,o2) to pk(o2,o1) +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; + +# no skip sort cases +--source suite/innodb/include/alter_table_pk_no_sort.inc + +--echo # +--echo # MDEV-15325 Incomplete validation of missing tablespace during recovery +--echo # + +--source include/no_checkpoint_start.inc +CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB; + +CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB; + +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1, t2; +--source include/no_checkpoint_end.inc + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let $check_no_innodb=SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); + +--echo # Wrong space_id in a dirty file and a missing file + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t0.ibd +--move_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd + +--source include/start_mysqld.inc +--eval $check_no_innodb +--source include/shutdown_mysqld.inc + +--echo # Restore t1 and t2 + +--move_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--move_file $MYSQLD_DATADIR/test/t0.ibd $MYSQLD_DATADIR/test/t1.ibd + +--source include/start_mysqld.inc + +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-18186 assertion failure on missing InnoDB index +--echo # + +--disable_query_log +call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary"); +call mtr.add_suppression("InnoDB indexes are inconsistent with what defined"); +call mtr.add_suppression("Table test/t contains 0 indexes"); +call mtr.add_suppression("InnoDB could not find key no"); +--enable_query_log + +# Test an attempt to rename a nonexistent index inside InnoDB +-- let $MYSQL_DATA_DIR = `SELECT @@datadir` +CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB; +-- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_ +DROP TABLE t; +CREATE TABLE t (a INT) ENGINE=INNODB; +-- remove_file $MYSQL_DATA_DIR/test/t.frm +-- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm +--enable_prepare_warnings +SHOW CREATE TABLE t; +--disable_prepare_warnings +DROP TABLE t; + +--echo # +--echo # MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column +--echo # +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; +DROP TABLE t1; + +--echo # +--echo # MDEV-27432 ASC/DESC primary and unique keys cause index +--echo # inconsistency between InnoDB and server +--echo # +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; +DROP TABLE t1; + +--echo # +--echo # MDEV-27445 Index inconsistency and assertion failure after +--echo # renaming virtual column with DESC key +--echo # +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; + +--echo # +--echo # MDEV-27592 DESC primary index fails to set wide format +--echo # while renaming the column +--echo # +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; +DROP TABLE t1; + +--echo # End of 10.8 tests + +--disable_query_log + +call mtr.add_suppression("InnoDB: Tablespace .* was not found at .*t[12].ibd."); +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); + +--enable_query_log |