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.
You only need to consider backward file format compatibility when using a recent version of InnoDB (the InnoDB Plugin, or 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. MySQL 5.5 and higher with 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 a prior 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. This can be
done with the
statement, as described in
Section 14.10.4, “Downgrading the File Format”.
The easiest way to determine the file format of an existing InnoDB
tablespace is to examine the properties of the table it contains,
SHOW TABLE STATUS command or querying
INFORMATION_SCHEMA.TABLES. If the
Row_format of the table is reported as
the tablespace containing the table uses the Barracuda format.
Otherwise, it uses the prior InnoDB file format, Antelope.
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 version 5.0.21 and later performs a compatibility check
when the table is opened. (In many cases, the
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
Section 14.13, “InnoDB Fast Index Creation”.)
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
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
InnoDB log files; usually two,
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
.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”.)