2
let $per_table=`select @@innodb_file_per_table`;
3
let $format=`select @@innodb_file_format`;
4
let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
5
set global innodb_file_per_table=off;
6
set global innodb_file_format=`0`;
8
create table t0(a int primary key) engine=innodb row_format=compressed;
9
create table t00(a int primary key) engine=innodb
10
key_block_size=4 row_format=compressed;
11
create table t1(a int primary key) engine=innodb row_format=dynamic;
12
create table t2(a int primary key) engine=innodb row_format=redundant;
13
create table t3(a int primary key) engine=innodb row_format=compact;
14
create table t4(a int primary key) engine=innodb key_block_size=9;
15
create table t5(a int primary key) engine=innodb
16
key_block_size=1 row_format=redundant;
18
set global innodb_file_per_table=on;
19
create table t6(a int primary key) engine=innodb
20
key_block_size=1 row_format=redundant;
21
set global innodb_file_format=`1`;
22
create table t7(a int primary key) engine=innodb
23
key_block_size=1 row_format=redundant;
24
create table t8(a int primary key) engine=innodb
25
key_block_size=1 row_format=fixed;
26
create table t9(a int primary key) engine=innodb
27
key_block_size=1 row_format=compact;
28
create table t10(a int primary key) engine=innodb
29
key_block_size=1 row_format=dynamic;
30
create table t11(a int primary key) engine=innodb
31
key_block_size=1 row_format=compressed;
32
create table t12(a int primary key) engine=innodb
34
create table t13(a int primary key) engine=innodb
35
row_format=compressed;
36
create table t14(a int primary key) engine=innodb key_block_size=9;
38
SELECT table_schema, table_name, row_format
39
FROM information_schema.tables WHERE engine='innodb';
41
drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
42
alter table t1 key_block_size=0;
43
alter table t1 row_format=dynamic;
44
SELECT table_schema, table_name, row_format
45
FROM information_schema.tables WHERE engine='innodb';
46
alter table t1 row_format=compact;
47
SELECT table_schema, table_name, row_format
48
FROM information_schema.tables WHERE engine='innodb';
49
alter table t1 row_format=redundant;
50
SELECT table_schema, table_name, row_format
51
FROM information_schema.tables WHERE engine='innodb';
54
create table t1(a int not null, b text, index(b(10))) engine=innodb
57
create table t2(b text)engine=innodb;
58
insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
60
insert into t1 select 1, b from t2;
63
connect (a,localhost,root,,);
64
connect (b,localhost,root,,);
68
update t1 set b=repeat('B',100);
71
select a,left(b,40) from t1 natural join t2;
77
select a,left(b,40) from t1 natural join t2;
83
SELECT table_schema, table_name, row_format
84
FROM information_schema.tables WHERE engine='innodb';
87
# The following should fail even in non-strict mode.
88
SET SESSION innodb_strict_mode = off;
89
--error ER_TOO_BIG_ROWSIZE
91
c TEXT NOT NULL, d TEXT NOT NULL,
92
PRIMARY KEY (c(767),d(767)))
93
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
94
--error ER_TOO_BIG_ROWSIZE
96
c TEXT NOT NULL, d TEXT NOT NULL,
97
PRIMARY KEY (c(767),d(767)))
98
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
100
c TEXT NOT NULL, d TEXT NOT NULL,
101
PRIMARY KEY (c(767),d(767)))
102
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
104
--error ER_TOO_BIG_ROWSIZE
105
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
106
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
107
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))
108
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
109
INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
113
# Test blob column inheritance (mantis issue#36)
116
create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
117
primary key(c1, c2(22), c3(22)))
118
engine = innodb row_format = dynamic;
120
insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
123
update t1 set c3 = repeat('D', 20000) where c1 = 1;
126
# one blob column which is unchanged in update and part of PK
127
# one blob column which is changed and part of of PK
128
# one blob column which is not part of PK and is unchanged
129
select count(*) from t1 where c2 = repeat('A', 20000);
130
select count(*) from t1 where c3 = repeat('D', 20000);
131
select count(*) from t1 where c4 = repeat('C', 20000);
133
update t1 set c3 = repeat('E', 20000) where c1 = 1;
138
# Test innodb_file_format
140
set global innodb_file_format=`0`;
141
select @@innodb_file_format;
142
set global innodb_file_format=`1`;
143
select @@innodb_file_format;
144
set global innodb_file_format=`2`;
145
set global innodb_file_format=`-1`;
146
set global innodb_file_format=`Antelope`;
147
set global innodb_file_format=`Barracuda`;
148
set global innodb_file_format=`Cheetah`;
149
set global innodb_file_format=`abc`;
150
set global innodb_file_format=`1a`;
151
set global innodb_file_format=``;
154
# this does not work anymore, has been removed from mysqltest
156
set global innodb_file_per_table = on;
157
set global innodb_file_format = `1`;
159
set innodb_strict_mode = off;
160
create table t1 (id int primary key) engine = innodb key_block_size = 0;
164
set innodb_strict_mode = on;
166
#Test different values of KEY_BLOCK_SIZE
168
--error ER_CANT_CREATE_TABLE
169
create table t1 (id int primary key) engine = innodb key_block_size = 0;
172
--error ER_CANT_CREATE_TABLE
173
create table t2 (id int primary key) engine = innodb key_block_size = 9;
177
create table t3 (id int primary key) engine = innodb key_block_size = 1;
178
create table t4 (id int primary key) engine = innodb key_block_size = 2;
179
create table t5 (id int primary key) engine = innodb key_block_size = 4;
180
create table t6 (id int primary key) engine = innodb key_block_size = 8;
181
create table t7 (id int primary key) engine = innodb key_block_size = 16;
183
#check various ROW_FORMAT values.
184
create table t8 (id int primary key) engine = innodb row_format = compressed;
185
create table t9 (id int primary key) engine = innodb row_format = dynamic;
186
create table t10(id int primary key) engine = innodb row_format = compact;
187
create table t11(id int primary key) engine = innodb row_format = redundant;
189
SELECT table_schema, table_name, row_format
190
FROM information_schema.tables WHERE engine='innodb';
191
drop table t3, t4, t5, t6, t7, t8, t9, t10, t11;
193
#test different values of ROW_FORMAT with KEY_BLOCK_SIZE
194
create table t1 (id int primary key) engine = innodb
195
key_block_size = 8 row_format = compressed;
197
--error ER_CANT_CREATE_TABLE
198
create table t2 (id int primary key) engine = innodb
199
key_block_size = 8 row_format = redundant;
202
--error ER_CANT_CREATE_TABLE
203
create table t3 (id int primary key) engine = innodb
204
key_block_size = 8 row_format = compact;
207
--error ER_CANT_CREATE_TABLE
208
create table t4 (id int primary key) engine = innodb
209
key_block_size = 8 row_format = dynamic;
212
--error ER_CANT_CREATE_TABLE
213
create table t5 (id int primary key) engine = innodb
214
key_block_size = 8 row_format = default;
217
SELECT table_schema, table_name, row_format
218
FROM information_schema.tables WHERE engine='innodb';
221
#test multiple errors
222
--error ER_CANT_CREATE_TABLE
223
create table t1 (id int primary key) engine = innodb
224
key_block_size = 9 row_format = redundant;
227
--error ER_CANT_CREATE_TABLE
228
create table t2 (id int primary key) engine = innodb
229
key_block_size = 9 row_format = compact;
232
--error ER_CANT_CREATE_TABLE
233
create table t2 (id int primary key) engine = innodb
234
key_block_size = 9 row_format = dynamic;
237
SELECT table_schema, table_name, row_format
238
FROM information_schema.tables WHERE engine='innodb';
240
#test valid values with innodb_file_per_table unset
241
set global innodb_file_per_table = off;
243
--error ER_CANT_CREATE_TABLE
244
create table t1 (id int primary key) engine = innodb key_block_size = 1;
246
--error ER_CANT_CREATE_TABLE
247
create table t2 (id int primary key) engine = innodb key_block_size = 2;
249
--error ER_CANT_CREATE_TABLE
250
create table t3 (id int primary key) engine = innodb key_block_size = 4;
252
--error ER_CANT_CREATE_TABLE
253
create table t4 (id int primary key) engine = innodb key_block_size = 8;
255
--error ER_CANT_CREATE_TABLE
256
create table t5 (id int primary key) engine = innodb key_block_size = 16;
258
--error ER_CANT_CREATE_TABLE
259
create table t6 (id int primary key) engine = innodb row_format = compressed;
261
--error ER_CANT_CREATE_TABLE
262
create table t7 (id int primary key) engine = innodb row_format = dynamic;
264
create table t8 (id int primary key) engine = innodb row_format = compact;
265
create table t9 (id int primary key) engine = innodb row_format = redundant;
267
SELECT table_schema, table_name, row_format
268
FROM information_schema.tables WHERE engine='innodb';
271
#test valid values with innodb_file_format unset
272
set global innodb_file_per_table = on;
273
set global innodb_file_format = `0`;
275
--error ER_CANT_CREATE_TABLE
276
create table t1 (id int primary key) engine = innodb key_block_size = 1;
278
--error ER_CANT_CREATE_TABLE
279
create table t2 (id int primary key) engine = innodb key_block_size = 2;
281
--error ER_CANT_CREATE_TABLE
282
create table t3 (id int primary key) engine = innodb key_block_size = 4;
284
--error ER_CANT_CREATE_TABLE
285
create table t4 (id int primary key) engine = innodb key_block_size = 8;
287
--error ER_CANT_CREATE_TABLE
288
create table t5 (id int primary key) engine = innodb key_block_size = 16;
290
--error ER_CANT_CREATE_TABLE
291
create table t6 (id int primary key) engine = innodb row_format = compressed;
293
--error ER_CANT_CREATE_TABLE
294
create table t7 (id int primary key) engine = innodb row_format = dynamic;
296
create table t8 (id int primary key) engine = innodb row_format = compact;
297
create table t9 (id int primary key) engine = innodb row_format = redundant;
299
SELECT table_schema, table_name, row_format
300
FROM information_schema.tables WHERE engine='innodb';
303
eval set global innodb_file_per_table=$per_table;
304
eval set global innodb_file_format=$format;
306
# Testing of tablespace tagging
308
set global innodb_file_per_table=on;
309
set global innodb_file_format=`Barracuda`;
310
set global innodb_file_format_check=`Antelope`;
311
create table normal_table (
314
select @@innodb_file_format_check;
315
create table zip_table (
317
) engine = innodb key_block_size = 8;
318
select @@innodb_file_format_check;
319
set global innodb_file_format_check=`Antelope`;
320
select @@innodb_file_format_check;
322
select @@innodb_file_format_check;
323
drop table normal_table, zip_table;
326
# restore environment to the state it was before this test execution
329
eval set global innodb_file_format=$format;
330
eval set global innodb_file_per_table=$per_table;
331
eval set global innodb_file_format_check=$innodb_file_format_check_orig;