Debian Codegraph @

mysql-test/suite/innodb_zip/r/index_large_prefix_4k.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(768))) 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 show warnings;
12 Level Code Message
13 SET sql_mode= default;
14 begin;
15 update worklog5743 set a = (repeat("x", 17000));
16 select @@session.transaction_isolation;
17 @@session.transaction_isolation
18 REPEATABLE-READ
19 connect con1,localhost,root,,;
20 select a = repeat("x", 17000) from worklog5743;
21 a = repeat("x", 17000)
22 0
23 select a = repeat("b", 16000) from worklog5743;
24 a = repeat("b", 16000)
25 1
26 connect con2,localhost,root,,;
27 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
28 select @@session.transaction_isolation;
29 @@session.transaction_isolation
30 READ-UNCOMMITTED
31 select a = repeat("x", 17000) from worklog5743;
32 a = repeat("x", 17000)
33 1
34 connection default;
35 rollback;
36 drop table worklog5743;
37 ### Test 2 ###
38 create table worklog5743(a1 int, a2 TEXT not null) 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 connection con1;
48 select @@session.transaction_isolation;
49 @@session.transaction_isolation
50 REPEATABLE-READ
51 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
52 id select_type table type possible_keys key key_len ref rows Extra
53 1 SIMPLE worklog5743 ref idx idx 5 const 1
54 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
55 a1 a2 = repeat("a", 10000)
56 9 1
57 connection con2;
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 connection default;
65 rollback;
66 drop table worklog5743;
67 ### Test 3 ###
68 create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
69 create index idx on worklog5743(a1, a2(50));
70 insert into worklog5743 values(9, repeat("a", 10000));
71 begin;
72 update worklog5743 set a1 = 2222;
73 connection con1;
74 select @@session.transaction_isolation;
75 @@session.transaction_isolation
76 REPEATABLE-READ
77 explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
78 id select_type table type possible_keys key key_len ref rows Extra
79 1 SIMPLE worklog5743 ref idx idx 5 const 1
80 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
81 a1 a2 = repeat("a", 10000)
82 9 1
83 connection con2;
84 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
85 select @@session.transaction_isolation;
86 @@session.transaction_isolation
87 READ-UNCOMMITTED
88 select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
89 a1 a2 = repeat("a", 10000)
90 connection default;
91 rollback;
92 drop table worklog5743;
93 ### Test 4 ###
94 create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
95 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
96 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
97 create index idx1 on worklog5743_1(a2(4000));
98 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
99 show warnings;
100 Level Code Message
101 Note 1071 Specified key was too long; max key length is 1173 bytes
102 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
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 1982. 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 1982. 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 1982. 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 1982. 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 SET sql_mode= '';
120 set innodb_strict_mode=off;
121 create index idx1 on worklog5743_2(a2(4000));
122 Warnings:
123 Note 1071 Specified key was too long; max key length is 1173 bytes
124 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
125 set innodb_strict_mode=on;
126 show warnings;
127 Level Code Message
128 Note 1071 Specified key was too long; max key length is 1173 bytes
129 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
130 show create table worklog5743_2;
131 Table Create Table
132 worklog5743_2 CREATE TABLE `worklog5743_2` (
133 `a1` int(11) DEFAULT NULL,
134 `a2` text NOT NULL,
135 KEY `idx1` (`a2`(1173))
136 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=2
137 drop index idx1 on worklog5743_2;
138 create index idx3 on worklog5743_2(a2(769));
139 show warnings;
140 Level Code Message
141 create index idx4 on worklog5743_2(a2(768));
142 show warnings;
143 Level Code Message
144 create index idx6 on worklog5743_2(a1, a2(768));
145 show warnings;
146 Level Code Message
147 set innodb_strict_mode=off;
148 create index idx1 on worklog5743_4(a2(4000));
149 Warnings:
150 Note 1071 Specified key was too long; max key length is 1173 bytes
151 set innodb_strict_mode=on;
152 show warnings;
153 Level Code Message
154 Note 1071 Specified key was too long; max key length is 1173 bytes
155 show create table worklog5743_4;
156 Table Create Table
157 worklog5743_4 CREATE TABLE `worklog5743_4` (
158 `a1` int(11) DEFAULT NULL,
159 `a2` text NOT NULL,
160 KEY `idx1` (`a2`(1173))
161 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=4
162 create index idx3 on worklog5743_4(a2(769));
163 show warnings;
164 Level Code Message
165 create index idx4 on worklog5743_4(a2(768));
166 show warnings;
167 Level Code Message
168 create index idx6 on worklog5743_4(a1, a2(764));
169 show warnings;
170 Level Code Message
171 SET sql_mode= default;
172 insert into worklog5743_1 values(9, repeat("a", 10000));
173 insert into worklog5743_2 values(9, repeat("a", 10000));
174 insert into worklog5743_4 values(9, repeat("a", 10000));
175 select a1, left(a2, 20) from worklog5743_1;
176 a1 left(a2, 20)
177 9 aaaaaaaaaaaaaaaaaaaa
178 select a1, left(a2, 20) from worklog5743_2;
179 a1 left(a2, 20)
180 9 aaaaaaaaaaaaaaaaaaaa
181 select a1, left(a2, 20) from worklog5743_4;
182 a1 left(a2, 20)
183 9 aaaaaaaaaaaaaaaaaaaa
184 begin;
185 update worklog5743_1 set a1 = 1000;
186 update worklog5743_2 set a1 = 1000;
187 update worklog5743_4 set a1 = 1000;
188 select a1, left(a2, 20) from worklog5743_1;
189 a1 left(a2, 20)
190 1000 aaaaaaaaaaaaaaaaaaaa
191 select a1, left(a2, 20) from worklog5743_2;
192 a1 left(a2, 20)
193 1000 aaaaaaaaaaaaaaaaaaaa
194 select a1, left(a2, 20) from worklog5743_4;
195 a1 left(a2, 20)
196 1000 aaaaaaaaaaaaaaaaaaaa
197 connection con1;
198 select @@session.transaction_isolation;
199 @@session.transaction_isolation
200 REPEATABLE-READ
201 explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
202 id select_type table type possible_keys key key_len ref rows Extra
203 1 SIMPLE worklog5743_1 ref idx6 idx6 5 const 1
204 explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
205 id select_type table type possible_keys key key_len ref rows Extra
206 1 SIMPLE worklog5743_2 ref idx6 idx6 5 const 1
207 explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
208 id select_type table type possible_keys key key_len ref rows Extra
209 1 SIMPLE worklog5743_4 ref idx6 idx6 5 const 1
210 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
211 a1 left(a2, 20)
212 9 aaaaaaaaaaaaaaaaaaaa
213 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
214 a1 left(a2, 20)
215 9 aaaaaaaaaaaaaaaaaaaa
216 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
217 a1 left(a2, 20)
218 9 aaaaaaaaaaaaaaaaaaaa
219 connection con2;
220 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
221 select @@session.transaction_isolation;
222 @@session.transaction_isolation
223 READ-UNCOMMITTED
224 select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
225 a1 left(a2, 20)
226 select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
227 a1 left(a2, 20)
228 select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
229 a1 left(a2, 20)
230 connection default;
231 rollback;
232 drop table worklog5743_1;
233 drop table worklog5743_2;
234 drop table worklog5743_4;
235 ### Test 5 ###
236 create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
237 create index idx1 on worklog5743(a2);
238 ERROR 42000: Specified key was too long; max key length is 1173 bytes
239 show warnings;
240 Level Code Message
241 Note 1071 Specified key was too long; max key length is 3072 bytes
242 Error 1071 Specified key was too long; max key length is 1173 bytes
243 drop table worklog5743;
244 create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
245 create index idx1 on worklog5743(a2);
246 ERROR 42000: Specified key was too long; max key length is 1173 bytes
247 show warnings;
248 Level Code Message
249 Error 1071 Specified key was too long; max key length is 1173 bytes
250 drop table worklog5743;
251 create table worklog5743(a1 int, a2 varchar(769)) ROW_FORMAT=DYNAMIC;
252 create index idx1 on worklog5743(a2);
253 show warnings;
254 Level Code Message
255 drop table worklog5743;
256 create table worklog5743(a1 int, a2 varchar(768)) ROW_FORMAT=DYNAMIC;
257 create index idx1 on worklog5743(a2);
258 show warnings;
259 Level Code Message
260 insert into worklog5743 values(9, repeat("a", 768));
261 update worklog5743 set a1 = 3333;
262 drop table worklog5743;
263 create table worklog5743(a1 int, a2 varchar(765)) ROW_FORMAT=DYNAMIC;
264 create index idx1 on worklog5743(a1, a2);
265 show warnings;
266 Level Code Message
267 drop table worklog5743;
268 create table worklog5743(a1 int, a2 varchar(764)) ROW_FORMAT=DYNAMIC;
269 create index idx1 on worklog5743(a1, a2);
270 show warnings;
271 Level Code Message
272 insert into worklog5743 values(9, repeat("a", 764));
273 begin;
274 update worklog5743 set a1 = 4444;
275 connection con1;
276 select @@session.transaction_isolation;
277 @@session.transaction_isolation
278 REPEATABLE-READ
279 explain select a1 from worklog5743 where a1 = 9;
280 id select_type table type possible_keys key key_len ref rows Extra
281 1 SIMPLE worklog5743 ref idx1 idx1 5 const 1 Using index
282 select a1 from worklog5743 where a1 = 9;
283 a1
284 9
285 connection con2;
286 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
287 select @@session.transaction_isolation;
288 @@session.transaction_isolation
289 READ-UNCOMMITTED
290 select a1 from worklog5743 where a1 = 9;
291 a1
292 connection default;
293 rollback;
294 drop table worklog5743;
295 ### Test 6 ###
296 create table worklog5743(a TEXT not null, primary key (a(1000)));
297 drop table worklog5743;
298 create table worklog5743(a TEXT) ROW_FORMAT=COMPACT;
299 create index idx on worklog5743(a(768));
300 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
301 create index idx on worklog5743(a(767));
302 insert into worklog5743 values(repeat("a", 20000));
303 begin;
304 insert into worklog5743 values(repeat("b", 20000));
305 update worklog5743 set a = (repeat("x", 25000));
306 select @@session.transaction_isolation;
307 @@session.transaction_isolation
308 REPEATABLE-READ
309 connection con1;
310 select a = repeat("a", 20000) from worklog5743;
311 a = repeat("a", 20000)
312 1
313 disconnect con1;
314 connection con2;
315 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
316 select @@session.transaction_isolation;
317 @@session.transaction_isolation
318 READ-UNCOMMITTED
319 select a = repeat("x", 25000) from worklog5743;
320 a = repeat("x", 25000)
321 1
322 1
323 disconnect con2;
324 connection default;
325 rollback;
326 drop table worklog5743;
327 ### Test 7 ###
328 create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
329 SET sql_mode= '';
330 create index idx1 on worklog5743(a(769));
331 show warnings;
332 Level Code Message
333 SET sql_mode= default;
334 create index idx2 on worklog5743(a(768));
335 show warnings;
336 Level Code Message
337 show create table worklog5743;
338 Table Create Table
339 worklog5743 CREATE TABLE `worklog5743` (
340 `a` text NOT NULL,
341 KEY `idx1` (`a`(769)),
342 KEY `idx2` (`a`(768))
343 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
344 insert into worklog5743 values(repeat("a", 768));
345 drop table worklog5743;
346 create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
347 create index idx on worklog5743(a(768));
348 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
349 create index idx2 on worklog5743(a(767));
350 drop table worklog5743;
351 create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
352 create index idx on worklog5743(a(768));
353 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
354 create index idx2 on worklog5743(a(767));
355 drop table worklog5743;
356 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;