WL#8307: InnoDB: Make ROW_FORMAT=DYNAMIC the default

Status: Complete

Description
Requirements
Dependent Tasks
High Level Architecture
Low Level Design

We will introduce a new global variable innodb_default_row_format for specifying the default ROW_FORMAT. It will be DYNAMIC by default. Allowed values are REDUNDANT, COMPACT, and DYNAMIC. The value COMPRESSED is not allowed for this parameter, because restrictions exist around ROW_FORMAT=COMPRESSED.

The new default ROW_FORMAT will be innodb_default_row_format, replacing the old default COMPACT that was introduced in MySQL 5.0.3. Unlike COMPACT, the DYNAMIC allows large VARCHAR, TEXT and BLOB columns to be stored completely off page with only a 20-byte pointer in the clustered index record. The COMPACT format always stores either the full column in the clustered index record, or a 768-byte prefix followed by the 20-byte pointer.

As explained in WL#7703 it only made sense to block ‘too new’ InnoDB formats when old incompatible versions of MySQL were still supported. Now that MySQL 5.1 and older versions are no longer supported, there is no reason to restrict the use of ROW_FORMAT=DYNAMIC.

This change poses a conflict with tables in the system tablespace which before WL#6205 did not support ROW_FORMAT=DYNAMIC tables. WL#6205 allows CREATE TABLE…ROW_FORMAT=DYNAMIC TABLESPACE=innodb_system.

With this WL#8307, the innodb_file_format setting will be ignored when ROW_FORMAT=DYNAMIC is specified. It will still be consulted for ROW_FORMAT=COMPRESSED.

FR1: The settable global parameter innodb_default_row_format controls how the implicit ROW_FORMAT=DEFAULT will be interpreted. The default value of this new parameter is DYNAMIC. The allowed parameter values are REDUNDANT, COMPACT, and DYNAMIC. COMPRESSED will not be allowed, because ROW_FORMAT=COMPRESSED is not available in the system tablespace, nor for CREATE TEMPORARY TABLE.

WL#7703: InnoDB: Change defaults and deprecate settings in next 5.7 release

Introducing the global variable innodb_default_row_format and changing the default from ROW_FORMAT=COMPACT to ROW_FORMAT=DYNAMIC will affect the replication of DDL. (We will not change the binlog records so that the implied attributes would be emitted.)

Also changing default for ROW_FORMAT option affects the semantics of DDL statements. Currently ALTER TABLE t1 ADD COLUMN and similar are implemented in such a way that they preserve ROW_FORMAT=DEFAULT if it was specified at table creation. In other words, if the default row format changes such ALTER will implicitly change the row format of the table.

Using ROW_FORMAT=DYNAMIC instead of ROW_FORMAT=COMPACT will together with WL#7703 (innodb_large_prefix=ON) remove some limitations around column prefix indexes.

Note that ROW_FORMAT=DYNAMIC will typically be less restrictive than ROW_FORMAT=COMPACT.

For temporary tables created for SQL execution (WL#6737), ROW_FORMAT=DYNAMIC will always be implied, overriding the innodb_default_row_format setting. The maximum record size will not be enforced during such table creation, not even when innodb_strict_mode=ON (the default since WL#7703).

Adding the parameter innodb_default_row_format

rec_format_t: The numeric value of REC_FORMAT_DYNAMIC is moved before REC_FORMAT_COMPRESSED. These values are not persisted anywhere.
innodb_default_row_format_names: The allowed innodb_default_row_format values: REDUNDANT, COMPACT, DYNAMIC. Note: COMPRESSED is not allowed.
innodb_default_row_format_typelib: The enumeration of allowed innodb_default_row_format values.
static MYSQL_SYSVAR_ENUM(default_row_format, innodb_default_row_format, PLUGIN_VAR_RQCMDARG, "...", NULL, NULL, REC_FORMAT_DYNAMIC, &innodb_default_row_format_typelib): The new settable global variable innodb_default_row_format.

Other changes

dict_build_tablespace_for_table(): Keep the table flags unchanged for the system tablespace.
create_table_info_t::create_options_are_invalid(): Remove the extra checks for ROW_TYPE_DYNAMIC.
create_table_info_t::innobase_table_flags(): Change the default from REC_FORMAT_COMPACT to innodb_default_row_format. Replace invalid values of ROW_FORMAT with ROW_FORMAT=DYNAMIC.
row_create_index_for_mysql(): Allow CREATE TEMPORARY TABLE…ROW_FORMAT=DYNAMIC to violate the size limits when innodb_strict_mode=OFF.