Debian Codegraph @

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

raw
1 SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
2 set session innodb_strict_mode=0;
3 set global innodb_file_per_table=off;
4 SET @@global.innodb_stats_on_metadata=ON;
5 create table t0(a int primary key) engine=innodb row_format=compressed;
6 Warnings:
7 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
8 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
9 create table t00(a int primary key) engine=innodb
10 key_block_size=4 row_format=compressed;
11 Warnings:
12 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
13 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
14 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
15 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
16 create table t1(a int primary key) engine=innodb row_format=dynamic;
17 create table t2(a int primary key) engine=innodb row_format=redundant;
18 create table t3(a int primary key) engine=innodb row_format=compact;
19 create table t4(a int primary key) engine=innodb key_block_size=9;
20 Warnings:
21 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
22 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
23 create table t5(a int primary key) engine=innodb
24 key_block_size=1 row_format=redundant;
25 Warnings:
26 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
27 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
28 set global innodb_file_per_table=on;
29 create table t6(a int primary key) engine=innodb
30 key_block_size=1 row_format=redundant;
31 Warnings:
32 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
33 create table t7(a int primary key) engine=innodb
34 key_block_size=1 row_format=redundant;
35 Warnings:
36 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
37 create table t8(a int primary key) engine=innodb
38 key_block_size=1 row_format=fixed;
39 Warnings:
40 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
41 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
42 create table t9(a int primary key) engine=innodb
43 key_block_size=1 row_format=compact;
44 Warnings:
45 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
46 create table t10(a int primary key) engine=innodb
47 key_block_size=1 row_format=dynamic;
48 Warnings:
49 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
50 create table t11(a int primary key) engine=innodb
51 key_block_size=1 row_format=compressed;
52 create table t12(a int primary key) engine=innodb
53 key_block_size=1;
54 create table t13(a int primary key) engine=innodb
55 row_format=compressed;
56 create table t14(a int primary key) engine=innodb key_block_size=9;
57 Warnings:
58 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
59 analyze table t0 ,t00 ,t1 ,t2 ,t3 ,t4 ,t5 ,t6 ,t7 ,t8 ,t9 ,t10 ,t11 ,t12 ,t13 ,t14;
60 Table Op Msg_type Msg_text
61 test.t0 analyze status OK
62 test.t00 analyze status OK
63 test.t1 analyze status OK
64 test.t2 analyze status OK
65 test.t3 analyze status OK
66 test.t4 analyze status OK
67 test.t5 analyze status OK
68 test.t6 analyze status OK
69 test.t7 analyze status OK
70 test.t8 analyze status OK
71 test.t9 analyze status OK
72 test.t10 analyze status OK
73 test.t11 analyze status OK
74 test.t12 analyze status OK
75 test.t13 analyze status OK
76 test.t14 analyze status OK
77 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
78 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
79 mtr asserted_test_suppressions Dynamic {valid} 0
80 mtr global_suppressions Dynamic {valid} {valid}
81 mtr test_ignored_global_suppressions Dynamic {valid} 0
82 mtr test_suppressions Dynamic {valid} 0
83 test t0 Dynamic {valid} 0
84 test t00 Dynamic {valid} 0
85 test t1 Dynamic {valid} 0
86 test t10 Dynamic {valid} 0
87 test t11 Compressed 1024 0
88 test t12 Compressed 1024 0
89 test t13 Compressed {valid} 0
90 test t14 Dynamic {valid} 0
91 test t2 Redundant {valid} 0
92 test t3 Compact {valid} 0
93 test t4 Dynamic {valid} 0
94 test t5 Redundant {valid} 0
95 test t6 Redundant {valid} 0
96 test t7 Redundant {valid} 0
97 test t8 Dynamic {valid} 0
98 test t9 Compact {valid} 0
99 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
100 alter table t1 key_block_size=0;
101 alter table t1 row_format=dynamic;
102 analyze table t1;
103 Table Op Msg_type Msg_text
104 test.t1 analyze status OK
105 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
106 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
107 mtr asserted_test_suppressions Dynamic {valid} 0
108 mtr global_suppressions Dynamic {valid} {valid}
109 mtr test_ignored_global_suppressions Dynamic {valid} 0
110 mtr test_suppressions Dynamic {valid} 0
111 test t1 Dynamic {valid} 0
112 alter table t1 row_format=compact;
113 analyze table t1;
114 Table Op Msg_type Msg_text
115 test.t1 analyze status OK
116 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
117 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
118 mtr asserted_test_suppressions Dynamic {valid} 0
119 mtr global_suppressions Dynamic {valid} {valid}
120 mtr test_ignored_global_suppressions Dynamic {valid} 0
121 mtr test_suppressions Dynamic {valid} 0
122 test t1 Compact {valid} 0
123 alter table t1 row_format=redundant;
124 analyze table t1;
125 Table Op Msg_type Msg_text
126 test.t1 analyze status OK
127 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
128 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
129 mtr asserted_test_suppressions Dynamic {valid} 0
130 mtr global_suppressions Dynamic {valid} {valid}
131 mtr test_ignored_global_suppressions Dynamic {valid} 0
132 mtr test_suppressions Dynamic {valid} 0
133 test t1 Redundant {valid} 0
134 drop table t1;
135 create table t1(a int not null, b text, index(b(10))) engine=innodb
136 key_block_size=1;
137 create table t2(b text)engine=innodb;
138 insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
139 insert into t1 select 1, b from t2;
140 commit;
141 begin;
142 update t1 set b=repeat('B',100);
143 select a,left(b,40) from t1 natural join t2;
144 a left(b,40)
145 1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
146 rollback;
147 select a,left(b,40) from t1 natural join t2;
148 a left(b,40)
149 1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
150 analyze table t1;
151 Table Op Msg_type Msg_text
152 test.t1 analyze status OK
153 analyze table t2;
154 Table Op Msg_type Msg_text
155 test.t2 analyze status OK
156 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
157 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
158 mtr asserted_test_suppressions Dynamic {valid} 0
159 mtr global_suppressions Dynamic {valid} {valid}
160 mtr test_ignored_global_suppressions Dynamic {valid} 0
161 mtr test_suppressions Dynamic {valid} 0
162 test t1 Compressed 2048 1024
163 test t2 Dynamic {valid} 0
164 drop table t1,t2;
165 create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
166 primary key(c1, c2(22), c3(22)))
167 engine = innodb row_format = dynamic;
168 begin;
169 insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
170 repeat('C', 20000));
171 update t1 set c3 = repeat('D', 20000) where c1 = 1;
172 commit;
173 select count(*) from t1 where c2 = repeat('A', 20000);
174 count(*)
175 1
176 select count(*) from t1 where c3 = repeat('D', 20000);
177 count(*)
178 1
179 select count(*) from t1 where c4 = repeat('C', 20000);
180 count(*)
181 1
182 update t1 set c3 = repeat('E', 20000) where c1 = 1;
183 drop table t1;
184 set global innodb_file_per_table = on;
185 set innodb_strict_mode = off;
186 create table t1 (id int primary key) engine = innodb key_block_size = 0;
187 drop table t1;
188 set innodb_strict_mode = on;
189 create table t1 (id int primary key) engine = innodb key_block_size = 0;
190 create table t2 (id int primary key) engine = innodb key_block_size = 9;
191 ERROR HY000: Table storage engine for 't2' doesn't have this option
192 show warnings;
193 Level Code Message
194 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
195 Error 1031 Table storage engine for 't2' doesn't have this option
196 create table t3 (id int primary key) engine = innodb key_block_size = 1;
197 create table t4 (id int primary key) engine = innodb key_block_size = 2;
198 create table t5 (id int primary key) engine = innodb key_block_size = 4;
199 create table t8 (id int primary key) engine = innodb row_format = compressed;
200 create table t9 (id int primary key) engine = innodb row_format = dynamic;
201 create table t10(id int primary key) engine = innodb row_format = compact;
202 create table t11(id int primary key) engine = innodb row_format = redundant;
203 analyze table t1;
204 Table Op Msg_type Msg_text
205 test.t1 analyze status OK
206 analyze table t3;
207 Table Op Msg_type Msg_text
208 test.t3 analyze status OK
209 analyze table t4;
210 Table Op Msg_type Msg_text
211 test.t4 analyze status OK
212 analyze table t5;
213 Table Op Msg_type Msg_text
214 test.t5 analyze status OK
215 analyze table t8;
216 Table Op Msg_type Msg_text
217 test.t8 analyze status OK
218 analyze table t9;
219 Table Op Msg_type Msg_text
220 test.t9 analyze status OK
221 analyze table t10;
222 Table Op Msg_type Msg_text
223 test.t10 analyze status OK
224 analyze table t11;
225 Table Op Msg_type Msg_text
226 test.t11 analyze status OK
227 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
228 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
229 mtr asserted_test_suppressions Dynamic {valid} 0
230 mtr global_suppressions Dynamic {valid} {valid}
231 mtr test_ignored_global_suppressions Dynamic {valid} 0
232 mtr test_suppressions Dynamic {valid} 0
233 test t1 Dynamic {valid} 0
234 test t10 Compact {valid} 0
235 test t11 Redundant {valid} 0
236 test t3 Compressed 1024 0
237 test t4 Compressed {valid} 0
238 test t5 Compressed {valid} 0
239 test t8 Compressed {valid} 0
240 test t9 Dynamic {valid} 0
241 drop table t1, t3, t4, t5, t8, t9, t10, t11;
242 create table t1 (id int primary key) engine = innodb
243 key_block_size = 4 row_format = compressed;
244 create table t2 (id int primary key) engine = innodb
245 key_block_size = 4 row_format = redundant;
246 ERROR HY000: Table storage engine for 't2' doesn't have this option
247 show warnings;
248 Level Code Message
249 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
250 Error 1031 Table storage engine for 't2' doesn't have this option
251 create table t3 (id int primary key) engine = innodb
252 key_block_size = 4 row_format = compact;
253 ERROR HY000: Table storage engine for 't3' doesn't have this option
254 show warnings;
255 Level Code Message
256 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
257 Error 1031 Table storage engine for 't3' doesn't have this option
258 create table t4 (id int primary key) engine = innodb
259 key_block_size = 4 row_format = dynamic;
260 ERROR HY000: Table storage engine for 't4' doesn't have this option
261 show warnings;
262 Level Code Message
263 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
264 Error 1031 Table storage engine for 't4' doesn't have this option
265 create table t5 (id int primary key) engine = innodb
266 key_block_size = 4 row_format = default;
267 analyze table t1;
268 Table Op Msg_type Msg_text
269 test.t1 analyze status OK
270 analyze table t5;
271 Table Op Msg_type Msg_text
272 test.t5 analyze status OK
273 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
274 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
275 mtr asserted_test_suppressions Dynamic {valid} 0
276 mtr global_suppressions Dynamic {valid} {valid}
277 mtr test_ignored_global_suppressions Dynamic {valid} 0
278 mtr test_suppressions Dynamic {valid} 0
279 test t1 Compressed {valid} 0
280 test t5 Compressed {valid} 0
281 drop table t1, t5;
282 create table t1 (id int primary key) engine = innodb
283 key_block_size = 9 row_format = redundant;
284 ERROR HY000: Table storage engine for 't1' doesn't have this option
285 show warnings;
286 Level Code Message
287 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
288 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
289 Error 1031 Table storage engine for 't1' doesn't have this option
290 create table t2 (id int primary key) engine = innodb
291 key_block_size = 9 row_format = compact;
292 ERROR HY000: Table storage engine for 't2' doesn't have this option
293 show warnings;
294 Level Code Message
295 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
296 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
297 Error 1031 Table storage engine for 't2' doesn't have this option
298 create table t2 (id int primary key) engine = innodb
299 key_block_size = 9 row_format = dynamic;
300 ERROR HY000: Table storage engine for 't2' doesn't have this option
301 show warnings;
302 Level Code Message
303 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
304 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
305 Error 1031 Table storage engine for 't2' doesn't have this option
306 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
307 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
308 mtr asserted_test_suppressions Dynamic {valid} 0
309 mtr global_suppressions Dynamic {valid} {valid}
310 mtr test_ignored_global_suppressions Dynamic {valid} 0
311 mtr test_suppressions Dynamic {valid} 0
312 set global innodb_file_per_table = off;
313 create table t1 (id int primary key) engine = innodb key_block_size = 1;
314 ERROR HY000: Table storage engine for 't1' doesn't have this option
315 show warnings;
316 Level Code Message
317 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
318 Error 1031 Table storage engine for 't1' doesn't have this option
319 create table t2 (id int primary key) engine = innodb key_block_size = 2;
320 ERROR HY000: Table storage engine for 't2' doesn't have this option
321 show warnings;
322 Level Code Message
323 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
324 Error 1031 Table storage engine for 't2' doesn't have this option
325 create table t3 (id int primary key) engine = innodb key_block_size = 4;
326 ERROR HY000: Table storage engine for 't3' doesn't have this option
327 show warnings;
328 Level Code Message
329 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
330 Error 1031 Table storage engine for 't3' doesn't have this option
331 create table t6 (id int primary key) engine = innodb row_format = compressed;
332 ERROR HY000: Table storage engine for 't6' doesn't have this option
333 show warnings;
334 Level Code Message
335 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
336 Error 1031 Table storage engine for 't6' doesn't have this option
337 create table t7 (id int primary key) engine = innodb row_format = dynamic;
338 create table t8 (id int primary key) engine = innodb row_format = compact;
339 create table t9 (id int primary key) engine = innodb row_format = redundant;
340 analyze table t7;
341 Table Op Msg_type Msg_text
342 test.t7 analyze status OK
343 analyze table t8;
344 Table Op Msg_type Msg_text
345 test.t8 analyze status OK
346 analyze table t9;
347 Table Op Msg_type Msg_text
348 test.t9 analyze status OK
349 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
350 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
351 mtr asserted_test_suppressions Dynamic {valid} 0
352 mtr global_suppressions Dynamic {valid} {valid}
353 mtr test_ignored_global_suppressions Dynamic {valid} 0
354 mtr test_suppressions Dynamic {valid} 0
355 test t7 Dynamic {valid} 0
356 test t8 Compact {valid} 0
357 test t9 Redundant {valid} 0
358 drop table t7, t8, t9;
359 set global innodb_file_per_table = on;
360 create table t8 (id int primary key) engine = innodb row_format = compact;
361 create table t9 (id int primary key) engine = innodb row_format = redundant;
362 analyze table t8;
363 Table Op Msg_type Msg_text
364 test.t8 analyze status OK
365 analyze table t9;
366 Table Op Msg_type Msg_text
367 test.t9 analyze status OK
368 SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' AND table_schema != 'sys' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;;
369 TABLE_SCHEMA TABLE_NAME ROW_FORMAT DATA_LENGTH INDEX_LENGTH
370 mtr asserted_test_suppressions Dynamic {valid} 0
371 mtr global_suppressions Dynamic {valid} {valid}
372 mtr test_ignored_global_suppressions Dynamic {valid} 0
373 mtr test_suppressions Dynamic {valid} 0
374 test t8 Compact {valid} 0
375 test t9 Redundant {valid} 0
376 drop table t8, t9;
377 set global innodb_file_per_table=1;
378 set global innodb_file_per_table=on;
379 create table normal_table (
380 c1 int
381 ) engine = innodb;
382 create table zip_table (
383 c1 int
384 ) engine = innodb key_block_size = 4;
385 drop table normal_table, zip_table;