Debian Codegraph @

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

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