MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
Specifying ROW_FORMAT=COMPRESSED
or
KEY_BLOCK_SIZE
in CREATE
TABLE
or ALTER TABLE
statements produces the following warnings if the Barracuda file
format is not enabled. You can view them with the SHOW
WARNINGS
statement.
Level | Code | Message |
---|---|---|
Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires
innodb_file_per_table. |
Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1 |
Warning | 1478 | InnoDB: ignoring
KEY_BLOCK_SIZE= |
Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires
innodb_file_per_table. |
Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
Notes:
By default, these messages are only warnings, not errors, and the table is created without compression, as if the options were not specified.
When innodb_strict_mode
is
enabled, MySQL generates an error, not a warning, for these
cases. The table is not created if the current configuration
does not permit using compressed tables.
The “non-strict” behavior lets you import a
mysqldump
file into a database that does not
support compressed tables, even if the source database contained
compressed tables. In that case, MySQL creates the table in
ROW_FORMAT=COMPACT
instead of preventing the
operation.
To import the dump file into a new database, and have the tables
re-created as they exist in the original database, ensure the
server has the proper settings for the configuration parameters
innodb_file_format
and
innodb_file_per_table
.
The attribute KEY_BLOCK_SIZE
is permitted only
when ROW_FORMAT
is specified as
COMPRESSED
or is omitted. Specifying a
KEY_BLOCK_SIZE
with any other
ROW_FORMAT
generates a warning that you can
view with SHOW WARNINGS
. However, the table is
non-compressed; the specified KEY_BLOCK_SIZE
is
ignored).
Level | Code | Message |
---|---|---|
Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE= |
If you are running with
innodb_strict_mode
enabled, the
combination of a KEY_BLOCK_SIZE
with any
ROW_FORMAT
other than
COMPRESSED
generates an error, not a warning,
and the table is not created.
Table 14.5, “ROW_FORMAT and KEY_BLOCK_SIZE Options”
provides an overview the ROW_FORMAT
and
KEY_BLOCK_SIZE
options that are used with
CREATE TABLE
or
ALTER TABLE
.
Table 14.5 ROW_FORMAT and KEY_BLOCK_SIZE Options
Option | Usage Notes | Description |
---|---|---|
ROW_FORMAT=REDUNDANT |
Storage format used prior to MySQL 5.0.3 | Less efficient than ROW_FORMAT=COMPACT ; for backward
compatibility |
ROW_FORMAT=COMPACT |
Default storage format since MySQL 5.0.3 | Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page |
ROW_FORMAT=DYNAMIC |
Available only with
innodb_file_format=Barracuda |
Store values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix) |
ROW_FORMAT=COMPRESSED |
Available only with
innodb_file_format=Barracuda |
Compresses the table and indexes using zlib |
KEY_BLOCK_SIZE= |
Available only with
innodb_file_format=Barracuda |
Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies
ROW_FORMAT=COMPRESSED |
Table 14.6, “CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF”
summarizes error conditions that occur with certain combinations
of configuration parameters and options on the
CREATE TABLE
or
ALTER TABLE
statements, and how the
options appear in the output of SHOW TABLE
STATUS
.
When innodb_strict_mode
is
OFF
, MySQL creates or alters the table, but
ignores certain settings as shown below. You can see the warning
messages in the MySQL error log. When
innodb_strict_mode
is
ON
, these specified combinations of options
generate errors, and the table is not created or altered. To see
the full description of the error condition, issue the
SHOW ERRORS
statement: example:
mysql>CREATE TABLE x (id INT PRIMARY KEY, c INT)
->ENGINE=INNODB KEY_BLOCK_SIZE=33333;
ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478) mysql>SHOW ERRORS;
+-------+------+-------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------+ | Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333. | | Error | 1005 | Can't create table 'test.x' (errno: 1478) | +-------+------+-------------------------------------------+
Table 14.6 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF
Syntax | Warning or Error Condition | Resulting ROW_FORMAT , as shown in SHOW TABLE
STATUS |
---|---|---|
ROW_FORMAT=REDUNDANT |
None | REDUNDANT |
ROW_FORMAT=COMPACT |
None | COMPACT |
ROW_FORMAT=COMPRESSED or
ROW_FORMAT=DYNAMIC or
KEY_BLOCK_SIZE is specified |
Ignored unless both
innodb_file_format =Barracuda
and innodb_file_per_table
are enabled |
COMPACT |
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8
or 16) |
KEY_BLOCK_SIZE is ignored |
the requested row format, or COMPACT by default |
ROW_FORMAT=COMPRESSED and valid
KEY_BLOCK_SIZE are specified |
None; KEY_BLOCK_SIZE specified is used |
COMPRESSED |
KEY_BLOCK_SIZE is specified with
REDUNDANT , COMPACT or
DYNAMIC row format |
KEY_BLOCK_SIZE is ignored |
REDUNDANT , COMPACT or
DYNAMIC |
ROW_FORMAT is not one of
REDUNDANT , COMPACT ,
DYNAMIC or COMPRESSED |
Ignored if recognized by the MySQL parser. Otherwise, an error is issued. | COMPACT or N/A |
When innodb_strict_mode
is
ON
, MySQL rejects invalid
ROW_FORMAT
or KEY_BLOCK_SIZE
parameters. For compatibility with earlier versions of MySQL,
strict mode is not enabled by default; instead, MySQL issues
warnings (not errors) for ignored invalid parameters.
It is not possible to see the chosen
KEY_BLOCK_SIZE
using SHOW TABLE
STATUS
. The statement SHOW CREATE
TABLE
displays the KEY_BLOCK_SIZE
(even if it was ignored when creating the table). The real
compressed page size of the table cannot be displayed by MySQL.