Edit File: innodb_index_large_prefix.result
set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; set global innodb_large_prefix=1; create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC, engine = innodb; insert into worklog5743 values(repeat("a", 20000)); update worklog5743 set a = (repeat("b", 16000)); create index idx on worklog5743(a(2000)); begin; update worklog5743 set a = (repeat("x", 17000)); select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ select a = repeat("x", 17000) from worklog5743; a = repeat("x", 17000) 0 select a = repeat("b", 16000) from worklog5743; a = repeat("b", 16000) 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a = repeat("x", 17000) from worklog5743; a = repeat("x", 17000) 1 rollback; drop table worklog5743; create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; create index idx on worklog5743(a1, a2(2000)); insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) 9 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) rollback; drop table worklog5743; create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; create index idx on worklog5743(a1, a2(50)); insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) 9 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) rollback; drop table worklog5743; create table worklog5743_2(a1 int, a2 TEXT not null) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; create table worklog5743_4(a1 int, a2 TEXT not null) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; create index idx1 on worklog5743_2(a1, a2(942)); ERROR HY000: Too big row create index idx1 on worklog5743_2(a1, a2(940)); create index idx1 on worklog5743_4(a1, a2(1966)); ERROR HY000: Too big row create index idx1 on worklog5743_4(a1, a2(1964)); insert into worklog5743_2 values(9, repeat("a", 10000)); insert into worklog5743_4 values(9, repeat("a", 10000)); begin; update worklog5743_2 set a1 = 1000; update worklog5743_4 set a1 = 1000; select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE worklog5743_2 ref idx1 idx1 5 const 1 Using where select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; a1 a2 = repeat("a", 10000) 9 1 select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; a1 a2 = repeat("a", 10000) 9 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; a1 a2 = repeat("a", 10000) select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; a1 a2 = repeat("a", 10000) rollback; drop table worklog5743_2; drop table worklog5743_4; create table worklog5743(a1 int, a2 varchar(3000)) ROW_FORMAT=DYNAMIC, engine = innodb; create index idx on worklog5743(a1, a2); insert into worklog5743 values(9, repeat("a", 3000)); begin; update worklog5743 set a1 = 1000; select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ explain select a1 from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where; Using index select a1 from worklog5743 where a1 = 9; a1 9 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a1 from worklog5743 where a1 = 9; a1 rollback; drop table worklog5743; create table worklog5743(a TEXT not null, primary key (a(1000))) engine = innodb; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. create table worklog5743(a TEXT) engine = innodb; create index idx on worklog5743(a(1000)); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. create index idx on worklog5743(a(725)); insert into worklog5743 values(repeat("a", 20000)); begin; insert into worklog5743 values(repeat("b", 20000)); update worklog5743 set a = (repeat("x", 25000)); select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ select a = repeat("a", 20000) from worklog5743; a = repeat("a", 20000) 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED select a = repeat("x", 25000) from worklog5743; a = repeat("x", 25000) 1 1 rollback; drop table worklog5743; create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; create index idx on worklog5743(a(3073)); Warnings: Warning 1071 Specified key was too long; max key length is 3072 bytes Warning 1071 Specified key was too long; max key length is 3072 bytes create index idx2 on worklog5743(a(3072)); show create table worklog5743; Table Create Table worklog5743 CREATE TABLE `worklog5743` ( `a` text NOT NULL, KEY `idx` (`a`(3072)), KEY `idx2` (`a`(3072)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC drop table worklog5743; create table worklog5743(a TEXT not null) engine = innodb; create index idx on worklog5743(a(768)); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. create index idx2 on worklog5743(a(767)); drop table worklog5743; SET GLOBAL innodb_file_format=Antelope; SET GLOBAL innodb_file_per_table=0; SET GLOBAL innodb_large_prefix=0;
Back to File Manager