- 3.2 Enabling Compression for a Table
- 3.2.1 Configuration Parameters for Compression
- 3.2.2 SQL Compression Syntax Warnings and Errors
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 command 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 commands if the “Barracuda”
file format has not been enabled produces these warnings that
you can view with the SHOW WARNINGS command:
| 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. |
Note
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,
This is the User’s Guide for InnoDB Plugin 1.0.6 for MySQL 5.1, generated on March 4, 2010 (rev 673:680M).
