1
set global innodb_file_per_table=off;
2
set global innodb_file_format=`0`;
3
create table t0(a int primary key) engine=innodb row_format=compressed;
5
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
6
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
7
create table t00(a int primary key) engine=innodb
8
key_block_size=4 row_format=compressed;
10
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
11
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
12
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
13
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
14
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
15
create table t1(a int primary key) engine=innodb row_format=dynamic;
17
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
18
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
19
create table t2(a int primary key) engine=innodb row_format=redundant;
20
create table t3(a int primary key) engine=innodb row_format=compact;
21
create table t4(a int primary key) engine=innodb key_block_size=9;
23
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
24
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
25
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
26
create table t5(a int primary key) engine=innodb
27
key_block_size=1 row_format=redundant;
29
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
30
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
31
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
32
set global innodb_file_per_table=on;
33
create table t6(a int primary key) engine=innodb
34
key_block_size=1 row_format=redundant;
36
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
37
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
38
set global innodb_file_format=`1`;
39
create table t7(a int primary key) engine=innodb
40
key_block_size=1 row_format=redundant;
42
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
43
create table t8(a int primary key) engine=innodb
44
key_block_size=1 row_format=fixed;
46
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
47
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
48
create table t9(a int primary key) engine=innodb
49
key_block_size=1 row_format=compact;
51
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
52
create table t10(a int primary key) engine=innodb
53
key_block_size=1 row_format=dynamic;
55
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
56
create table t11(a int primary key) engine=innodb
57
key_block_size=1 row_format=compressed;
58
create table t12(a int primary key) engine=innodb
60
create table t13(a int primary key) engine=innodb
61
row_format=compressed;
62
create table t14(a int primary key) engine=innodb key_block_size=9;
64
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
65
SELECT table_schema, table_name, row_format
66
FROM information_schema.tables WHERE engine='innodb';
67
table_schema table_name row_format
84
drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
85
alter table t1 key_block_size=0;
87
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0.
88
alter table t1 row_format=dynamic;
89
SELECT table_schema, table_name, row_format
90
FROM information_schema.tables WHERE engine='innodb';
91
table_schema table_name row_format
93
alter table t1 row_format=compact;
94
SELECT table_schema, table_name, row_format
95
FROM information_schema.tables WHERE engine='innodb';
96
table_schema table_name row_format
98
alter table t1 row_format=redundant;
99
SELECT table_schema, table_name, row_format
100
FROM information_schema.tables WHERE engine='innodb';
101
table_schema table_name row_format
104
create table t1(a int not null, b text, index(b(10))) engine=innodb
106
create table t2(b text)engine=innodb;
107
insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
108
insert into t1 select 1, b from t2;
111
update t1 set b=repeat('B',100);
112
select a,left(b,40) from t1 natural join t2;
114
1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
116
select a,left(b,40) from t1 natural join t2;
118
1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
119
SELECT table_schema, table_name, row_format
120
FROM information_schema.tables WHERE engine='innodb';
121
table_schema table_name row_format
126
c TEXT NOT NULL, d TEXT NOT NULL,
127
PRIMARY KEY (c(767),d(767)))
128
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
129
INSERT INTO t1 VALUES(
130
'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~(*,.02468:<>@BDFHJLNPRTVXZ\^`bdfhjlnprtvxz|~)+-/13579;=?ACEGIKMOQSUWY[]_acegikmoqsuwy{}(+.147:=@CFILORUX[^adgjmpsvy|(+.147:=@CFILORUX[^adgjmpsvy|(+.147:=@CFILORUX[^adgjmpsvy|(,048<@DHLPTX\`dhlptx|)-159=AEIMQUY]aeimquy}*.26:>BFJNRVZ^bfjnrvz~+/37;?CGKOSW[_cgkosw{(-27<AFKPUZ_dinsx}+05:?DINSX]bglqv{).38=BGLQV[`ejoty~,16;@EJOTY^chmrw|*/49>CHMRW\afkpuz(.4:@FLRX^djpv|+17=CIOU[agmsy(.4:@FLRX^djpv|+17=CIOU[agmsy(.4:@FLRX^djpv|+17=CIOU[agmsy(/6=DKRY`gnu|,3:AHOV]dkry)07>ELSZahov}-4;BIPW^elsz*18?FMT[bipw~.5<CJQX_fmt{+29@GNU\cjqx(08@HPX`hpx)19AIQYaiqy*2:BJRZbjrz+3;CKS[cks{,4<DLT\dlt|-5=EMU]emu}.6>FNV^fnv~/7?GOW_gow(1:CLU^gpy+4=FOXajs|.7@IR[dmv(1:CLU^gpy+4=FOXajs|.7@IR[dmv(1:CLU^gpy+4=',
131
'FOXajs|.7@IR[dmv(2<FPZdnx+5?IS]gq{.8BLV`jt~1;EOYcmw*4>HR\fpz-7AKU_is}0:DNXblv)3=GQ[eoy,6@JT^hr|/9CMWaku(3>IT_ju)4?JU`kv*5@KValw+6ALWbmx,7BMXcny-8CNYdoz.9DOZep{/:EP[fq|0;FQ\gr}1<GR]hs~2=HS^it(4@LXdp|1=IUamy.:FR^jv+7CO[gs(4@LXdp|1=IUamy.:FR^jv+7CO[gs(4@LXdp|1=IUamy.:FR^jv+7CO[gs(5BO\iv,9FS`mz0=JWdq~4AN[hu+8ER_ly/<IVcp}3@MZgt*7DQ^kx.;HUbo|2?LYfs)6CP]jw-:GTan{1>KXer(6DR`n|3AO]ky0>LZhv-;IWes*8FTbp~5CQ_m{2@N\jx/=KYgu,:HVdr)7ESao}4BP^lz1?M[iw.<JXft+9GUcq(7FUds+:IXgv.=L[jy1@O^m|4CRap(7FUds+:IXgv.=L[jy1@O^m|4CRap(7FUds+:IXgv.=L[jy1@O^m|4CRap(8HXhx1AQaq*:JZjz3CScs,<L\l|5EUeu.>N^n~7GWgw0@P`p)9IYiy2BRbr+;K[k{4DTdt-=M]m}6FVfv/?O_o(9J[l}7HYj{5FWhy3DUfw1BSdu/@Qbs->O`q+<M^o):K\m~8IZk|6GXiz4EVgx2CTev0ARct.?Par,=N_p*;L]n(:L^p+=Oas.@Rdv1CUgy4FXj|7I[m(:L^p+=Oas.@Rdv1CUgy4FXj|7');
132
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
134
set global innodb_file_format=`0`;
135
select @@innodb_file_format;
138
set global innodb_file_format=`1`;
139
select @@innodb_file_format;
142
set global innodb_file_format=`2`;
143
ERROR HY000: Incorrect arguments to SET
144
set global innodb_file_format=`-1`;
145
ERROR HY000: Incorrect arguments to SET
146
set global innodb_file_format=`Antelope`;
147
set global innodb_file_format=`Barracuda`;
148
set global innodb_file_format=`Cheetah`;
149
ERROR HY000: Incorrect arguments to SET
150
set global innodb_file_format=`abc`;
151
ERROR HY000: Incorrect arguments to SET
152
set global innodb_file_format=`1a`;
153
ERROR HY000: Incorrect arguments to SET
154
set global innodb_file_format=``;
155
ERROR HY000: Incorrect arguments to SET
156
set global innodb_file_per_table = on;
157
set global innodb_file_format = `1`;
158
set innodb_strict_mode = on;
159
create table t1 (id int primary key) engine = innodb key_block_size = 0;
160
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
163
Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16]
164
Error 1005 Can't create table 'test.t1' (errno: 1478)
165
create table t2 (id int primary key) engine = innodb key_block_size = 9;
166
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
169
Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
170
Error 1005 Can't create table 'test.t2' (errno: 1478)
171
create table t3 (id int primary key) engine = innodb key_block_size = 1;
172
create table t4 (id int primary key) engine = innodb key_block_size = 2;
173
create table t5 (id int primary key) engine = innodb key_block_size = 4;
174
create table t6 (id int primary key) engine = innodb key_block_size = 8;
175
create table t7 (id int primary key) engine = innodb key_block_size = 16;
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
181
FROM information_schema.tables WHERE engine='innodb';
182
table_schema table_name row_format
192
drop table t3, t4, t5, t6, t7, t8, t9, t10, t11;
193
create table t1 (id int primary key) engine = innodb
194
key_block_size = 8 row_format = compressed;
195
create table t2 (id int primary key) engine = innodb
196
key_block_size = 8 row_format = redundant;
197
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
200
Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
201
Error 1005 Can't create table 'test.t2' (errno: 1478)
202
create table t3 (id int primary key) engine = innodb
203
key_block_size = 8 row_format = compact;
204
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
207
Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
208
Error 1005 Can't create table 'test.t3' (errno: 1478)
209
create table t4 (id int primary key) engine = innodb
210
key_block_size = 8 row_format = dynamic;
211
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
214
Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
215
Error 1005 Can't create table 'test.t4' (errno: 1478)
216
create table t5 (id int primary key) engine = innodb
217
key_block_size = 8 row_format = default;
218
ERROR HY000: Can't create table 'test.t5' (errno: 1478)
221
Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
222
Error 1005 Can't create table 'test.t5' (errno: 1478)
223
SELECT table_schema, table_name, row_format
224
FROM information_schema.tables WHERE engine='innodb';
225
table_schema table_name row_format
228
create table t1 (id int primary key) engine = innodb
229
key_block_size = 9 row_format = redundant;
230
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
233
Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
234
Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
235
Error 1005 Can't create table 'test.t1' (errno: 1478)
236
create table t2 (id int primary key) engine = innodb
237
key_block_size = 9 row_format = compact;
238
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
241
Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
242
Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
243
Error 1005 Can't create table 'test.t2' (errno: 1478)
244
create table t2 (id int primary key) engine = innodb
245
key_block_size = 9 row_format = dynamic;
246
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
249
Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
250
Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
251
Error 1005 Can't create table 'test.t2' (errno: 1478)
252
SELECT table_schema, table_name, row_format
253
FROM information_schema.tables WHERE engine='innodb';
254
table_schema table_name row_format
255
set global innodb_file_per_table = off;
256
create table t1 (id int primary key) engine = innodb key_block_size = 1;
257
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
260
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
261
Error 1005 Can't create table 'test.t1' (errno: 1478)
262
create table t2 (id int primary key) engine = innodb key_block_size = 2;
263
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
266
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
267
Error 1005 Can't create table 'test.t2' (errno: 1478)
268
create table t3 (id int primary key) engine = innodb key_block_size = 4;
269
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
272
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
273
Error 1005 Can't create table 'test.t3' (errno: 1478)
274
create table t4 (id int primary key) engine = innodb key_block_size = 8;
275
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
278
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
279
Error 1005 Can't create table 'test.t4' (errno: 1478)
280
create table t5 (id int primary key) engine = innodb key_block_size = 16;
281
ERROR HY000: Can't create table 'test.t5' (errno: 1478)
284
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
285
Error 1005 Can't create table 'test.t5' (errno: 1478)
286
create table t6 (id int primary key) engine = innodb row_format = compressed;
287
ERROR HY000: Can't create table 'test.t6' (errno: 1478)
290
Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
291
Error 1005 Can't create table 'test.t6' (errno: 1478)
292
create table t7 (id int primary key) engine = innodb row_format = dynamic;
293
ERROR HY000: Can't create table 'test.t7' (errno: 1478)
296
Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
297
Error 1005 Can't create table 'test.t7' (errno: 1478)
298
create table t8 (id int primary key) engine = innodb row_format = compact;
299
create table t9 (id int primary key) engine = innodb row_format = redundant;
300
SELECT table_schema, table_name, row_format
301
FROM information_schema.tables WHERE engine='innodb';
302
table_schema table_name row_format
306
set global innodb_file_per_table = on;
307
set global innodb_file_format = `0`;
308
create table t1 (id int primary key) engine = innodb key_block_size = 1;
309
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
312
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
313
Error 1005 Can't create table 'test.t1' (errno: 1478)
314
create table t2 (id int primary key) engine = innodb key_block_size = 2;
315
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
318
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
319
Error 1005 Can't create table 'test.t2' (errno: 1478)
320
create table t3 (id int primary key) engine = innodb key_block_size = 4;
321
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
324
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
325
Error 1005 Can't create table 'test.t3' (errno: 1478)
326
create table t4 (id int primary key) engine = innodb key_block_size = 8;
327
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
330
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
331
Error 1005 Can't create table 'test.t4' (errno: 1478)
332
create table t5 (id int primary key) engine = innodb key_block_size = 16;
333
ERROR HY000: Can't create table 'test.t5' (errno: 1478)
336
Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
337
Error 1005 Can't create table 'test.t5' (errno: 1478)
338
create table t6 (id int primary key) engine = innodb row_format = compressed;
339
ERROR HY000: Can't create table 'test.t6' (errno: 1478)
342
Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
343
Error 1005 Can't create table 'test.t6' (errno: 1478)
344
create table t7 (id int primary key) engine = innodb row_format = dynamic;
345
ERROR HY000: Can't create table 'test.t7' (errno: 1478)
348
Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
349
Error 1005 Can't create table 'test.t7' (errno: 1478)
350
create table t8 (id int primary key) engine = innodb row_format = compact;
351
create table t9 (id int primary key) engine = innodb row_format = redundant;
352
SELECT table_schema, table_name, row_format
353
FROM information_schema.tables WHERE engine='innodb';
354
table_schema table_name row_format
358
set global innodb_file_per_table=0;
359
set global innodb_file_format=Antelope;
360
set innodb_strict_mode=0;
361
set global innodb_file_per_table=on;
362
set global innodb_file_format=`Barracuda`;
363
set global innodb_file_format_check=`Antelope`;
364
create table normal_table (
367
select @@innodb_file_format_check;
368
@@innodb_file_format_check
370
create table zip_table (
372
) engine = innodb key_block_size = 8;
373
select @@innodb_file_format_check;
374
@@innodb_file_format_check
376
set global innodb_file_format_check=`Antelope`;
377
select @@innodb_file_format_check;
378
@@innodb_file_format_check
381
select @@innodb_file_format_check;
382
@@innodb_file_format_check
384
set global innodb_file_format_check=`Cheetah`;
385
ERROR HY000: Incorrect arguments to SET
386
set global innodb_file_format_check=`on`;
387
ERROR HY000: Incorrect arguments to SET
388
set global innodb_file_format_check=`off`;
389
ERROR HY000: Incorrect arguments to SET
390
select @@innodb_file_format_check;
391
@@innodb_file_format_check
393
drop table normal_table, zip_table;
added
removed
mysql-test/innodb-zip.result
