Debian Codegraph @

mysql-test/suite/innodb_zip/r/create_options.result

raw
1 SET default_storage_engine=InnoDB;
2 SET GLOBAL innodb_file_per_table=ON;
3 SET SESSION innodb_strict_mode = ON;
4 # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
5 # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
6 # 'FIXED' is sent to InnoDB since it is used by MyISAM.
7 # But it is an invalid mode in InnoDB
8 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
9 ERROR HY000: Table storage engine for 't1' doesn't have this option
10 SHOW WARNINGS;
11 Level Code Message
12 Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
13 Error 1031 Table storage engine for 't1' doesn't have this option
14 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
15 SHOW WARNINGS;
16 Level Code Message
17 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
18 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
19 t1 Compressed row_format=COMPRESSED
20 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
21 SHOW WARNINGS;
22 Level Code Message
23 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
24 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
25 t1 Compact row_format=COMPACT
26 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
27 SHOW WARNINGS;
28 Level Code Message
29 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
30 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
31 t1 Dynamic row_format=DYNAMIC
32 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
33 SHOW WARNINGS;
34 Level Code Message
35 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
36 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
37 t1 Redundant row_format=REDUNDANT
38 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
39 SHOW WARNINGS;
40 Level Code Message
41 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
42 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
43 t1 Dynamic
44 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
45 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
46 SHOW WARNINGS;
47 Level Code Message
48 Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
49 Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
50 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
51 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
52 t1 Dynamic
53 DROP TABLE t1;
54 # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
55 # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC
56 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
57 ERROR HY000: Table storage engine for 't1' doesn't have this option
58 SHOW WARNINGS;
59 Level Code Message
60 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
61 Error 1031 Table storage engine for 't1' doesn't have this option
62 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
63 ERROR HY000: Table storage engine for 't1' doesn't have this option
64 SHOW WARNINGS;
65 Level Code Message
66 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
67 Error 1031 Table storage engine for 't1' doesn't have this option
68 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
69 ERROR HY000: Table storage engine for 't1' doesn't have this option
70 SHOW WARNINGS;
71 Level Code Message
72 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
73 Error 1031 Table storage engine for 't1' doesn't have this option
74 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
75 SHOW WARNINGS;
76 Level Code Message
77 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
78 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
79 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
80 ALTER TABLE t1 ADD COLUMN f1 INT;
81 SHOW WARNINGS;
82 Level Code Message
83 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
84 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
85 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
86 DROP TABLE t1;
87 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
88 SHOW WARNINGS;
89 Level Code Message
90 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
91 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
92 t1 Compressed KEY_BLOCK_SIZE=1
93 ALTER TABLE t1 ADD COLUMN f1 INT;
94 SHOW WARNINGS;
95 Level Code Message
96 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
97 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
98 t1 Compressed KEY_BLOCK_SIZE=1
99 # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
100 DROP TABLE t1;
101 CREATE TABLE t1 ( i INT );
102 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
103 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
104 SHOW WARNINGS;
105 Level Code Message
106 Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
107 Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
108 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
109 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
110 SHOW WARNINGS;
111 Level Code Message
112 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
113 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
114 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
115 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
116 SHOW WARNINGS;
117 Level Code Message
118 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
119 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
120 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
121 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
122 SHOW WARNINGS;
123 Level Code Message
124 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
125 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
126 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
127 SHOW WARNINGS;
128 Level Code Message
129 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
130 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
131 t1 Compressed KEY_BLOCK_SIZE=1
132 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
133 SHOW WARNINGS;
134 Level Code Message
135 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
136 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
137 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
138 # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE
139 DROP TABLE t1;
140 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
141 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
142 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
143 t1 Compact row_format=COMPACT
144 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
145 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
146 SHOW WARNINGS;
147 Level Code Message
148 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
149 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
150 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
151 SHOW WARNINGS;
152 Level Code Message
153 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
154 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
155 t1 Redundant row_format=REDUNDANT
156 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
157 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
158 SHOW WARNINGS;
159 Level Code Message
160 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
161 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
162 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
163 SHOW WARNINGS;
164 Level Code Message
165 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
166 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
167 t1 Dynamic row_format=DYNAMIC
168 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
169 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
170 SHOW WARNINGS;
171 Level Code Message
172 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
173 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
174 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
175 SHOW WARNINGS;
176 Level Code Message
177 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
178 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
179 t1 Compressed row_format=COMPRESSED
180 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
181 SHOW WARNINGS;
182 Level Code Message
183 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
184 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
185 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
186 DROP TABLE t1;
187 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
188 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
189 SHOW WARNINGS;
190 Level Code Message
191 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
192 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
193 t1 Compressed KEY_BLOCK_SIZE=1
194 # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE
195 # ALTER with each ROW_FORMAT
196 DROP TABLE t1;
197 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2;
198 SHOW CREATE TABLE t1;
199 Table Create Table
200 t1 CREATE TABLE `t1` (
201 `i` int DEFAULT NULL
202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci KEY_BLOCK_SIZE=2
203 ALTER TABLE t1 ADD COLUMN f1 INT;
204 SHOW CREATE TABLE t1;
205 Table Create Table
206 t1 CREATE TABLE `t1` (
207 `i` int DEFAULT NULL,
208 `f1` int DEFAULT NULL
209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci KEY_BLOCK_SIZE=2
210 ALTER TABLE t1 ROW_FORMAT=COMPACT;
211 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
212 SHOW WARNINGS;
213 Level Code Message
214 Warning 1478 InnoDB: cannot specify ROW_FORMAT=COMPACT with KEY_BLOCK_SIZE.
215 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
216 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
217 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
218 SHOW WARNINGS;
219 Level Code Message
220 Warning 1478 InnoDB: cannot specify ROW_FORMAT=REDUNDANT with KEY_BLOCK_SIZE.
221 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
222 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
223 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
224 SHOW WARNINGS;
225 Level Code Message
226 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
227 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
228 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
229 SHOW WARNINGS;
230 Level Code Message
231 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
232 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
233 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
234 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
235 SHOW WARNINGS;
236 Level Code Message
237 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
238 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
239 t1 Dynamic
240 ALTER TABLE t1 ROW_FORMAT=COMPACT;
241 SHOW WARNINGS;
242 Level Code Message
243 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
244 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
245 t1 Compact row_format=COMPACT
246 DROP TABLE t1;
247 # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE.
248 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9;
249 ERROR HY000: Table storage engine for 't1' doesn't have this option
250 SHOW WARNINGS;
251 Level Code Message
252 Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
253 Error 1031 Table storage engine for 't1' doesn't have this option
254 # Test 7) StrictMode=ON.
255 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4;
256 SHOW WARNINGS;
257 Level Code Message
258 DROP TABLE t1;
259 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
260 SHOW WARNINGS;
261 Level Code Message
262 DROP TABLE t1;
263 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
264 SHOW WARNINGS;
265 Level Code Message
266 DROP TABLE t1;
267 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
268 SHOW WARNINGS;
269 Level Code Message
270 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
271 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
272 t1 Redundant row_format=REDUNDANT
273 DROP TABLE t1;
274 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
275 SHOW WARNINGS;
276 Level Code Message
277 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
278 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
279 t1 Compact row_format=COMPACT
280 DROP TABLE t1;
281 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
282 SHOW WARNINGS;
283 Level Code Message
284 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
285 SHOW WARNINGS;
286 Level Code Message
287 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
288 SHOW WARNINGS;
289 Level Code Message
290 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
291 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
292 SHOW WARNINGS;
293 Level Code Message
294 Warning 1478 InnoDB: cannot specify ROW_FORMAT=DYNAMIC with KEY_BLOCK_SIZE.
295 Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
296 DROP TABLE t1;
297 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
298 ALTER TABLE t1 ADD COLUMN f1 INT;
299 SHOW CREATE TABLE t1;
300 Table Create Table
301 t1 CREATE TABLE `t1` (
302 `i` int DEFAULT NULL,
303 `f1` int DEFAULT NULL
304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
305 SHOW WARNINGS;
306 Level Code Message
307 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
308 SHOW WARNINGS;
309 Level Code Message
310 ALTER TABLE t1 ADD COLUMN f2 INT;
311 SHOW WARNINGS;
312 Level Code Message
313 # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
314 # and a valid non-zero KEY_BLOCK_SIZE are rejected with
315 # innodb_file_per_table=OFF and that they can be set to default
316 # values during strict mode.
317 SET GLOBAL innodb_file_per_table=OFF;
318 DROP TABLE t1;
319 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
320 ERROR HY000: Table storage engine for 't1' doesn't have this option
321 SHOW WARNINGS;
322 Level Code Message
323 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
324 Error 1031 Table storage engine for 't1' doesn't have this option
325 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
326 ERROR HY000: Table storage engine for 't1' doesn't have this option
327 SHOW WARNINGS;
328 Level Code Message
329 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
330 Error 1031 Table storage engine for 't1' doesn't have this option
331 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
332 DROP TABLE t1;
333 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
334 SHOW WARNINGS;
335 Level Code Message
336 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
337 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
338 t1 Redundant row_format=REDUNDANT
339 DROP TABLE t1;
340 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
341 SHOW WARNINGS;
342 Level Code Message
343 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
344 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
345 t1 Compact row_format=COMPACT
346 DROP TABLE t1;
347 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
348 SHOW WARNINGS;
349 Level Code Message
350 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
351 ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
352 SHOW WARNINGS;
353 Level Code Message
354 Error 1478 InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
355 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
356 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
357 ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
358 SHOW WARNINGS;
359 Level Code Message
360 Error 1478 InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
361 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
362 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
363 ALTER TABLE t1 ROW_FORMAT=COMPACT;
364 SHOW WARNINGS;
365 Level Code Message
366 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
367 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
368 t1 Compact row_format=COMPACT
369 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
370 SHOW WARNINGS;
371 Level Code Message
372 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
373 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
374 t1 Redundant row_format=REDUNDANT
375 ALTER TABLE t1 ROW_FORMAT=DEFAULT;
376 SHOW WARNINGS;
377 Level Code Message
378 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
379 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
380 t1 Dynamic
381 SET GLOBAL innodb_file_per_table=ON;
382 DROP TABLE t1;
383 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
384 SET GLOBAL innodb_file_per_table=OFF;
385 ALTER TABLE t1 ADD COLUMN f1 INT;
386 SHOW WARNINGS;
387 Level Code Message
388 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
389 SHOW WARNINGS;
390 Level Code Message
391 ALTER TABLE t1 ADD COLUMN f2 INT;
392 SHOW WARNINGS;
393 Level Code Message
394 SET GLOBAL innodb_file_per_table=ON;
395 ##################################################
396 SET SESSION innodb_strict_mode = OFF;
397 # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
398 # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
399 # 'FIXED' is sent to InnoDB since it is used by MyISAM.
400 # It is an invalid mode in InnoDB, use COMPACT
401 DROP TABLE t1;
402 CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
403 Warnings:
404 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
405 SHOW WARNINGS;
406 Level Code Message
407 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
408 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
409 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
410 t1 Dynamic row_format=DYNAMIC
411 DROP TABLE t1;
412 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
413 SHOW WARNINGS;
414 Level Code Message
415 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
416 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
417 t1 Compressed row_format=COMPRESSED
418 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
419 SHOW WARNINGS;
420 Level Code Message
421 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
422 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
423 t1 Compact row_format=COMPACT
424 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
425 SHOW WARNINGS;
426 Level Code Message
427 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
428 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
429 t1 Dynamic row_format=DYNAMIC
430 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
431 SHOW WARNINGS;
432 Level Code Message
433 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
434 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
435 t1 Redundant row_format=REDUNDANT
436 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
437 SHOW WARNINGS;
438 Level Code Message
439 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
440 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
441 t1 Dynamic
442 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
443 Warnings:
444 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
445 SHOW WARNINGS;
446 Level Code Message
447 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
448 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
449 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
450 t1 Dynamic row_format=DYNAMIC
451 # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
452 # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC
453 DROP TABLE t1;
454 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
455 Warnings:
456 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
457 SHOW WARNINGS;
458 Level Code Message
459 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
460 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
461 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
462 t1 Compact row_format=COMPACT
463 DROP TABLE t1;
464 CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
465 Warnings:
466 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
467 SHOW WARNINGS;
468 Level Code Message
469 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
470 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
471 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
472 t1 Redundant row_format=REDUNDANT
473 DROP TABLE t1;
474 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
475 Warnings:
476 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 as ROW_FORMAT is not COMPRESSED.
477 SHOW WARNINGS;
478 Level Code Message
479 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 as ROW_FORMAT is not COMPRESSED.
480 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
481 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
482 t1 Dynamic row_format=DYNAMIC
483 DROP TABLE t1;
484 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
485 SHOW WARNINGS;
486 Level Code Message
487 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
488 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
489 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
490 ALTER TABLE t1 ADD COLUMN f1 INT;
491 SHOW WARNINGS;
492 Level Code Message
493 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
494 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
495 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
496 DROP TABLE t1;
497 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
498 SHOW WARNINGS;
499 Level Code Message
500 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
501 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
502 t1 Compressed KEY_BLOCK_SIZE=1
503 ALTER TABLE t1 ADD COLUMN f1 INT;
504 SHOW WARNINGS;
505 Level Code Message
506 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
507 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
508 t1 Compressed KEY_BLOCK_SIZE=1
509 # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
510 DROP TABLE t1;
511 CREATE TABLE t1 ( i INT );
512 ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
513 Warnings:
514 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
515 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
516 SHOW WARNINGS;
517 Level Code Message
518 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
519 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
520 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
521 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
522 t1 Dynamic row_format=DYNAMIC
523 DROP TABLE t1;
524 CREATE TABLE t1 ( i INT );
525 ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
526 Warnings:
527 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
528 SHOW WARNINGS;
529 Level Code Message
530 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
531 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
532 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
533 t1 Compact row_format=COMPACT
534 DROP TABLE t1;
535 CREATE TABLE t1 ( i INT );
536 ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
537 Warnings:
538 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 as ROW_FORMAT is not COMPRESSED.
539 SHOW WARNINGS;
540 Level Code Message
541 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 as ROW_FORMAT is not COMPRESSED.
542 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
543 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
544 t1 Dynamic row_format=DYNAMIC
545 DROP TABLE t1;
546 CREATE TABLE t1 ( i INT );
547 ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
548 Warnings:
549 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
550 SHOW WARNINGS;
551 Level Code Message
552 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
553 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
554 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
555 t1 Redundant row_format=REDUNDANT
556 DROP TABLE t1;
557 CREATE TABLE t1 ( i INT );
558 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
559 SHOW WARNINGS;
560 Level Code Message
561 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
562 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
563 t1 Compressed KEY_BLOCK_SIZE=1
564 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
565 SHOW WARNINGS;
566 Level Code Message
567 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
568 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
569 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
570 # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE
571 DROP TABLE t1;
572 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
573 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
574 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
575 t1 Compact row_format=COMPACT
576 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
577 Warnings:
578 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
579 SHOW WARNINGS;
580 Level Code Message
581 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 as ROW_FORMAT is not COMPRESSED.
582 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
583 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
584 t1 Compact row_format=COMPACT
585 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
586 SHOW WARNINGS;
587 Level Code Message
588 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
589 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
590 t1 Redundant row_format=REDUNDANT
591 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
592 SHOW WARNINGS;
593 Level Code Message
594 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
595 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
596 t1 Dynamic row_format=DYNAMIC
597 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
598 SHOW WARNINGS;
599 Level Code Message
600 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
601 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
602 t1 Compressed row_format=COMPRESSED
603 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
604 SHOW WARNINGS;
605 Level Code Message
606 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
607 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
608 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
609 DROP TABLE t1;
610 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
611 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=2;
612 SHOW WARNINGS;
613 Level Code Message
614 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
615 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
616 t1 Compressed KEY_BLOCK_SIZE=2
617 # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE
618 # ALTER with each ROW_FORMAT
619 DROP TABLE t1;
620 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1;
621 SHOW WARNINGS;
622 Level Code Message
623 SHOW CREATE TABLE t1;
624 Table Create Table
625 t1 CREATE TABLE `t1` (
626 `i` int DEFAULT NULL
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci KEY_BLOCK_SIZE=1
628 ALTER TABLE t1 ADD COLUMN f1 INT;
629 SHOW WARNINGS;
630 Level Code Message
631 SHOW CREATE TABLE t1;
632 Table Create Table
633 t1 CREATE TABLE `t1` (
634 `i` int DEFAULT NULL,
635 `f1` int DEFAULT NULL
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci KEY_BLOCK_SIZE=1
637 ALTER TABLE t1 ROW_FORMAT=COMPACT;
638 Warnings:
639 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
640 SHOW WARNINGS;
641 Level Code Message
642 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 as ROW_FORMAT is not COMPRESSED.
643 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
644 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
645 t1 Compact row_format=COMPACT
646 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
647 SHOW WARNINGS;
648 Level Code Message
649 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
650 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
651 t1 Redundant row_format=REDUNDANT
652 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
653 SHOW WARNINGS;
654 Level Code Message
655 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
656 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
657 t1 Dynamic row_format=DYNAMIC
658 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
659 SHOW WARNINGS;
660 Level Code Message
661 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
662 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
663 t1 Compressed row_format=COMPRESSED
664 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
665 SHOW WARNINGS;
666 Level Code Message
667 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
668 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
669 t1 Dynamic
670 ALTER TABLE t1 ROW_FORMAT=COMPACT;
671 SHOW WARNINGS;
672 Level Code Message
673 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
674 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
675 t1 Compact row_format=COMPACT
676 # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE,
677 # it defaults to half of the page size.
678 DROP TABLE t1;
679 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15;
680 Warnings:
681 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15.
682 SHOW WARNINGS;
683 Level Code Message
684 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15.
685 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
686 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
687 t1 Dynamic
688 # Test 15) StrictMode=OFF.
689 DROP TABLE t1;
690 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
691 SHOW WARNINGS;
692 Level Code Message
693 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
694 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
695 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
696 ALTER TABLE t1 ADD COLUMN f1 INT;
697 SHOW WARNINGS;
698 Level Code Message
699 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
700 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
701 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
702 ALTER TABLE t1 ADD COLUMN f2 INT;
703 SHOW WARNINGS;
704 Level Code Message
705 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
706 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
707 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
708 DROP TABLE t1;
709 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
710 SHOW WARNINGS;
711 Level Code Message
712 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
713 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
714 t1 Dynamic row_format=DYNAMIC
715 ALTER TABLE t1 ADD COLUMN f1 INT;
716 SHOW WARNINGS;
717 Level Code Message
718 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
719 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
720 t1 Dynamic row_format=DYNAMIC
721 ALTER TABLE t1 ADD COLUMN f2 INT;
722 SHOW WARNINGS;
723 Level Code Message
724 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
725 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
726 t1 Dynamic row_format=DYNAMIC
727 # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
728 valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF
729 and then used again when innodb_file_per_table=ON.
730 DROP TABLE t1;
731 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
732 SHOW WARNINGS;
733 Level Code Message
734 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
735 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
736 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
737 SET GLOBAL innodb_file_per_table=OFF;
738 ALTER TABLE t1 ADD COLUMN f1 INT;
739 SHOW WARNINGS;
740 Level Code Message
741 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
742 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
743 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
744 SET GLOBAL innodb_file_per_table=ON;
745 ALTER TABLE t1 ADD COLUMN f2 INT;
746 SHOW WARNINGS;
747 Level Code Message
748 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
749 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
750 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
751 DROP TABLE t1;
752 CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
753 SHOW WARNINGS;
754 Level Code Message
755 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
756 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
757 t1 Dynamic row_format=DYNAMIC
758 SET GLOBAL innodb_file_per_table=OFF;
759 ALTER TABLE t1 ADD COLUMN f1 INT;
760 SHOW WARNINGS;
761 Level Code Message
762 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
763 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
764 t1 Dynamic row_format=DYNAMIC
765 SET GLOBAL innodb_file_per_table=ON;
766 ALTER TABLE t1 ADD COLUMN f2 INT;
767 SHOW WARNINGS;
768 Level Code Message
769 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
770 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
771 t1 Dynamic row_format=DYNAMIC
772 # Cleanup
773 DROP TABLE t1;