14.7.1 Enabling File Formats

The innodb_file_format configuration parameter defines the file format to use for new InnoDB tables that are created in file_per_table tablespaces. Therefore, to use the innodb_file_format parameter, innodb_file_per_table must be enabled.

To create new tables that take advantage of features supported by the Barracuda file format, including table compression (see Section 14.8, “InnoDB Row Storage and Row Formats”), off-page storage for long column data (see Section 14.8, “InnoDB Row Storage and Row Formats”), and index key prefixes up to 3072 bytes (innodb_large_prefix), set innodb_file_format to Barracuda. You must also specify ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC when creating the table.

To preclude the use of features supported by the Barracuda file that make your database inaccessible to the built-in InnoDB in MySQL 5.1 and prior releases, set innodb_file_format to Antelope. Alternatively, you can disable innodb_file_per_table to have new tables created in the system tablespace. The system tablespace is stored in the original Antelope file format.

You can set the value of innodb_file_format on the command line when you start mysqld, or in the option file (my.cnf on Unix, my.ini on Windows). You can also change it dynamically with the SET GLOBAL statement.

mysql> SET GLOBAL innodb_file_format=BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

Be aware that ALTER TABLE operations that recreate InnoDB tables use the current innodb_file_format setting.

Although Oracle recommends using the Barracuda format for new tables where practical, in MySQL 5.6 the default file format is Antelope, for maximum compatibility with replication configurations containing earlier MySQL releases.