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 --source include/have_innodb.inc
4 --source include/have_innodb_4k.inc
5 --source include/test_db_charset_latin1.inc
6 SET default_storage_engine=InnoDB;
7
8 --disable_query_log
9 call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
10 --enable_query_log
11
12 -- echo ### Test 1 ###
13 # Create a table of DYNAMIC format, with a primary index of 768 bytes in
14 # size
15 create table worklog5743(a TEXT not null, primary key (a(768))) ROW_FORMAT=DYNAMIC;
16 show warnings;
17
18 # Do some insertion and update to excercise the external cache
19 # code path
20 insert into worklog5743 values(repeat("a", 20000));
21
22 # default session, update the table
23 update worklog5743 set a = (repeat("b", 16000));
24
25 # Create a secondary index
26 SET sql_mode= '';
27 create index idx on worklog5743(a(900));
28 show warnings;
29 SET sql_mode= default;
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) ROW_FORMAT=DYNAMIC;
64 show warnings;
65 create index idx on worklog5743(a1, a2(750));
66 show warnings;
67
68 insert into worklog5743 values(9, repeat("a", 10000));
69
70 begin;
71
72 update worklog5743 set a1 = 1111;
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 = 2222;
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) KEY_BLOCK_SIZE=1;
125 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
126 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
127
128 # The maximum overall index record (not prefix) length of a
129 # compressed table is dependent on innodb-page-size (IPS),
130 # key_block_size (KBS) and the number of fields (NF).
131 # "Too big row" error (HA_ERR_TOO_BIG_ROW) will be returned if this
132 # limit is exceeded.
133 # See page_zip_empty_size() and Bug #47495 for more detail.
134
135 # Test edge cases for indexes using key_block_size=1
136 -- error ER_TOO_BIG_ROWSIZE
137 create index idx1 on worklog5743_1(a2(4000));
138 show warnings;
139 -- error ER_TOO_BIG_ROWSIZE
140 create index idx3 on worklog5743_1(a2(436));
141 show warnings;
142 # MDEV-27634 innodb_zip tests failing on s390x
143 create index idx4 on worklog5743_1(a2(290));
144 show warnings;
145 -- error ER_TOO_BIG_ROWSIZE
146 create index idx5 on worklog5743_1(a1, a2(430));
147 show warnings;
148 # MDEV-27634 innodb_zip tests failing on s390x
149 create index idx6 on worklog5743_1(a1, a2(283));
150 show warnings;
151
152 # Test edge cases for indexes using key_block_size=2
153 SET sql_mode= '';
154 set innodb_strict_mode=off;
155 create index idx1 on worklog5743_2(a2(4000));
156 set innodb_strict_mode=on;
157 show warnings;
158 show create table worklog5743_2;
159 drop index idx1 on worklog5743_2;
160 create index idx3 on worklog5743_2(a2(769));
161 show warnings;
162 create index idx4 on worklog5743_2(a2(768));
163 show warnings;
164 create index idx6 on worklog5743_2(a1, a2(768));
165 show warnings;
166 # Test edge cases for indexes using key_block_size=4
167 set innodb_strict_mode=off;
168 create index idx1 on worklog5743_4(a2(4000));
169 set innodb_strict_mode=on;
170 show warnings;
171 show create table worklog5743_4;
172 create index idx3 on worklog5743_4(a2(769));
173 show warnings;
174 create index idx4 on worklog5743_4(a2(768));
175 show warnings;
176 create index idx6 on worklog5743_4(a1, a2(764));
177 show warnings;
178 SET sql_mode= default;
179 # Insert a large record into each of these tables.
180 insert into worklog5743_1 values(9, repeat("a", 10000));
181 insert into worklog5743_2 values(9, repeat("a", 10000));
182 insert into worklog5743_4 values(9, repeat("a", 10000));
183
184 select a1, left(a2, 20) from worklog5743_1;
185 select a1, left(a2, 20) from worklog5743_2;
186 select a1, left(a2, 20) from worklog5743_4;
187
188 begin;
189
190 update worklog5743_1 set a1 = 1000;
191 update worklog5743_2 set a1 = 1000;
192 update worklog5743_4 set a1 = 1000;
193 select a1, left(a2, 20) from worklog5743_1;
194 select a1, left(a2, 20) from worklog5743_2;
195 select a1, left(a2, 20) from worklog5743_4;
196
197 # Do a select from another connection that would use the secondary index
198 --connection con1
199 select @@session.transaction_isolation;
200 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
201 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
202 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
203 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
204 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
205 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
206
207 # Do read uncommitted in another session, it would show there is no
208 # row with a1 = 9
209 --connection con2
210 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
211 select @@session.transaction_isolation;
212 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
213 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
214 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
215
216 --connection default
217 rollback;
218
219 drop table worklog5743_1;
220 drop table worklog5743_2;
221 drop table worklog5743_4;
222
223 -- echo ### Test 5 ###
224 # Create a table with large varchar columns and create indexes
225 # directly on these large columns to show that prefix limit is
226 # automatically applied and to show that limit.
227
228 # This commented form of the test causes an unlimited page split
229 # on update of the int field - Bug 12636590 - INNODB; UPDATE OF
230 # LARGE RECORD CAUSES UNLIMITED PAGE SPLITS IN 8K PAGE SIZE
231 #create table worklog5743(a1 int,
232 # a2 varchar(20000),
233 # a3 varchar(3073),
234 # a4 varchar(3072),
235 # a5 varchar(3069),
236 # a6 varchar(3068))
237 # ROW_FORMAT=DYNAMIC;
238 #create index idx1 on worklog5743(a2);
239 #create index idx2 on worklog5743(a3);
240 #create index idx3 on worklog5743(a4);
241 #show warnings;
242 #-- error ER_TOO_LONG_KEY
243 #create index idx4 on worklog5743(a1, a2);
244 #show warnings;
245 #-- error ER_TOO_LONG_KEY
246 #create index idx5 on worklog5743(a1, a5);
247 #show warnings;
248 #create index idx6 on worklog5743(a1, a6);
249 #show warnings;
250 #show create table worklog5743;
251 #
252 #insert into worklog5743 values(9,
253 # repeat("a", 20000), repeat("a", 3073),
254 # repeat("a", 3072), repeat("a", 3069),
255 # repeat("a", 3068));
256 #
257
258 create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
259 -- error ER_TOO_LONG_KEY
260 create index idx1 on worklog5743(a2);
261 show warnings;
262 drop table worklog5743;
263
264 create table worklog5743(a1 int, a2 varchar(3072)) 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(769)) ROW_FORMAT=DYNAMIC;
271 create index idx1 on worklog5743(a2);
272 show warnings;
273 drop table worklog5743;
274
275 create table worklog5743(a1 int, a2 varchar(768)) ROW_FORMAT=DYNAMIC;
276 create index idx1 on worklog5743(a2);
277 show warnings;
278 insert into worklog5743 values(9, repeat("a", 768));
279 update worklog5743 set a1 = 3333;
280 drop table worklog5743;
281
282 create table worklog5743(a1 int, a2 varchar(765)) ROW_FORMAT=DYNAMIC;
283 create index idx1 on worklog5743(a1, a2);
284 show warnings;
285 drop table worklog5743;
286
287 create table worklog5743(a1 int, a2 varchar(764)) ROW_FORMAT=DYNAMIC;
288 create index idx1 on worklog5743(a1, a2);
289 show warnings;
290 insert into worklog5743 values(9, repeat("a", 764));
291
292 begin;
293 update worklog5743 set a1 = 4444;
294
295 # Do a select from another connection that would use the secondary index
296 --connection con1
297 select @@session.transaction_isolation;
298 explain select a1 from worklog5743 where a1 = 9;
299 select a1 from worklog5743 where a1 = 9;
300
301 # Do read uncommitted, it would show there is no row with a1 = 9
302 --connection con2
303 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
304 select @@session.transaction_isolation;
305 select a1 from worklog5743 where a1 = 9;
306
307 --connection default
308 rollback;
309
310 drop table worklog5743;
311
312 -- echo ### Test 6 ###
313 create table worklog5743(a TEXT not null, primary key (a(1000)));
314 drop table worklog5743;
315
316 create table worklog5743(a TEXT) ROW_FORMAT=COMPACT;
317
318 # Excercise the column length check in ha_innobase::add_index()
319 -- error ER_INDEX_COLUMN_TOO_LONG
320 create index idx on worklog5743(a(768));
321
322 # This should be successful
323 create index idx on worklog5743(a(767));
324
325 # Perform some DMLs
326 insert into worklog5743 values(repeat("a", 20000));
327
328 begin;
329 insert into worklog5743 values(repeat("b", 20000));
330 update worklog5743 set a = (repeat("x", 25000));
331
332 # Start a new session to select the table to force it build
333 # an earlier version of the cluster index through undo log
334 select @@session.transaction_isolation;
335 --connection con1
336 select a = repeat("a", 20000) from worklog5743;
337 --disconnect con1
338
339 --connection con2
340 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
341 select @@session.transaction_isolation;
342 select a = repeat("x", 25000) from worklog5743;
343 --disconnect con2
344
345 --connection default
346 rollback;
347
348 drop table worklog5743;
349
350 -- echo ### Test 7 ###
351 # Some border line tests on the column length.
352 # We have a limit of 3072 bytes for Barracuda table
353 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
354
355 # Length exceeds maximum supported key length
356 # It will be auto-truncated to 3072 if the page size were not 4k.
357 # With this page size, the prefix length is less.
358 SET sql_mode= '';
359 create index idx1 on worklog5743(a(769));
360 show warnings;
361 SET sql_mode= default;
362 create index idx2 on worklog5743(a(768));
363 show warnings;
364 show create table worklog5743;
365 insert into worklog5743 values(repeat("a", 768));
366 drop table worklog5743;
367
368 # We have a limit of 767 bytes for Antelope tables
369 create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
370 -- error ER_INDEX_COLUMN_TOO_LONG
371 create index idx on worklog5743(a(768));
372 create index idx2 on worklog5743(a(767));
373 drop table worklog5743;
374
375 create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
376 -- error ER_INDEX_COLUMN_TOO_LONG
377 create index idx on worklog5743(a(768));
378 create index idx2 on worklog5743(a(767));
379 drop table worklog5743;
380
381 --source include/test_db_charset_restore.inc