1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
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;
|