Debian Codegraph @

mysql-test/suite/innodb_zip/t/innodb-zip.test

raw
1 --source include/innodb_page_size_small.inc
2
3 CREATE DATABASE mysqltest_innodb_zip;
4 USE mysqltest_innodb_zip;
5 SELECT table_name, row_format, data_length, index_length
6 FROM information_schema.tables
7 WHERE table_schema='mysqltest_innodb_zip';
8
9 SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
10
11 --let $query_i_s = 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
12
13 set session innodb_strict_mode=0;
14 SET @save_fpt=@@GLOBAL.innodb_file_per_table;
15 set global innodb_file_per_table=off;
16 SET @@global.innodb_stats_on_metadata=ON;
17
18 create table t0(a int primary key) engine=innodb row_format=compressed;
19 create table t00(a int primary key) engine=innodb
20 key_block_size=4 row_format=compressed;
21 create table t1(a int primary key) engine=innodb row_format=dynamic;
22 create table t2(a int primary key) engine=innodb row_format=redundant;
23 create table t3(a int primary key) engine=innodb row_format=compact;
24 create table t4(a int primary key) engine=innodb key_block_size=9;
25 create table t5(a int primary key) engine=innodb
26 key_block_size=1 row_format=redundant;
27
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 create table t7(a int primary key) engine=innodb
32 key_block_size=1 row_format=redundant;
33 create table t8(a int primary key) engine=innodb
34 key_block_size=1 row_format=fixed;
35 create table t9(a int primary key) engine=innodb
36 key_block_size=1 row_format=compact;
37 create table t10(a int primary key) engine=innodb
38 key_block_size=1 row_format=dynamic;
39 create table t11(a int primary key) engine=innodb
40 key_block_size=1 row_format=compressed;
41 create table t12(a int primary key) engine=innodb
42 key_block_size=1;
43 create table t13(a int primary key) engine=innodb
44 row_format=compressed;
45 create table t14(a int primary key) engine=innodb key_block_size=9;
46
47 --replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
48 --eval $query_i_s
49
50 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
51 alter table t1 key_block_size=0;
52 alter table t1 row_format=dynamic;
53 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
54 --eval $query_i_s
55 alter table t1 row_format=compact;
56 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
57 --eval $query_i_s
58 alter table t1 row_format=redundant;
59 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
60 --eval $query_i_s
61 drop table t1;
62
63 create table t1(a int not null, b text, index(b(10))) engine=innodb
64 key_block_size=1;
65
66 create table t2(b text)engine=innodb;
67 insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
68
69 insert into t1 select 1, b from t2;
70 commit;
71
72 connect (a,localhost,root,,mysqltest_innodb_zip);
73 connect (b,localhost,root,,mysqltest_innodb_zip);
74
75 connection a;
76 begin;
77 update t1 set b=repeat('B',100);
78
79 connection b;
80 select a,left(b,40) from t1 natural join t2;
81
82 connection a;
83 rollback;
84
85 connection b;
86 select a,left(b,40) from t1 natural join t2;
87
88 connection default;
89 disconnect a;
90 disconnect b;
91
92 analyze table t1;
93 analyze table t2;
94 --replace_result 16384 {valid} 12288 {valid}
95 --eval $query_i_s
96 drop table t1,t2;
97
98 #
99 # Bug #50945 moved to innodb_16k.test, innodb_8k.test, & innodb_4k.test
100 #
101
102 #
103 # Test blob column inheritance (mantis issue#36)
104 #
105
106 create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
107 primary key(c1, c2(22), c3(22)))
108 engine = innodb row_format = dynamic;
109 begin;
110 insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
111 repeat('C', 20000));
112
113 update t1 set c3 = repeat('D', 20000) where c1 = 1;
114 commit;
115
116 # one blob column which is unchanged in update and part of PK
117 # one blob column which is changed and part of of PK
118 # one blob column which is not part of PK and is unchanged
119 select count(*) from t1 where c2 = repeat('A', 20000);
120 select count(*) from t1 where c3 = repeat('D', 20000);
121 select count(*) from t1 where c4 = repeat('C', 20000);
122
123 update t1 set c3 = repeat('E', 20000) where c1 = 1;
124 drop table t1;
125
126 set innodb_strict_mode = off;
127 create table t1 (id int primary key) engine = innodb key_block_size = 0;
128 drop table t1;
129
130 #set strict_mode
131 set innodb_strict_mode = on;
132
133 #Test different values of KEY_BLOCK_SIZE
134
135 create table t1 (id int primary key) engine = innodb key_block_size = 0;
136
137 --replace_regex / - .*[0-9]*[)]/)/
138 --error ER_CANT_CREATE_TABLE
139 create table t2 (id int primary key) engine = innodb key_block_size = 9;
140 --replace_regex / - .*[0-9]*[)]/)/
141 show warnings;
142
143
144 create table t3 (id int primary key) engine = innodb key_block_size = 1;
145 create table t4 (id int primary key) engine = innodb key_block_size = 2;
146 create table t5 (id int primary key) engine = innodb key_block_size = 4;
147 # These tests are now done in innodb_16k, innodb_8k and innodb_4k
148 # where they get different result depending on page size
149 # create table t6 (id int primary key) engine = innodb key_block_size = 8;
150 # create table t7 (id int primary key) engine = innodb key_block_size = 16;
151
152 #check various ROW_FORMAT values.
153 create table t8 (id int primary key) engine = innodb row_format = compressed;
154 create table t9 (id int primary key) engine = innodb row_format = dynamic;
155 create table t10(id int primary key) engine = innodb row_format = compact;
156 create table t11(id int primary key) engine = innodb row_format = redundant;
157
158 --replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
159 --eval $query_i_s
160 drop table t1, t3, t4, t5, t8, t9, t10, t11;
161
162 #test different values of ROW_FORMAT with KEY_BLOCK_SIZE
163 create table t1 (id int primary key) engine = innodb
164 key_block_size = 4 row_format = compressed;
165
166 --replace_regex / - .*[0-9]*[)]/)/
167 --error ER_CANT_CREATE_TABLE
168 create table t2 (id int primary key) engine = innodb
169 key_block_size = 4 row_format = redundant;
170 --replace_regex / - .*[0-9]*[)]/)/
171 show warnings;
172
173 --replace_regex / - .*[0-9]*[)]/)/
174 --error ER_CANT_CREATE_TABLE
175 create table t3 (id int primary key) engine = innodb
176 key_block_size = 4 row_format = compact;
177 --replace_regex / - .*[0-9]*[)]/)/
178 show warnings;
179
180 --replace_regex / - .*[0-9]*[)]/)/
181 --error ER_CANT_CREATE_TABLE
182 create table t4 (id int primary key) engine = innodb
183 key_block_size = 4 row_format = dynamic;
184 --replace_regex / - .*[0-9]*[)]/)/
185 show warnings;
186
187 create table t5 (id int primary key) engine = innodb
188 key_block_size = 4 row_format = default;
189
190 --eval $query_i_s
191 drop table t1, t5;
192
193 #test multiple errors
194 --replace_regex / - .*[0-9]*[)]/)/
195 --error ER_CANT_CREATE_TABLE
196 create table t1 (id int primary key) engine = innodb
197 key_block_size = 9 row_format = redundant;
198 --replace_regex / - .*[0-9]*[)]/)/
199 show warnings;
200
201 --replace_regex / - .*[0-9]*[)]/)/
202 --error ER_CANT_CREATE_TABLE
203 create table t2 (id int primary key) engine = innodb
204 key_block_size = 9 row_format = compact;
205 --replace_regex / - .*[0-9]*[)]/)/
206 show warnings;
207
208 --replace_regex / - .*[0-9]*[)]/)/
209 --error ER_CANT_CREATE_TABLE
210 create table t2 (id int primary key) engine = innodb
211 key_block_size = 9 row_format = dynamic;
212 --replace_regex / - .*[0-9]*[)]/)/
213 show warnings;
214
215 --eval $query_i_s
216
217 #test valid values with innodb_file_per_table unset
218 set global innodb_file_per_table = off;
219
220 --replace_regex / - .*[0-9]*[)]/)/
221 --error ER_CANT_CREATE_TABLE
222 create table t1 (id int primary key) engine = innodb key_block_size = 1;
223 --replace_regex / - .*[0-9]*[)]/)/
224 show warnings;
225 --replace_regex / - .*[0-9]*[)]/)/
226 --error ER_CANT_CREATE_TABLE
227 create table t2 (id int primary key) engine = innodb key_block_size = 2;
228 --replace_regex / - .*[0-9]*[)]/)/
229 show warnings;
230 --replace_regex / - .*[0-9]*[)]/)/
231 --error ER_CANT_CREATE_TABLE
232 create table t3 (id int primary key) engine = innodb key_block_size = 4;
233 show warnings;
234
235 # Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k
236 # and innodb_4k since they get different warnings with smaller page sizes.
237
238 --error ER_CANT_CREATE_TABLE
239 create table t6 (id int primary key) engine = innodb row_format = compressed;
240 --replace_regex / - .*[0-9]*[)]/)/
241 show warnings;
242 create table t7 (id int primary key) engine = innodb row_format = dynamic;
243 show warnings;
244 create table t8 (id int primary key) engine = innodb row_format = compact;
245 create table t9 (id int primary key) engine = innodb row_format = redundant;
246
247 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
248 --eval $query_i_s
249 drop table t7, t8, t9;
250
251 #
252 # restore environment to the state it was before this test execution
253 #
254
255 SET GLOBAL innodb_file_per_table=@save_fpt;
256 SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;
257
258 DROP DATABASE mysqltest_innodb_zip;