mirror of
https://github.com/MariaDB/server.git
synced 2025-04-04 22:35:33 +02:00
8ef727b3d0
InnoDB in Debian uses utf8mb4 as default character set since version 10.0.20-2. This leads to major pain due to keys longer than 767 bytes. MariaDB 10.2 (and MySQL 5.7) introduced the setting innodb_default_row_format that is DYNAMIC by default. These versions also changed the default values of the parameters innodb_large_prefix=ON and innodb_file_format=Barracuda. This would allow longer column index prefixes to be created. The original purpose of these parameters was to allow InnoDB to be downgraded to MySQL 5.1, which is long out of support. Every InnoDB version since MySQL 5.5 does support operation with the relaxed limits. We backport the parameter innodb_default_row_format to MariaDB 10.1, but we will keep its default value at COMPACT. This allows MariaDB 10.1 to be configured so that CREATE TABLE is less likely to encounter a problem with the limitation: loose_innodb_large_prefix=ON loose_innodb_default_row_format=DYNAMIC (Note that the setting innodb_large_prefix was deprecated in MariaDB 10.2 and removed in MariaDB 10.3.) The only observable difference in the behaviour with the default settings should be that ROW_FORMAT=DYNAMIC tables can be created both in the system tablespace and in .ibd files, no matter what innodb_file_format has been assigned to. Unlike MariaDB 10.2, we are not changing the default value of innodb_file_format, so ROW_FORMAT=COMPRESSED tables cannot be created without changing the parameter.
262 lines
8.5 KiB
Text
262 lines
8.5 KiB
Text
--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;
|