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:

  1. 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.
  2. 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.
  3. 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!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • I’ve never come across a site that used any of the SQL_MODE compatibility aliases. Hardly anyone will notice these aliases becoming deprecated.

    I agree that supporting compatibility with an ancient version of MySQL is also not widely used, and is probably not worth maintaining in the product. It’s hard enough to explain to people the need for version-commented syntax. “It’s so you can dump data from MySQL 5.6 and restore it on MySQL 4.1 or even older versions, and your partitions and triggers won’t break the restoration” usually receives puzzled looks.

    The mysqldump options are a bit more useful, when people want to export data to transfer to another brand of RDBMS.

    One might say “why would MySQL make any effort to help people move to another database?” but I have said for years that if there’s a way to get data out in a format that is less proprietary, it can actually encourage users to try MySQL, when they would have been reluctant to do so if they thought their data would be locked into MySQL with no export solution provided.

    I don’t think it’s a problem that no_field_options does not skip standard SQL-99 field options like CHARACTER SET. The purpose of no_field_options and the others is to suppress MySQL-proprietary syntax that would make it harder to use the dump file as input to another brand of SQL database. But retaining standard syntax is fine.

    We know that there’s no support for true compatibility. Even many plain data types have different names in different implementations (in spite of the SQL standard). So it’s always going to require either another third-party solution (e.g. a Perl module such as SQL::Translator http://search.cpan.org/~ilmari/SQL-Translator-0.11021/lib/SQL/Translator.pm), or else some homebrew code.

    • To clarify a point you raise (mostly for other readers) removal of the aliases shouldn’t make migrations away from MySQL any harder, since the underlying behavior flags will still remain.

      I don’t believe all databases support setting character set on a per-column basis. One alternative for no_field_options, no_key_options, no_table_options that might be viable, is to merge these into a compact/minimal ddl option. This is why I’m looking specifically looking for feedback on how these options are used, and if it is always together 🙂

  • Justin Swanhart

    I have seen combination sql modes used now again again, but personally, I think a stored procedure called as init_sql (or whenever you want an old sql_mode) that sets a combination of flags based on an old name would be a workaround.

  • For downgrades to older MySQL versions the version-specific comments should be used instead of an SQL mode. (e.g. skip not supported option on import instead of during export).

    The sql modes and export options to get a format supported by another database vendor are not very usefull.
    – Not all data is included (autoinc/serial?, foreign keys? users? events? etc)
    – Tends to be a bit buggy (generated format requires editing in some cases)
    – There are better tools available
    – Hard to do incremental migration (binlogs etc)

    So I’d say: deprecate it or make it a complete tool