diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-index.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-index.test | 530 |
1 files changed, 530 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 4549b3b6b47..721808c038c 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -546,3 +546,533 @@ show create table t2c; --disable_info DROP TABLE t1,t2,t2c,t2i; +--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_EXITS +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_EXITS +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_EXITS +alter table t1 drop column f1; +--error ER_KEY_COLUMN_DOES_NOT_EXITS +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); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t1 drop primary key, 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_EXITS +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_EXITS +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_EXITS +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 |