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 --source include/have_innodb.inc
4 --source include/have_innodb_8k.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 1000 bytes in
14 # size
15 create table worklog5743(a TEXT not null, primary key (a(1000))) 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(2000));
28 show warnings;
29 SET sql_mode= default;
30
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) ROW_FORMAT=DYNAMIC;
65 show warnings;
66 create index idx on worklog5743(a1, a2(1250));
67 show warnings;
68
69 insert into worklog5743 values(9, repeat("a", 10000));
70
71 begin;
72
73 update worklog5743 set a1 = 1000;
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 = 1000;
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) KEY_BLOCK_SIZE=1;
126 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
127 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
128 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
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_BIG_ROWSIZE
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 # MDEV-27634 innodb_zip tests failing on s390x
145 create index idx4 on worklog5743_1(a2(290));
146 show warnings;
147 -- error ER_TOO_BIG_ROWSIZE
148 create index idx5 on worklog5743_1(a1, a2(430));
149 show warnings;
150 # MDEV-27634 innodb_zip tests failing on s390x
151 create index idx6 on worklog5743_1(a1, a2(283));
152 show warnings;
153
154 # Test edge cases for indexes using key_block_size=2
155 -- error ER_TOO_BIG_ROWSIZE
156 create index idx2 on worklog5743_2(a2(4000));
157 show warnings;
158 -- error ER_TOO_BIG_ROWSIZE
159 create index idx3 on worklog5743_2(a2(948));
160 show warnings;
161 # MDEV-27634 innodb_zip tests failing on s390x
162 create index idx4 on worklog5743_2(a2(802));
163 show warnings;
164 -- error ER_TOO_BIG_ROWSIZE
165 create index idx5 on worklog5743_2(a1, a2(942));
166 show warnings;
167 # MDEV-27634 innodb_zip tests failing on s390x
168 create index idx6 on worklog5743_2(a1, a2(795));
169 show warnings;
170
171 # Test edge cases for indexes using key_block_size=4
172 create index idx3 on worklog5743_4(a2(1537));
173 show warnings;
174 create index idx4 on worklog5743_4(a2(1536));
175 show warnings;
176 -- error ER_TOO_LONG_KEY
177 create index idx5 on worklog5743_4(a1, a2(1533));
178 show warnings;
179 create index idx6 on worklog5743_4(a1, a2(1532));
180 show warnings;
181
182 # Test edge cases for indexes using key_block_size=8
183 create index idx2 on worklog5743_8(a2(3073));
184 show warnings;
185 create index idx3 on worklog5743_8(a2(3072));
186 show warnings;
187 -- error ER_TOO_LONG_KEY
188 create index idx4 on worklog5743_8(a1, a2(1533));
189 show warnings;
190 create index idx5 on worklog5743_8(a1, a2(1532));
191 show warnings;
192 SET sql_mode= default;
193
194 # Insert a large record into each of these tables.
195 insert into worklog5743_1 values(9, repeat("a", 10000));
196 insert into worklog5743_2 values(9, repeat("a", 10000));
197 insert into worklog5743_4 values(9, repeat("a", 10000));
198 insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
199
200 select a1, left(a2, 20) from worklog5743_1;
201 select a1, left(a2, 20) from worklog5743_2;
202 select a1, left(a2, 20) from worklog5743_4;
203 select a1, left(a2, 20) from worklog5743_8;
204
205 begin;
206
207 update worklog5743_1 set a1 = 1000;
208 update worklog5743_2 set a1 = 1000;
209 update worklog5743_4 set a1 = 1000;
210 update worklog5743_8 set a1 = 1000;
211 select a1, left(a2, 20) from worklog5743_1;
212 select a1, left(a2, 20) from worklog5743_2;
213 select a1, left(a2, 20) from worklog5743_4;
214 select a1, left(a2, 20) from worklog5743_8;
215
216
217 # Do a select from another connection that would use the secondary index
218 --connection con1
219 select @@session.transaction_isolation;
220 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
221 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
222 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
223 explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
224 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
225 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
226 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
227 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
228
229 # Do read uncommitted in another session, it would show there is no
230 # row with a1 = 9
231 --connection con2
232 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
233 select @@session.transaction_isolation;
234 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
235 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
236 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
237 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
238
239 --connection default
240 rollback;
241
242 drop table worklog5743_1;
243 drop table worklog5743_2;
244 drop table worklog5743_4;
245 drop table worklog5743_8;
246
247 -- echo ### Test 5 ###
248 # Create a table with large varchar columns and create indexes
249 # directly on these large columns to show that prefix limit is
250 # automatically applied and to show that limit.
251
252 # This commented form of the test causes an unlimited page split
253 # on update of the int field - Bug 12636590 - INNODB; UPDATE OF
254 # LARGE RECORD CAUSES UNLIMITED PAGE SPLITS IN 8K PAGE SIZE
255 #create table worklog5743(a1 int,
256 # a2 varchar(20000),
257 # a3 varchar(3073),
258 # a4 varchar(3072),
259 # a5 varchar(3069),
260 # a6 varchar(3068))
261 # ROW_FORMAT=DYNAMIC;
262 #create index idx1 on worklog5743(a2);
263 #create index idx2 on worklog5743(a3);
264 #create index idx3 on worklog5743(a4);
265 #show warnings;
266 #-- error ER_TOO_LONG_KEY
267 #create index idx4 on worklog5743(a1, a2);
268 #show warnings;
269 #-- error ER_TOO_LONG_KEY
270 #create index idx5 on worklog5743(a1, a5);
271 #show warnings;
272 #create index idx6 on worklog5743(a1, a6);
273 #show warnings;
274 #show create table worklog5743;
275 #
276 #insert into worklog5743 values(9,
277 # repeat("a", 20000), repeat("a", 3073),
278 # repeat("a", 3072), repeat("a", 3069),
279 # repeat("a", 3068));
280 #
281
282 create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
283 -- error ER_TOO_LONG_KEY
284 create index idx1 on worklog5743(a2);
285 drop table worklog5743;
286
287 create table worklog5743(a1 int, a2 varchar(1537)) ROW_FORMAT=DYNAMIC;
288 -- error ER_TOO_LONG_KEY
289 create index idx1 on worklog5743(a2);
290 drop table worklog5743;
291
292 create table worklog5743(a1 int, a2 varchar(1536)) ROW_FORMAT=DYNAMIC;
293 create index idx1 on worklog5743(a2);
294 show warnings;
295 insert into worklog5743 values(9, repeat("a", 1536));
296 update worklog5743 set a1 = 1000;
297 drop table worklog5743;
298
299 create table worklog5743(a1 int, a2 varchar(1533)) ROW_FORMAT=DYNAMIC;
300 -- error ER_TOO_LONG_KEY
301 create index idx1 on worklog5743(a1, a2);
302 show warnings;
303 drop table worklog5743;
304
305 create table worklog5743(a1 int, a2 varchar(1532)) ROW_FORMAT=DYNAMIC;
306 create index idx1 on worklog5743(a1, a2);
307 show warnings;
308 insert into worklog5743 values(9, repeat("a", 1532));
309 update worklog5743 set a1 = 1000;
310
311 begin;
312 update worklog5743 set a1 = 1000;
313
314 # Do a select from another connection that would use the secondary index
315 --connection con1
316 select @@session.transaction_isolation;
317 explain select a1 from worklog5743 where a1 = 9;
318 select a1 from worklog5743 where a1 = 9;
319
320 # Do read uncommitted, it would show there is no row with a1 = 9
321 --connection con2
322 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
323 select @@session.transaction_isolation;
324 select a1 from worklog5743 where a1 = 9;
325
326 --connection default
327 rollback;
328
329 drop table worklog5743;
330
331 -- echo ### Test 6 ###
332 # Create a table with old format, and the limit is 768 bytes.
333 -- error ER_INDEX_COLUMN_TOO_LONG
334 create table worklog5743(a TEXT not null, primary key (a(1000)))
335 row_format=compact;
336
337 create table worklog5743(a TEXT) row_format=compact;
338
339 # Excercise the column length check in ha_innobase::add_index()
340 -- error ER_INDEX_COLUMN_TOO_LONG
341 create index idx on worklog5743(a(768));
342
343 # This should be successful
344 create index idx on worklog5743(a(767));
345
346 # Perform some DMLs
347 insert into worklog5743 values(repeat("a", 20000));
348
349 begin;
350 insert into worklog5743 values(repeat("b", 20000));
351 update worklog5743 set a = (repeat("x", 25000));
352
353 # Start a new session to select the table to force it build
354 # an earlier version of the cluster index through undo log
355 select @@session.transaction_isolation;
356 --connection con1
357 select a = repeat("a", 20000) from worklog5743;
358 --disconnect con1
359
360 --connection con2
361 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
362 select @@session.transaction_isolation;
363 select a = repeat("x", 25000) from worklog5743;
364 --disconnect con2
365
366 --connection default
367 rollback;
368
369 drop table worklog5743;
370
371 -- echo ### Test 7 ###
372 # Some border line tests on the column length.
373 # We have a limit of 3072 bytes for Barracuda table
374 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
375
376 # Length exceeds maximum supported key length
377 # It will be auto-truncated to 3072
378 SET sql_mode= '';
379 create index idx1 on worklog5743(a(3073));
380 create index idx2 on worklog5743(a(3072));
381 SET sql_mode= default;
382 show create table worklog5743;
383 drop table worklog5743;
384
385 # We have a limit of 767 bytes for Antelope tables
386 create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
387 -- error ER_INDEX_COLUMN_TOO_LONG
388 create index idx on worklog5743(a(768));
389 create index idx2 on worklog5743(a(767));
390 drop table worklog5743;
391
392 create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
393 -- error ER_INDEX_COLUMN_TOO_LONG
394 create index idx on worklog5743(a(768));
395 create index idx2 on worklog5743(a(767));
396 drop table worklog5743;
397
398 --source include/test_db_charset_restore.inc