InnoDB Plugin 1.0 for MySQL 5.1 User's Guide  /  ...  /  Configuration Parameters for Compression

3.2.1. Configuration Parameters for Compression

Compressed tables are stored in a format that previous versions of InnoDB cannot process. To preserve downward compatibility of database files, compression can be specified only when the Barracuda data file format is enabled using the configuration parameter innodb_file_format.

Table compression is also not available for the InnoDB system tablespace. The system tablespace (space 0, the ibdata* files) may contain user data, but it also contains internal InnoDB system information, and therefore is never compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces.

To use compression, enable the file per table mode using the configuration parameter innodb_file_per_table and enable the Barracuda disk file format using the parameter innodb_file_format. You can set these parameters in the MySQL option file my.cnf or my.ini, but both are dynamic parameters that you can change with the SET statement without shutting down the MySQL server, as noted in Section 9.5, “Configuring the InnoDB Plugin”.

Specifying ROW_FORMAT=COMPRESSED or a KEY_BLOCK_SIZE in the CREATE TABLE or ALTER TABLE statements if the Barracuda file format has not been enabled produces these warnings that you can view with the SHOW WARNINGS statement:

Warning1478InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning1478InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1.
Warning1478InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning1478InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning1478InnoDB: assuming ROW_FORMAT=COMPACT.

These messages are only warnings, not errors, and the table is created as if the options were not specified. Enabling InnoDB strict mode (see Section 8.5, “InnoDB Strict Mode”) causes InnoDB to generate an error, not a warning, for these cases. In strict mode, the table is not created if the current configuration does not permit using compressed tables.

The non-strict behavior is intended to permit you to import a mysqldump file into a database that does not support compressed tables, even if the source database contained compressed tables. In that case, the InnoDB Plugin creates the table in ROW_FORMAT=COMPACT instead of preventing the operation.

When you import the dump file into a new database, if you want to have the tables re-created as they exist in the original database, ensure the server is running the InnoDB Plugin with the proper settings for the configuration parameters innodb_file_format and innodb_file_per_table,

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