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
39 # 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 and non-zero KEY_BLOCK_SIZEs.
53 #
54 # See InnoDB documentation page "SQL Compression Syntax Warnings and Errors"
55 # This test case does not try to create tables with KEY_BLOCK_SIZE > 4
56 # since they are rejected for InnoDB page sizes of 8k and 16k.
57 # See innodb_16k and innodb_8k for those tests.
58
59 --source include/innodb_page_size_small.inc
60 SET default_storage_engine=InnoDB;
61
62 # The first half of these tests are with strict mode ON.
63 SET SESSION innodb_strict_mode = ON;
64
65 --echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
66 --echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
67 --echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
68 --echo # But it is an invalid mode in InnoDB
69 --error ER_ILLEGAL_HA, 1005
70 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
71 SHOW WARNINGS;
72 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
73 SHOW WARNINGS;
74 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
75 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
76 SHOW WARNINGS;
77 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
78 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
79 SHOW WARNINGS;
80 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
81 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
82 SHOW WARNINGS;
83 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
84 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
85 SHOW WARNINGS;
86 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
87 --error ER_ILLEGAL_HA_CREATE_OPTION
88 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
89 SHOW WARNINGS;
90 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
91
92
93
94 --echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
95 --echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC
96 DROP TABLE t1;
97 --error ER_ILLEGAL_HA,1005
98 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
99 SHOW WARNINGS;
100 --error ER_ILLEGAL_HA,1005
101 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
102 SHOW WARNINGS;
103 --error ER_ILLEGAL_HA,1005
104 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
105 SHOW WARNINGS;
106 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
107 SHOW WARNINGS;
108 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
109 ALTER TABLE t1 ADD COLUMN f1 INT;
110 SHOW WARNINGS;
111 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
112 DROP TABLE t1;
113 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
114 SHOW WARNINGS;
115 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
116 ALTER TABLE t1 ADD COLUMN f1 INT;
117 SHOW WARNINGS;
118 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
119
120
121 --echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
122 DROP TABLE t1;
123 CREATE TABLE t1 ( i INT );
124 --error ER_ILLEGAL_HA_CREATE_OPTION
125 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
126 SHOW WARNINGS;
127 --error ER_ILLEGAL_HA_CREATE_OPTION
128 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
129 SHOW WARNINGS;
130 --error ER_ILLEGAL_HA_CREATE_OPTION
131 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
132 SHOW WARNINGS;
133 --error ER_ILLEGAL_HA_CREATE_OPTION
134 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
135 SHOW WARNINGS;
136 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
137 SHOW WARNINGS;
138 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
139 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
140 SHOW WARNINGS;
141 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
142
143
144 --echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE
145 DROP TABLE t1;
146 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
147 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
148 --error ER_ILLEGAL_HA_CREATE_OPTION
149 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
150 SHOW WARNINGS;
151 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
152 SHOW WARNINGS;
153 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
154 --error ER_ILLEGAL_HA_CREATE_OPTION
155 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
156 SHOW WARNINGS;
157 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
158 SHOW WARNINGS;
159 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
160 --error ER_ILLEGAL_HA_CREATE_OPTION
161 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
162 SHOW WARNINGS;
163 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
164 SHOW WARNINGS;
165 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
166 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
167 SHOW WARNINGS;
168 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
169 DROP TABLE t1;
170 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
171 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
172 SHOW WARNINGS;
173 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
174
175 --echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE
176 --echo # ALTER with each ROW_FORMAT
177 DROP TABLE t1;
178 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2;
179 SHOW CREATE TABLE t1;
180 ALTER TABLE t1 ADD COLUMN f1 INT;
181 SHOW CREATE TABLE t1;
182 --error ER_ILLEGAL_HA_CREATE_OPTION
183 ALTER TABLE t1 ROW_FORMAT=COMPACT;
184 SHOW WARNINGS;
185 --error ER_ILLEGAL_HA_CREATE_OPTION
186 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
187 SHOW WARNINGS;
188 --error ER_ILLEGAL_HA_CREATE_OPTION
189 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
190 SHOW WARNINGS;
191 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
192 SHOW WARNINGS;
193 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
194 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
195 SHOW WARNINGS;
196 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
197 ALTER TABLE t1 ROW_FORMAT=COMPACT;
198 SHOW WARNINGS;
199 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
200
201 --echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE.
202 DROP TABLE t1;
203 --error ER_ILLEGAL_HA, 1005
204 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9;
205 SHOW WARNINGS;
206
207 --echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
208 --echo # and a valid non-zero KEY_BLOCK_SIZE
209 --echo # can be set to default values during strict mode.
210 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
211 ALTER TABLE t1 ADD COLUMN f1 INT;
212 SHOW WARNINGS;
213 SHOW CREATE TABLE t1;
214 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
215 SHOW WARNINGS;
216 ALTER TABLE t1 ADD COLUMN f2 INT;
217 SHOW WARNINGS;
218
219 --echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT=COMPRESSED
220 --echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with
221 --echo # innodb_file_per_table=OFF and that they can be set to default
222 --echo # values during strict mode.
223 SET GLOBAL innodb_file_per_table=OFF;
224 DROP TABLE t1;
225 --error ER_ILLEGAL_HA,1005
226 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
227 SHOW WARNINGS;
228 --error ER_ILLEGAL_HA,1005
229 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
230 SHOW WARNINGS;
231 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
232 SHOW WARNINGS;
233 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
234 DROP TABLE t1;
235 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
236 SHOW WARNINGS;
237 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
238 DROP TABLE t1;
239 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
240 SHOW WARNINGS;
241 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
242 DROP TABLE t1;
243 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
244 SHOW WARNINGS;
245 --error ER_ILLEGAL_HA_CREATE_OPTION
246 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
247 SHOW WARNINGS;
248 --error ER_ILLEGAL_HA_CREATE_OPTION
249 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
250 SHOW WARNINGS;
251 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
252 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
253 SHOW WARNINGS;
254 ALTER TABLE t1 ROW_FORMAT=COMPACT;
255 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
256 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
257 SHOW WARNINGS;
258 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
259 ALTER TABLE t1 ROW_FORMAT=DEFAULT;
260 SHOW WARNINGS;
261 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
262 SET GLOBAL innodb_file_per_table=ON;
263 DROP TABLE t1;
264 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
265 SET GLOBAL innodb_file_per_table=OFF;
266 ALTER TABLE t1 ADD COLUMN f1 INT;
267 SHOW WARNINGS;
268 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
269 SHOW WARNINGS;
270 ALTER TABLE t1 ADD COLUMN f2 INT;
271 SHOW WARNINGS;
272 SET GLOBAL innodb_file_per_table=ON;
273
274 --echo ##################################################
275 SET SESSION innodb_strict_mode = OFF;
276
277 --echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
278 --echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
279 --echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
280 --echo # It is an invalid mode in InnoDB, use COMPACT
281 DROP TABLE t1;
282 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
283 SHOW WARNINGS;
284 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
285 DROP TABLE t1;
286 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
287 SHOW WARNINGS;
288 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
289 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
290 SHOW WARNINGS;
291 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
292 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
293 SHOW WARNINGS;
294 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
295 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
296 SHOW WARNINGS;
297 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
298 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
299 SHOW WARNINGS;
300 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
301 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
302 SHOW WARNINGS;
303 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
304
305 --echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
306 --echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC
307 DROP TABLE t1;
308 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
309 SHOW WARNINGS;
310 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
311 DROP TABLE t1;
312 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
313 SHOW WARNINGS;
314 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
315 DROP TABLE t1;
316 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
317 SHOW WARNINGS;
318 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
319 DROP TABLE t1;
320 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
321 SHOW WARNINGS;
322 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
323 ALTER TABLE t1 ADD COLUMN f1 INT;
324 SHOW WARNINGS;
325 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
326 DROP TABLE t1;
327 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
328 SHOW WARNINGS;
329 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
330 ALTER TABLE t1 ADD COLUMN f1 INT;
331 SHOW WARNINGS;
332 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
333
334
335 --echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
336 DROP TABLE t1;
337 CREATE TABLE t1 ( i INT );
338 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
339 SHOW WARNINGS;
340 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
341 DROP TABLE t1;
342 CREATE TABLE t1 ( i INT );
343 ALTER TABLE t1 ROW_FORMAT=COMPACT 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 );
348 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
349 SHOW WARNINGS;
350 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
351 DROP TABLE t1;
352 CREATE TABLE t1 ( i INT );
353 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
354 SHOW WARNINGS;
355 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
356 DROP TABLE t1;
357 CREATE TABLE t1 ( i INT );
358 ALTER TABLE t1 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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
362 SHOW WARNINGS;
363 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
364
365
366 --echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE
367 DROP TABLE t1;
368 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
369 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
370 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
371 SHOW WARNINGS;
372 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
373 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
374 SHOW WARNINGS;
375 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
376 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
377 SHOW WARNINGS;
378 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
379 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
380 SHOW WARNINGS;
381 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
382 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
383 SHOW WARNINGS;
384 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
385 DROP TABLE t1;
386 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
387 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=2;
388 SHOW WARNINGS;
389 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
390
391 --echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE
392 --echo # ALTER with each ROW_FORMAT
393 DROP TABLE t1;
394 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
395 SHOW WARNINGS;
396 SHOW CREATE TABLE t1;
397 ALTER TABLE t1 ADD COLUMN f1 INT;
398 SHOW WARNINGS;
399 SHOW CREATE TABLE t1;
400 ALTER TABLE t1 ROW_FORMAT=COMPACT;
401 SHOW WARNINGS;
402 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
403 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
404 SHOW WARNINGS;
405 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
406 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
407 SHOW WARNINGS;
408 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
409 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
410 SHOW WARNINGS;
411 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
412 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
413 SHOW WARNINGS;
414 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
415 ALTER TABLE t1 ROW_FORMAT=COMPACT;
416 SHOW WARNINGS;
417 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
418
419 --echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE,
420 --echo # it defaults to half of the page size.
421 DROP TABLE t1;
422 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15;
423 SHOW WARNINGS;
424 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
425
426 --echo # Test 15) StrictMode=OFF.
427 DROP TABLE t1;
428 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
429 SHOW WARNINGS;
430 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
431 ALTER TABLE t1 ADD COLUMN f1 INT;
432 SHOW WARNINGS;
433 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
434 ALTER TABLE t1 ADD COLUMN f2 INT;
435 SHOW WARNINGS;
436 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
437 DROP TABLE t1;
438 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
439 SHOW WARNINGS;
440 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
441 ALTER TABLE t1 ADD COLUMN f1 INT;
442 SHOW WARNINGS;
443 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
444 ALTER TABLE t1 ADD COLUMN f2 INT;
445 SHOW WARNINGS;
446 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
447
448 --echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
449 --echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF
450 --echo and then used again when innodb_file_per_table=ON.
451 DROP TABLE t1;
452 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
453 SHOW WARNINGS;
454 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
455 SET GLOBAL innodb_file_per_table=OFF;
456 ALTER TABLE t1 ADD COLUMN f1 INT;
457 SHOW WARNINGS;
458 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
459 SET GLOBAL innodb_file_per_table=ON;
460 ALTER TABLE t1 ADD COLUMN f2 INT;
461 SHOW WARNINGS;
462 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
463 DROP TABLE t1;
464 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
465 SHOW WARNINGS;
466 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
467 SET GLOBAL innodb_file_per_table=OFF;
468 ALTER TABLE t1 ADD COLUMN f1 INT;
469 SHOW WARNINGS;
470 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
471 SET GLOBAL innodb_file_per_table=ON;
472 ALTER TABLE t1 ADD COLUMN f2 INT;
473 SHOW WARNINGS;
474 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
475
476
477 --echo # Cleanup
478 DROP TABLE t1;