Debian Codegraph @

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