InnoDB Plugin 1.0 for MySQL 5.1 User's Guide  /  ...  /  SQL Compression Syntax Warnings and Errors

3.2.2. SQL Compression Syntax Warnings and Errors

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).

LevelCodeMessage
Warning1478 InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

If you are running in InnoDB strict mode, 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 3.1, “Meaning of CREATE TABLE and ALTER TABLE Options” summarizes how the various options on CREATE TABLE and ALTER TABLE are handled.

Table 3.1. Meaning of CREATE TABLE and ALTER TABLE Options

OptionUsageDescription
ROW_FORMAT=​REDUNDANTStorage format used prior to MySQL 5.0.3Less efficient than ROW_FORMAT=COMPACT; for backward compatibility
ROW_FORMAT=​COMPACTDefault storage format since MySQL 5.0.3Stores 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=​DYNAMICAvailable only with innodb_file​_format=BarracudaStore 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=​COMPRESSEDAvailable only with innodb_file​_format=BarracudaCompresses the table and indexes using zlib to default compressed page size of 8K bytes; implies ROW_FORMAT=DYNAMIC
KEY_BLOCK_​SIZE=nAvailable only with innodb_file​_format=BarracudaSpecifies compressed page size of 1, 2, 4, 8 or 16K bytes; implies ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED

Table 3.2, “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, InnoDB creates or alters the table, but may ignore 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. You can see the full description of the error condition with SHOW ERRORS. For 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 3.2. CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

SyntaxWarning or Error ConditionResulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=REDUNDANTNoneREDUNDANT
ROW_FORMAT=COMPACTNoneCOMPACT
ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC or KEY_BLOCK_SIZE is specifiedIgnored unless you override the default settings for innodb_file_format and innodb_file_per_tableCOMPACT
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)KEY_BLOCK_SIZE is ignoredthe requested one, or COMPACT by default
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specifiedNone; KEY_BLOCK_SIZE specified is used, not the 8K defaultCOMPRESSED
KEY_BLOCK_SIZE is specified with REDUNDANT, COMPACT or DYNAMIC row formatKEY_BLOCK_SIZE is ignoredREDUNDANT, COMPACT or DYNAMIC
ROW_FORMAT is not one of REDUNDANT, COMPACT, DYNAMIC or COMPRESSEDIgnored if recognized by the MySQL parser. Otherwise, an error is issued.COMPACT or N/A

When InnoDB strict mode is ON (innodb_strict_mode=1), the InnoDB Plugin rejects invalid ROW_FORMAT or KEY_BLOCK_SIZE parameters. For compatibility with the built-in InnoDB in MySQL, InnoDB strict mode is not enabled by default, and in this default non-strict mode, the InnoDB Plugin 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 by InnoDB). The real compressed page size inside InnoDB cannot be displayed by MySQL.

User Comments
Sign Up Login You must be logged in to post a comment.