1
set global innodb_file_format="Barracuda";
2
set global innodb_file_per_table=1;
3
set global innodb_large_prefix=1;
4
create table worklog5743(a TEXT not null, primary key (a(1000)))
5
ROW_FORMAT=DYNAMIC, engine = innodb;
6
insert into worklog5743 values(repeat("a", 20000));
7
update worklog5743 set a = (repeat("b", 16000));
8
create index idx on worklog5743(a(2000));
10
update worklog5743 set a = (repeat("x", 17000));
11
select @@session.tx_isolation;
12
@@session.tx_isolation
14
select a = repeat("x", 17000) from worklog5743;
15
a = repeat("x", 17000)
17
select a = repeat("b", 16000) from worklog5743;
18
a = repeat("b", 16000)
20
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
21
select @@session.tx_isolation;
22
@@session.tx_isolation
24
select a = repeat("x", 17000) from worklog5743;
25
a = repeat("x", 17000)
28
drop table worklog5743;
29
create table worklog5743(a1 int, a2 TEXT not null)
30
ROW_FORMAT=DYNAMIC, engine = innodb;
31
create index idx on worklog5743(a1, a2(2000));
32
insert into worklog5743 values(9, repeat("a", 10000));
34
update worklog5743 set a1 = 1000;
35
select @@session.tx_isolation;
36
@@session.tx_isolation
38
explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
39
id select_type table type possible_keys key key_len ref rows Extra
40
1 SIMPLE worklog5743 ref idx idx 5 const 1
41
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
42
a1 a2 = repeat("a", 10000)
44
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
45
select @@session.tx_isolation;
46
@@session.tx_isolation
48
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
49
a1 a2 = repeat("a", 10000)
51
drop table worklog5743;
52
create table worklog5743(a1 int, a2 TEXT not null)
53
ROW_FORMAT=DYNAMIC, engine = innodb;
54
create index idx on worklog5743(a1, a2(50));
55
insert into worklog5743 values(9, repeat("a", 10000));
57
update worklog5743 set a1 = 1000;
58
select @@session.tx_isolation;
59
@@session.tx_isolation
61
explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
62
id select_type table type possible_keys key key_len ref rows Extra
63
1 SIMPLE worklog5743 ref idx idx 5 const 1
64
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
65
a1 a2 = repeat("a", 10000)
67
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
68
select @@session.tx_isolation;
69
@@session.tx_isolation
71
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
72
a1 a2 = repeat("a", 10000)
74
drop table worklog5743;
75
create table worklog5743_2(a1 int, a2 TEXT not null)
76
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb;
77
create table worklog5743_4(a1 int, a2 TEXT not null)
78
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb;
79
create index idx1 on worklog5743_2(a1, a2(942));
80
ERROR HY000: Too big row
81
create index idx1 on worklog5743_2(a1, a2(940));
82
create index idx1 on worklog5743_4(a1, a2(1966));
83
ERROR HY000: Too big row
84
create index idx1 on worklog5743_4(a1, a2(1964));
85
insert into worklog5743_2 values(9, repeat("a", 10000));
86
insert into worklog5743_4 values(9, repeat("a", 10000));
88
update worklog5743_2 set a1 = 1000;
89
update worklog5743_4 set a1 = 1000;
90
select @@session.tx_isolation;
91
@@session.tx_isolation
93
explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9;
94
id select_type table type possible_keys key key_len ref rows Extra
95
1 SIMPLE worklog5743_2 ref idx1 idx1 5 const 1
96
select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9;
97
a1 a2 = repeat("a", 10000)
99
select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9;
100
a1 a2 = repeat("a", 10000)
102
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
103
select @@session.tx_isolation;
104
@@session.tx_isolation
106
select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9;
107
a1 a2 = repeat("a", 10000)
108
select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9;
109
a1 a2 = repeat("a", 10000)
111
drop table worklog5743_2;
112
drop table worklog5743_4;
113
create table worklog5743(a1 int, a2 varchar(3000))
114
ROW_FORMAT=DYNAMIC, engine = innodb;
115
create index idx on worklog5743(a1, a2);
116
insert into worklog5743 values(9, repeat("a", 3000));
118
update worklog5743 set a1 = 1000;
119
select @@session.tx_isolation;
120
@@session.tx_isolation
122
explain select a1 from worklog5743 where a1 = 9;
123
id select_type table type possible_keys key key_len ref rows Extra
124
1 SIMPLE worklog5743 ref idx idx 5 const 1 Using index
125
select a1 from worklog5743 where a1 = 9;
128
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
129
select @@session.tx_isolation;
130
@@session.tx_isolation
132
select a1 from worklog5743 where a1 = 9;
135
drop table worklog5743;
136
create table worklog5743(a TEXT not null, primary key (a(1000)))
138
ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
139
create table worklog5743(a TEXT) engine = innodb;
140
create index idx on worklog5743(a(1000));
141
ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
142
create index idx on worklog5743(a(725));
143
insert into worklog5743 values(repeat("a", 20000));
145
insert into worklog5743 values(repeat("b", 20000));
146
update worklog5743 set a = (repeat("x", 25000));
147
select @@session.tx_isolation;
148
@@session.tx_isolation
150
select a = repeat("a", 20000) from worklog5743;
151
a = repeat("a", 20000)
153
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
154
select @@session.tx_isolation;
155
@@session.tx_isolation
157
select a = repeat("x", 25000) from worklog5743;
158
a = repeat("x", 25000)
162
drop table worklog5743;
163
create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb;
164
create index idx on worklog5743(a(3073));
166
Warning 1071 Specified key was too long; max key length is 3072 bytes
167
Warning 1071 Specified key was too long; max key length is 3072 bytes
168
create index idx2 on worklog5743(a(3072));
169
show create table worklog5743;
171
worklog5743 CREATE TABLE `worklog5743` (
173
KEY `idx` (`a`(3072)),
174
KEY `idx2` (`a`(3072))
175
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
176
drop table worklog5743;
177
create table worklog5743(a TEXT not null) engine = innodb;
178
create index idx on worklog5743(a(768));
179
ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
180
create index idx2 on worklog5743(a(767));
181
drop table worklog5743;
182
SET GLOBAL innodb_file_format=Antelope;
183
SET GLOBAL innodb_file_per_table=0;
184
SET GLOBAL innodb_large_prefix=0;