1
SET storage_engine=InnoDB;
2
SET GLOBAL innodb_file_format=`Barracuda`;
3
SET GLOBAL innodb_file_per_table=ON;
4
SET SESSION innodb_strict_mode = ON;
5
# Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
6
# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
7
DROP TABLE IF EXISTS t1;
9
Note 1051 Unknown table 't1'
10
# 'FIXED' is sent to InnoDB since it is used by MyISAM.
11
# But it is an invalid mode in InnoDB
12
CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
13
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
16
Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
17
Error 1005 Can't create table 'test.t1' (errno: 1478)
18
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
21
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
22
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
23
t1 Compressed row_format=COMPRESSED
24
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
27
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
28
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
29
t1 Compact row_format=COMPACT
30
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
33
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
34
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
35
t1 Dynamic row_format=DYNAMIC
36
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
39
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
40
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
41
t1 Redundant row_format=REDUNDANT
42
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
45
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
46
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
48
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
49
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
52
Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
53
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
54
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
55
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
57
# Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
58
# KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC
59
DROP TABLE IF EXISTS t1;
60
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
61
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
64
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
65
Error 1005 Can't create table 'test.t1' (errno: 1478)
66
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
67
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
70
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
71
Error 1005 Can't create table 'test.t1' (errno: 1478)
72
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
73
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
76
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
77
Error 1005 Can't create table 'test.t1' (errno: 1478)
78
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
81
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
82
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
83
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
84
ALTER TABLE t1 ADD COLUMN f1 INT;
87
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
88
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
89
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
90
DROP TABLE IF EXISTS t1;
91
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
94
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
95
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
96
t1 Compressed KEY_BLOCK_SIZE=16
97
ALTER TABLE t1 ADD COLUMN f1 INT;
100
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
101
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
102
t1 Compressed KEY_BLOCK_SIZE=16
103
# Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
104
DROP TABLE IF EXISTS t1;
105
CREATE TABLE t1 ( i INT );
106
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
107
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
110
Warning 1478 InnoDB: invalid ROW_FORMAT specifier.
111
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
112
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
113
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
116
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
117
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
118
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
119
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
122
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
123
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
124
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
125
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
128
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
129
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
130
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
133
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
134
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
135
t1 Compressed KEY_BLOCK_SIZE=16
136
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
139
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
140
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
141
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
142
# Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE
143
DROP TABLE IF EXISTS t1;
144
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
145
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
146
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
147
t1 Compact row_format=COMPACT
148
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
149
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
152
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
153
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
154
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
157
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
158
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
159
t1 Redundant row_format=REDUNDANT
160
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
161
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
164
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
165
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
166
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
169
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
170
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
171
t1 Dynamic row_format=DYNAMIC
172
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
173
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
176
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
177
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
178
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
181
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
182
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
183
t1 Compressed row_format=COMPRESSED
184
ALTER TABLE t1 KEY_BLOCK_SIZE=16;
187
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
188
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
189
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
190
DROP TABLE IF EXISTS t1;
191
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
192
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
195
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
196
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
197
t1 Compressed KEY_BLOCK_SIZE=1
198
# Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE
199
# ALTER with each ROW_FORMAT
200
DROP TABLE IF EXISTS t1;
201
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2;
202
SHOW CREATE TABLE t1;
204
t1 CREATE TABLE `t1` (
205
`i` int(11) DEFAULT NULL
206
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2
207
ALTER TABLE t1 ADD COLUMN f1 INT;
208
SHOW CREATE TABLE t1;
210
t1 CREATE TABLE `t1` (
211
`i` int(11) DEFAULT NULL,
212
`f1` int(11) DEFAULT NULL
213
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2
214
ALTER TABLE t1 ROW_FORMAT=COMPACT;
215
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
218
Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.
219
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
220
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
221
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
224
Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.
225
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
226
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
227
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
230
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
231
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
232
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
235
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
236
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
237
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
238
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
241
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
242
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
244
ALTER TABLE t1 ROW_FORMAT=COMPACT;
247
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
248
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
249
t1 Compact row_format=COMPACT
250
# Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE.
251
DROP TABLE IF EXISTS t1;
252
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9;
253
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
256
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
257
Error 1005 Can't create table 'test.t1' (errno: 1478)
258
# Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
259
# and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope
260
# and that they can be set to default values during strict mode.
261
SET GLOBAL innodb_file_format=Antelope;
262
DROP TABLE IF EXISTS t1;
264
Note 1051 Unknown table 't1'
265
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4;
266
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
269
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
270
Error 1005 Can't create table 'test.t1' (errno: 1478)
271
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
272
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
275
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
276
Error 1005 Can't create table 'test.t1' (errno: 1478)
277
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
278
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
281
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
282
Error 1005 Can't create table 'test.t1' (errno: 1478)
283
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
286
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
287
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
288
t1 Redundant row_format=REDUNDANT
289
DROP TABLE IF EXISTS t1;
290
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
293
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
294
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
295
t1 Compact row_format=COMPACT
296
DROP TABLE IF EXISTS t1;
297
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
300
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
301
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
304
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
305
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
306
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
307
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
310
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
311
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
312
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
313
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
316
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
317
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
318
SET GLOBAL innodb_file_format=Barracuda;
319
DROP TABLE IF EXISTS t1;
320
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
321
SET GLOBAL innodb_file_format=Antelope;
322
ALTER TABLE t1 ADD COLUMN f1 INT;
323
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
326
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
327
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
328
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
329
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
332
ALTER TABLE t1 ADD COLUMN f2 INT;
335
SET GLOBAL innodb_file_format=Barracuda;
336
# Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
337
# and a valid non-zero KEY_BLOCK_SIZE are rejected with
338
# innodb_file_per_table=OFF and that they can be set to default
339
# values during strict mode.
340
SET GLOBAL innodb_file_per_table=OFF;
341
DROP TABLE IF EXISTS t1;
342
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16;
343
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
346
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
347
Error 1005 Can't create table 'test.t1' (errno: 1478)
348
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
349
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
352
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
353
Error 1005 Can't create table 'test.t1' (errno: 1478)
354
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
355
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
358
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
359
Error 1005 Can't create table 'test.t1' (errno: 1478)
360
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
363
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
364
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
365
t1 Redundant row_format=REDUNDANT
366
DROP TABLE IF EXISTS t1;
367
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
370
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
371
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
372
t1 Compact row_format=COMPACT
373
DROP TABLE IF EXISTS t1;
374
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
377
ALTER TABLE t1 KEY_BLOCK_SIZE=1;
378
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
381
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
382
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
383
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
384
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
387
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
388
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
389
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
390
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
393
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
394
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
395
ALTER TABLE t1 ROW_FORMAT=COMPACT;
398
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
399
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
400
t1 Compact row_format=COMPACT
401
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
404
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
405
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
406
t1 Redundant row_format=REDUNDANT
407
ALTER TABLE t1 ROW_FORMAT=DEFAULT;
410
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
411
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
413
SET GLOBAL innodb_file_per_table=ON;
414
DROP TABLE IF EXISTS t1;
415
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
416
SET GLOBAL innodb_file_per_table=OFF;
417
ALTER TABLE t1 ADD COLUMN f1 INT;
418
ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
421
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
422
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
423
Error 1005 Can't create table '#sql-temporary' (errno: 1478)
424
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
427
ALTER TABLE t1 ADD COLUMN f2 INT;
430
SET GLOBAL innodb_file_per_table=ON;
431
##################################################
432
SET SESSION innodb_strict_mode = OFF;
433
# Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
434
# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
435
# 'FIXED' is sent to InnoDB since it is used by MyISAM.
436
# It is an invalid mode in InnoDB, use COMPACT
437
DROP TABLE IF EXISTS t1;
438
CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
440
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
443
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
444
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
445
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
446
t1 Compact row_format=FIXED
447
DROP TABLE IF EXISTS t1;
448
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
451
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
452
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
453
t1 Compressed row_format=COMPRESSED
454
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
457
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
458
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
459
t1 Compact row_format=COMPACT
460
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
463
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
464
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
465
t1 Dynamic row_format=DYNAMIC
466
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
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 Redundant row_format=REDUNDANT
472
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
475
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
476
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
478
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
480
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
483
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
484
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
485
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
486
t1 Compact row_format=FIXED
487
# Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
488
# KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC
489
DROP TABLE IF EXISTS t1;
490
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
492
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
495
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
496
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
497
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
498
t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=1
499
DROP TABLE IF EXISTS t1;
500
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
502
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
505
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
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 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2
509
DROP TABLE IF EXISTS t1;
510
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
512
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED.
515
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED.
516
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
517
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
518
t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=4
519
DROP TABLE IF EXISTS t1;
520
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
523
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
524
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
525
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
526
ALTER TABLE t1 ADD COLUMN f1 INT;
529
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
530
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
531
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
532
DROP TABLE IF EXISTS t1;
533
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
536
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
537
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
538
t1 Compressed KEY_BLOCK_SIZE=16
539
ALTER TABLE t1 ADD COLUMN f1 INT;
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 Compressed KEY_BLOCK_SIZE=16
545
# Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
546
DROP TABLE IF EXISTS t1;
547
CREATE TABLE t1 ( i INT );
548
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
550
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
551
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
554
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED.
555
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
556
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
557
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
558
t1 Compact row_format=FIXED KEY_BLOCK_SIZE=1
559
DROP TABLE IF EXISTS t1;
560
CREATE TABLE t1 ( i INT );
561
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
563
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
566
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
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 Compact row_format=COMPACT KEY_BLOCK_SIZE=2
570
DROP TABLE IF EXISTS t1;
571
CREATE TABLE t1 ( i INT );
572
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
574
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED.
577
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED.
578
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
579
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
580
t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=4
581
DROP TABLE IF EXISTS t1;
582
CREATE TABLE t1 ( i INT );
583
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
585
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED.
588
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED.
589
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
590
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
591
t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=8
592
DROP TABLE IF EXISTS t1;
593
CREATE TABLE t1 ( i INT );
594
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
597
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
598
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
599
t1 Compressed KEY_BLOCK_SIZE=16
600
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
603
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
604
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
605
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
606
# Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE
607
DROP TABLE IF EXISTS t1;
608
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
609
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
610
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
611
t1 Compact row_format=COMPACT
612
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
614
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
617
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
618
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
619
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
620
t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=2
621
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
623
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
626
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
627
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
628
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
629
t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2
630
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
632
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
635
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED.
636
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
637
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
638
t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=2
639
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
642
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
643
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
644
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
645
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
648
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
649
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
650
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
651
DROP TABLE IF EXISTS t1;
652
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
653
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8;
656
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
657
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
658
t1 Compressed KEY_BLOCK_SIZE=8
659
# Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE
660
# ALTER with each ROW_FORMAT
661
DROP TABLE IF EXISTS t1;
662
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16;
665
SHOW CREATE TABLE t1;
667
t1 CREATE TABLE `t1` (
668
`i` int(11) DEFAULT NULL
669
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16
670
ALTER TABLE t1 ADD COLUMN f1 INT;
673
SHOW CREATE TABLE t1;
675
t1 CREATE TABLE `t1` (
676
`i` int(11) DEFAULT NULL,
677
`f1` int(11) DEFAULT NULL
678
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16
679
ALTER TABLE t1 ROW_FORMAT=COMPACT;
681
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
684
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
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 Compact row_format=COMPACT KEY_BLOCK_SIZE=16
688
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
690
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
693
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
694
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
695
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
696
t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=16
697
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
699
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
702
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED.
703
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
704
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
705
t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=16
706
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
709
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
710
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
711
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
712
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
715
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
716
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
718
ALTER TABLE t1 ROW_FORMAT=COMPACT;
721
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
722
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
723
t1 Compact row_format=COMPACT
724
# Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8
725
DROP TABLE IF EXISTS t1;
726
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15;
728
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15.
731
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15.
732
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
733
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
734
t1 Compact KEY_BLOCK_SIZE=15
735
# Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
736
valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT
737
is reverted to Antelope and then used again when ROW_FORMAT=Barracuda.
738
DROP TABLE IF EXISTS t1;
739
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
742
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
743
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
744
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
745
SET GLOBAL innodb_file_format=Antelope;
746
ALTER TABLE t1 ADD COLUMN f1 INT;
748
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
749
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
750
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
751
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
754
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
755
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1.
756
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
757
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
758
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
759
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
760
t1 Compact row_format=COMPRESSED KEY_BLOCK_SIZE=1
761
SET GLOBAL innodb_file_format=Barracuda;
762
ALTER TABLE t1 ADD COLUMN f2 INT;
765
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
766
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
767
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
768
DROP TABLE IF EXISTS t1;
769
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
772
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
773
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
774
t1 Dynamic row_format=DYNAMIC
775
SET GLOBAL innodb_file_format=Antelope;
776
ALTER TABLE t1 ADD COLUMN f1 INT;
778
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
779
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
782
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
783
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
784
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
785
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
786
t1 Compact row_format=DYNAMIC
787
SET GLOBAL innodb_file_format=Barracuda;
788
ALTER TABLE t1 ADD COLUMN f2 INT;
791
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
792
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
793
t1 Dynamic row_format=DYNAMIC
794
# Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
795
valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF
796
and then used again when innodb_file_per_table=ON.
797
DROP TABLE IF EXISTS t1;
798
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
801
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
802
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
803
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
804
SET GLOBAL innodb_file_per_table=OFF;
805
ALTER TABLE t1 ADD COLUMN f1 INT;
807
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
808
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2.
809
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
810
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
813
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
814
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2.
815
Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
816
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
817
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
818
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
819
t1 Compact row_format=COMPRESSED KEY_BLOCK_SIZE=2
820
SET GLOBAL innodb_file_per_table=ON;
821
ALTER TABLE t1 ADD COLUMN f2 INT;
824
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
825
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
826
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
827
DROP TABLE IF EXISTS t1;
828
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
831
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
832
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
833
t1 Dynamic row_format=DYNAMIC
834
SET GLOBAL innodb_file_per_table=OFF;
835
ALTER TABLE t1 ADD COLUMN f1 INT;
837
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
838
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
841
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
842
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
843
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
844
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
845
t1 Compact row_format=DYNAMIC
846
SET GLOBAL innodb_file_per_table=ON;
847
ALTER TABLE t1 ADD COLUMN f2 INT;
850
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
851
TABLE_NAME ROW_FORMAT CREATE_OPTIONS
852
t1 Dynamic row_format=DYNAMIC
854
DROP TABLE IF EXISTS t1;