Table of Contents
As InnoDB evolves, new on-disk data structures are sometimes required to support new features. This release of InnoDB introduces two such new data structures: compressed tables (see Chapter 3, InnoDB Data Compression), and long variable-length columns stored off-page (see Chapter 5, Storage of Variable-Length Columns). These new data structures are not compatible with prior versions of InnoDB. Note, however, that the other new features of the InnoDB Plugin do not require the use of the new file format.
In general, a newer version of InnoDB may create a table or index that cannot safely be read or written with a prior version of InnoDB without risk of crashes, hangs, wrong results or corruptions. The InnoDB Plugin introduces a new mechanism to guard against these conditions, and to help preserve compatibility among database files and versions of InnoDB. Henceforth, users can take advantage of some new features of an InnoDB release (e.g., performance improvements and bug fixes), and still preserve the option of using their database with a prior version of InnoDB, by precluding the use of new features that create downward incompatible on-disk data structures.
The InnoDB Plugin introduces the idea of a named file format and a configuration parameter to enable the use of features that require use of that format. The new file format is the “Barracuda” format, and the file format supported by prior releases of InnoDB is called file format “Antelope”. Compressed tables and the new row format that stores long columns “off-page” require the use of the “Barracuda” file format or newer. Future versions of InnoDB may introduce a series of file formats, identified with the names of animals, in ascending alphabetical order.
Beginning with this release, every InnoDB per-table
tablespace file is labeled with a file format identifier. This
does not apply to the system tablespace (the
ibdata files) but only the files of separate
tablespaces (the *.ibd files where tables and
indexes are stored in their own tablespace). As noted below,
however, the system tablespace 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.
In this release, when you create a compressed table, or a
table with ROW_FORMAT=DYNAMIC, the file header
for the corresponding .ibd file and the table
type in the InnoDB data dictionary are updated 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 this new file format. To protect against
anomalous behavior, InnoDB version 5.0.21 and later will perform
a compatibility check when the table is opened, as described
below. (Note that the ALTER TABLE command
will, in many cases, cause a table to be recreated and thereby
change its properties. The special case of adding or dropping
indexes without rebuilding the table is described in
Chapter 2, Fast Index Creation in the InnoDB Storage Engine.)
If a version of InnoDB supports a particular file format (whether or not it is enabled), you can access and even 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 by the currently running software, it cannot be accessed at all, even for read access.
The only way to “downgrade” an InnoDB
tablespace to an earlier 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 ALTER TABLE command, as described
in Section 4.6, “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, 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 uses the “Barracuda” format.
Otherwise, it uses the prior InnoDB file format, “Antelope”.
The new configuration parameter
innodb_file_format controls whether such
commands as CREATE TABLE and
ALTER TABLE can be used to create tables that
depend on support for the “Barracuda” file format.
The file format is a dynamic, global parameter that can be
specified in the MySQL option file (my.cnf or
my.ini) or changed with the
SET GLOBAL command, as described in
Section 9.5, “Configuring the InnoDB Plugin”.
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:
ibdata files)
that contain internal system information (including internal
catalogs and undo information) and may include user data and
indexes
*.ibd files)
ib_logfile1
and ib_logfile2), used for crash recovery and
in backups
This collection of files is transactionally consistent, and
recoverable as a unit.
An “ib-file set” specifically does not include
the related MySQL .frm files that contain
meta data about InnoDB tables.
The .frm files are created and managed
exclusively by MySQL, and can sometimes get out of sync
with the internal meta data in InnoDB.
Instead of “ib-file set”, we might call such a collection a “database”. However, MySQL uses the word “database” to mean a logical collection of tables, what other systems term a “schema” or “catalog”. Given MySQL terminology, multiple tables (even from more than one database) can be stored in a single “ib-file set”.
The InnoDB Plugin incorporates several checks to guard against the possible crashes and data corruptions that might occur if you use an ib-file set in a file format that is not supported by the software release in use. These checks take place when the server is started, and when you first access a table. This section details these checks, how you can control them, and error and warning conditions that may arise.
To prevent possible crashes or data corruptions when
InnoDB Plugin opens an ib-file set, it will check 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 the problems
described in Section 11.4, “Possible Problems”.
Beginning with version 1.0.1 of the InnoDB Plugin,
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 new 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 will be 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 will prevent the InnoDB Plugin 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
will be useful (with future
releases of InnoDB) if you manually “downgrade”
all of the tables in an ib-file set (as described in
Chapter 11, Downgrading from the InnoDB Plugin). 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 “too new” format
(one that is not supported by the software you’re using). If you
set the configuration parameter innodb_file_format_check to
OFF, the InnoDB Plugin will open the database,
but issue 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 very dangerous setting, as it permits the
recovery process to run, possibly corrupting your database if
the previous shutdown was a crash 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 will occur. In a future
release, this parameter setting may be renamed from
OFF to
UNSAFE. (However, until there are newer releases
of InnoDB that support additional file formats, even disabling
the startup checking is in fact “safe”.)
Note that 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 will not be
updated, but table-level compatibility checking will apply (and
an error will be issued), as described in Section 4.4.2, “Table-access File Format Compatibility Checking”.
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;
will display the name
of the newest file format known to be used by tables in the
currently open ib-file set and supported by the currently
executing software.
To best illustrate this behavior, consider the scenario described in Table 4.1, “InnoDB data file compatibility and related InnoDB parameters”. Imagine that some future version of InnoDB supports the “Cheetah” format and that an ib-file set has been used with that version.
Table 4.1. InnoDB data file compatibility and related InnoDB parameters
| innodb file format check | innodb file format | Highest file format used in ib-file set | Highest file format supported by InnoDB | Result |
|---|---|---|---|---|
OFF | Antelope or
Barracuda | Barracuda | Barracuda | Database will open; tables can be created which require “Antelope” or “Barracuda” file format |
OFF | Antelope or
Barracuda | Cheetah | Barracuda | Database will open with a warning, since the db contains files in a “too new” format; tables can be created which require “Antelope” or “Barracuda” file format; tables in “Cheetah” format cannot be accessed |
OFF | Cheetah | Barracuda | Barracuda | Database will not open; innodb_file_format
cannot be set to “Cheetah” |
ON | Antelope or
Barracuda | Barracuda | Barracuda | Database will open; tables can be created which require “Antelope” or “Barracuda” file format |
ON | Antelope or
Barracuda | Cheetah | Barracuda | Database will not open, since the db contains files in a “too new” format (“Cheetah”) |
ON | Cheetah | Barracuda | Barracuda | Database will not open; innodb_file_format
cannot be set to “Cheetah” |
When a table is first accessed, InnoDB (including some releases prior to InnoDB Plugin 1.0) check 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.
Note that 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
specific file formats, even if they are 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 any supported format.
As noted in Section 4.2, “Named File Formats”, versions of InnoDB 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 (e.g., 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 will report the following error:
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
Furthermore, InnoDB will write a message to the error log:
InnoDB: tabletest/t1: unknown table type33
The table type should be equal to the tablespace flags, which contains the file format version as discussed in Section 4.5, “Identifying the File Format in Use”.
Versions of InnoDB prior to 4.1 did not include table format identifiers in the database files, and versions prior to 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 “too new” format is used with versions of InnoDB prior to 5.0.21.
The new file format management capability introduced with the InnoDB Plugin (comprising 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 will still apply.
Users are strongly urged not to use database files that contain “Barracuda” file format tables with releases of InnoDB older than the InnoDB Plugin. It is possible to “downgrade” such tables to the “Antelope” format (that the built-in InnoDB in MySQL up to version 5.1 supports) with the procedure described in Section 4.6, “Downgrading the File Format”.
Although you may have enabled a given
innodb_file_format at a particular time, unless
you create a new table, the database file format is unchanged.
If you do create a new table, the tablespace containing the table
is tagged with the “earliest” or “simplest”
file format that is required for the table’s features. For example,
if you enable file format “Barracuda”, and create a new table that
is not compressed and does not use ROW_FORMAT=DYNAMIC,
the new tablespace that contains the table will be tagged as using file
format “Antelope”.
It is easy to identify the file format used by a given
tablespace or table. The table uses the “Barracuda” format if
the Row_format reported by
SHOW CREATE TABLE or
INFORMATION_SCHEMA.TABLES is
one of 'Compressed' or
'Dynamic'. (Please note that the
Row_format is a separate column, and ignore the
contents of the Create_options column, which
may contain the string ROW_FORMAT.) If the
table in a tablespace uses neither of those features, the file
uses the format supported by prior releases of InnoDB, now
called file format “Antelope”. Then, the
Row_format will be one of
'Redundant' or
'Compact'.
The file format identifier is written as part of the
tablespace flags (a 32-bit number) in the *.ibd
file in the 4 bytes starting at position 54 of the file, most
significant byte first. (The first byte of the file is byte
zero.) On some systems, you can display these bytes in
hexadecimal with the command od -t x1 -j 54 -N 4
. If all bytes
are zero, the tablespace uses the “Antelope” file format (which
is the format used by the standard built-in InnoDB in MySQL up to version 5.1).
Otherwise, the least significant bit should be set in the
tablespace flags, and the file format identifier is written in the
bits 5 through 11. (Divide the tablespace flags by 32 and take
the remainder after dividing the integer part of the result by
128.)
tablename.ibd
The InnoDB tablespaces files (*.ibd file) are
tagged with the file format used to create its table and indexes.
The way to downgrade the tablespace is to re-create the table and
its indexes. The easiest way to recreate a table and its indexes
is to use the command
ALTER TABLEtROW_FORMAT=COMPACT;
on each table that you want to downgrade. The
COMPACT row format uses the file format “Antelope”.
It was introduced in MySQL 5.0.3.
The file format used by the standard built-in InnoDB in MySQL 5.1 is the “Antelope” format, and the new file format introduced with the InnoDB Plugin 1.0 is the “Barracuda” format. No definitive plans have been made to introduce new features that would require additional new file formats. However, the file format mechanism introduced with the InnoDB Plugin allows for further enhancements.
For the sake of completeness, these are the file format names that might be used for future file formats: Antelope, Barracuda, Cheetah, Dragon, Elk, Fox, Gazelle, Hornet, Impala, Jaguar, Kangaroo, Leopard, Moose, Nautilus, Ocelot, Porpoise, Quail, Rabbit, Shark, Tiger, Urchin, Viper, Whale, X, Y and Zebra. These file formats correspond to the internal identifiers 0..25.