Debian Codegraph @

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