14.10 InnoDB File-Format Management

14.10.1 Enabling File Formats

The 'innodb_file_format' configuration option enables an 'InnoDB' file format for file-per-table tablespaces.

'Barracuda' is the default 'innodb_file_format' setting. In earlier releases, the default file format was 'Antelope'.

Note:

The 'innodb_file_format' configuration option is deprecated and may be removed in a future release. For more information, see *note innodb-file-format::.

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

 SET GLOBAL innodb_file_format=Barracuda;

Usage notes

• 'InnoDB' file format settings do not apply to tables stored in note general tablespaces: general-tablespaces. General tablespaces provide support for all row formats and associated features. For more information, see note general-tablespaces::.

• The 'innodb_file_format' setting is not applicable when using the 'TABLESPACE [=] innodb_system' table option with note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. to store a 'DYNAMIC' table in the system tablespace.

• The 'innodb_file_format' setting is ignored when creating tables that use the 'DYNAMIC' row format. For more information, see *note innodb-row-format-dynamic::.

 File: manual.info.tmp, Node: innodb-file-format-compatibility, Next: innodb-file-format-identifying, Prev: innodb-file-format-enabling, Up: innodb-file-format

14.10.2 Verifying File Format Compatibility

• Menu:

• innodb-file-format-compatibility-checking:: Compatibility Check When InnoDB Is Started

• innodb-file-format-compatibility-checking-table-access:: Compatibility Check When a Table Is Opened

InnoDB incorporates several checks to guard against the possible crashes and data corruptions that might occur if you run an old release of the MySQL server on InnoDB data files that use a newer file format. These checks take place when the server is started, and when you first access a table. This section describes these checks, how you can control them, and error and warning conditions that might arise.

Backward Compatibility

You only need to consider backward file format compatibility when using a recent version of InnoDB (MySQL 5.5 and higher with InnoDB) alongside an older version (MySQL 5.1 or earlier, with the built-in InnoDB rather than the InnoDB Plugin). To minimize the chance of compatibility issues, you can standardize on the InnoDB Plugin for all your MySQL 5.1 and earlier database servers.

In general, a newer version of InnoDB may create a table or index that cannot safely be read or written with an older version of InnoDB without risk of crashes, hangs, wrong results or corruptions. InnoDB includes a mechanism to guard against these conditions, and to help preserve compatibility among database files and versions of InnoDB. This mechanism lets you take advantage of some new features of an InnoDB release (such as performance improvements and bug fixes), and still preserve the option of using your database with an old version of InnoDB, by preventing accidental use of new features that create downward-incompatible disk files.

If a version of InnoDB supports a particular file format (whether or not that format is the default), you can query and update any table that requires that format or an earlier format. Only the creation of new tables using new features is limited based on the particular file format enabled. Conversely, if a tablespace contains a table or index that uses a file format that is not supported, it cannot be accessed at all, even for read access.

The only way to 'downgrade' an InnoDB tablespace to the earlier Antelope file format is to copy the data to a new table, in a tablespace that uses the earlier format.

The easiest way to determine the file format of an existing InnoDB tablespace is to examine the properties of the table it contains, using the 'SHOW TABLE STATUS' command or querying the table 'INFORMATION_SCHEMA.TABLES'. If the 'Row_format' of the table is reported as ''Compressed'' or ''Dynamic'', the tablespace containing the table supports the Barracuda format.

Internal Details

Every InnoDB file-per-table tablespace (represented by a '*.ibd' file) file is labeled with a file format identifier. The system tablespace (represented by the 'ibdata' files) is tagged with the 'highest' file format in use in a group of InnoDB database files, and this tag is checked when the files are opened.

Creating a compressed table, or a table with 'ROW_FORMAT=DYNAMIC', updates the file header of the corresponding file-per-table '.ibd' file and the table type in the InnoDB data dictionary with the identifier for the Barracuda file format. From that point forward, the table cannot be used with a version of InnoDB that does not support the Barracuda file format. To protect against anomalous behavior, InnoDB performs a compatibility check when the table is opened. (In many cases, the note 'ALTER TABLE': alter-table. statement recreates a table and thus changes its properties. The special case of adding or dropping indexes without rebuilding the table is described in note innodb-online-ddl-operations::.)

General tablespaces, which are also represented by a '.ibd' file, support both Antelope and Barracuda file formats. For more information about general tablespaces, see note general-tablespaces::.

Definition of ib-file set

To avoid confusion, for the purposes of this discussion we define the term 'ib-file set' to mean the set of operating system files that InnoDB manages as a unit. The ib-file set includes the following files:

• The system tablespace (one or more 'ibdata' files) that contain internal system information (including internal catalogs and undo information) and may include user data and indexes.

• Zero or more single-table tablespaces (also called 'file per table' files, named '*.ibd' files).

• InnoDB log files; usually two, 'ib_logfile0' and 'ib_logfile1'. Used for crash recovery and in backups.

An 'ib-file set' does not include the corresponding '.frm' files that contain metadata about InnoDB tables. The '.frm' files are created and managed by MySQL, and can sometimes get out of sync with the internal metadata in InnoDB.

Multiple tables, even from more than one database, can be stored in a single 'ib-file set'. (In MySQL, a 'database' is a logical collection of tables, what other systems refer to as a 'schema' or 'catalog'.)

 File: manual.info.tmp, Node: innodb-file-format-compatibility-checking, Next: innodb-file-format-compatibility-checking-table-access, Prev: innodb-file-format-compatibility, Up: innodb-file-format-compatibility

14.10.2.1 Compatibility Check When InnoDB Is Started ....................................................

To prevent possible crashes or data corruptions when InnoDB opens an ib-file set, it checks that it can fully support the file formats in use within the ib-file set. If the system is restarted following a crash, or a 'fast shutdown' (i.e., 'innodb_fast_shutdown' is greater than zero), there may be on-disk data structures (such as redo or undo entries, or doublewrite pages) that are in a 'too-new' format for the current software. During the recovery process, serious damage can be done to your data files if these data structures are accessed. The startup check of the file format occurs before any recovery process begins, thereby preventing consistency issues with the new tables or startup problems for the MySQL server.

Beginning with version InnoDB 1.0.1, the system tablespace records an identifier or tag for the 'highest' file format used by any table in any of the tablespaces that is part of the ib-file set. Checks against this file format tag are controlled by the configuration parameter 'innodb_file_format_check', which is 'ON' by default.

If the file format tag in the system tablespace is newer or higher than the highest version supported by the particular currently executing software and if 'innodb_file_format_check' is 'ON', the following error is issued when the server is started:

 InnoDB: Error: the system tablespace is in a
 file format that this version doesn't support

You can also set 'innodb_file_format' to a file format name. Doing so prevents InnoDB from starting if the current software does not support the file format specified. It also sets the 'high water mark' to the value you specify. The ability to set 'innodb_file_format_check' is useful (with future releases) if you manually 'downgrade' all of the tables in an ib-file set. You can then rely on the file format check at startup if you subsequently use an older version of InnoDB to access the ib-file set.

In some limited circumstances, you might want to start the server and use an ib-file set that is in a new file format that is not supported by the software you are using. If you set the configuration parameter 'innodb_file_format_check' to 'OFF', InnoDB opens the database, but issues this warning message in the error log:

 InnoDB: Warning: the system tablespace is in a
 file format that this version doesn't support

Note:

This is a dangerous setting, as it permits the recovery process to run, possibly corrupting your database if the previous shutdown was an unexpected exit or 'fast shutdown'. You should only set 'innodb_file_format_check' to 'OFF' if you are sure that the previous shutdown was done with 'innodb_fast_shutdown=0', so that essentially no recovery process occurs.

The parameter 'innodb_file_format_check' affects only what happens when a database is opened, not subsequently. Conversely, the parameter 'innodb_file_format' (which enables a specific format) only determines whether or not a new table can be created in the enabled format and has no effect on whether or not a database can be opened.

The file format tag is a 'high water mark', and as such it is increased after the server is started, if a table in a 'higher' format is created or an existing table is accessed for read or write (assuming its format is supported). If you access an existing table in a format higher than the format the running software supports, the system tablespace tag is not updated, but table-level compatibility checking applies (and an error is issued), as described in *note innodb-file-format-compatibility-checking-table-access::. Any time the high water mark is updated, the value of 'innodb_file_format_check' is updated as well, so the command 'SELECT @@innodb_file_format_check;' displays the name of the latest file format known to be used by tables in the currently open ib-file set and supported by the currently executing software.

 File: manual.info.tmp, Node: innodb-file-format-compatibility-checking-table-access, Prev: innodb-file-format-compatibility-checking, Up: innodb-file-format-compatibility

14.10.2.2 Compatibility Check When a Table Is Opened ....................................................

When a table is first accessed, InnoDB (including some releases prior to InnoDB 1.0) checks that the file format of the tablespace in which the table is stored is fully supported. This check prevents crashes or corruptions that would otherwise occur when tables using a 'too new' data structure are encountered.

All tables using any file format supported by a release can be read or written (assuming the user has sufficient privileges). The setting of the system configuration parameter 'innodb_file_format' can prevent creating a new table that uses a specific file format, even if the file format is supported by a given release. Such a setting might be used to preserve backward compatibility, but it does not prevent accessing any table that uses a supported format.

Versions of MySQL older than 5.0.21 cannot reliably use database files created by newer versions if a new file format was used when a table was created. To prevent various error conditions or corruptions, InnoDB checks file format compatibility when it opens a file (for example, upon first access to a table). If the currently running version of InnoDB does not support the file format identified by the table type in the InnoDB data dictionary, MySQL reports the following error:

 ERROR 1146 (42S02): Table 'TEST.T1' doesn't exist

InnoDB also writes a message to the error log:

 InnoDB: table TEST/T1: unknown table type 33

The table type should be equal to the tablespace flags, which contains the file format version as discussed in *note innodb-file-format-identifying::.

Versions of InnoDB prior to MySQL 4.1 did not include table format identifiers in the database files, and versions prior to MySQL 5.0.21 did not include a table format compatibility check. Therefore, there is no way to ensure proper operations if a table in a newer file format is used with versions of InnoDB prior to 5.0.21.

The file format management capability in InnoDB 1.0 and higher (tablespace tagging and run-time checks) allows InnoDB to verify as soon as possible that the running version of software can properly process the tables existing in the database.

If you permit InnoDB to open a database containing files in a format it does not support (by setting the parameter 'innodb_file_format_check' to 'OFF'), the table-level checking described in this section still applies.

Users are strongly urged not to use database files that contain Barracuda file format tables with releases of InnoDB older than the MySQL 5.1 with the InnoDB Plugin. It may be possible to rebuild such tables to use the Antelope format.

 File: manual.info.tmp, Node: innodb-file-format-identifying, Next: innodb-file-format-downgrading, Prev: innodb-file-format-compatibility, Up: innodb-file-format

14.10.3 Identifying the File Format in Use

If you enable a different file format using the 'innodb_file_format' configuration option, the change only applies to newly created tables. Also, when you create a new table, the tablespace containing the table is tagged with the 'earliest' or 'simplest' file format that is required to support the table's features. For example, if you enable the 'Barracuda' file format, and create a new table that does not use the Dynamic or Compressed row format, the new tablespace that contains the table is tagged as using the 'Antelope' file format .

It is easy to identify the file format used by a given table. The table uses the 'Antelope' file format if the row format reported by 'SHOW TABLE STATUS' is either 'Compact' or 'Redundant'. The table uses the 'Barracuda' file format if the row format reported by 'SHOW TABLE STATUS' is either 'Compressed' or 'Dynamic'.

 mysql> SHOW TABLE STATUS\G
 *************************** 1. row ***************************
            Name: t1
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 0
  Avg_row_length: 0
     Data_length: 16384
 Max_data_length: 0
    Index_length: 16384
       Data_free: 0
  Auto_increment: 1
     Create_time: 2014-11-03 13:32:10
     Update_time: NULL
      Check_time: NULL
       Collation: latin1_swedish_ci
        Checksum: NULL
  Create_options:
         Comment:

You can also identify the file format used by a given table or tablespace using 'InnoDB' 'INFORMATION_SCHEMA' tables. For example:

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1'\G
 *************************** 1. row ***************************
      TABLE_ID: 44
          NAME: test/t1
          FLAG: 1
        N_COLS: 6
         SPACE: 30
   FILE_FORMAT: Antelope
    ROW_FORMAT: Compact
 ZIP_PAGE_SIZE: 0

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/t1'\G
 *************************** 1. row ***************************
         SPACE: 30
          NAME: test/t1
          FLAG: 0
   FILE_FORMAT: Antelope
    ROW_FORMAT: Compact or Redundant
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0

 File: manual.info.tmp, Node: innodb-file-format-downgrading, Prev: innodb-file-format-identifying, Up: innodb-file-format

14.10.4 Modifying the File Format

Each InnoDB tablespace file (with a name matching '*.ibd') is tagged with the file format used to create its table and indexes. The way to modify the file format is to re-create the table and its indexes. The easiest way to recreate a table and its indexes is to use the following command on each table that you want to modify:

 ALTER TABLE T ROW_FORMAT=FORMAT_NAME;

If you are modifying the file format to downgrade to an older MySQL version, there may be incompatibilities in table storage formats that require additional steps. For information about downgrading to a previous MySQL version, see *note downgrading::.

 File: manual.info.tmp, Node: innodb-row-format, Next: innodb-disk-management, Prev: innodb-file-format, Up: innodb-storage-engine