1
set session innodb_strict_mode=0;
2
set global innodb_file_per_table=off;
3
set global innodb_file_format=`0`;
4
create table t0(a int primary key) engine=innodb row_format=compressed;
6
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
7
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
8
create table t00(a int primary key) engine=innodb
9
key_block_size=4 row_format=compressed;
11
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
12
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
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=COMPACT.
16
create table t1(a int primary key) engine=innodb row_format=dynamic;
18
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
19
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
20
create table t2(a int primary key) engine=innodb row_format=redundant;
21
create table t3(a int primary key) engine=innodb row_format=compact;
22
create table t4(a int primary key) engine=innodb key_block_size=9;
24
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
25
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
26
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
27
create table t5(a int primary key) engine=innodb
28
key_block_size=1 row_format=redundant;
30
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
31
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
32
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
33
set global innodb_file_per_table=on;
34
create table t6(a int primary key) engine=innodb
35
key_block_size=1 row_format=redundant;
37
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
38
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
39
set global innodb_file_format=`1`;
40
create table t7(a int primary key) engine=innodb
41
key_block_size=1 row_format=redundant;
43
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
44
create table t8(a int primary key) engine=innodb
45
key_block_size=1 row_format=fixed;
47
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
48
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
49
create table t9(a int primary key) engine=innodb
50
key_block_size=1 row_format=compact;
52
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
53
create table t10(a int primary key) engine=innodb
54
key_block_size=1 row_format=dynamic;
56
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
57
create table t11(a int primary key) engine=innodb
58
key_block_size=1 row_format=compressed;
59
create table t12(a int primary key) engine=innodb
61
create table t13(a int primary key) engine=innodb
62
row_format=compressed;
63
create table t14(a int primary key) engine=innodb key_block_size=9;
65
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
66
SELECT table_schema, table_name, row_format, data_length, index_length
67
FROM information_schema.tables WHERE engine='innodb';
68
table_schema table_name row_format data_length index_length
69
test t0 Compact 16384 0
70
test t00 Compact 16384 0
71
test t1 Compact 16384 0
72
test t10 Dynamic 16384 0
73
test t11 Compressed 1024 0
74
test t12 Compressed 1024 0
75
test t13 Compressed 8192 0
76
test t14 Compact 16384 0
77
test t2 Redundant 16384 0
78
test t3 Compact 16384 0
79
test t4 Compact 16384 0
80
test t5 Redundant 16384 0
81
test t6 Redundant 16384 0
82
test t7 Redundant 16384 0
83
test t8 Compact 16384 0
84
test t9 Compact 16384 0
85
drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
86
alter table t1 key_block_size=0;
87
alter table t1 row_format=dynamic;
88
SELECT table_schema, table_name, row_format, data_length, index_length
89
FROM information_schema.tables WHERE engine='innodb';
90
table_schema table_name row_format data_length index_length
91
test t1 Dynamic 16384 0
92
alter table t1 row_format=compact;
93
SELECT table_schema, table_name, row_format, data_length, index_length
94
FROM information_schema.tables WHERE engine='innodb';
95
table_schema table_name row_format data_length index_length
96
test t1 Compact 16384 0
97
alter table t1 row_format=redundant;
98
SELECT table_schema, table_name, row_format, data_length, index_length
99
FROM information_schema.tables WHERE engine='innodb';
100
table_schema table_name row_format data_length index_length
101
test t1 Redundant 16384 0
103
create table t1(a int not null, b text, index(b(10))) engine=innodb
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;
110
update t1 set b=repeat('B',100);
111
select a,left(b,40) from t1 natural join t2;
113
1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
115
select a,left(b,40) from t1 natural join t2;
117
1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
118
SELECT table_schema, table_name, row_format, data_length, index_length
119
FROM information_schema.tables WHERE engine='innodb';
120
table_schema table_name row_format data_length index_length
121
test t1 Compressed 2048 1024
122
test t2 Compact 16384 0
124
SET SESSION innodb_strict_mode = off;
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
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
131
c TEXT NOT NULL, d TEXT NOT NULL,
132
PRIMARY KEY (c(767),d(767)))
133
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
134
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
136
c TEXT NOT NULL, d TEXT NOT NULL,
137
PRIMARY KEY (c(767),d(767)))
138
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
140
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
141
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
142
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
143
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438)))
144
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
145
INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
147
create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
148
primary key(c1, c2(22), c3(22)))
149
engine = innodb row_format = dynamic;
151
insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
153
update t1 set c3 = repeat('D', 20000) where c1 = 1;
155
select count(*) from t1 where c2 = repeat('A', 20000);
158
select count(*) from t1 where c3 = repeat('D', 20000);
161
select count(*) from t1 where c4 = repeat('C', 20000);
164
update t1 set c3 = repeat('E', 20000) where c1 = 1;
166
set global innodb_file_format=`0`;
167
select @@innodb_file_format;
170
set global innodb_file_format=`1`;
171
select @@innodb_file_format;
174
set global innodb_file_format=`2`;
175
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of '2'
176
set global innodb_file_format=`-1`;
177
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of '-1'
178
set global innodb_file_format=`Antelope`;
179
set global innodb_file_format=`Barracuda`;
180
set global innodb_file_format=`Cheetah`;
181
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of 'Cheetah'
182
set global innodb_file_format=`abc`;
183
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of 'abc'
184
set global innodb_file_format=`1a`;
185
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of '1a'
186
set global innodb_file_format=``;
187
ERROR 42000: Variable 'innodb_file_format' can't be set to the value of ''
188
set global innodb_file_per_table = on;
189
set global innodb_file_format = `1`;
190
set innodb_strict_mode = off;
191
create table t1 (id int primary key) engine = innodb key_block_size = 0;
193
set innodb_strict_mode = on;
194
create table t1 (id int primary key) engine = innodb key_block_size = 0;
195
create table t2 (id int primary key) engine = innodb key_block_size = 9;
196
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
199
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
200
Error 1005 Can't create table 'test.t2' (errno: 1478)
201
create table t3 (id int primary key) engine = innodb key_block_size = 1;
202
create table t4 (id int primary key) engine = innodb key_block_size = 2;
203
create table t5 (id int primary key) engine = innodb key_block_size = 4;
204
create table t6 (id int primary key) engine = innodb key_block_size = 8;
205
create table t7 (id int primary key) engine = innodb key_block_size = 16;
206
create table t8 (id int primary key) engine = innodb row_format = compressed;
207
create table t9 (id int primary key) engine = innodb row_format = dynamic;
208
create table t10(id int primary key) engine = innodb row_format = compact;
209
create table t11(id int primary key) engine = innodb row_format = redundant;
210
SELECT table_schema, table_name, row_format, data_length, index_length
211
FROM information_schema.tables WHERE engine='innodb';
212
table_schema table_name row_format data_length index_length
213
test t1 Compact 16384 0
214
test t10 Compact 16384 0
215
test t11 Redundant 16384 0
216
test t3 Compressed 1024 0
217
test t4 Compressed 2048 0
218
test t5 Compressed 4096 0
219
test t6 Compressed 8192 0
220
test t7 Compressed 16384 0
221
test t8 Compressed 8192 0
222
test t9 Dynamic 16384 0
223
drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11;
224
create table t1 (id int primary key) engine = innodb
225
key_block_size = 8 row_format = compressed;
226
create table t2 (id int primary key) engine = innodb
227
key_block_size = 8 row_format = redundant;
228
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
231
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
232
Error 1005 Can't create table 'test.t2' (errno: 1478)
233
create table t3 (id int primary key) engine = innodb
234
key_block_size = 8 row_format = compact;
235
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
238
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
239
Error 1005 Can't create table 'test.t3' (errno: 1478)
240
create table t4 (id int primary key) engine = innodb
241
key_block_size = 8 row_format = dynamic;
242
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
245
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
246
Error 1005 Can't create table 'test.t4' (errno: 1478)
247
create table t5 (id int primary key) engine = innodb
248
key_block_size = 8 row_format = default;
249
SELECT table_schema, table_name, row_format, data_length, index_length
250
FROM information_schema.tables WHERE engine='innodb';
251
table_schema table_name row_format data_length index_length
252
test t1 Compressed 8192 0
253
test t5 Compressed 8192 0
255
create table t1 (id int primary key) engine = innodb
256
key_block_size = 9 row_format = redundant;
257
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
260
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
261
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
262
Error 1005 Can't create table 'test.t1' (errno: 1478)
263
create table t2 (id int primary key) engine = innodb
264
key_block_size = 9 row_format = compact;
265
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
268
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
269
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
270
Error 1005 Can't create table 'test.t2' (errno: 1478)
271
create table t2 (id int primary key) engine = innodb
272
key_block_size = 9 row_format = dynamic;
273
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
276
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
277
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
278
Error 1005 Can't create table 'test.t2' (errno: 1478)
279
SELECT table_schema, table_name, row_format, data_length, index_length
280
FROM information_schema.tables WHERE engine='innodb';
281
table_schema table_name row_format data_length index_length
282
set global innodb_file_per_table = off;
283
create table t1 (id int primary key) engine = innodb key_block_size = 1;
284
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
287
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
288
Error 1005 Can't create table 'test.t1' (errno: 1478)
289
create table t2 (id int primary key) engine = innodb key_block_size = 2;
290
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
293
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
294
Error 1005 Can't create table 'test.t2' (errno: 1478)
295
create table t3 (id int primary key) engine = innodb key_block_size = 4;
296
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
299
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
300
Error 1005 Can't create table 'test.t3' (errno: 1478)
301
create table t4 (id int primary key) engine = innodb key_block_size = 8;
302
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
305
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
306
Error 1005 Can't create table 'test.t4' (errno: 1478)
307
create table t5 (id int primary key) engine = innodb key_block_size = 16;
308
ERROR HY000: Can't create table 'test.t5' (errno: 1478)
311
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
312
Error 1005 Can't create table 'test.t5' (errno: 1478)
313
create table t6 (id int primary key) engine = innodb row_format = compressed;
314
ERROR HY000: Can't create table 'test.t6' (errno: 1478)
317
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
318
Error 1005 Can't create table 'test.t6' (errno: 1478)
319
create table t7 (id int primary key) engine = innodb row_format = dynamic;
320
ERROR HY000: Can't create table 'test.t7' (errno: 1478)
323
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
324
Error 1005 Can't create table 'test.t7' (errno: 1478)
325
create table t8 (id int primary key) engine = innodb row_format = compact;
326
create table t9 (id int primary key) engine = innodb row_format = redundant;
327
SELECT table_schema, table_name, row_format, data_length, index_length
328
FROM information_schema.tables WHERE engine='innodb';
329
table_schema table_name row_format data_length index_length
330
test t8 Compact 16384 0
331
test t9 Redundant 16384 0
333
set global innodb_file_per_table = on;
334
set global innodb_file_format = `0`;
335
create table t1 (id int primary key) engine = innodb key_block_size = 1;
336
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
339
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
340
Error 1005 Can't create table 'test.t1' (errno: 1478)
341
create table t2 (id int primary key) engine = innodb key_block_size = 2;
342
ERROR HY000: Can't create table 'test.t2' (errno: 1478)
345
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
346
Error 1005 Can't create table 'test.t2' (errno: 1478)
347
create table t3 (id int primary key) engine = innodb key_block_size = 4;
348
ERROR HY000: Can't create table 'test.t3' (errno: 1478)
351
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
352
Error 1005 Can't create table 'test.t3' (errno: 1478)
353
create table t4 (id int primary key) engine = innodb key_block_size = 8;
354
ERROR HY000: Can't create table 'test.t4' (errno: 1478)
357
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
358
Error 1005 Can't create table 'test.t4' (errno: 1478)
359
create table t5 (id int primary key) engine = innodb key_block_size = 16;
360
ERROR HY000: Can't create table 'test.t5' (errno: 1478)
363
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
364
Error 1005 Can't create table 'test.t5' (errno: 1478)
365
create table t6 (id int primary key) engine = innodb row_format = compressed;
366
ERROR HY000: Can't create table 'test.t6' (errno: 1478)
369
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
370
Error 1005 Can't create table 'test.t6' (errno: 1478)
371
create table t7 (id int primary key) engine = innodb row_format = dynamic;
372
ERROR HY000: Can't create table 'test.t7' (errno: 1478)
375
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
376
Error 1005 Can't create table 'test.t7' (errno: 1478)
377
create table t8 (id int primary key) engine = innodb row_format = compact;
378
create table t9 (id int primary key) engine = innodb row_format = redundant;
379
SELECT table_schema, table_name, row_format, data_length, index_length
380
FROM information_schema.tables WHERE engine='innodb';
381
table_schema table_name row_format data_length index_length
382
test t8 Compact 16384 0
383
test t9 Redundant 16384 0
385
set global innodb_file_per_table=0;
386
set global innodb_file_format=Antelope;
387
set global innodb_file_per_table=on;
388
set global innodb_file_format=`Barracuda`;
389
set global innodb_file_format_max=`Antelope`;
390
create table normal_table (
393
select @@innodb_file_format_max;
394
@@innodb_file_format_max
396
create table zip_table (
398
) engine = innodb key_block_size = 8;
399
select @@innodb_file_format_max;
400
@@innodb_file_format_max
402
set global innodb_file_format_max=`Antelope`;
403
select @@innodb_file_format_max;
404
@@innodb_file_format_max
407
select @@innodb_file_format_max;
408
@@innodb_file_format_max
410
drop table normal_table, zip_table;