Table of Contents
As InnoDB evolves, new on-disk data structures are sometimes required to support new features. Features such as compressed tables (see Chapter 3, InnoDB Data Compression), and long variable-length columns stored off-page (see Chapter 5, How InnoDB Stores Variable-Length Columns) require data file formats that are not compatible with prior versions of InnoDB. These features both require use of the new Barracuda file format.
All other new features are compatible with the original Antelope file format and do not require the Barracuda file format.
This section discusses how to specify the file format for new InnoDB tables, compatibility of different file formats between MySQL releases,
Named File Formats. InnoDB 1.1 has 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 original InnoDB file format is called 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 alphabetic order.
The configuration parameter
innodb_file_format
controls
whether such statements as CREATE
TABLE
and ALTER TABLE
can be used to create tables that depend on support for the
Barracuda file format.
Although Oracle recommends using the Barracuda format for new tables where practical, in MySQL 5.5 the default file format is still Antelope, for maximum compatibility with replication configurations containing different MySQL releases.
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.
InnoDB 1.1 incorporates several checks to guard against the possible crashes and data corruptions that might occur if you run an older release of the MySQL server on InnoDB data files using 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.
Considerations of backward compatibility only apply when using a recent version of InnoDB (the InnoDB Plugin, or MySQL 5.5 and higher with InnoDB 1.1) alongside an older one (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 a prior version of InnoDB without risk of crashes, hangs, wrong results or corruptions. InnoDB 1.1 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 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
statement, as described
in Section 4.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,
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.
Every InnoDB 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 for
the corresponding .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 this new
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
Chapter 2, Fast Index Creation in the InnoDB Storage Engine.)
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”.)
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
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 the InnoDB Storage Engine). 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 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
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 occurs. 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”.)
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
Section 4.2.2, “Compatibility Check When a Table Is Opened”.
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
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 can be opened; tables can be created which require Antelope or Barracuda file format |
OFF | Antelope or Barracuda | Cheetah | Barracuda | Database can be opened with a warning, since the database contains files in a “too new” format; tables can be created in Antelope or Barracuda file format; tables in Cheetah format cannot be accessed |
OFF | Cheetah | Barracuda | Barracuda | Database cannot be opened;
innodb_file_format
cannot be set to Cheetah |
ON | Antelope or Barracuda | Barracuda | Barracuda | Database can be opened; tables can be created in Antelope or Barracuda file format |
ON | Antelope or Barracuda | Cheetah | Barracuda | Database cannot be opened, since the database contains files in a “too new” format (Cheetah) |
ON | Cheetah | Barracuda | Barracuda | Database cannot be opened;
innodb_file_format
cannot be set to Cheetah |
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 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 Named File Formats, 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: 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.3, “Identifying the File Format in Use”.
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 “too new” 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 is possible to “downgrade” such tables to the Antelope format with the procedure described in Section 4.4, “Downgrading the File Format”.
After you enable a given
innodb_file_format
, this
change applies only to newly created tables rather than existing
ones. 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 is 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'
. (The Row_format
is a separate column; 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
is 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 InnoDB storage engine 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
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 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 TABLEt
ROW_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. The file format introduced with InnoDB Plugin 1.0 is the Barracuda format. Although no new features have been announced that would require additional new file formats, the InnoDB file format mechanism allows for future 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, Xenops, Yak and Zebra. These file formats correspond to the internal identifiers 0..25.