Debian Codegraph @

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