UPDATED: explaining the role of innodb_strict_mode and correcting introduction of innodb_file_format

Compressed tables is an example of an InnoDB feature that became available with the Barracuda file format, introduced in the InnoDB plugin. They can bring significant gains in raw performance and scalability: given the data is stored in a compressed format the amount of memory and disk space necessary to hold it and move it around (disk/memory) is lower, thus making them attractive for servers equipped with SSD drives of smaller capacity.

The notion of “file formats” (defined by the variable innodb_file_format) was first introduced when InnoDB was still a plugin. The evolution of InnoDB has lead to the development of new features and some of them required the support of new on-disk data structures. That means those particular features (like compressed tables) will only work with the newer file format. To make things clear and help manage compatibility issues when upgrading and (specially) downgrading MySQL the original file format started being referred to as Antelope.

The default file format in MySQL 5.6 and the latest 5.5 releases is Antelope. Note this can be a bit confusing as the first releases of 5.5 (until 5.5.7) introduced the new file format as being the default one, a decision that was later reversed to assure maximum compatibility in replication configurations comprised of servers running different versions of MySQL. To be sure about which file format is the one set as default in your server you can issue:

mysql> SHOW VARIABLES LIKE 'innodb_file_format';

The important lesson here that motivated me to write this post is that the file format can only be defined for tablespaces – not tables, in general. This is documented in the manual but maybe not entirely clear:

innodb_file_format: The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

Even if you configure your server with innodb_file_format=Barracuda and recreate the datadir and basic tables with the script mysql_install_db, the common tablespace will always use Antelope. So, to create tables under the new file format it is imperative you use innodb_file_per_table. Although this requirements is documented what might be misleading here is the fact there’s no error being issued if you set the file format to Barracuda and create a new compressed table without having innodb_one_file_per_table set – only a couple of warnings, if you pay close attention. Here’s an example:

mysql> SET GLOBAL innodb_file_format=Barracuda;

Query OK, 0 rows affected (0.00 sec)

mysql> create table test.testA (id int) row_format=Compressed;

Query OK, 0 rows affected, 2 warnings (0.96 sec)



If you do choose to check the warnings, you’ll find:



mysql> show WARNINGS;

+---------+------+---------------------------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------------------------+

| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |

+---------+------+---------------------------------------------------------------+

2 rows in set (0.00 sec)



This happens when innodb_strict_mode is turned OFF, as it usually is. If it was turned ON the table creation would fail with the following error:

mysql> create table test.testA (id int) row_format=Compressed;

ERROR 1031 (HY000): Table storage engine for 'testA' doesn't have this option

Now, let’s take a look at what the INFORMATION_SCHEMA tell us about this table:

mysql> SELECT * FROM information_schema.tables WHERE table_schema='test' and table_name='testA'G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: testA

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Compact

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 0

AUTO_INCREMENT: NULL

CREATE_TIME: 2014-01-07 14:21:05

UPDATE_TIME: NULL

CHECK_TIME: NULL

TABLE_COLLATION: latin1_swedish_ci

CHECKSUM: NULL

CREATE_OPTIONS: row_format=COMPRESSED

TABLE_COMMENT:

1 row in set (0.00 sec)



There’s two at-first-look “contradictory” fields here:

“ROW_FORMAT” says the table is using the Compact format while

“CREATE_OPTIONS” indicates “row_format=COMPRESSED” has been used when creating the table

The one to consider is ROW_FORMAT: CREATE_OPTION is used to store the options that were used at the moment the table was created and is evoked by the SHOW CREATE TABLE statement to “reconstruct” it:

mysql> show create table test.testA;

*************************** 1. row ***************************

Table: testA

Create Table: CREATE TABLE testA (

id int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

1 row in set (0.00 sec)

Conclusion

A customer contacted us asking how he could get a list of the tables using the compression format, which we can obtain by interrogating INFORMATION_SCHEMA:

mysql> SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ROW_FORMAT=Compressed’;

To their surprise this statement returned an empty set. We verified that the tables created by them specified ROW_FORMAT=Compressed but as shown in this article this method is not to be trusted – ask the INFORMATION_SCHEMA instead.