Debian Codegraph @

mysql-test/suite/innodb_zip/r/index_large_prefix.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 SET default_storage_engine=InnoDB;
3 ### Test 1 ###
4 create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
5 show warnings;
6 Level Code Message
7 insert into worklog5743 values(repeat("a", 20000));
8 update worklog5743 set a = (repeat("b", 16000));
9 create index idx on worklog5743(a(2000));
10 show warnings;
11 Level Code Message
12 begin;
13 update worklog5743 set a = (repeat("x", 17000));
14 select @@session.transaction_isolation;
15 @@session.transaction_isolation
16 REPEATABLE-READ
17 connect con1,localhost,root,,;
18 select a = repeat("x", 17000) from worklog5743;
19 a = repeat("x", 17000)
20 0
21 select a = repeat("b", 16000) from worklog5743;
22 a = repeat("b", 16000)
23 1
24 connect con2,localhost,root,,;
25 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
26 select @@session.transaction_isolation;
27 @@session.transaction_isolation
28 READ-UNCOMMITTED
29 select a = repeat("x", 17000) from worklog5743;
30 a = repeat("x", 17000)
31 1
32 connection default;
33 rollback;
34 drop table worklog5743;
35 ### Test 2 ###
36 create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
37 show warnings;
38 Level Code Message
39 create index idx on worklog5743(a1, a2(2000));
40 show warnings;
41 Level Code Message
42 insert into worklog5743 values(9, repeat("a", 10000));
43 begin;
44 update worklog5743 set a1 = 1000;
45 connection con1;
46 select @@session.transaction_isolation;
47 @@session.transaction_isolation
48 REPEATABLE-READ
49 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
50 id select_type table type possible_keys key key_len ref rows Extra
51 1 SIMPLE worklog5743 ref idx idx 5 const 1
52 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
53 a1 a2 = repeat("a", 10000)
54 9 1
55 connection con2;
56 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
57 select @@session.transaction_isolation;
58 @@session.transaction_isolation
59 READ-UNCOMMITTED
60 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
61 a1 a2 = repeat("a", 10000)
62 connection default;
63 rollback;
64 drop table worklog5743;
65 ### Test 3 ###
66 create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
67 create index idx on worklog5743(a1, a2(50));
68 insert into worklog5743 values(9, repeat("a", 10000));
69 begin;
70 update worklog5743 set a1 = 1000;
71 connection con1;
72 select @@session.transaction_isolation;
73 @@session.transaction_isolation
74 REPEATABLE-READ
75 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
76 id select_type table type possible_keys key key_len ref rows Extra
77 1 SIMPLE worklog5743 ref idx idx 5 const 1
78 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
79 a1 a2 = repeat("a", 10000)
80 9 1
81 connection con2;
82 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
83 select @@session.transaction_isolation;
84 @@session.transaction_isolation
85 READ-UNCOMMITTED
86 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
87 a1 a2 = repeat("a", 10000)
88 connection default;
89 rollback;
90 drop table worklog5743;
91 ### Test 4 ###
92 create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
93 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
94 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
95 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
96 create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=16;
97 create index idx2 on worklog5743_1(a2(4000));
98 Got one of the listed errors
99 show warnings;
100 Level Code Message
101 Note 1071 Specified key was too long; max key length is 3072 bytes
102 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
103 create index idx3 on worklog5743_1(a2(436));
104 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
105 show warnings;
106 Level Code Message
107 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
108 create index idx4 on worklog5743_1(a2(290));
109 show warnings;
110 Level Code Message
111 create index idx5 on worklog5743_1(a1, a2(430));
112 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
113 show warnings;
114 Level Code Message
115 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
116 create index idx6 on worklog5743_1(a1, a2(283));
117 show warnings;
118 Level Code Message
119 create index idx2 on worklog5743_2(a2(4000));
120 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
121 show warnings;
122 Level Code Message
123 Note 1071 Specified key was too long; max key length is 3072 bytes
124 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
125 create index idx3 on worklog5743_2(a2(948));
126 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
127 show warnings;
128 Level Code Message
129 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
130 create index idx4 on worklog5743_2(a2(802));
131 show warnings;
132 Level Code Message
133 create index idx5 on worklog5743_2(a1, a2(942));
134 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
135 show warnings;
136 Level Code Message
137 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
138 create index idx6 on worklog5743_2(a1, a2(795));
139 show warnings;
140 Level Code Message
141 create index idx2 on worklog5743_4(a2(4000));
142 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
143 show warnings;
144 Level Code Message
145 Note 1071 Specified key was too long; max key length is 3072 bytes
146 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
147 create index idx3 on worklog5743_4(a2(1972));
148 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
149 show warnings;
150 Level Code Message
151 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
152 create index idx4 on worklog5743_4(a2(1826));
153 show warnings;
154 Level Code Message
155 create index idx5 on worklog5743_4(a1, a2(1966));
156 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
157 show warnings;
158 Level Code Message
159 Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
160 create index idx6 on worklog5743_4(a1, a2(1819));
161 show warnings;
162 Level Code Message
163 create index idx2 on worklog5743_8(a2(3073));
164 Warnings:
165 Note 1071 Specified key was too long; max key length is 3072 bytes
166 show warnings;
167 Level Code Message
168 Note 1071 Specified key was too long; max key length is 3072 bytes
169 create index idx3 on worklog5743_8(a2(3072));
170 Warnings:
171 Note 1831 Duplicate index `idx3`. This is deprecated and will be disallowed in a future release
172 show warnings;
173 Level Code Message
174 Note 1831 Duplicate index `idx3`. This is deprecated and will be disallowed in a future release
175 create index idx4 on worklog5743_8(a1, a2(3069));
176 ERROR 42000: Specified key was too long; max key length is 3072 bytes
177 show warnings;
178 Level Code Message
179 Error 1071 Specified key was too long; max key length is 3072 bytes
180 create index idx5 on worklog5743_8(a1, a2(3068));
181 show warnings;
182 Level Code Message
183 create index idx6 on worklog5743_8(a1, a2(2000), a3(1069));
184 ERROR 42000: Specified key was too long; max key length is 3072 bytes
185 show warnings;
186 Level Code Message
187 Error 1071 Specified key was too long; max key length is 3072 bytes
188 create index idx7 on worklog5743_8(a1, a2(2000), a3(1068));
189 show warnings;
190 Level Code Message
191 create index idx2 on worklog5743_16(a2(3073));
192 Warnings:
193 Note 1071 Specified key was too long; max key length is 3072 bytes
194 show warnings;
195 Level Code Message
196 Note 1071 Specified key was too long; max key length is 3072 bytes
197 create index idx3 on worklog5743_16(a2(3072));
198 Warnings:
199 Note 1831 Duplicate index `idx3`. This is deprecated and will be disallowed in a future release
200 show warnings;
201 Level Code Message
202 Note 1831 Duplicate index `idx3`. This is deprecated and will be disallowed in a future release
203 create index idx4 on worklog5743_16(a1, a2(3069));
204 ERROR 42000: Specified key was too long; max key length is 3072 bytes
205 show warnings;
206 Level Code Message
207 Error 1071 Specified key was too long; max key length is 3072 bytes
208 create index idx5 on worklog5743_16(a1, a2(3068));
209 show warnings;
210 Level Code Message
211 create index idx6 on worklog5743_16(a1, a2(2000), a3(1069));
212 ERROR 42000: Specified key was too long; max key length is 3072 bytes
213 show warnings;
214 Level Code Message
215 Error 1071 Specified key was too long; max key length is 3072 bytes
216 create index idx7 on worklog5743_16(a1, a2(2000), a3(1068));
217 show warnings;
218 Level Code Message
219 set sql_mode= default;
220 insert into worklog5743_1 values(9, repeat("a", 10000));
221 insert into worklog5743_2 values(9, repeat("a", 10000));
222 insert into worklog5743_4 values(9, repeat("a", 10000));
223 insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
224 insert into worklog5743_16 values(9, repeat("a", 10000), repeat("a", 10000));
225 select a1, left(a2, 20) from worklog5743_1;
226 a1 left(a2, 20)
227 9 aaaaaaaaaaaaaaaaaaaa
228 select a1, left(a2, 20) from worklog5743_2;
229 a1 left(a2, 20)
230 9 aaaaaaaaaaaaaaaaaaaa
231 select a1, left(a2, 20) from worklog5743_4;
232 a1 left(a2, 20)
233 9 aaaaaaaaaaaaaaaaaaaa
234 select a1, left(a2, 20) from worklog5743_8;
235 a1 left(a2, 20)
236 9 aaaaaaaaaaaaaaaaaaaa
237 select a1, left(a2, 20) from worklog5743_16;
238 a1 left(a2, 20)
239 9 aaaaaaaaaaaaaaaaaaaa
240 begin;
241 update worklog5743_1 set a1 = 1000;
242 update worklog5743_2 set a1 = 1000;
243 update worklog5743_4 set a1 = 1000;
244 update worklog5743_8 set a1 = 1000;
245 update worklog5743_16 set a1 = 1000;
246 select a1, left(a2, 20) from worklog5743_1;
247 a1 left(a2, 20)
248 1000 aaaaaaaaaaaaaaaaaaaa
249 select a1, left(a2, 20) from worklog5743_2;
250 a1 left(a2, 20)
251 1000 aaaaaaaaaaaaaaaaaaaa
252 select a1, left(a2, 20) from worklog5743_4;
253 a1 left(a2, 20)
254 1000 aaaaaaaaaaaaaaaaaaaa
255 select a1, left(a2, 20) from worklog5743_8;
256 a1 left(a2, 20)
257 1000 aaaaaaaaaaaaaaaaaaaa
258 select a1, left(a2, 20) from worklog5743_16;
259 a1 left(a2, 20)
260 1000 aaaaaaaaaaaaaaaaaaaa
261 connection con1;
262 select @@session.transaction_isolation;
263 @@session.transaction_isolation
264 REPEATABLE-READ
265 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
266 id select_type table type possible_keys key key_len ref rows Extra
267 1 SIMPLE worklog5743_1 ref idx6 idx6 5 const 1
268 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
269 id select_type table type possible_keys key key_len ref rows Extra
270 1 SIMPLE worklog5743_2 ref idx6 idx6 5 const 1
271 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
272 id select_type table type possible_keys key key_len ref rows Extra
273 1 SIMPLE worklog5743_4 ref idx6 idx6 5 const 1
274 explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
275 id select_type table type possible_keys key key_len ref rows Extra
276 1 SIMPLE worklog5743_8 ref idx5,idx7 idx5 5 const 1
277 explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
278 id select_type table type possible_keys key key_len ref rows Extra
279 1 SIMPLE worklog5743_16 ref idx5,idx7 idx5 5 const 1
280 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
281 a1 left(a2, 20)
282 9 aaaaaaaaaaaaaaaaaaaa
283 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
284 a1 left(a2, 20)
285 9 aaaaaaaaaaaaaaaaaaaa
286 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
287 a1 left(a2, 20)
288 9 aaaaaaaaaaaaaaaaaaaa
289 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
290 a1 left(a2, 20)
291 9 aaaaaaaaaaaaaaaaaaaa
292 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
293 a1 left(a2, 20)
294 9 aaaaaaaaaaaaaaaaaaaa
295 connection con2;
296 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
297 select @@session.transaction_isolation;
298 @@session.transaction_isolation
299 READ-UNCOMMITTED
300 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
301 a1 left(a2, 20)
302 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
303 a1 left(a2, 20)
304 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
305 a1 left(a2, 20)
306 select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
307 a1 left(a2, 20)
308 select a1, left(a2, 20) from worklog5743_16 where a1 = 9;
309 a1 left(a2, 20)
310 connection default;
311 rollback;
312 drop table worklog5743_1;
313 drop table worklog5743_2;
314 drop table worklog5743_4;
315 drop table worklog5743_8;
316 drop table worklog5743_16;
317 ### Test 5 ###
318 create table worklog5743(a1 int,
319 a2 varchar(20000),
320 a3 varchar(3073),
321 a4 varchar(3072),
322 a5 varchar(3069),
323 a6 varchar(3068))
324 ROW_FORMAT=DYNAMIC;
325 SET sql_mode='';
326 create index idx1 on worklog5743(a2);
327 Warnings:
328 Note 1071 Specified key was too long; max key length is 3072 bytes
329 create index idx2 on worklog5743(a3);
330 Warnings:
331 Note 1071 Specified key was too long; max key length is 3072 bytes
332 create index idx3 on worklog5743(a4);
333 show warnings;
334 Level Code Message
335 SET sql_mode= default;
336 create index idx4 on worklog5743(a1, a2);
337 ERROR 42000: Specified key was too long; max key length is 3072 bytes
338 show warnings;
339 Level Code Message
340 Note 1071 Specified key was too long; max key length is 3072 bytes
341 Error 1071 Specified key was too long; max key length is 3072 bytes
342 create index idx5 on worklog5743(a1, a5);
343 ERROR 42000: Specified key was too long; max key length is 3072 bytes
344 show warnings;
345 Level Code Message
346 Error 1071 Specified key was too long; max key length is 3072 bytes
347 create index idx6 on worklog5743(a1, a6);
348 show warnings;
349 Level Code Message
350 show create table worklog5743;
351 Table Create Table
352 worklog5743 CREATE TABLE `worklog5743` (
353 `a1` int(11) DEFAULT NULL,
354 `a2` varchar(20000) DEFAULT NULL,
355 `a3` varchar(3073) DEFAULT NULL,
356 `a4` varchar(3072) DEFAULT NULL,
357 `a5` varchar(3069) DEFAULT NULL,
358 `a6` varchar(3068) DEFAULT NULL,
359 KEY `idx1` (`a2`(3072)),
360 KEY `idx2` (`a3`(3072)),
361 KEY `idx3` (`a4`),
362 KEY `idx6` (`a1`,`a6`)
363 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
364 insert into worklog5743 values(9,
365 repeat("a", 20000), repeat("a", 3073),
366 repeat("a", 3072), repeat("a", 3069),
367 repeat("a", 3068));
368 begin;
369 update worklog5743 set a1 = 1000;
370 connection con1;
371 select @@session.transaction_isolation;
372 @@session.transaction_isolation
373 REPEATABLE-READ
374 explain select a1 from worklog5743 where a1 = 9;
375 id select_type table type possible_keys key key_len ref rows Extra
376 1 SIMPLE worklog5743 ref idx6 idx6 5 const 1 Using index
377 select a1 from worklog5743 where a1 = 9;
378 a1
379 9
380 connection con2;
381 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
382 select @@session.transaction_isolation;
383 @@session.transaction_isolation
384 READ-UNCOMMITTED
385 select a1 from worklog5743 where a1 = 9;
386 a1
387 connection default;
388 rollback;
389 drop table worklog5743;
390 ### Test 6 ###
391 create table worklog5743(a TEXT not null, primary key (a(1000)))
392 row_format=compact;
393 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
394 create table worklog5743(a TEXT)
395 row_format=compact;
396 create index idx on worklog5743(a(768));
397 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
398 create index idx on worklog5743(a(767));
399 insert into worklog5743 values(repeat("a", 20000));
400 begin;
401 insert into worklog5743 values(repeat("b", 20000));
402 update worklog5743 set a = (repeat("x", 25000));
403 select @@session.transaction_isolation;
404 @@session.transaction_isolation
405 REPEATABLE-READ
406 connection con1;
407 select a = repeat("a", 20000) from worklog5743;
408 a = repeat("a", 20000)
409 1
410 disconnect con1;
411 connection con2;
412 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
413 select @@session.transaction_isolation;
414 @@session.transaction_isolation
415 READ-UNCOMMITTED
416 select a = repeat("x", 25000) from worklog5743;
417 a = repeat("x", 25000)
418 1
419 1
420 disconnect con2;
421 connection default;
422 rollback;
423 drop table worklog5743;
424 ### Test 7 ###
425 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
426 SET sql_mode='';
427 create index idx1 on worklog5743(a(3073));
428 Warnings:
429 Note 1071 Specified key was too long; max key length is 3072 bytes
430 create index idx2 on worklog5743(a(3072));
431 Warnings:
432 Note 1831 Duplicate index `idx2`. This is deprecated and will be disallowed in a future release
433 show create table worklog5743;
434 Table Create Table
435 worklog5743 CREATE TABLE `worklog5743` (
436 `a` text NOT NULL,
437 KEY `idx1` (`a`(3072)),
438 KEY `idx2` (`a`(3072))
439 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
440 drop table worklog5743;
441 SET sql_mode= default;
442 create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
443 create index idx on worklog5743(a(768));
444 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
445 create index idx2 on worklog5743(a(767));
446 drop table worklog5743;
447 create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
448 create index idx on worklog5743(a(768));
449 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
450 create index idx2 on worklog5743(a(767));
451 drop table worklog5743;
452 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;