Debian Codegraph @

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

raw
1 CREATE DATABASE mysqltest_innodb_zip;
2 USE mysqltest_innodb_zip;
3 SELECT table_name, row_format, data_length, index_length
4 FROM information_schema.tables
5 WHERE table_schema='mysqltest_innodb_zip';
6 table_name row_format data_length index_length
7 SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
8 set session innodb_strict_mode=0;
9 SET @save_fpt=@@GLOBAL.innodb_file_per_table;
10 set global innodb_file_per_table=off;
11 Warnings:
12 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
13 SET @@global.innodb_stats_on_metadata=ON;
14 create table t0(a int primary key) engine=innodb row_format=compressed;
15 Warnings:
16 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
17 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
18 create table t00(a int primary key) engine=innodb
19 key_block_size=4 row_format=compressed;
20 Warnings:
21 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
22 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
23 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
24 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
25 create table t1(a int primary key) engine=innodb row_format=dynamic;
26 create table t2(a int primary key) engine=innodb row_format=redundant;
27 create table t3(a int primary key) engine=innodb row_format=compact;
28 create table t4(a int primary key) engine=innodb key_block_size=9;
29 Warnings:
30 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
31 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
32 create table t5(a int primary key) engine=innodb
33 key_block_size=1 row_format=redundant;
34 Warnings:
35 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
36 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
37 set global innodb_file_per_table=on;
38 Warnings:
39 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
40 create table t6(a int primary key) engine=innodb
41 key_block_size=1 row_format=redundant;
42 Warnings:
43 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
44 create table t7(a int primary key) engine=innodb
45 key_block_size=1 row_format=redundant;
46 Warnings:
47 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
48 create table t8(a int primary key) engine=innodb
49 key_block_size=1 row_format=fixed;
50 Warnings:
51 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
52 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
53 create table t9(a int primary key) engine=innodb
54 key_block_size=1 row_format=compact;
55 Warnings:
56 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
57 create table t10(a int primary key) engine=innodb
58 key_block_size=1 row_format=dynamic;
59 Warnings:
60 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
61 create table t11(a int primary key) engine=innodb
62 key_block_size=1 row_format=compressed;
63 create table t12(a int primary key) engine=innodb
64 key_block_size=1;
65 create table t13(a int primary key) engine=innodb
66 row_format=compressed;
67 create table t14(a int primary key) engine=innodb key_block_size=9;
68 Warnings:
69 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
70 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' order by table_name;
71 table_schema table_name row_format data_length index_length
72 mysqltest_innodb_zip t0 Dynamic {valid} 0
73 mysqltest_innodb_zip t00 Dynamic {valid} 0
74 mysqltest_innodb_zip t1 Dynamic {valid} 0
75 mysqltest_innodb_zip t10 Dynamic {valid} 0
76 mysqltest_innodb_zip t11 Compressed 1024 0
77 mysqltest_innodb_zip t12 Compressed 1024 0
78 mysqltest_innodb_zip t13 Compressed {valid} 0
79 mysqltest_innodb_zip t14 Dynamic {valid} 0
80 mysqltest_innodb_zip t2 Redundant {valid} 0
81 mysqltest_innodb_zip t3 Compact {valid} 0
82 mysqltest_innodb_zip t4 Dynamic {valid} 0
83 mysqltest_innodb_zip t5 Redundant {valid} 0
84 mysqltest_innodb_zip t6 Redundant {valid} 0
85 mysqltest_innodb_zip t7 Redundant {valid} 0
86 mysqltest_innodb_zip t8 Dynamic {valid} 0
87 mysqltest_innodb_zip t9 Compact {valid} 0
88 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
89 alter table t1 key_block_size=0;
90 alter table t1 row_format=dynamic;
91 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' order by table_name;
92 table_schema table_name row_format data_length index_length
93 mysqltest_innodb_zip t1 Dynamic {valid} 0
94 alter table t1 row_format=compact;
95 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' order by table_name;
96 table_schema table_name row_format data_length index_length
97 mysqltest_innodb_zip t1 Compact {valid} 0
98 alter table t1 row_format=redundant;
99 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' order by table_name;
100 table_schema table_name row_format data_length index_length
101 mysqltest_innodb_zip t1 Redundant {valid} 0
102 drop table t1;
103 create table t1(a int not null, b text, index(b(10))) engine=innodb
104 key_block_size=1;
105 create table t2(b text)engine=innodb;
106 insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
107 insert into t1 select 1, b from t2;
108 commit;
109 connect a,localhost,root,,mysqltest_innodb_zip;
110 connect b,localhost,root,,mysqltest_innodb_zip;
111 connection a;
112 begin;
113 update t1 set b=repeat('B',100);
114 connection b;
115 select a,left(b,40) from t1 natural join t2;
116 a left(b,40)
117 1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
118 connection a;
119 rollback;
120 connection b;
121 select a,left(b,40) from t1 natural join t2;
122 a left(b,40)
123 1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
124 connection default;
125 disconnect a;
126 disconnect b;
127 analyze table t1;
128 Table Op Msg_type Msg_text
129 mysqltest_innodb_zip.t1 analyze status Engine-independent statistics collected
130 mysqltest_innodb_zip.t1 analyze Warning Engine-independent statistics are not collected for column 'b'
131 mysqltest_innodb_zip.t1 analyze status OK
132 analyze table t2;
133 Table Op Msg_type Msg_text
134 mysqltest_innodb_zip.t2 analyze status Engine-independent statistics collected
135 mysqltest_innodb_zip.t2 analyze Warning Engine-independent statistics are not collected for column 'b'
136 mysqltest_innodb_zip.t2 analyze status OK
137 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' order by table_name;
138 table_schema table_name row_format data_length index_length
139 mysqltest_innodb_zip t1 Compressed 2048 1024
140 mysqltest_innodb_zip t2 Dynamic {valid} 0
141 drop table t1,t2;
142 create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
143 primary key(c1, c2(22), c3(22)))
144 engine = innodb row_format = dynamic;
145 begin;
146 insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
147 repeat('C', 20000));
148 update t1 set c3 = repeat('D', 20000) where c1 = 1;
149 commit;
150 select count(*) from t1 where c2 = repeat('A', 20000);
151 count(*)
152 1
153 select count(*) from t1 where c3 = repeat('D', 20000);
154 count(*)
155 1
156 select count(*) from t1 where c4 = repeat('C', 20000);
157 count(*)
158 1
159 update t1 set c3 = repeat('E', 20000) where c1 = 1;
160 drop table t1;
161 set innodb_strict_mode = off;
162 create table t1 (id int primary key) engine = innodb key_block_size = 0;
163 drop table t1;
164 set innodb_strict_mode = on;
165 create table t1 (id int primary key) engine = innodb key_block_size = 0;
166 create table t2 (id int primary key) engine = innodb key_block_size = 9;
167 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
168 show warnings;
169 Level Code Message
170 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
171 Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
172 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
173 create table t3 (id int primary key) engine = innodb key_block_size = 1;
174 create table t4 (id int primary key) engine = innodb key_block_size = 2;
175 create table t5 (id int primary key) engine = innodb key_block_size = 4;
176 create table t8 (id int primary key) engine = innodb row_format = compressed;
177 create table t9 (id int primary key) engine = innodb row_format = dynamic;
178 create table t10(id int primary key) engine = innodb row_format = compact;
179 create table t11(id int primary key) engine = innodb row_format = redundant;
180 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' order by table_name;
181 table_schema table_name row_format data_length index_length
182 mysqltest_innodb_zip t1 Dynamic {valid} 0
183 mysqltest_innodb_zip t10 Compact {valid} 0
184 mysqltest_innodb_zip t11 Redundant {valid} 0
185 mysqltest_innodb_zip t3 Compressed 1024 0
186 mysqltest_innodb_zip t4 Compressed {valid} 0
187 mysqltest_innodb_zip t5 Compressed {valid} 0
188 mysqltest_innodb_zip t8 Compressed {valid} 0
189 mysqltest_innodb_zip t9 Dynamic {valid} 0
190 drop table t1, t3, t4, t5, t8, t9, t10, t11;
191 create table t1 (id int primary key) engine = innodb
192 key_block_size = 4 row_format = compressed;
193 create table t2 (id int primary key) engine = innodb
194 key_block_size = 4 row_format = redundant;
195 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
196 show warnings;
197 Level Code Message
198 Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
199 Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
200 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
201 create table t3 (id int primary key) engine = innodb
202 key_block_size = 4 row_format = compact;
203 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t3` (errno: 140 "Wrong create options")
204 show warnings;
205 Level Code Message
206 Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
207 Error 1005 Can't create table `mysqltest_innodb_zip`.`t3` (errno: 140 "Wrong create options")
208 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
209 create table t4 (id int primary key) engine = innodb
210 key_block_size = 4 row_format = dynamic;
211 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t4` (errno: 140 "Wrong create options")
212 show warnings;
213 Level Code Message
214 Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
215 Error 1005 Can't create table `mysqltest_innodb_zip`.`t4` (errno: 140 "Wrong create options")
216 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
217 create table t5 (id int primary key) engine = innodb
218 key_block_size = 4 row_format = default;
219 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' order by table_name;
220 table_schema table_name row_format data_length index_length
221 mysqltest_innodb_zip t1 Compressed 4096 0
222 mysqltest_innodb_zip t5 Compressed 4096 0
223 drop table t1, t5;
224 create table t1 (id int primary key) engine = innodb
225 key_block_size = 9 row_format = redundant;
226 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t1` (errno: 140 "Wrong create options")
227 show warnings;
228 Level Code Message
229 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
230 Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
231 Error 1005 Can't create table `mysqltest_innodb_zip`.`t1` (errno: 140 "Wrong create options")
232 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
233 create table t2 (id int primary key) engine = innodb
234 key_block_size = 9 row_format = compact;
235 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
236 show warnings;
237 Level Code Message
238 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
239 Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
240 Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
241 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
242 create table t2 (id int primary key) engine = innodb
243 key_block_size = 9 row_format = dynamic;
244 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
245 show warnings;
246 Level Code Message
247 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
248 Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
249 Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
250 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
251 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' order by table_name;
252 table_schema table_name row_format data_length index_length
253 set global innodb_file_per_table = off;
254 Warnings:
255 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
256 create table t1 (id int primary key) engine = innodb key_block_size = 1;
257 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t1` (errno: 140 "Wrong create options")
258 show warnings;
259 Level Code Message
260 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
261 Error 1005 Can't create table `mysqltest_innodb_zip`.`t1` (errno: 140 "Wrong create options")
262 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
263 create table t2 (id int primary key) engine = innodb key_block_size = 2;
264 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
265 show warnings;
266 Level Code Message
267 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
268 Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options")
269 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
270 create table t3 (id int primary key) engine = innodb key_block_size = 4;
271 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t3` (errno: 140 "Wrong create options")
272 show warnings;
273 Level Code Message
274 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
275 Error 1005 Can't create table `mysqltest_innodb_zip`.`t3` (errno: 140 "Wrong create options")
276 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
277 create table t6 (id int primary key) engine = innodb row_format = compressed;
278 ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t6` (errno: 140 "Wrong create options")
279 show warnings;
280 Level Code Message
281 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
282 Error 1005 Can't create table `mysqltest_innodb_zip`.`t6` (errno: 140 "Wrong create options")
283 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
284 create table t7 (id int primary key) engine = innodb row_format = dynamic;
285 show warnings;
286 Level Code Message
287 create table t8 (id int primary key) engine = innodb row_format = compact;
288 create table t9 (id int primary key) engine = innodb row_format = redundant;
289 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' order by table_name;
290 table_schema table_name row_format data_length index_length
291 mysqltest_innodb_zip t7 Dynamic {valid} 0
292 mysqltest_innodb_zip t8 Compact {valid} 0
293 mysqltest_innodb_zip t9 Redundant {valid} 0
294 drop table t7, t8, t9;
295 SET GLOBAL innodb_file_per_table=@save_fpt;
296 Warnings:
297 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
298 SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;
299 DROP DATABASE mysqltest_innodb_zip;