Debian Codegraph @

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

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