Difference in InnoDB's default Row Format between MySQL 5.6 and 5.7

tl;dr The default row format for InnoDB tables in MySQL 5.6 is COMPACT and in MySQL 5.7 is DYNAMIC.

TIL there is a difference in the default InnoDB Row Format value between MySQL 5.6 and 5.7. I'll explain why, and provide you some more background information and good-to-know things.

What is row format?

The row format of a table determines how its rows are physically stored. This can affect the performane of queries and DML operations. As more rows fit into a single disk page:

  • queries and index lookups can work faster;
  • less cache memory is required in the buffer pool;
  • less I/O is required to write out updated values.

Supported row formats

The InnoDB storage engine supports four row formats: REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED.

Here's a quick overview:

Row Format REDUNDANT COMPACT DYNAMIC COMPRESSED
Compact Storage Characteristics No Yes Yes Yes
Enhanced Variable-Length Column Storage No No Yes Yes
Large Index Key Prefix Support No No Yes Yes
Compression Support No No No Yes
Supported Tablespace Types system, file-per table, general* system, file-per-table, general* file-per-table, general* file-per-table, general*
Required File Format Antelope or Barracuda Antelope or Barracuda Barracuda Barracuda

*only available in MySQL 5.7

Default value

The default row format for InnoDB table sis defined by innodb_default_row_format variable. The default value is different in MySQL 5.6 and 5.7. If you are now aware of this difference, and you use the default value in MySQL 5.6, you can encouter problems like the following:

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

The different default value between MySQL 5.6 and 5.7

The default row format for InnoDB tables in MySQL 5.6 is COMPACT and in MySQL 5.7 is DYNAMIC.

Determining the Row Format of a Table

You can determine the row format of a table, by using SHOW TABLE STATUS:

mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2016-09-14 16:29:38
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

Alternatively, you can query the INFORMATION_SCHEME.INNODB_TABLES table:

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME     | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic    |
+----------+------------+

Define the row format of a table explicitly

You can explicitly define the row format of a table, by using the ROW_FORMAT table option in a CREATE TABLE or ALTER TABLE statement. For example:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;
SHARE THIS ARTICLE