Debian Codegraph @

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

raw
1 # Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE
2 # Related bugs;
3 # Bug#54679: ALTER TABLE causes compressed row_format to revert to compact
4 # Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error
5 # Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED
6 # Rules for interpreting CREATE_OPTIONS
7 # 1) Create options on an ALTER are added to the options on the
8 # previous CREATE or ALTER statements.
9 # 2) KEY_BLOCK_SIZE=0 is considered a unspecified value.
10 # If the current ROW_FORMAT has explicitly been set to COMPRESSED,
11 # InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE
12 # will not be used.
13 # 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT.
14 # 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to
15 # unset or erase the values persisted in the MySQL dictionary and
16 # by SHOW CTREATE TABLE.
17 # 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are
18 # both explicitly given, the ROW_FORMAT is always used in non-strict
19 # mode.
20 # 6) InnoDB will automatically convert a table to COMPRESSED only if a
21 # valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT
22 # or has not been used on a previous CREATE TABLE or ALTER TABLE.
23 # 7) InnoDB strict mode is designed to prevent incompatible create
24 # options from being used together.
25 # 8) The non-strict behavior is intended to permit you to import a
26 # mysqldump file into a database that does not support compressed
27 # tables, even if the source database contained compressed tables.
28 # All invalid values and/or incompatible combinations of ROW_FORMAT
29 # and KEY_BLOCK_SIZE are automatically corrected
30 #
31 # *** innodb_strict_mode=ON ***
32 # 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC
33 # & REDUNDANT. All others are rejected.
34 # 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected.
35 # 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'.
36 # 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC &
37 # REDUNDANT.
38 # 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and
39 # ROW_FORMAT=DYNAMIC are incompatible with innodb_file_per_table=OFF
40 # 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED
41 # or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified
42 # as COMPACT, DYNAMIC or REDUNDANT.
43 # 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the
44 # previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified.
45 #
46 # *** innodb_strict_mode=OFF ***
47 # 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8.
48 # 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT.
49 # 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid
50 # ROW_FORMAT is specified.
51 # 4. If innodb_file_per_table=OFF
52 # it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will
53 # ignore all non-zero KEY_BLOCK_SIZEs.
54 #
55 # See InnoDB documentation page "SQL Compression Syntax Warnings and Errors"
56 # This test case does not try to create tables with KEY_BLOCK_SIZE > 4
57 # since they are rejected for InnoDB page sizes of 8k and 16k.
58 # See innodb_16k and innodb_8k for those tests.
59
60 -- source include/have_innodb_max_16k.inc
61
62 SET default_storage_engine=InnoDB;
63
64 --disable_query_log
65 # These values can change during the test
66 LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
67 LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
68 --enable_query_log
69
70 SET GLOBAL innodb_file_per_table=ON;
71
72 # The first half of these tests are with strict mode ON.
73 SET SESSION innodb_strict_mode = ON;
74
75 --echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
76 --echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
77 --echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
78 --echo # But it is an invalid mode in InnoDB
79 --error ER_ILLEGAL_HA
80 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
81 SHOW WARNINGS;
82 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
83 SHOW WARNINGS;
84 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
85 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
86 SHOW WARNINGS;
87 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
88 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
89 SHOW WARNINGS;
90 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
91 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
92 SHOW WARNINGS;
93 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
94 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
95 SHOW WARNINGS;
96 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
97 --error ER_ILLEGAL_HA_CREATE_OPTION
98 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
99 SHOW WARNINGS;
100 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
101 DROP TABLE t1;
102
103
104 --echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
105 --echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC
106 --error ER_ILLEGAL_HA
107 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
108 SHOW WARNINGS;
109 --error ER_ILLEGAL_HA
110 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
111 SHOW WARNINGS;
112 --error ER_ILLEGAL_HA
113 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
114 SHOW WARNINGS;
115 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
116 SHOW WARNINGS;
117 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
118 ALTER TABLE t1 ADD COLUMN f1 INT;
119 SHOW WARNINGS;
120 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
121 DROP TABLE t1;
122 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
123 SHOW WARNINGS;
124 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
125 ALTER TABLE t1 ADD COLUMN f1 INT;
126 SHOW WARNINGS;
127 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
128
129
130 --echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
131 DROP TABLE t1;
132 CREATE TABLE t1 ( i INT );
133 --error ER_ILLEGAL_HA_CREATE_OPTION
134 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
135 SHOW WARNINGS;
136 --error ER_ILLEGAL_HA_CREATE_OPTION
137 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
138 SHOW WARNINGS;
139 --error ER_ILLEGAL_HA_CREATE_OPTION
140 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
141 SHOW WARNINGS;
142 --error ER_ILLEGAL_HA_CREATE_OPTION
143 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
144 SHOW WARNINGS;
145 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
146 SHOW WARNINGS;
147 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
148 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
149 SHOW WARNINGS;
150 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
151
152
153 --echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE
154 DROP TABLE t1;
155 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
156 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
157 --error ER_ILLEGAL_HA_CREATE_OPTION
158 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
159 SHOW WARNINGS;
160 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
161 SHOW WARNINGS;
162 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
163 --error ER_ILLEGAL_HA_CREATE_OPTION
164 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
165 SHOW WARNINGS;
166 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
167 SHOW WARNINGS;
168 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
169 --error ER_ILLEGAL_HA_CREATE_OPTION
170 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
171 SHOW WARNINGS;
172 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
173 SHOW WARNINGS;
174 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
175 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
176 SHOW WARNINGS;
177 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
178 DROP TABLE t1;
179 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
180 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
181 SHOW WARNINGS;
182 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
183
184 --echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE
185 --echo # ALTER with each ROW_FORMAT
186 DROP TABLE t1;
187 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2;
188 SHOW CREATE TABLE t1;
189 ALTER TABLE t1 ADD COLUMN f1 INT;
190 SHOW CREATE TABLE t1;
191 --error ER_ILLEGAL_HA_CREATE_OPTION
192 ALTER TABLE t1 ROW_FORMAT=COMPACT;
193 SHOW WARNINGS;
194 --error ER_ILLEGAL_HA_CREATE_OPTION
195 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
196 SHOW WARNINGS;
197 --error ER_ILLEGAL_HA_CREATE_OPTION
198 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
199 SHOW WARNINGS;
200 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
201 SHOW WARNINGS;
202 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
203 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
204 SHOW WARNINGS;
205 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
206 ALTER TABLE t1 ROW_FORMAT=COMPACT;
207 SHOW WARNINGS;
208 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
209 DROP TABLE t1;
210
211 --echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE.
212 --error ER_ILLEGAL_HA
213 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9;
214 SHOW WARNINGS;
215
216 --echo # Test 7) StrictMode=ON.
217 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4;
218 SHOW WARNINGS;
219 DROP TABLE t1;
220 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
221 SHOW WARNINGS;
222 DROP TABLE t1;
223 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
224 SHOW WARNINGS;
225 DROP TABLE t1;
226 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
227 SHOW WARNINGS;
228 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
229 DROP TABLE t1;
230 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
231 SHOW WARNINGS;
232 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
233 DROP TABLE t1;
234 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
235 SHOW WARNINGS;
236 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
237 SHOW WARNINGS;
238 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
239 SHOW WARNINGS;
240 --error ER_ILLEGAL_HA_CREATE_OPTION
241 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
242 SHOW WARNINGS;
243 DROP TABLE t1;
244 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
245 ALTER TABLE t1 ADD COLUMN f1 INT;
246 SHOW CREATE TABLE t1;
247 SHOW WARNINGS;
248 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
249 SHOW WARNINGS;
250 ALTER TABLE t1 ADD COLUMN f2 INT;
251 SHOW WARNINGS;
252
253 --echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
254 --echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with
255 --echo # innodb_file_per_table=OFF and that they can be set to default
256 --echo # values during strict mode.
257 SET GLOBAL innodb_file_per_table=OFF;
258 DROP TABLE t1;
259 --error ER_ILLEGAL_HA
260 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
261 SHOW WARNINGS;
262 --error ER_ILLEGAL_HA
263 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
264 SHOW WARNINGS;
265 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
266 DROP TABLE t1;
267 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
268 SHOW WARNINGS;
269 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
270 DROP TABLE t1;
271 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
272 SHOW WARNINGS;
273 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
274 DROP TABLE t1;
275 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
276 SHOW WARNINGS;
277 --error ER_ILLEGAL_HA_CREATE_OPTION
278 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
279 SHOW WARNINGS;
280 --error ER_ILLEGAL_HA_CREATE_OPTION
281 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
282 SHOW WARNINGS;
283 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
284 ALTER TABLE t1 ROW_FORMAT=COMPACT;
285 SHOW WARNINGS;
286 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
287 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
288 SHOW WARNINGS;
289 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
290 ALTER TABLE t1 ROW_FORMAT=DEFAULT;
291 SHOW WARNINGS;
292 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
293 SET GLOBAL innodb_file_per_table=ON;
294 DROP TABLE t1;
295 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
296 SET GLOBAL innodb_file_per_table=OFF;
297 ALTER TABLE t1 ADD COLUMN f1 INT;
298 SHOW WARNINGS;
299 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
300 SHOW WARNINGS;
301 ALTER TABLE t1 ADD COLUMN f2 INT;
302 SHOW WARNINGS;
303 SET GLOBAL innodb_file_per_table=ON;
304
305 --echo ##################################################
306 SET SESSION innodb_strict_mode = OFF;
307
308 --echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
309 --echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
310 --echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
311 --echo # It is an invalid mode in InnoDB, use COMPACT
312 DROP TABLE t1;
313 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
314 SHOW WARNINGS;
315 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
316 DROP TABLE t1;
317 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
318 SHOW WARNINGS;
319 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
320 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
321 SHOW WARNINGS;
322 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
323 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
324 SHOW WARNINGS;
325 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
326 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
327 SHOW WARNINGS;
328 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
329 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
330 SHOW WARNINGS;
331 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
332 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
333 SHOW WARNINGS;
334 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
335
336 --echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
337 --echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC
338 DROP TABLE t1;
339 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
340 SHOW WARNINGS;
341 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
342 DROP TABLE t1;
343 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
344 SHOW WARNINGS;
345 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
346 DROP TABLE t1;
347 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
348 SHOW WARNINGS;
349 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
350 DROP TABLE t1;
351 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
352 SHOW WARNINGS;
353 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
354 ALTER TABLE t1 ADD COLUMN f1 INT;
355 SHOW WARNINGS;
356 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
357 DROP TABLE t1;
358 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
359 SHOW WARNINGS;
360 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
361 ALTER TABLE t1 ADD COLUMN f1 INT;
362 SHOW WARNINGS;
363 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
364
365
366 --echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
367 DROP TABLE t1;
368 CREATE TABLE t1 ( i INT );
369 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
370 SHOW WARNINGS;
371 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
372 DROP TABLE t1;
373 CREATE TABLE t1 ( i INT );
374 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
375 SHOW WARNINGS;
376 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
377 DROP TABLE t1;
378 CREATE TABLE t1 ( i INT );
379 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
380 SHOW WARNINGS;
381 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
382 DROP TABLE t1;
383 CREATE TABLE t1 ( i INT );
384 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
385 SHOW WARNINGS;
386 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
387 DROP TABLE t1;
388 CREATE TABLE t1 ( i INT );
389 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
390 SHOW WARNINGS;
391 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
392 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
393 SHOW WARNINGS;
394 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
395
396
397 --echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE
398 DROP TABLE t1;
399 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
400 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
401 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
402 SHOW WARNINGS;
403 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
404 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
405 SHOW WARNINGS;
406 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
407 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
408 SHOW WARNINGS;
409 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
410 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
411 SHOW WARNINGS;
412 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
413 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
414 SHOW WARNINGS;
415 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
416 DROP TABLE t1;
417 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
418 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=2;
419 SHOW WARNINGS;
420 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
421
422 --echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE
423 --echo # ALTER with each ROW_FORMAT
424 DROP TABLE t1;
425 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
426 SHOW WARNINGS;
427 SHOW CREATE TABLE t1;
428 ALTER TABLE t1 ADD COLUMN f1 INT;
429 SHOW WARNINGS;
430 SHOW CREATE TABLE t1;
431 ALTER TABLE t1 ROW_FORMAT=COMPACT;
432 SHOW WARNINGS;
433 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
434 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
435 SHOW WARNINGS;
436 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
437 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
438 SHOW WARNINGS;
439 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
440 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
441 SHOW WARNINGS;
442 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
443 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
444 SHOW WARNINGS;
445 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
446 ALTER TABLE t1 ROW_FORMAT=COMPACT;
447 SHOW WARNINGS;
448 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
449
450 --echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE,
451 --echo # it defaults to half of the page size.
452 DROP TABLE t1;
453 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15;
454 SHOW WARNINGS;
455 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
456
457 --echo # Test 15) StrictMode=OFF.
458 DROP TABLE t1;
459 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
460 SHOW WARNINGS;
461 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
462 ALTER TABLE t1 ADD COLUMN f1 INT;
463 SHOW WARNINGS;
464 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
465 ALTER TABLE t1 ADD COLUMN f2 INT;
466 SHOW WARNINGS;
467 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
468 DROP TABLE t1;
469 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
470 SHOW WARNINGS;
471 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
472 ALTER TABLE t1 ADD COLUMN f1 INT;
473 SHOW WARNINGS;
474 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
475 ALTER TABLE t1 ADD COLUMN f2 INT;
476 SHOW WARNINGS;
477 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
478
479 --echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
480 --echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF
481 --echo and then used again when innodb_file_per_table=ON.
482 DROP TABLE t1;
483 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
484 SHOW WARNINGS;
485 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
486 SET GLOBAL innodb_file_per_table=OFF;
487 ALTER TABLE t1 ADD COLUMN f1 INT;
488 SHOW WARNINGS;
489 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
490 SET GLOBAL innodb_file_per_table=ON;
491 ALTER TABLE t1 ADD COLUMN f2 INT;
492 SHOW WARNINGS;
493 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
494 DROP TABLE t1;
495 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
496 SHOW WARNINGS;
497 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
498 SET GLOBAL innodb_file_per_table=OFF;
499 ALTER TABLE t1 ADD COLUMN f1 INT;
500 SHOW WARNINGS;
501 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
502 SET GLOBAL innodb_file_per_table=ON;
503 ALTER TABLE t1 ADD COLUMN f2 INT;
504 SHOW WARNINGS;
505 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
506
507
508 --echo # Cleanup
509 DROP TABLE t1;
510
511 --disable_query_log
512 EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
513 --enable_query_log