5.4.6.6. SQL Compression Syntax Warnings and Errors

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=4.`
Warning	1478	`InnoDB: ROW_FORMAT=COMPRESSED requiresinnodb_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=nunless ROW_FORMAT=COMPRESSED.`

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 5.6, "Meaning of CREATE TABLE and ALTER TABLE options" summarizes how the various options on CREATE TABLE and ALTER TABLE are handled.

Table 5.6. Meaning of CREATE TABLE andALTER TABLE options

Option	Usage	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 to default compressed page size of 8K bytes; implies `ROW_FORMAT=DYNAMIC`
`KEY_BLOCK_SIZE=n`	Available only with `innodb_file_format=Barracuda`	Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies `ROW_FORMAT=DYNAMIC` and`ROW_FORMAT=COMPRESSED`

Table 5.7, "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) | +-------+------+-------------------------------------------+ 2 rows in set (0.00 sec)

Table 5.7. CREATE/ALTER TABLE Warnings and Errors when InnoDBStrict 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 one, or `COMPACT` by default
`ROW_FORMAT=COMPRESSED` and valid `KEY_BLOCK_SIZE` are specified	None; `KEY_BLOCK_SIZE` specified is used, not the 8K default	`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 isissued.	`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.

Note that 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.