WL#7703: InnoDB: Change defaults and deprecate settings in next 5.7 release
This work changes the defaults of:
innodb_strict_mode to "ON" (was: OFF). This to ensure that InnoDB returns errors rather than warnings for certain conditions.
innodb_file_format to "Barracuda" (was: Antelope). Barracuda is needed for DYNAMIC and COMPRESSED formats. Antelope formats (REDUNDANT and COMPACT) are still supported.
innodb_large_prefix to "ON" (was: OFF). This allows index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
The default values of the following variables will NOT be changed:
innodb_file_format_check=ON (was: ON) innodb_file_format_max=Antelope (was: Antelope)
The value of the variable innodb_file_format_max is related to innodb_file_format and DDL operations. Even if innodb_file_format=Barracuda, innodb_file_format_max will report Antelope, unless ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED is used in CREATE TABLE or ALTER TABLE statements.
We will also deprecate the following 4 parameters:
innodb_large_prefix innodb_file_format innodb_file_format_check innodb_file_format_max
If non-default startup values are used for these 4 deprecated parameters, InnoDB will issue a warning to the server error log.
We return the same deprecation and removal warning to the client when any of the 4 deprecated variables are set by the user dynamically in the running server using a SET statement.
The variable innodb_strict_mode will not be deprecated, and no warning will be issued when it is used with the SET statement.
Of the settings above, only the following have a noticeable effect on DDL behaviour:
innodb_strict_mode=ON will refuse certain CREATE TABLE and ALTER TABLE statements
Setting both innodb_file_format=Barracuda and innodb_large_prefix=ON will allow longer column prefix indexes to be created when using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. If either of these is not set, we will silently truncate column prefixes to 768 bytes. If both are set, the maximum prefix length is about 3000 bytes, as long as the record size does not exceed half of innodb_page_size minus some framing.
The deprecation, or logging of warnings in the error log would happen in 5.7 and the removal will happen in 5.8.
Why deprecate?
The innodb_file_format family of parameters only made sense for the InnoDB Plugin in MySQL 5.1 when it was distributed separately from MySQL. Its purpose was to allow users to downgrade to the built-in InnoDB in MySQL 5.1, to avoid using incompatible format when testing the 5.1 plugin. Since MySQL 5.1, the InnoDB formats have changed without us bumping the innodb_file_format. Some examples:
5.5: New change buffer format for delete-buffering 5.6: Multiple rollback segments and undo logs; (SYS_DATAFILES and SYS_TABLESPACES tables) 5.7: R-tree indexes, changes to temporary tables, tablespace flags for CREATE TABLESPACE, new column added to SYS_INDEXES
The parameter innodb_large_prefix was introduced in MySQL 5.5, so that users could avoid accidentally creating tables that would be incompatible with the InnoDB Plugin in MySQL 5.1. Now that MySQL 5.1 is not supported any more, it makes no sense to use any other value than innodb_large_prefix=ON.
Basically, the only purpose of innodb_file_format and innodb_large_prefix is to cripple the capabilities of InnoDB. This is the reason to change the default values to ‘uncrippled’ and to deprecate and removethe parameters for future removal, so that InnoDB cannot be accidentally crippled.
Because DDL operation is affected by changing the defaults, cross-version replication and the replay of mysqldump output will be affected.
The file-format related defaults are less strict than the old ones, and the innodb_strict_mode=ON change is more strict than the old one.
On replication slaves and for replaying mysqldump output, to avoid errors, the server settings can be less strict, but they should not be stricter than what the data source assumes. More relaxed is OK. So, setting
innodb_strict_mode=OFF
should avoid any trouble when replicating from older versions to WL#7703 version, or when replaying mysqldump.
When replicating from WL#7703 to older slaves, it would be advisable to use the following settings on the slave, corresponding to the new defaults:
innodb_file_format=Barracuda innodb_large_prefix=ON
innodb_strict_mode=ON means that CREATE TABLE and ALTER TABLE will return errors instead of warnings for certain operations.
innodb_file_format=Barracuda means that KEY_BLOCK_SIZE and ROW_FORMAT=COMPRESSED are not ignored (or rejected if innodb_strict_mode=ON) except when creating a table in a shared tablespace or the system tablespace. It also means that ROW_FORMAT=DYNAMIC will not be ignored (unless creating a table in the system tablespace). When these settings are ignored, they are mapped to ROW_FORMAT=COMPACT.
innodb_large_prefix=ON means that with ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED, longer index column prefixes will be allowed. When this option is not set, MySQL will silently truncate specified index column prefixes to 768 bytes.
Deprecation warnings will be issued to the server error log on startup, and to the client connection when handling SET statements.
The bulk of the changes is due to removing assignments to innodb_file_format=Barracuda in many tests. All code changes are to ha_innodb.cc. Here is a summary: innobase_init(): Issue a deprecation warning to the server error log if innodb_file_format or innodb_file_format_max is specified in the configuration, or if innodb_file_format_check=OFF or innodb_large_prefix=OFF. (The two Boolean parameters will default to ON.) innodb_file_format_name_update(): Issue a warning to the client. This is invoked by SET GLOBAL innodb_file_format. innodb_file_format_max_update(): Issue a warning to the client. This is invoked by SET GLOBAL innodb_file_format_max. [New] innodb_large_prefix_update(): Issue a warning to the client. This is invoked by SET GLOBAL innodb_large_prefix.