Proposal to deprecate "compatibility" SQL Modes

In the MySQL team, we are currently discussing deprecating several of the SQL mode options which are used by mysqldump to change the output format. From the mysqldump command:

$ mysqldump --help
..
--compatible=name
 Change the dump to be compatible with a given mode. By
 default tables are dumped in a format optimized for
 MySQL. Legal modes are: ansi, mysql323, mysql40,
 postgresql, oracle, mssql, db2, maxdb, no_key_options,
 no_table_options, no_field_options. One can use several
 modes separated by commas. Note: Requires MySQL server
 version 4.1.0 or higher. This option is ignored with
 earlier server versions.

To explain the rationale for this proposal:

The options mysql323, mysql40 are designed to allow mysqldump to create an output format that can be restored on a MySQL Server of version 3.23 or 4.0. While we aim to support the upgrade case from these versions, supporting a downgrade is not something we support, as restoring data to a 10 year old release poses a number of challenges.
The options postgresql, oracle, mssql, db2, maxdb are ‘special’ SQL modes, in that they are not really modes themselves but aliases to switch on other SQL modes. For example:
```
mysql> set sql_mode = 'mssql';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
1 row in set (0.00 sec)
```
Having aliases creates a number of interesting challenges:
- It complicates training and support as users may have multiple options in order to achieve the same outcome (either listing all sql modes individually or via one of the aliases). In a similar example we removed unique option prefixes in MySQL 5.7 to reduce this confusion.
- The options that we enable for each alias imply (but have not measurably offered) compatibility with the other database that is mentioned. Furthermore, as other databases will release newer versions, the singularity of the alias name does not account for this.
- Related to the above; if newer versions of other database products desire new sql modes enabled, it is more flexible (and future proof) to have the list of which behaviour options that should be enabled for each other database in documentation or client programs rather than the server itself. This allows us to not change behavior in a server GA release.

The options no_key_options, no_field_options and no_table_options remove MySQL-specific meta data which I have highlighted below in bold:

# no_key_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# no_field_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# no_table_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note that no_field_options did not remove the character set attribute for the column b and thus is incomplete in its current implementation. no_field_options also disables the same meta-data which is disabled by no_key_options.

The no_key_options and no_table_options remain useful, although it should be noted that information_schema may better support a custom view of schema to match the capabilities of the destined database. We are working on making information_schema queries much faster in the future, via our native data dictionary project.

To summarize this proposal:

Our plan is to deprecate the following options in MySQL 5.7, for removal in a later version:
mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb.
We are also seeking input from those that use the following SQL modes, as we may decide to deprecate these in the future:
no_field_options, no_key_options, no_table_options.

Will these changes impact you? Please leave a comment or get in touch!