Debian Codegraph @

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