Debian Codegraph @

mysql-test/suite/innodb_zip/t/index_large_prefix.test

raw
1 # Testcase for worklog #5743: Lift the limit of index key prefixes
2
3 SET default_storage_engine=InnoDB;
4
5 --disable_query_log
6 call mtr.add_suppression("Cannot alter the table .* because the record size will exceed the maximum allowed size for a record .*");
7 --enable_query_log
8
9 let $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
10
11 set global innodb_file_per_table=1;
12
13 -- echo ### Test 1 ###
14 # Create a table of DYNAMIC format, with a primary index of 1000 bytes in
15 # size
16 create table worklog5743(a TEXT not null, primary key (a(1000))) charset latin1 ROW_FORMAT=DYNAMIC;
17 show warnings;
18
19 # Do some insertion and update to excercise the external cache
20 # code path
21 insert into worklog5743 values(repeat("a", 20000));
22
23 # default session, update the table
24 update worklog5743 set a = (repeat("b", 16000));
25
26 # Create a secondary index
27 create index idx on worklog5743(a(2000));
28 show warnings;
29
30 # Start a few sessions to do selections on table being updated in default
31 # session, so it would rebuild the previous version from undo log.
32 # 1) Default session: Initiate an update on the externally stored column
33 # 2) Session con1: Select from table with repeated read
34 # 3) Session con2: Select from table with read uncommitted
35 # 4) Default session: rollback updates
36
37 begin;
38 update worklog5743 set a = (repeat("x", 17000));
39
40 # Start a new session to select the column to force it build
41 # an earlier version of the clustered index through undo log. So it should
42 # just see the result of repeat("b", 16000)
43 select @@session.transaction_isolation;
44 --connect (con1,localhost,root,,)
45 select a = repeat("x", 17000) from worklog5743;
46 select a = repeat("b", 16000) from worklog5743;
47
48 # Start another session doing "read uncommitted" query, it
49 # should see the uncommitted update
50 --connect (con2,localhost,root,,)
51 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
52 select @@session.transaction_isolation;
53 select a = repeat("x", 17000) from worklog5743;
54
55 # Roll back the transaction
56 --connection default
57 rollback;
58
59 drop table worklog5743;
60
61 -- echo ### Test 2 ###
62 # Create a table with only a secondary index has large prefix column
63 create table worklog5743(a1 int, a2 TEXT not null) charset latin1 ROW_FORMAT=DYNAMIC;
64 show warnings;
65 create index idx on worklog5743(a1, a2(2000));
66 show warnings;
67
68 insert into worklog5743 values(9, repeat("a", 10000));
69
70 begin;
71
72 update worklog5743 set a1 = 1000;
73
74 # Do a select from another connection that would use the secondary index
75 --connection con1
76 select @@session.transaction_isolation;
77 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
78 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
79
80 # Do read uncommitted in another session, it would show there is no
81 # row with a1 = 9
82 --connection con2
83 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
84 select @@session.transaction_isolation;
85 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
86
87 --connection default
88 rollback;
89
90 drop table worklog5743;
91
92 -- echo ### Test 3 ###
93 # Create a table with a secondary index has small (50 bytes) prefix column
94 create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
95
96 create index idx on worklog5743(a1, a2(50));
97
98 insert into worklog5743 values(9, repeat("a", 10000));
99
100 begin;
101
102 update worklog5743 set a1 = 1000;
103
104 # Do a select from another connection that would use the secondary index
105 --connection con1
106 select @@session.transaction_isolation;
107 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
108 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
109
110 # Do read uncommitted in another session, it would show there is no
111 # row with a1 = 9
112 --connection con2
113 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
114 select @@session.transaction_isolation;
115 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
116
117 --connection default
118 rollback;
119
120 drop table worklog5743;
121
122 -- echo ### Test 4 ###
123 # Create compressed tables with each KEY_BLOCK_SIZE.
124 create table worklog5743_1(a1 int, a2 TEXT not null) charset latin1 KEY_BLOCK_SIZE=1;
125 create table worklog5743_2(a1 int, a2 TEXT not null) charset latin1 KEY_BLOCK_SIZE=2;
126 create table worklog5743_4(a1 int, a2 TEXT not null) charset latin1 KEY_BLOCK_SIZE=4;
127 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) charset latin1 KEY_BLOCK_SIZE=8;
128 create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) charset latin1 KEY_BLOCK_SIZE=16;
129
130 # The maximum overall index record (not prefix) length of a
131 # compressed table is dependent on innodb-page-size (IPS),
132 # key_block_size (KBS) and the number of fields (NF).
133 # "Too big row" error (HA_ERR_TOO_BIG_ROW) will be returned if this
134 # limit is exceeded.
135 # See page_zip_empty_size() and Bug #47495 for more detail.
136
137 # Test edge cases for indexes using key_block_size=1
138 -- error ER_TOO_LONG_KEY
139 create index idx2 on worklog5743_1(a2(4000));
140 show warnings;
141 -- error ER_TOO_BIG_ROWSIZE
142 create index idx3 on worklog5743_1(a2(436));
143 show warnings;
144 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
145 create index idx4 on worklog5743_1(a2(434));
146 show warnings;
147 -- error ER_TOO_BIG_ROWSIZE
148 create index idx5 on worklog5743_1(a1, a2(430));
149 show warnings;
150 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
151 create index idx6 on worklog5743_1(a1, a2(428));
152 show warnings;
153
154 # Test edge cases for indexes using key_block_size=2
155 # Check index creation behavior without STRICT mode
156 SET sql_mode= '';
157 -- error ER_TOO_BIG_ROWSIZE
158 create index idx2 on worklog5743_2(a2(4000));
159 show warnings;
160 -- error ER_TOO_BIG_ROWSIZE
161 create index idx3 on worklog5743_2(a2(948));
162 show warnings;
163 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
164 create index idx4 on worklog5743_2(a2(946));
165 show warnings;
166 -- error ER_TOO_BIG_ROWSIZE
167 create index idx5 on worklog5743_2(a1, a2(942));
168 show warnings;
169 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
170 create index idx6 on worklog5743_2(a1, a2(940));
171 show warnings;
172
173 # Test edge cases for indexes using key_block_size=4
174 -- error ER_TOO_BIG_ROWSIZE
175 create index idx2 on worklog5743_4(a2(4000));
176 show warnings;
177 -- error ER_TOO_BIG_ROWSIZE
178 create index idx3 on worklog5743_4(a2(1972));
179 show warnings;
180 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
181 create index idx4 on worklog5743_4(a2(1970));
182 show warnings;
183 -- error ER_TOO_BIG_ROWSIZE
184 create index idx5 on worklog5743_4(a1, a2(1966));
185 show warnings;
186 # Bug#13391353 Limit is one byte less on on 32bit-Linux only
187 create index idx6 on worklog5743_4(a1, a2(1964));
188 show warnings;
189
190 # Test edge cases for indexes using key_block_size=8
191 create index idx2 on worklog5743_8(a2(3073));
192 show warnings;
193 create index idx3 on worklog5743_8(a2(3072));
194 show warnings;
195 -- error ER_TOO_LONG_KEY
196 create index idx4 on worklog5743_8(a1, a2(3069));
197 show warnings;
198 create index idx5 on worklog5743_8(a1, a2(3068));
199 show warnings;
200 -- error ER_TOO_LONG_KEY
201 create index idx6 on worklog5743_8(a1, a2(2000), a3(1069));
202 show warnings;
203 create index idx7 on worklog5743_8(a1, a2(2000), a3(1068));
204 show warnings;
205
206 # Test edge cases for indexes using key_block_size=16
207 create index idx2 on worklog5743_16(a2(3073));
208 show warnings;
209 create index idx3 on worklog5743_16(a2(3072));
210 show warnings;
211 -- error ER_TOO_LONG_KEY
212 create index idx4 on worklog5743_16(a1, a2(3069));
213 show warnings;
214 create index idx5 on worklog5743_16(a1, a2(3068));
215 show warnings;
216 -- error ER_TOO_LONG_KEY
217 create index idx6 on worklog5743_16(a1, a2(2000), a3(1069));
218 show warnings;
219 create index idx7 on worklog5743_16(a1, a2(2000), a3(1068));
220 show warnings;
221 set sql_mode= default;
222
223 # Insert a large record into each of these tables.
224 insert into worklog5743_1 values(9, repeat("a", 10000));
225 insert into worklog5743_2 values(9, repeat("a", 10000));
226 insert into worklog5743_4 values(9, repeat("a", 10000));
227 insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
228 insert into worklog5743_16 values(9, repeat("a", 10000), repeat("a", 10000));
229
230 insert into worklog5743_1 values(2, repeat("b", 10000));
231 insert into worklog5743_2 values(2, repeat("b", 10000));
232 insert into worklog5743_4 values(2, repeat("b", 10000));
233 insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000));
234 insert into worklog5743_16 values(2, repeat("b", 10000), repeat("b", 10000));
235
236 select a1, left(a2, 20) from worklog5743_1;
237 select a1, left(a2, 20) from worklog5743_2;
238 select a1, left(a2, 20) from worklog5743_4;
239 select a1, left(a2, 20) from worklog5743_8;
240 select a1, left(a2, 20) from worklog5743_16;
241
242 begin;
243
244 update worklog5743_1 set a1 = 1000;
245 update worklog5743_2 set a1 = 1000;
246 update worklog5743_4 set a1 = 1000;
247 update worklog5743_8 set a1 = 1000;
248 update worklog5743_16 set a1 = 1000;
249 select a1, left(a2, 20) from worklog5743_1;
250 select a1, left(a2, 20) from worklog5743_2;
251 select a1, left(a2, 20) from worklog5743_4;
252 select a1, left(a2, 20) from worklog5743_8;
253 select a1, left(a2, 20) from worklog5743_16;
254
255
256 # Do a select from another connection that would use the secondary index
257 --connection con1
258 select @@session.transaction_isolation;
259 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
260 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
261 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
262 explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
263 explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
264 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
265 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
266 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
267 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
268 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
269
270 # Do read uncommitted in another session, it would show there is no
271 # row with a1 = 9
272 --connection con2
273 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
274 select @@session.transaction_isolation;
275 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
276 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
277 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
278 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
279 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
280
281 --connection default
282 rollback;
283
284 drop table worklog5743_1;
285 drop table worklog5743_2;
286 drop table worklog5743_4;
287 drop table worklog5743_8;
288 drop table worklog5743_16;
289
290 -- echo ### Test 5 ###
291 # Create a table with large varchar columns and create indexes
292 # directly on these large columns to show that prefix limit is
293 # automatically applied and to show that limit.
294 create table worklog5743(a1 int,
295 a2 varchar(20000),
296 a3 varchar(3073),
297 a4 varchar(3072),
298 a5 varchar(3069),
299 a6 varchar(3068))
300 charset latin1
301 ROW_FORMAT=DYNAMIC;
302 # Check index creation behavior without STRICT mode
303 SET sql_mode='';
304 create index idx1 on worklog5743(a2);
305 create index idx2 on worklog5743(a3);
306 create index idx3 on worklog5743(a4);
307 show warnings;
308 SET sql_mode= default;
309 -- error ER_TOO_LONG_KEY
310 create index idx4 on worklog5743(a1, a2);
311 show warnings;
312 -- error ER_TOO_LONG_KEY
313 create index idx5 on worklog5743(a1, a5);
314 show warnings;
315 create index idx6 on worklog5743(a1, a6);
316 show warnings;
317 show create table worklog5743;
318
319 insert into worklog5743 values(9,
320 repeat("a", 20000), repeat("a", 3073),
321 repeat("a", 3072), repeat("a", 3069),
322 repeat("a", 3068));
323
324 begin;
325
326 update worklog5743 set a1 = 1000;
327
328 # Do a select from another connection that would use the secondary index
329 --connection con1
330 select @@session.transaction_isolation;
331 explain select a1 from worklog5743 where a1 = 9;
332 select a1 from worklog5743 where a1 = 9;
333
334 # Do read uncommitted, it would show there is no row with a1 = 9
335 --connection con2
336 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
337 select @@session.transaction_isolation;
338 select a1 from worklog5743 where a1 = 9;
339
340 --connection default
341 rollback;
342
343 drop table worklog5743;
344
345 -- echo ### Test 6 ###
346 # Create a table with old format, and the limit is 768 bytes.
347 -- error ER_TOO_LONG_KEY
348 create table worklog5743(a TEXT not null, primary key (a(1000)))
349 charset latin1
350 row_format=compact;
351
352 create table worklog5743(a TEXT)
353 charset latin1
354 row_format=compact;
355
356 # Excercise the column length check in ha_innobase::add_index()
357 -- error ER_TOO_LONG_KEY
358 create index idx on worklog5743(a(768));
359
360 # This should be successful
361 create index idx on worklog5743(a(767));
362
363 # Perform some DMLs
364 insert into worklog5743 values(repeat("a", 20000));
365
366 begin;
367 insert into worklog5743 values(repeat("b", 20000));
368 update worklog5743 set a = (repeat("x", 25000));
369
370 # Start a new session to select the table to force it build
371 # an earlier version of the cluster index through undo log
372 select @@session.transaction_isolation;
373 --connection con1
374 select a = repeat("a", 20000) from worklog5743;
375 --disconnect con1
376
377 --connection con2
378 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
379 select @@session.transaction_isolation;
380 select a = repeat("x", 25000) from worklog5743;
381 --disconnect con2
382
383 --connection default
384 rollback;
385
386 drop table worklog5743;
387
388 -- echo ### Test 7 ###
389 # Some border line tests on the column length.
390 # We have a limit of 3072 bytes for Barracuda table
391 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
392
393 # Length exceeds maximum supported key length
394 # It will be auto-truncated to 3072
395 # Check index creation behavior without STRICT mode
396 SET sql_mode='';
397 create index idx1 on worklog5743(a(3073));
398 create index idx2 on worklog5743(a(3072));
399 show create table worklog5743;
400 drop table worklog5743;
401 SET sql_mode= default;
402
403 # We have a limit of 767 bytes for Antelope tables
404 create table worklog5743(a TEXT not null) charset latin1 ROW_FORMAT=REDUNDANT;
405 -- error ER_TOO_LONG_KEY
406 create index idx on worklog5743(a(768));
407 create index idx2 on worklog5743(a(767));
408 drop table worklog5743;
409
410 create table worklog5743(a TEXT not null) charset latin1 ROW_FORMAT=COMPACT;
411 -- error ER_TOO_LONG_KEY
412 create index idx on worklog5743(a(768));
413 create index idx2 on worklog5743(a(767));
414 drop table worklog5743;
415
416
417 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;