Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.

You must have the PROCESS privilege to
query this table.

Because tablespace flags are always zero for all Antelope file
formats (unlike table flags), there is no way to determine
from this flag integer if the tablespace row format is
Redundant or Compact. As a result, the possible values for the
ROW_FORMAT field are “Compact or
Redundant”, “Compressed”, or
“Dynamic.”

Until MySQL 5.6, table and tablespace flags were the same except
for the bit position 0 settings. In MySQL 5.6, support was added
for 4K and 8K pages, which required an additional 4 bits to hold
the logical page size. Also in MySQL 5.6, support was added for
the CREATE TABLE and
ALTER TABLEDATA
DIRECTORY clause, which allows file-per-table
tablespaces to be stored in a location outside of the MySQL data
directory. This feature required an additional bit for both table
and tablespace flags, but not at the same position.

You can interpret the tablespace FLAG column
value by adding together the applicable decimal numeric values
that are provided in the following table.

This bit is set if the row format of tables in the tablespace is
DYNAMIC or
COMPRESSED. This information can help
you distinguish between Antelope and Barracuda file
formats but not between REDUNDANT and
COMPACT file formats
(DYNAMIC and
COMPRESSED row formats require the
Barracuda file format). If it is a file-per-table
tablespace, you must query
INNODB_SYS_TABLES to determine which of
the two Antelope row formats is used
(REDUNDANT or
COMPACT).

0 - REDUNDANT or
COMPACT
(FILE_FORMAT=Antelope)

1 - DYNAMIC or
COMPRESSED
(FILE_FORMAT=Barracuda)

1-4

These four bits contain a small number that represents the compressed
page size (the KEY_BLOCK_SIZE or
“physical block size”) of the tablespace.

0 - Not Compressed

2 - 1024 Byte Compressed Page Size

4 - 2048 Byte Compressed Page Size

6 - 4096 Byte Compressed Page Size

8 - 8192 Byte Compressed Page Size

10 - 16384 Byte Compressed Page Size

5

This bit is set for file-per-table tablespaces if the row format of the
table is DYNAMIC or
COMPRESSED.

0 - REDUNDANT or
COMPACT

32 - DYNAMIC or
COMPRESSED

6-9

These four bits contain a small number that represents the uncompressed
page size (logical page size) of the tablespace. The
setting is zero if the logical page size is the original
InnoDB default page size of 16K.

192 - 4096 Byte Logical/Uncompressed Page Size

256 - 8192 Byte Logical/Uncompressed Page Size

0 - 16384 Byte Logical/Uncompressed Page size

10

This bit is set if the DATA DIRECTORY option is used
with CREATE TABLE or
ALTER TABLE. This bit is
set for file-per-table tablespaces that are located in
directories other than the default data directory
(datadir). For these
tables, a
tablename.isl
file is present in the same location as the
tablename.frm
file. The
tablename.isl
file stores the actual directory path to the
tablename.ibd
file-per-table tablespace file.

0 - Not a remote file-per-table tablespace

1024 - A remote file-per-table tablespace

In the following example, table t1 is created
with innodb_file_per_table=ON,
which creates table t1 in its own tablespace.
When querying INNODB_SYS_TABLESPACES,
we see that the tablespace has a FLAG value of
33. To determine how this value is arrived at, review the bit
values described in the preceding table. Bit 0 has a value of 1
because table t1 uses the
DYNAMIC row format. Bit 5 has a value of 32
because the tablespace is a file-per-table tablespace that uses a
DYNAMIC row format. Bit position 6-9 is 0
because innodb_page_size is set
to the default 16K value. The other bit values are not applicable
and are therefore set to 0. The values for bit position 0 and bit
position 5 add up to a FLAG value of 33.