1
# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE
3
# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact
4
# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error
5
# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED
6
# Rules for interpreting CREATE_OPTIONS
7
# 1) Create options on an ALTER are added to the options on the
8
# previous CREATE or ALTER statements.
9
# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value.
10
# If the current ROW_FORMAT has explicitly been set to COMPRESSED,
11
# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE
13
# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT.
14
# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to
15
# unset or erase the values persisted in the MySQL dictionary and
16
# by SHOW CTREATE TABLE.
17
# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are
18
# both explicitly given, the ROW_FORMAT is always used in non-strict
20
# 6) InnoDB will automatically convert a table to COMPRESSED only if a
21
# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT
22
# or has not been used on a previous CREATE TABLE or ALTER TABLE.
23
# 7) InnoDB strict mode is designed to prevent incompatible create
24
# options from being used together.
25
# 8) The non-strict behavior is intended to permit you to import a
26
# mysqldump file into a database that does not support compressed
27
# tables, even if the source database contained compressed tables.
28
# All invalid values and/or incompatible combinations of ROW_FORMAT
29
# and KEY_BLOCK_SIZE are automatically corrected
31
# *** innodb_strict_mode=ON ***
32
# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC
33
# & REDUNDANT. All others are rejected.
34
# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected.
35
# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'.
36
# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC &
38
# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and
39
# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope
40
# and innodb_file_per_table=OFF
41
# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED
42
# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified
43
# as COMPACT, DYNAMIC or REDUNDANT.
44
# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the
45
# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified.
47
# *** innodb_strict_mode=OFF ***
48
# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8.
49
# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT.
50
# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid
51
# ROW_FORMAT is specified.
52
# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF
53
# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will
54
# ignore all non-zero KEY_BLOCK_SIZEs.
56
# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors"
58
-- source include/have_innodb_plugin.inc
59
SET storage_engine=InnoDB;
62
# These values can change during the test
63
LET $innodb_file_format_orig=`select @@innodb_file_format`;
64
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
65
LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
68
SET GLOBAL innodb_file_format=`Barracuda`;
69
SET GLOBAL innodb_file_per_table=ON;
71
# The first half of these tests are with strict mode ON.
72
SET SESSION innodb_strict_mode = ON;
74
--echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
75
--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
76
DROP TABLE IF EXISTS t1;
77
--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
78
--echo # But it is an invalid mode in InnoDB
79
--error ER_CANT_CREATE_TABLE
80
CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
82
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
84
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
85
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
87
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
88
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
90
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
91
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
93
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
94
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
96
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
97
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
98
--error ER_CANT_CREATE_TABLE
99
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
100
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
102
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
106
--echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
107
--echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC
108
DROP TABLE IF EXISTS t1;
109
--error ER_CANT_CREATE_TABLE
110
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
112
--error ER_CANT_CREATE_TABLE
113
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
115
--error ER_CANT_CREATE_TABLE
116
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
118
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
120
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
121
ALTER TABLE t1 ADD COLUMN f1 INT;
123
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
124
DROP TABLE IF EXISTS t1;
125
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
127
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
128
ALTER TABLE t1 ADD COLUMN f1 INT;
130
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
133
--echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE
134
DROP TABLE IF EXISTS t1;
135
CREATE TABLE t1 ( i INT );
136
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
137
--error ER_CANT_CREATE_TABLE
138
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
139
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
141
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
142
--error ER_CANT_CREATE_TABLE
143
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
144
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
146
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
147
--error ER_CANT_CREATE_TABLE
148
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
149
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
151
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
152
--error ER_CANT_CREATE_TABLE
153
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
154
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
156
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
158
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
159
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
161
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
164
--echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE
165
DROP TABLE IF EXISTS t1;
166
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
167
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
168
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
169
--error ER_CANT_CREATE_TABLE
170
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
171
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
173
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
175
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
176
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
177
--error ER_CANT_CREATE_TABLE
178
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
179
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
181
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
183
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
184
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
185
--error ER_CANT_CREATE_TABLE
186
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
187
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
189
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
191
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
192
ALTER TABLE t1 KEY_BLOCK_SIZE=16;
194
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
195
DROP TABLE IF EXISTS t1;
196
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
197
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1;
199
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
201
--echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE
202
--echo # ALTER with each ROW_FORMAT
203
DROP TABLE IF EXISTS t1;
204
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2;
205
SHOW CREATE TABLE t1;
206
ALTER TABLE t1 ADD COLUMN f1 INT;
207
SHOW CREATE TABLE t1;
208
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
209
--error ER_CANT_CREATE_TABLE
210
ALTER TABLE t1 ROW_FORMAT=COMPACT;
211
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
213
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
214
--error ER_CANT_CREATE_TABLE
215
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
216
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
218
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
219
--error ER_CANT_CREATE_TABLE
220
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
221
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
223
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
225
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
226
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
228
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
229
ALTER TABLE t1 ROW_FORMAT=COMPACT;
231
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
233
--echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE.
234
DROP TABLE IF EXISTS t1;
235
--error ER_CANT_CREATE_TABLE
236
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9;
239
--echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
240
--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope
241
--echo # and that they can be set to default values during strict mode.
242
SET GLOBAL innodb_file_format=Antelope;
243
DROP TABLE IF EXISTS t1;
244
--error ER_CANT_CREATE_TABLE
245
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4;
247
--error ER_CANT_CREATE_TABLE
248
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
250
--error ER_CANT_CREATE_TABLE
251
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
253
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
255
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
256
DROP TABLE IF EXISTS t1;
257
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
259
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
260
DROP TABLE IF EXISTS t1;
261
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
263
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
264
--error ER_CANT_CREATE_TABLE
265
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
266
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
268
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
269
--error ER_CANT_CREATE_TABLE
270
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
271
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
273
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
274
--error ER_CANT_CREATE_TABLE
275
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
276
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
278
SET GLOBAL innodb_file_format=Barracuda;
279
DROP TABLE IF EXISTS t1;
280
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
281
SET GLOBAL innodb_file_format=Antelope;
282
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
283
--error ER_CANT_CREATE_TABLE
284
ALTER TABLE t1 ADD COLUMN f1 INT;
285
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
287
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
289
ALTER TABLE t1 ADD COLUMN f2 INT;
291
SET GLOBAL innodb_file_format=Barracuda;
293
--echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and
294
--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with
295
--echo # innodb_file_per_table=OFF and that they can be set to default
296
--echo # values during strict mode.
297
SET GLOBAL innodb_file_per_table=OFF;
298
DROP TABLE IF EXISTS t1;
299
--error ER_CANT_CREATE_TABLE
300
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16;
302
--error ER_CANT_CREATE_TABLE
303
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
305
--error ER_CANT_CREATE_TABLE
306
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
308
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT;
310
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
311
DROP TABLE IF EXISTS t1;
312
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
314
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
315
DROP TABLE IF EXISTS t1;
316
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT;
318
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
319
--error ER_CANT_CREATE_TABLE
320
ALTER TABLE t1 KEY_BLOCK_SIZE=1;
321
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
323
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
324
--error ER_CANT_CREATE_TABLE
325
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
326
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
328
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
329
--error ER_CANT_CREATE_TABLE
330
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
331
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
333
ALTER TABLE t1 ROW_FORMAT=COMPACT;
335
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
336
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
338
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
339
ALTER TABLE t1 ROW_FORMAT=DEFAULT;
341
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
342
SET GLOBAL innodb_file_per_table=ON;
343
DROP TABLE IF EXISTS t1;
344
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
345
SET GLOBAL innodb_file_per_table=OFF;
346
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
347
--error ER_CANT_CREATE_TABLE
348
ALTER TABLE t1 ADD COLUMN f1 INT;
349
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
351
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
353
ALTER TABLE t1 ADD COLUMN f2 INT;
355
SET GLOBAL innodb_file_per_table=ON;
357
--echo ##################################################
358
SET SESSION innodb_strict_mode = OFF;
360
--echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0
361
--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified'
362
--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM.
363
--echo # It is an invalid mode in InnoDB, use COMPACT
364
DROP TABLE IF EXISTS t1;
365
CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED;
367
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
368
DROP TABLE IF EXISTS t1;
369
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0;
371
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
372
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0;
374
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
375
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
377
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
378
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0;
380
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
381
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
383
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
384
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0;
386
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
388
--echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
389
--echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC
390
DROP TABLE IF EXISTS t1;
391
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
393
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
394
DROP TABLE IF EXISTS t1;
395
CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2;
397
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
398
DROP TABLE IF EXISTS t1;
399
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
401
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
402
DROP TABLE IF EXISTS t1;
403
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
405
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
406
ALTER TABLE t1 ADD COLUMN f1 INT;
408
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
409
DROP TABLE IF EXISTS t1;
410
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
412
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
413
ALTER TABLE t1 ADD COLUMN f1 INT;
415
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
418
--echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE
419
DROP TABLE IF EXISTS t1;
420
CREATE TABLE t1 ( i INT );
421
ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1;
423
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
424
DROP TABLE IF EXISTS t1;
425
CREATE TABLE t1 ( i INT );
426
ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2;
428
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
429
DROP TABLE IF EXISTS t1;
430
CREATE TABLE t1 ( i INT );
431
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4;
433
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
434
DROP TABLE IF EXISTS t1;
435
CREATE TABLE t1 ( i INT );
436
ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
438
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
439
DROP TABLE IF EXISTS t1;
440
CREATE TABLE t1 ( i INT );
441
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16;
443
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
444
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
446
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
449
--echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE
450
DROP TABLE IF EXISTS t1;
451
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
452
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
453
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
455
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
456
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
458
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
459
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
461
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
462
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
464
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
465
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
467
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
468
DROP TABLE IF EXISTS t1;
469
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
470
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8;
472
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
474
--echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE
475
--echo # ALTER with each ROW_FORMAT
476
DROP TABLE IF EXISTS t1;
477
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16;
479
SHOW CREATE TABLE t1;
480
ALTER TABLE t1 ADD COLUMN f1 INT;
482
SHOW CREATE TABLE t1;
483
ALTER TABLE t1 ROW_FORMAT=COMPACT;
485
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
486
ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
488
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
489
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
491
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
492
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
494
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
495
ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
497
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
498
ALTER TABLE t1 ROW_FORMAT=COMPACT;
500
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
502
--echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8
503
DROP TABLE IF EXISTS t1;
504
CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15;
506
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
508
--echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
509
--echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT
510
--echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda.
511
DROP TABLE IF EXISTS t1;
512
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
514
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
515
SET GLOBAL innodb_file_format=Antelope;
516
ALTER TABLE t1 ADD COLUMN f1 INT;
518
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
519
SET GLOBAL innodb_file_format=Barracuda;
520
ALTER TABLE t1 ADD COLUMN f2 INT;
522
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
523
DROP TABLE IF EXISTS t1;
524
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
526
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
527
SET GLOBAL innodb_file_format=Antelope;
528
ALTER TABLE t1 ADD COLUMN f1 INT;
530
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
531
SET GLOBAL innodb_file_format=Barracuda;
532
ALTER TABLE t1 ADD COLUMN f2 INT;
534
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
536
--echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a
537
--echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF
538
--echo and then used again when innodb_file_per_table=ON.
539
DROP TABLE IF EXISTS t1;
540
CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
542
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
543
SET GLOBAL innodb_file_per_table=OFF;
544
ALTER TABLE t1 ADD COLUMN f1 INT;
546
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
547
SET GLOBAL innodb_file_per_table=ON;
548
ALTER TABLE t1 ADD COLUMN f2 INT;
550
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
551
DROP TABLE IF EXISTS t1;
552
CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC;
554
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
555
SET GLOBAL innodb_file_per_table=OFF;
556
ALTER TABLE t1 ADD COLUMN f1 INT;
558
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
559
SET GLOBAL innodb_file_per_table=ON;
560
ALTER TABLE t1 ADD COLUMN f2 INT;
562
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1';
566
DROP TABLE IF EXISTS t1;
569
EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig;
570
EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
571
EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;