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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
|
# Testcase for worklog #5743: Lift the limit of index key prefixes
--source include/have_innodb.inc
--source include/have_innodb_16k.inc
SET default_storage_engine=InnoDB;
--disable_query_log
call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
--enable_query_log
let $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
set global innodb_file_per_table=1;
-- echo ### Test 1 ###
# Create a table of DYNAMIC format, with a primary index of 1000 bytes in
# size
create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
show warnings;
# Do some insertion and update to excercise the external cache
# code path
insert into worklog5743 values(repeat("a", 20000));
# default session, update the table
update worklog5743 set a = (repeat("b", 16000));
# Create a secondary index
create index idx on worklog5743(a(2000));
show warnings;
# Start a few sessions to do selections on table being updated in default
# session, so it would rebuild the previous version from undo log.
# 1) Default session: Initiate an update on the externally stored column
# 2) Session con1: Select from table with repeated read
# 3) Session con2: Select from table with read uncommitted
# 4) Default session: rollback updates
begin;
update worklog5743 set a = (repeat("x", 17000));
# Start a new session to select the column to force it build
# an earlier version of the clustered index through undo log. So it should
# just see the result of repeat("b", 16000)
select @@session.tx_isolation;
--connect (con1,localhost,root,,)
select a = repeat("x", 17000) from worklog5743;
select a = repeat("b", 16000) from worklog5743;
# Start another session doing "read uncommitted" query, it
# should see the uncommitted update
--connect (con2,localhost,root,,)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a = repeat("x", 17000) from worklog5743;
# Roll back the transaction
--connection default
rollback;
drop table worklog5743;
-- echo ### Test 2 ###
# Create a table with only a secondary index has large prefix column
create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
show warnings;
create index idx on worklog5743(a1, a2(2000));
show warnings;
insert into worklog5743 values(9, repeat("a", 10000));
begin;
update worklog5743 set a1 = 1000;
# Do a select from another connection that would use the secondary index
--connection con1
select @@session.tx_isolation;
explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
# Do read uncommitted in another session, it would show there is no
# row with a1 = 9
--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
--connection default
rollback;
drop table worklog5743;
-- echo ### Test 3 ###
# Create a table with a secondary index has small (50 bytes) prefix column
create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
create index idx on worklog5743(a1, a2(50));
insert into worklog5743 values(9, repeat("a", 10000));
begin;
update worklog5743 set a1 = 1000;
# Do a select from another connection that would use the secondary index
--connection con1
select @@session.tx_isolation;
explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
# Do read uncommitted in another session, it would show there is no
# row with a1 = 9
--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
--connection default
rollback;
drop table worklog5743;
-- echo ### Test 4 ###
# Create compressed tables with each KEY_BLOCK_SIZE.
create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=16;
# The maximum overall index record (not prefix) length of a
# compressed table is dependent on innodb-page-size (IPS),
# key_block_size (KBS) and the number of fields (NF).
# "Too big row" error (HA_ERR_TOO_BIG_ROW) will be returned if this
# limit is exceeded.
# See page_zip_empty_size() and Bug #47495 for more detail.
# Test edge cases for indexes using key_block_size=1
set global innodb_large_prefix=0;
-- error ER_TOO_LONG_KEY,1118
create index idx1 on worklog5743_1(a2(4000));
show warnings;
set global innodb_large_prefix=1;
-- error ER_TOO_LONG_KEY,1118
create index idx2 on worklog5743_1(a2(4000));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx3 on worklog5743_1(a2(436));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx4 on worklog5743_1(a2(434));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx5 on worklog5743_1(a1, a2(430));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx6 on worklog5743_1(a1, a2(428));
show warnings;
# Test edge cases for indexes using key_block_size=2
set global innodb_large_prefix=0;
# Check index creation behavior without STRICT mode
SET sql_mode= '';
create index idx1 on worklog5743_2(a2(4000));
show warnings;
set global innodb_large_prefix=1;
-- error ER_TOO_BIG_ROWSIZE
create index idx2 on worklog5743_2(a2(4000));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx3 on worklog5743_2(a2(948));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx4 on worklog5743_2(a2(946));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx5 on worklog5743_2(a1, a2(942));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx6 on worklog5743_2(a1, a2(940));
show warnings;
# Test edge cases for indexes using key_block_size=4
set global innodb_large_prefix=0;
create index idx1 on worklog5743_4(a2(4000));
show warnings;
set global innodb_large_prefix=1;
-- error ER_TOO_BIG_ROWSIZE
create index idx2 on worklog5743_4(a2(4000));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx3 on worklog5743_4(a2(1972));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx4 on worklog5743_4(a2(1970));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx5 on worklog5743_4(a1, a2(1966));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx6 on worklog5743_4(a1, a2(1964));
show warnings;
# Test edge cases for indexes using key_block_size=8
set global innodb_large_prefix=0;
create index idx1 on worklog5743_8(a2(1000));
show warnings;
set global innodb_large_prefix=1;
create index idx2 on worklog5743_8(a2(3073));
show warnings;
create index idx3 on worklog5743_8(a2(3072));
show warnings;
-- error ER_TOO_LONG_KEY
create index idx4 on worklog5743_8(a1, a2(3069));
show warnings;
create index idx5 on worklog5743_8(a1, a2(3068));
show warnings;
-- error ER_TOO_LONG_KEY
create index idx6 on worklog5743_8(a1, a2(2000), a3(1069));
show warnings;
create index idx7 on worklog5743_8(a1, a2(2000), a3(1068));
show warnings;
# Test edge cases for indexes using key_block_size=16
set global innodb_large_prefix=0;
create index idx1 on worklog5743_16(a2(1000));
show warnings;
set global innodb_large_prefix=1;
create index idx2 on worklog5743_16(a2(3073));
show warnings;
create index idx3 on worklog5743_16(a2(3072));
show warnings;
-- error ER_TOO_LONG_KEY
create index idx4 on worklog5743_16(a1, a2(3069));
show warnings;
create index idx5 on worklog5743_16(a1, a2(3068));
show warnings;
-- error ER_TOO_LONG_KEY
create index idx6 on worklog5743_16(a1, a2(2000), a3(1069));
show warnings;
create index idx7 on worklog5743_16(a1, a2(2000), a3(1068));
show warnings;
set sql_mode= default;
# Insert a large record into each of these tables.
insert into worklog5743_1 values(9, repeat("a", 10000));
insert into worklog5743_2 values(9, repeat("a", 10000));
insert into worklog5743_4 values(9, repeat("a", 10000));
insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
insert into worklog5743_16 values(9, repeat("a", 10000), repeat("a", 10000));
# Now if we change the global innodb_large_prefix back to 767,
# updates to these indexes should still be allowed.
set global innodb_large_prefix=0;
insert into worklog5743_1 values(2, repeat("b", 10000));
insert into worklog5743_2 values(2, repeat("b", 10000));
insert into worklog5743_4 values(2, repeat("b", 10000));
insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000));
insert into worklog5743_16 values(2, repeat("b", 10000), repeat("b", 10000));
set global innodb_large_prefix=1;
select a1, left(a2, 20) from worklog5743_1;
select a1, left(a2, 20) from worklog5743_2;
select a1, left(a2, 20) from worklog5743_4;
select a1, left(a2, 20) from worklog5743_8;
select a1, left(a2, 20) from worklog5743_16;
begin;
update worklog5743_1 set a1 = 1000;
update worklog5743_2 set a1 = 1000;
update worklog5743_4 set a1 = 1000;
update worklog5743_8 set a1 = 1000;
update worklog5743_16 set a1 = 1000;
select a1, left(a2, 20) from worklog5743_1;
select a1, left(a2, 20) from worklog5743_2;
select a1, left(a2, 20) from worklog5743_4;
select a1, left(a2, 20) from worklog5743_8;
select a1, left(a2, 20) from worklog5743_16;
# Do a select from another connection that would use the secondary index
--connection con1
select @@session.tx_isolation;
explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
# Do read uncommitted in another session, it would show there is no
# row with a1 = 9
--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
--connection default
rollback;
drop table worklog5743_1;
drop table worklog5743_2;
drop table worklog5743_4;
drop table worklog5743_8;
drop table worklog5743_16;
-- echo ### Test 5 ###
# Create a table with large varchar columns and create indexes
# directly on these large columns to show that prefix limit is
# automatically applied and to show that limit.
create table worklog5743(a1 int,
a2 varchar(20000),
a3 varchar(3073),
a4 varchar(3072),
a5 varchar(3069),
a6 varchar(3068))
ROW_FORMAT=DYNAMIC;
# Check index creation behavior without STRICT mode
SET sql_mode='';
create index idx1 on worklog5743(a2);
create index idx2 on worklog5743(a3);
create index idx3 on worklog5743(a4);
show warnings;
SET sql_mode= default;
-- error ER_TOO_LONG_KEY
create index idx4 on worklog5743(a1, a2);
show warnings;
-- error ER_TOO_LONG_KEY
create index idx5 on worklog5743(a1, a5);
show warnings;
create index idx6 on worklog5743(a1, a6);
show warnings;
show create table worklog5743;
insert into worklog5743 values(9,
repeat("a", 20000), repeat("a", 3073),
repeat("a", 3072), repeat("a", 3069),
repeat("a", 3068));
begin;
update worklog5743 set a1 = 1000;
# Do a select from another connection that would use the secondary index
--connection con1
select @@session.tx_isolation;
explain select a1 from worklog5743 where a1 = 9;
select a1 from worklog5743 where a1 = 9;
# Do read uncommitted, it would show there is no row with a1 = 9
--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a1 from worklog5743 where a1 = 9;
--connection default
rollback;
drop table worklog5743;
-- echo ### Test 6 ###
# Create a table with old format, and the limit is 768 bytes.
-- error ER_INDEX_COLUMN_TOO_LONG
create table worklog5743(a TEXT not null, primary key (a(1000)))
row_format=compact;
create table worklog5743(a TEXT)
row_format=compact;
# Excercise the column length check in ha_innobase::add_index()
-- error ER_INDEX_COLUMN_TOO_LONG
create index idx on worklog5743(a(768));
# This should be successful
create index idx on worklog5743(a(767));
# Perform some DMLs
insert into worklog5743 values(repeat("a", 20000));
begin;
insert into worklog5743 values(repeat("b", 20000));
update worklog5743 set a = (repeat("x", 25000));
# Start a new session to select the table to force it build
# an earlier version of the cluster index through undo log
select @@session.tx_isolation;
--connection con1
select a = repeat("a", 20000) from worklog5743;
--disconnect con1
--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a = repeat("x", 25000) from worklog5743;
--disconnect con2
--connection default
rollback;
drop table worklog5743;
-- echo ### Test 7 ###
# Some border line tests on the column length.
# We have a limit of 3072 bytes for Barracuda table
create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
# Length exceeds maximum supported key length
# It will be auto-truncated to 3072
# Check index creation behavior without STRICT mode
SET sql_mode='';
create index idx1 on worklog5743(a(3073));
create index idx2 on worklog5743(a(3072));
show create table worklog5743;
drop table worklog5743;
SET sql_mode= default;
# We have a limit of 767 bytes for Antelope tables
create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
-- error ER_INDEX_COLUMN_TOO_LONG
create index idx on worklog5743(a(768));
create index idx2 on worklog5743(a(767));
drop table worklog5743;
create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
-- error ER_INDEX_COLUMN_TOO_LONG
create index idx on worklog5743(a(768));
create index idx2 on worklog5743(a(767));
drop table worklog5743;
eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
|