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;