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 --source include/have_innodb.inc
4 --source include/have_innodb_16k.inc
5 --source include/test_db_charset_latin1.inc
6
7 SET default_storage_engine=InnoDB;
8
9 --disable_query_log
10 call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
11 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
12 SET GLOBAL innodb_read_only_compressed=OFF;
13 --enable_query_log
14
15 -- echo ### Test 1 ###
16 # Create a table of DYNAMIC format, with a primary index of 1000 bytes in
17 # size
18 create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
19 show warnings;
20
21 # Do some insertion and update to excercise the external cache
22 # code path
23 insert into worklog5743 values(repeat("a", 20000));
24
25 # default session, update the table
26 update worklog5743 set a = (repeat("b", 16000));
27
28 # Create a secondary index
29 create index idx on worklog5743(a(2000));
30 show warnings;
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) ROW_FORMAT=DYNAMIC;
66 show warnings;
67 create index idx on worklog5743(a1, a2(2000));
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) KEY_BLOCK_SIZE=1;
127 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
128 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
129 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
130 create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=16;
131
132 # The maximum overall index record (not prefix) length of a
133 # compressed table is dependent on innodb-page-size (IPS),
134 # key_block_size (KBS) and the number of fields (NF).
135 # "Too big row" error (HA_ERR_TOO_BIG_ROW) will be returned if this
136 # limit is exceeded.
137 # See page_zip_empty_size() and Bug #47495 for more detail.
138
139 # Test edge cases for indexes using key_block_size=1
140 -- error ER_TOO_LONG_KEY,1118
141 create index idx2 on worklog5743_1(a2(4000));
142 show warnings;
143 -- error ER_TOO_BIG_ROWSIZE
144 create index idx3 on worklog5743_1(a2(436));
145 show warnings;
146 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
147 create index idx4 on worklog5743_1(a2(290));
148 show warnings;
149 -- error ER_TOO_BIG_ROWSIZE
150 create index idx5 on worklog5743_1(a1, a2(430));
151 show warnings;
152 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
153 create index idx6 on worklog5743_1(a1, a2(283));
154 show warnings;
155
156 # Test edge cases for indexes using key_block_size=2
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 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
164 create index idx4 on worklog5743_2(a2(802));
165 show warnings;
166 -- error ER_TOO_BIG_ROWSIZE
167 create index idx5 on worklog5743_2(a1, a2(942));
168 show warnings;
169 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
170 create index idx6 on worklog5743_2(a1, a2(795));
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 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
181 create index idx4 on worklog5743_4(a2(1826));
182 show warnings;
183 -- error ER_TOO_BIG_ROWSIZE
184 create index idx5 on worklog5743_4(a1, a2(1966));
185 show warnings;
186 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
187 create index idx6 on worklog5743_4(a1, a2(1819));
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 select a1, left(a2, 20) from worklog5743_1;
231 select a1, left(a2, 20) from worklog5743_2;
232 select a1, left(a2, 20) from worklog5743_4;
233 select a1, left(a2, 20) from worklog5743_8;
234 select a1, left(a2, 20) from worklog5743_16;
235
236 begin;
237
238 update worklog5743_1 set a1 = 1000;
239 update worklog5743_2 set a1 = 1000;
240 update worklog5743_4 set a1 = 1000;
241 update worklog5743_8 set a1 = 1000;
242 update worklog5743_16 set a1 = 1000;
243 select a1, left(a2, 20) from worklog5743_1;
244 select a1, left(a2, 20) from worklog5743_2;
245 select a1, left(a2, 20) from worklog5743_4;
246 select a1, left(a2, 20) from worklog5743_8;
247 select a1, left(a2, 20) from worklog5743_16;
248
249
250 # Do a select from another connection that would use the secondary index
251 --connection con1
252 select @@session.transaction_isolation;
253 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
254 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
255 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
256 explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
257 explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
258 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
259 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
260 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
261 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
262 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
263
264 # Do read uncommitted in another session, it would show there is no
265 # row with a1 = 9
266 --connection con2
267 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
268 select @@session.transaction_isolation;
269 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
270 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
271 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
272 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
273 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
274
275 --connection default
276 rollback;
277
278 drop table worklog5743_1;
279 drop table worklog5743_2;
280 drop table worklog5743_4;
281 drop table worklog5743_8;
282 drop table worklog5743_16;
283
284 -- echo ### Test 5 ###
285 # Create a table with large varchar columns and create indexes
286 # directly on these large columns to show that prefix limit is
287 # automatically applied and to show that limit.
288 create table worklog5743(a1 int,
289 a2 varchar(20000),
290 a3 varchar(3073),
291 a4 varchar(3072),
292 a5 varchar(3069),
293 a6 varchar(3068))
294 ROW_FORMAT=DYNAMIC;
295 # Check index creation behavior without STRICT mode
296 SET sql_mode='';
297 create index idx1 on worklog5743(a2);
298 create index idx2 on worklog5743(a3);
299 create index idx3 on worklog5743(a4);
300 show warnings;
301 SET sql_mode= default;
302 -- error ER_TOO_LONG_KEY
303 create index idx4 on worklog5743(a1, a2);
304 show warnings;
305 -- error ER_TOO_LONG_KEY
306 create index idx5 on worklog5743(a1, a5);
307 show warnings;
308 create index idx6 on worklog5743(a1, a6);
309 show warnings;
310 show create table worklog5743;
311
312 insert into worklog5743 values(9,
313 repeat("a", 20000), repeat("a", 3073),
314 repeat("a", 3072), repeat("a", 3069),
315 repeat("a", 3068));
316
317 begin;
318
319 update worklog5743 set a1 = 1000;
320
321 # Do a select from another connection that would use the secondary index
322 --connection con1
323 select @@session.transaction_isolation;
324 explain select a1 from worklog5743 where a1 = 9;
325 select a1 from worklog5743 where a1 = 9;
326
327 # Do read uncommitted, it would show there is no row with a1 = 9
328 --connection con2
329 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
330 select @@session.transaction_isolation;
331 select a1 from worklog5743 where a1 = 9;
332
333 --connection default
334 rollback;
335
336 drop table worklog5743;
337
338 -- echo ### Test 6 ###
339 # Create a table with old format, and the limit is 768 bytes.
340 -- error ER_INDEX_COLUMN_TOO_LONG
341 create table worklog5743(a TEXT not null, primary key (a(1000)))
342 row_format=compact;
343
344 create table worklog5743(a TEXT)
345 row_format=compact;
346
347 # Excercise the column length check in ha_innobase::add_index()
348 -- error ER_INDEX_COLUMN_TOO_LONG
349 create index idx on worklog5743(a(768));
350
351 # This should be successful
352 create index idx on worklog5743(a(767));
353
354 # Perform some DMLs
355 insert into worklog5743 values(repeat("a", 20000));
356
357 begin;
358 insert into worklog5743 values(repeat("b", 20000));
359 update worklog5743 set a = (repeat("x", 25000));
360
361 # Start a new session to select the table to force it build
362 # an earlier version of the cluster index through undo log
363 select @@session.transaction_isolation;
364 --connection con1
365 select a = repeat("a", 20000) from worklog5743;
366 --disconnect con1
367
368 --connection con2
369 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
370 select @@session.transaction_isolation;
371 select a = repeat("x", 25000) from worklog5743;
372 --disconnect con2
373
374 --connection default
375 rollback;
376
377 drop table worklog5743;
378
379 -- echo ### Test 7 ###
380 # Some border line tests on the column length.
381 # We have a limit of 3072 bytes for Barracuda table
382 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
383
384 # Length exceeds maximum supported key length
385 # It will be auto-truncated to 3072
386 # Check index creation behavior without STRICT mode
387 SET sql_mode='';
388 create index idx1 on worklog5743(a(3073));
389 create index idx2 on worklog5743(a(3072));
390 show create table worklog5743;
391 drop table worklog5743;
392 SET sql_mode= default;
393
394 # We have a limit of 767 bytes for Antelope tables
395 create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
396 -- error ER_INDEX_COLUMN_TOO_LONG
397 create index idx on worklog5743(a(768));
398 create index idx2 on worklog5743(a(767));
399 drop table worklog5743;
400
401 create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
402 -- error ER_INDEX_COLUMN_TOO_LONG
403 create index idx on worklog5743(a(768));
404 create index idx2 on worklog5743(a(767));
405 --disable_query_log
406 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
407 --enable_query_log
408 drop table worklog5743;
409
410 --source include/test_db_charset_restore.inc