Debian Codegraph @

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

raw
1 -- source include/have_innodb_max_16k.inc
2
3 let $per_table=`select @@innodb_file_per_table`;
4 SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
5
6
7 --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' AND table_schema != 'PERFORMANCE_SCHEMA' AND table_schema != 'information_schema' ORDER BY table_schema, table_name;
8
9 set session innodb_strict_mode=0;
10 set global innodb_file_per_table=off;
11 SET @@global.innodb_stats_on_metadata=ON;
12
13 create table t0(a int primary key) engine=innodb row_format=compressed;
14 create table t00(a int primary key) engine=innodb
15 key_block_size=4 row_format=compressed;
16 create table t1(a int primary key) engine=innodb row_format=dynamic;
17 create table t2(a int primary key) engine=innodb row_format=redundant;
18 create table t3(a int primary key) engine=innodb row_format=compact;
19 create table t4(a int primary key) engine=innodb key_block_size=9;
20 create table t5(a int primary key) engine=innodb
21 key_block_size=1 row_format=redundant;
22
23 set global innodb_file_per_table=on;
24 create table t6(a int primary key) engine=innodb
25 key_block_size=1 row_format=redundant;
26 create table t7(a int primary key) engine=innodb
27 key_block_size=1 row_format=redundant;
28 create table t8(a int primary key) engine=innodb
29 key_block_size=1 row_format=fixed;
30 create table t9(a int primary key) engine=innodb
31 key_block_size=1 row_format=compact;
32 create table t10(a int primary key) engine=innodb
33 key_block_size=1 row_format=dynamic;
34 create table t11(a int primary key) engine=innodb
35 key_block_size=1 row_format=compressed;
36 create table t12(a int primary key) engine=innodb
37 key_block_size=1;
38 create table t13(a int primary key) engine=innodb
39 row_format=compressed;
40 create table t14(a int primary key) engine=innodb key_block_size=9;
41
42 analyze table t0 ,t00 ,t1 ,t2 ,t3 ,t4 ,t5 ,t6 ,t7 ,t8 ,t9 ,t10 ,t11 ,t12 ,t13 ,t14;
43
44 --replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
45 --eval $query_i_s
46
47 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
48 alter table t1 key_block_size=0;
49 alter table t1 row_format=dynamic;
50 analyze table t1;
51 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
52 --eval $query_i_s
53 alter table t1 row_format=compact;
54 analyze table t1;
55 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
56 --eval $query_i_s
57 alter table t1 row_format=redundant;
58 analyze table t1;
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,,);
73 connect (b,localhost,root,,);
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} 8192 {valid} 4096 {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 #test strict mode.
127 # this does not work anymore, has been removed from mysqltest
128 # -- enable_errors
129 set global innodb_file_per_table = on;
130
131 set innodb_strict_mode = off;
132 create table t1 (id int primary key) engine = innodb key_block_size = 0;
133 drop table t1;
134
135 #set strict_mode
136 set innodb_strict_mode = on;
137
138 #Test different values of KEY_BLOCK_SIZE
139
140 create table t1 (id int primary key) engine = innodb key_block_size = 0;
141
142 --error ER_ILLEGAL_HA
143 create table t2 (id int primary key) engine = innodb key_block_size = 9;
144 show warnings;
145
146
147 create table t3 (id int primary key) engine = innodb key_block_size = 1;
148 create table t4 (id int primary key) engine = innodb key_block_size = 2;
149 create table t5 (id int primary key) engine = innodb key_block_size = 4;
150 # These tests are now done in innodb_16k, innodb_8k and innodb_4k
151 # where they get different result depending on page size
152 # create table t6 (id int primary key) engine = innodb key_block_size = 8;
153 # create table t7 (id int primary key) engine = innodb key_block_size = 16;
154
155 #check various ROW_FORMAT values.
156 create table t8 (id int primary key) engine = innodb row_format = compressed;
157 create table t9 (id int primary key) engine = innodb row_format = dynamic;
158 create table t10(id int primary key) engine = innodb row_format = compact;
159 create table t11(id int primary key) engine = innodb row_format = redundant;
160
161 analyze table t1;
162 analyze table t3;
163 analyze table t4;
164 analyze table t5;
165 analyze table t8;
166 analyze table t9;
167 analyze table t10;
168 analyze table t11;
169 --replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
170 --eval $query_i_s
171 drop table t1, t3, t4, t5, t8, t9, t10, t11;
172
173 #test different values of ROW_FORMAT with KEY_BLOCK_SIZE
174 create table t1 (id int primary key) engine = innodb
175 key_block_size = 4 row_format = compressed;
176
177 --error ER_ILLEGAL_HA
178 create table t2 (id int primary key) engine = innodb
179 key_block_size = 4 row_format = redundant;
180 show warnings;
181
182 --error ER_ILLEGAL_HA
183 create table t3 (id int primary key) engine = innodb
184 key_block_size = 4 row_format = compact;
185 show warnings;
186
187 --error ER_ILLEGAL_HA
188 create table t4 (id int primary key) engine = innodb
189 key_block_size = 4 row_format = dynamic;
190 show warnings;
191
192 create table t5 (id int primary key) engine = innodb
193 key_block_size = 4 row_format = default;
194
195 analyze table t1;
196 analyze table t5;
197
198 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
199 --eval $query_i_s
200 drop table t1, t5;
201
202 #test multiple errors
203 --error ER_ILLEGAL_HA
204 create table t1 (id int primary key) engine = innodb
205 key_block_size = 9 row_format = redundant;
206 show warnings;
207
208 --error ER_ILLEGAL_HA
209 create table t2 (id int primary key) engine = innodb
210 key_block_size = 9 row_format = compact;
211 show warnings;
212
213 --error ER_ILLEGAL_HA
214 create table t2 (id int primary key) engine = innodb
215 key_block_size = 9 row_format = dynamic;
216 show warnings;
217
218 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
219 --eval $query_i_s
220
221 #test valid values with innodb_file_per_table unset
222 set global innodb_file_per_table = off;
223
224 --error ER_ILLEGAL_HA
225 create table t1 (id int primary key) engine = innodb key_block_size = 1;
226 show warnings;
227 --error ER_ILLEGAL_HA
228 create table t2 (id int primary key) engine = innodb key_block_size = 2;
229 show warnings;
230 --error ER_ILLEGAL_HA
231 create table t3 (id int primary key) engine = innodb key_block_size = 4;
232 show warnings;
233
234 # Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k
235 # and innodb_4k since they get different warnings with smaller page sizes.
236
237 --error ER_ILLEGAL_HA
238 create table t6 (id int primary key) engine = innodb row_format = compressed;
239 show warnings;
240 create table t7 (id int primary key) engine = innodb row_format = dynamic;
241 create table t8 (id int primary key) engine = innodb row_format = compact;
242 create table t9 (id int primary key) engine = innodb row_format = redundant;
243
244 analyze table t7;
245 analyze table t8;
246 analyze table t9;
247 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
248 --eval $query_i_s
249 drop table t7, t8, t9;
250
251 set global innodb_file_per_table = on;
252
253 create table t8 (id int primary key) engine = innodb row_format = compact;
254 create table t9 (id int primary key) engine = innodb row_format = redundant;
255
256 analyze table t8;
257 analyze table t9;
258
259 --replace_result 16384 {valid} 8192 {valid} 4096 {valid}
260 --eval $query_i_s
261 drop table t8, t9;
262
263 eval set global innodb_file_per_table=$per_table;
264 #
265 # Testing of tablespace tagging
266 #
267 -- disable_info
268 set global innodb_file_per_table=on;
269 create table normal_table (
270 c1 int
271 ) engine = innodb;
272 create table zip_table (
273 c1 int
274 ) engine = innodb key_block_size = 4;
275 drop table normal_table, zip_table;
276
277 #
278 # restore environment to the state it was before this test execution
279 #
280
281 -- disable_query_log
282 eval set global innodb_file_per_table=$per_table;
283 SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;