Skip to content

Commit bd708b4

Browse files
author
Jimmy Yang
committed
Implement worklog #5743 InnoDB: Lift the limit of index key prefixes.
With this change, the index prefix column length lifted from 767 bytes to 3072 bytes if "innodb_large_prefix" is set to "true". rb://603 approved by Marko
1 parent 942cd5f commit bd708b4

31 files changed

+827
-156
lines changed

‎include/my_base.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -446,7 +446,8 @@ enum ha_base_keytype {
446446
#define HA_ERR_FILE_TOO_SHORT 175 /* File too short */
447447
#define HA_ERR_WRONG_CRC 176 /* Wrong CRC on page */
448448
#define HA_ERR_TOO_MANY_CONCURRENT_TRXS 177 /*Too many active concurrent transactions */
449-
#define HA_ERR_LAST 177 /* Copy of last error nr */
449+
#define HA_ERR_INDEX_COL_TOO_LONG 178 /* Index column length exceeds limit */
450+
#define HA_ERR_LAST 178 /* Copy of last error nr */
450451

451452
/* Number of different errors */
452453
#define HA_ERR_ERRORS (HA_ERR_LAST - HA_ERR_FIRST + 1)

‎mysql-test/suite/innodb/r/innodb-index.result‎

Lines changed: 46 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -842,31 +842,31 @@ create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
842842
i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob,
843843
q blob,r blob,s blob,t blob,u blob)
844844
engine=innodb row_format=dynamic;
845-
create index t1a on t1 (a(1));
846-
create index t1b on t1 (b(1));
847-
create index t1c on t1 (c(1));
848-
create index t1d on t1 (d(1));
849-
create index t1e on t1 (e(1));
850-
create index t1f on t1 (f(1));
851-
create index t1g on t1 (g(1));
852-
create index t1h on t1 (h(1));
853-
create index t1i on t1 (i(1));
854-
create index t1j on t1 (j(1));
855-
create index t1k on t1 (k(1));
856-
create index t1l on t1 (l(1));
857-
create index t1m on t1 (m(1));
858-
create index t1n on t1 (n(1));
859-
create index t1o on t1 (o(1));
860-
create index t1p on t1 (p(1));
861-
create index t1q on t1 (q(1));
862-
create index t1r on t1 (r(1));
863-
create index t1s on t1 (s(1));
864-
create index t1t on t1 (t(1));
865-
create index t1u on t1 (u(1));
845+
create index t1a on t1 (a(767));
846+
create index t1b on t1 (b(767));
847+
create index t1c on t1 (c(767));
848+
create index t1d on t1 (d(767));
849+
create index t1e on t1 (e(767));
850+
create index t1f on t1 (f(767));
851+
create index t1g on t1 (g(767));
852+
create index t1h on t1 (h(767));
853+
create index t1i on t1 (i(767));
854+
create index t1j on t1 (j(767));
855+
create index t1k on t1 (k(767));
856+
create index t1l on t1 (l(767));
857+
create index t1m on t1 (m(767));
858+
create index t1n on t1 (n(767));
859+
create index t1o on t1 (o(767));
860+
create index t1p on t1 (p(767));
861+
create index t1q on t1 (q(767));
862+
create index t1r on t1 (r(767));
863+
create index t1s on t1 (s(767));
864+
create index t1t on t1 (t(767));
865+
create index t1u on t1 (u(767));
866866
ERROR HY000: Too big row
867-
create index t1ut on t1 (u(1), t(1));
867+
create index t1ut on t1 (u(767), t(767));
868868
ERROR HY000: Too big row
869-
create index t1st on t1 (s(1), t(1));
869+
create index t1st on t1 (s(767), t(767));
870870
show create table t1;
871871
Table Create Table
872872
t1 CREATE TABLE `t1` (
@@ -891,32 +891,32 @@ t1 CREATE TABLE `t1` (
891891
`s` blob,
892892
`t` blob,
893893
`u` blob,
894-
KEY `t1a` (`a`(1)),
895-
KEY `t1b` (`b`(1)),
896-
KEY `t1c` (`c`(1)),
897-
KEY `t1d` (`d`(1)),
898-
KEY `t1e` (`e`(1)),
899-
KEY `t1f` (`f`(1)),
900-
KEY `t1g` (`g`(1)),
901-
KEY `t1h` (`h`(1)),
902-
KEY `t1i` (`i`(1)),
903-
KEY `t1j` (`j`(1)),
904-
KEY `t1k` (`k`(1)),
905-
KEY `t1l` (`l`(1)),
906-
KEY `t1m` (`m`(1)),
907-
KEY `t1n` (`n`(1)),
908-
KEY `t1o` (`o`(1)),
909-
KEY `t1p` (`p`(1)),
910-
KEY `t1q` (`q`(1)),
911-
KEY `t1r` (`r`(1)),
912-
KEY `t1s` (`s`(1)),
913-
KEY `t1t` (`t`(1)),
914-
KEY `t1st` (`s`(1),`t`(1))
894+
KEY `t1a` (`a`(767)),
895+
KEY `t1b` (`b`(767)),
896+
KEY `t1c` (`c`(767)),
897+
KEY `t1d` (`d`(767)),
898+
KEY `t1e` (`e`(767)),
899+
KEY `t1f` (`f`(767)),
900+
KEY `t1g` (`g`(767)),
901+
KEY `t1h` (`h`(767)),
902+
KEY `t1i` (`i`(767)),
903+
KEY `t1j` (`j`(767)),
904+
KEY `t1k` (`k`(767)),
905+
KEY `t1l` (`l`(767)),
906+
KEY `t1m` (`m`(767)),
907+
KEY `t1n` (`n`(767)),
908+
KEY `t1o` (`o`(767)),
909+
KEY `t1p` (`p`(767)),
910+
KEY `t1q` (`q`(767)),
911+
KEY `t1r` (`r`(767)),
912+
KEY `t1s` (`s`(767)),
913+
KEY `t1t` (`t`(767)),
914+
KEY `t1st` (`s`(767),`t`(767))
915915
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
916-
create index t1u on t1 (u(1));
916+
create index t1u on t1 (u(767));
917917
ERROR HY000: Too big row
918918
alter table t1 row_format=compact;
919-
create index t1u on t1 (u(1));
919+
create index t1u on t1 (u(767));
920920
drop table t1;
921921
set global innodb_file_per_table=0;
922922
set global innodb_file_format=Antelope;

‎mysql-test/suite/innodb/r/innodb_index_large_prefix.result‎

Lines changed: 185 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,185 @@
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));
9+
begin;
10+
update worklog5743 set a = (repeat("x", 17000));
11+
select @@session.tx_isolation;
12+
@@session.tx_isolation
13+
REPEATABLE-READ
14+
select a = repeat("x", 17000) from worklog5743;
15+
a = repeat("x", 17000)
16+
0
17+
select a = repeat("b", 16000) from worklog5743;
18+
a = repeat("b", 16000)
19+
1
20+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
21+
select @@session.tx_isolation;
22+
@@session.tx_isolation
23+
READ-UNCOMMITTED
24+
select a = repeat("x", 17000) from worklog5743;
25+
a = repeat("x", 17000)
26+
1
27+
rollback;
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));
33+
begin;
34+
update worklog5743 set a1 = 1000;
35+
select @@session.tx_isolation;
36+
@@session.tx_isolation
37+
REPEATABLE-READ
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 Using where
41+
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
42+
a1 a2 = repeat("a", 10000)
43+
9 1
44+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
45+
select @@session.tx_isolation;
46+
@@session.tx_isolation
47+
READ-UNCOMMITTED
48+
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
49+
a1 a2 = repeat("a", 10000)
50+
rollback;
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));
56+
begin;
57+
update worklog5743 set a1 = 1000;
58+
select @@session.tx_isolation;
59+
@@session.tx_isolation
60+
REPEATABLE-READ
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 Using where
64+
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
65+
a1 a2 = repeat("a", 10000)
66+
9 1
67+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
68+
select @@session.tx_isolation;
69+
@@session.tx_isolation
70+
READ-UNCOMMITTED
71+
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
72+
a1 a2 = repeat("a", 10000)
73+
rollback;
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));
87+
begin;
88+
update worklog5743_2 set a1 = 1000;
89+
update worklog5743_4 set a1 = 1000;
90+
select @@session.tx_isolation;
91+
@@session.tx_isolation
92+
REPEATABLE-READ
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 Using where
96+
select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9;
97+
a1 a2 = repeat("a", 10000)
98+
9 1
99+
select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9;
100+
a1 a2 = repeat("a", 10000)
101+
9 1
102+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
103+
select @@session.tx_isolation;
104+
@@session.tx_isolation
105+
READ-UNCOMMITTED
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)
110+
rollback;
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));
117+
begin;
118+
update worklog5743 set a1 = 1000;
119+
select @@session.tx_isolation;
120+
@@session.tx_isolation
121+
REPEATABLE-READ
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 where; Using index
125+
select a1 from worklog5743 where a1 = 9;
126+
a1
127+
9
128+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
129+
select @@session.tx_isolation;
130+
@@session.tx_isolation
131+
READ-UNCOMMITTED
132+
select a1 from worklog5743 where a1 = 9;
133+
a1
134+
rollback;
135+
drop table worklog5743;
136+
create table worklog5743(a TEXT not null, primary key (a(1000)))
137+
engine = innodb;
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));
144+
begin;
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
149+
REPEATABLE-READ
150+
select a = repeat("a", 20000) from worklog5743;
151+
a = repeat("a", 20000)
152+
1
153+
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
154+
select @@session.tx_isolation;
155+
@@session.tx_isolation
156+
READ-UNCOMMITTED
157+
select a = repeat("x", 25000) from worklog5743;
158+
a = repeat("x", 25000)
159+
1
160+
1
161+
rollback;
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));
165+
Warnings:
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;
170+
Table Create Table
171+
worklog5743 CREATE TABLE `worklog5743` (
172+
`a` text NOT NULL,
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_file_format_max=Antelope;
185+
SET GLOBAL innodb_large_prefix=0;

‎mysql-test/suite/innodb/t/innodb-index.test‎

Lines changed: 25 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -369,36 +369,36 @@ create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
369369
i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob,
370370
q blob,r blob,s blob,t blob,u blob)
371371
engine=innodb row_format=dynamic;
372-
create index t1a on t1 (a(1));
373-
create index t1b on t1 (b(1));
374-
create index t1c on t1 (c(1));
375-
create index t1d on t1 (d(1));
376-
create index t1e on t1 (e(1));
377-
create index t1f on t1 (f(1));
378-
create index t1g on t1 (g(1));
379-
create index t1h on t1 (h(1));
380-
create index t1i on t1 (i(1));
381-
create index t1j on t1 (j(1));
382-
create index t1k on t1 (k(1));
383-
create index t1l on t1 (l(1));
384-
create index t1m on t1 (m(1));
385-
create index t1n on t1 (n(1));
386-
create index t1o on t1 (o(1));
387-
create index t1p on t1 (p(1));
388-
create index t1q on t1 (q(1));
389-
create index t1r on t1 (r(1));
390-
create index t1s on t1 (s(1));
391-
create index t1t on t1 (t(1));
372+
create index t1a on t1 (a(767));
373+
create index t1b on t1 (b(767));
374+
create index t1c on t1 (c(767));
375+
create index t1d on t1 (d(767));
376+
create index t1e on t1 (e(767));
377+
create index t1f on t1 (f(767));
378+
create index t1g on t1 (g(767));
379+
create index t1h on t1 (h(767));
380+
create index t1i on t1 (i(767));
381+
create index t1j on t1 (j(767));
382+
create index t1k on t1 (k(767));
383+
create index t1l on t1 (l(767));
384+
create index t1m on t1 (m(767));
385+
create index t1n on t1 (n(767));
386+
create index t1o on t1 (o(767));
387+
create index t1p on t1 (p(767));
388+
create index t1q on t1 (q(767));
389+
create index t1r on t1 (r(767));
390+
create index t1s on t1 (s(767));
391+
create index t1t on t1 (t(767));
392392
--error 139
393-
create index t1u on t1 (u(1));
393+
create index t1u on t1 (u(767));
394394
--error 139
395-
create index t1ut on t1 (u(1), t(1));
396-
create index t1st on t1 (s(1), t(1));
395+
create index t1ut on t1 (u(767), t(767));
396+
create index t1st on t1 (s(767), t(767));
397397
show create table t1;
398398
--error 139
399-
create index t1u on t1 (u(1));
399+
create index t1u on t1 (u(767));
400400
alter table t1 row_format=compact;
401-
create index t1u on t1 (u(1));
401+
create index t1u on t1 (u(767));
402402

403403
drop table t1;
404404
eval set global innodb_file_per_table=$per_table;

0 commit comments

Comments
 (0)