5.4.7.3. Identifying the File Format in Use

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'.

Internal Details

InnoDB has two different file formats (Antelope and Barracuda) and four different row formats (Redundant, Compact, Dynamic, and Compressed). The Antelope file format contains Redundant and Compact row formats. A tablespace that uses the Barracuda file format uses either the Dynamic or Compressed row format.

File and row format information is written in 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 tablename.ibd. 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. The system tablespace will always have zero in the tablespace flags.

The first 10 bits of the tablespace flags can be described this way:

Bit 0: Zero for Antelope, and bits 1 to 5 will also be zero. One for Barracuda, and bits 1 to 5 may be set.
Bits 1 to 4: A 4 bit number representing the compressed page size. 0 = not compressed, 1 = 1k, 2 = 2k, 3 = 4k, 4 = 8k.
Bit 5: Same value as Bit 0, zero for Antelope, one for Barracuda. If bits 0 and 5 are set and bits 1 to 4 are not, the row format is Dynamic.
Bits 6 to 9: A 4-bit number indicating the physical page size of the tablespace. 0=16k (original default), 3=4k, 4=8k, 5=16k. These are the only valid values for My SQL 5.6 and later.
Bit 10: Tablespace location. 0 = default, 1 = used DATA DIRECTORY in CREATE TABLE to choose the tablespace location.

Note

Tablespace flags are similar to table flags found in the InnoDB dictionary table, "SYS_TABLES". They differ in the meaning of bit 0 and bits 6 to 10. Table flags will set bit 0 to one if the row format of a particular table is "Compact". Tablespace flags cannot do that since the system tablespace can contain both Redundant and Compact row formats. So, for tablespace flags, bit 0 and bit 5 are always the same value.

Table flags can be viewed by issuing the command:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

The first 7 bits of the table flags can be described this way:

Bit 0: Zero for Redundant row format, and bits 1 to 5 will be zero. One for Compact row format, and bits 1 to 5 may be set.
Bits 1 to 4: A 4 bit number representing the compressed page size. 0 = not compressed, 1 = 1k, 2 = 2k, 3 = 4k, 4 = 8k.
Bit 5: Zero for Antelope file format, and one for Barracuda file format. If bit 5 is set and bits 1 to 4 are not, the row format is Dynamic. Also, if bit 5 is set, bit 0 must also be set.
Bit 6: Tablespace location. 0 = default, 1 = DATA DIRECTORY was used in CREATE TABLE to choose a tablespace location.

If bits 7 to 31 are not zero, the table is corrupt or the SYS_TABLES record is corrupt, and the table cannot be used.