diff options
Diffstat (limited to 'mysql-test/suite/innodb_zip/t/innodb-zip.test')
| -rw-r--r-- | mysql-test/suite/innodb_zip/t/innodb-zip.test | 262 |
1 files changed, 262 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test new file mode 100644 index 00000000..9049b231 --- /dev/null +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -0,0 +1,262 @@ +--source include/innodb_page_size_small.inc + +CREATE DATABASE mysqltest_innodb_zip; +USE mysqltest_innodb_zip; +SELECT table_name, row_format, data_length, index_length + FROM information_schema.tables + WHERE table_schema='mysqltest_innodb_zip'; + +let $per_table=`select @@innodb_file_per_table`; +SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; + +--let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name + +set session innodb_strict_mode=0; +set global innodb_file_per_table=off; +SET @@global.innodb_stats_on_metadata=ON; + +create table t0(a int primary key) engine=innodb row_format=compressed; +create table t00(a int primary key) engine=innodb +key_block_size=4 row_format=compressed; +create table t1(a int primary key) engine=innodb row_format=dynamic; +create table t2(a int primary key) engine=innodb row_format=redundant; +create table t3(a int primary key) engine=innodb row_format=compact; +create table t4(a int primary key) engine=innodb key_block_size=9; +create table t5(a int primary key) engine=innodb +key_block_size=1 row_format=redundant; + +set global innodb_file_per_table=on; +create table t6(a int primary key) engine=innodb +key_block_size=1 row_format=redundant; +create table t7(a int primary key) engine=innodb +key_block_size=1 row_format=redundant; +create table t8(a int primary key) engine=innodb +key_block_size=1 row_format=fixed; +create table t9(a int primary key) engine=innodb +key_block_size=1 row_format=compact; +create table t10(a int primary key) engine=innodb +key_block_size=1 row_format=dynamic; +create table t11(a int primary key) engine=innodb +key_block_size=1 row_format=compressed; +create table t12(a int primary key) engine=innodb +key_block_size=1; +create table t13(a int primary key) engine=innodb +row_format=compressed; +create table t14(a int primary key) engine=innodb key_block_size=9; + +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid} +--eval $query_i_s + +drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14; +alter table t1 key_block_size=0; +alter table t1 row_format=dynamic; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s +alter table t1 row_format=compact; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s +alter table t1 row_format=redundant; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s +drop table t1; + +create table t1(a int not null, b text, index(b(10))) engine=innodb +key_block_size=1; + +create table t2(b text)engine=innodb; +insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000))); + +insert into t1 select 1, b from t2; +commit; + +connect (a,localhost,root,,mysqltest_innodb_zip); +connect (b,localhost,root,,mysqltest_innodb_zip); + +connection a; +begin; +update t1 set b=repeat('B',100); + +connection b; +select a,left(b,40) from t1 natural join t2; + +connection a; +rollback; + +connection b; +select a,left(b,40) from t1 natural join t2; + +connection default; +disconnect a; +disconnect b; + +analyze table t1; +analyze table t2; +--replace_result 16384 {valid} 12288 {valid} +--eval $query_i_s +drop table t1,t2; + +# +# Bug #50945 moved to innodb_16k.test, innodb_8k.test, & innodb_4k.test +# + +# +# Test blob column inheritance (mantis issue#36) +# + +create table t1( c1 int not null, c2 blob, c3 blob, c4 blob, + primary key(c1, c2(22), c3(22))) + engine = innodb row_format = dynamic; +begin; +insert into t1 values(1, repeat('A', 20000), repeat('B', 20000), + repeat('C', 20000)); + +update t1 set c3 = repeat('D', 20000) where c1 = 1; +commit; + +# one blob column which is unchanged in update and part of PK +# one blob column which is changed and part of of PK +# one blob column which is not part of PK and is unchanged +select count(*) from t1 where c2 = repeat('A', 20000); +select count(*) from t1 where c3 = repeat('D', 20000); +select count(*) from t1 where c4 = repeat('C', 20000); + +update t1 set c3 = repeat('E', 20000) where c1 = 1; +drop table t1; + +set global innodb_file_per_table = on; + +set innodb_strict_mode = off; +create table t1 (id int primary key) engine = innodb key_block_size = 0; +drop table t1; + +#set strict_mode +set innodb_strict_mode = on; + +#Test different values of KEY_BLOCK_SIZE + +create table t1 (id int primary key) engine = innodb key_block_size = 0; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t2 (id int primary key) engine = innodb key_block_size = 9; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + + +create table t3 (id int primary key) engine = innodb key_block_size = 1; +create table t4 (id int primary key) engine = innodb key_block_size = 2; +create table t5 (id int primary key) engine = innodb key_block_size = 4; +# These tests are now done in innodb_16k, innodb_8k and innodb_4k +# where they get different result depending on page size +# create table t6 (id int primary key) engine = innodb key_block_size = 8; +# create table t7 (id int primary key) engine = innodb key_block_size = 16; + +#check various ROW_FORMAT values. +create table t8 (id int primary key) engine = innodb row_format = compressed; +create table t9 (id int primary key) engine = innodb row_format = dynamic; +create table t10(id int primary key) engine = innodb row_format = compact; +create table t11(id int primary key) engine = innodb row_format = redundant; + +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid} +--eval $query_i_s +drop table t1, t3, t4, t5, t8, t9, t10, t11; + +#test different values of ROW_FORMAT with KEY_BLOCK_SIZE +create table t1 (id int primary key) engine = innodb +key_block_size = 4 row_format = compressed; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t2 (id int primary key) engine = innodb +key_block_size = 4 row_format = redundant; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t3 (id int primary key) engine = innodb +key_block_size = 4 row_format = compact; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t4 (id int primary key) engine = innodb +key_block_size = 4 row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +create table t5 (id int primary key) engine = innodb +key_block_size = 4 row_format = default; + +--eval $query_i_s +drop table t1, t5; + +#test multiple errors +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t1 (id int primary key) engine = innodb +key_block_size = 9 row_format = redundant; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t2 (id int primary key) engine = innodb +key_block_size = 9 row_format = compact; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t2 (id int primary key) engine = innodb +key_block_size = 9 row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; + +--eval $query_i_s + +#test valid values with innodb_file_per_table unset +set global innodb_file_per_table = off; + +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t1 (id int primary key) engine = innodb key_block_size = 1; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t2 (id int primary key) engine = innodb key_block_size = 2; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; +--replace_regex / - .*[0-9]*[)]/)/ +--error ER_CANT_CREATE_TABLE +create table t3 (id int primary key) engine = innodb key_block_size = 4; +show warnings; + +# Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k +# and innodb_4k since they get different warnings with smaller page sizes. + +--error ER_CANT_CREATE_TABLE +create table t6 (id int primary key) engine = innodb row_format = compressed; +--replace_regex / - .*[0-9]*[)]/)/ +show warnings; +create table t7 (id int primary key) engine = innodb row_format = dynamic; +show warnings; +create table t8 (id int primary key) engine = innodb row_format = compact; +create table t9 (id int primary key) engine = innodb row_format = redundant; + +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s +drop table t7, t8, t9; + +# +# restore environment to the state it was before this test execution +# + +-- disable_query_log +eval set global innodb_file_per_table=$per_table; +SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; +--enable_query_log + +DROP DATABASE mysqltest_innodb_zip; |