With the shipment of the first release candidate (RC) of MySQL 5.7, the next major version of the server is rapidly shaping up. Over the course of the nearly two and a half years that have passed since 5.6 went GA, we have put a lot of work into streamlining the server code in order to ease the burden of developing and maintaining such a large product and codebase.

An important aspect of this work is deprecation and removal. To explain the terms, deprecating a feature means that we signal to the outside world that “this feature is available now, but it will be removed in a future release, so please adjust your use case accordingly”. Removing a feature means just that – in one version the feature is available, but then it is gone, and if you try to use it, you will get an error message saying that the feature is unknown.

Generally we don’t remove features in existing GA releases, but we reserve the right to deprecate features that we will later remove in a development release (which is often the upcoming GA).

Earlier, the approach here was quite conservative: broadly speaking, the rule was to put in deprecation warnings in version N, and only remove in version N+2. However, in 5.7, we have seen a more proactive attitude towards both deprecation and removal, in that we in some cases have created deprecation warnings for features in 5.6 point releases and then removed the features in 5.7.

This blog post gives a summary of all the deprecations and removals that have taken place in MySQL 5.7. Most of these have already been covered in the various milestone blog posts that Geir Høydalsvik has published.

Features that have been removed

InnoDB monitoring features

The magic of the built-in InnoDB table names: innodb_monitor, innodb_lock_monitor, innodb_tablespace_monitor, innodb_table_monitor, and innodb_mem_validate have been removed. Historically, CREATE TABLE on one of these names (inside any database schema) has set flags inside InnoDB to enable additional debug output to the error log. DROP TABLE has cleared the flags and stopped the output. This mechanism was awkward, and it was difficult to maintain with current and future refactoring plans in InnoDB, so it was removed. Going forward,

1. Instead of innodb_monitor, one can use SET GLOBAL innodb_status_output=ON|OFF
2. Instead of innodb_lock_monitor, use
   • SET GLOBAL innodb_status_output=ON|OFF
   • SET GLOBAL innodb_status_output_locks=ON|OFF
3. There is no direct replacement for the two tables innodb_tablespace_monitor and innodb_table_monitor, but information is available in INFORMATION_SCHEMA.SYS_TABLES and INFORMATION_SCHEMA.SYS_TABLESPACES. We also plan to add more information to INFORMATION_SCHEMA.FILES.
4. innodb_mem_validate was completely removed, as the functionality was only present when the UNIV_MEM_DEBUG flag was set at compile time, something which is not even done for normal debug builds.

(Remove innodb_*monitor tables in 5.7, WL#7377, see also Morgan Tocker’s blog post)

Old Passwords

In MySQL 5.6 we deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords was removed in 5.7:

• The server-side mysql_old_password authentication plugin is removed. Accounts that use this plugin are disabled at startup and the server writes an “unknown plugin” message to the error log.
• The client-side mysql_old_password authentication plugin is removed from the C client library.
• The --secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated and will be removed in a future MySQL release.
• The --skip-secure-auth option to the server and client programs is no longer supported and using it produces an error.
• The secure_auth system variable permits only a value of 1; a value of 0 is no longer permitted.
• For the old_passwords system variable, a value of 1 (produce pre-4.1 hashes) is no longer permitted.
• The OLD_PASSWORD() function is removed.

(Remove the deprecated old_password plugin, WL#8006, see also Morgan Tocker’s blog post)

SQL syntax

In 5.7 we have removed IGNORE for ALTER TABLE. We made this decision after Morgan’s blog post seeking community advice on this topic. We decided to remove IGNORE because it caused problems with foreign keys (rows removed in parent table), prohibits true online CREATE UNIQUE INDEX, and caused problems for replication. (Remove IGNORE for ALTER TABLE in 5.7, WL#7395)

The SQL modes, ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES are folded into STRICT MODE in 5.7. Unfortunately, as reported by Simon Mudd in bug#75439, this change breaks replication, and we are currently reconsidering this decision. (Remove ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES and make their functionality part of STRICT MODE in 5.7, WL#7467, see also Morgan Tocker’s blog post)

We removed the support for INSERT DELAYED in 5.7 and onwards. INSERT DELAYED has a lot of limitations and issues attached to it, and with InnoDB as default engine it becomes even less relevant. We decided to keep the INSERT/REPLACE DELAYED syntax, but convert it to INSERT/REPLACE with a warning before execution. (Remove INSERT DELAYED, WL#6073)

In 5.7, we removed all remnants of YEAR(2) related code. Issues with the YEAR(2) data type include ambiguity of displayed values, and possible loss of information when values are dumped and reloaded or converted to strings. Overall we removed the support for YEAR(2) in 5.6, but we kept some limited support for old YEAR(2) columns to allow for upgrade procedures to MySQL 5.6. Even this limited support is now gone in 5.7. (Remove limited YEAR(2) support, WL#6263)

Options and Variables

We have deprecated unique option prefixes in 5.5 and 5.6, and from 5.7 we only accept full option names. The old behavior has been found to be confusing and non-future proof. For example, the --compress option could be given to mysqldump as --compr, but not as --comp because the latter was ambiguous. However, adding extra options through plugins could cause prefixes in scripts to be unreliable even across server restarts. (Deprecate Unique Option Prefixes, WL#6978)

The timed_mutexes system variable was deprecated in 5.5/5.6 and removed in 5.7. This system variable had already been disconnected from any underlying InnoDB code, so it no longer served any purpose. (Deprecate and remove the timed_mutexes system variable, WL#7436)

The server options which disable InnoDB have been removed. The system tables are being moved from MyISAM to InnoDB, so in 5.7+ it will not be possible to run the MySQL server without InnoDB (which is the default storage engine since 5.5). (Deprecate skip-innodb in 5.6 and remove it in 5.7, WL#7976)

The variables innodb_use_sys_malloc and innodb_additional_mem_pool_size system variable were removed in 5.7 because it simplified InnoDB scalability efforts – the InnoDB home grown memory manager was not as good as jemalloc or tcmalloc, since the latter two are more scalable. (Remove innodb_use_sys_malloc & innodb_additional_mem_ pool_size system variable in 5.7, WL#7628)

The innodb_mirrored_log_groups system variable was deprecated in 5.6 and removed in 5.7. The allowed range of the parameter innodb_mirrored_log_groups was 1..10, but there has always been (at least since 3.23.53) a check within InnoDB startup that refuses any value except 1. This is not meaningful and thus we decided to remove it. (Remove innodb_mirrored_log_groups, WL#6808)

The innodb_file_io_threads system variable, which was deprecated in 5.5, was removed in 5.7 (Remove the innodb_file_io_threads variable in 5.7, WL#7149)

The storage_engine system variable was deprecated in 5.5 and finally removed in 5.7. You should instead use the default_storage_engine system variable. (Remove the storage_engine system variable in 5.7, WL#7148)

The --basedir and --datadir options to mysql_upgrade were no longer in use, and they have been removed (WL#7010).

Outdated Command Line Programs

This work was triggered by a blog post from my colleague Todd Farmer, with the title Spring cleaning useless clients, where he asked about the relevance of a number of clients that we shipped with the MySQL Server.

As a consequence, and after some more feedback from a blog post by Morgan Tocker, the following clients were deprecated in 5.6 and have been removed in 5.7: mysqlaccess (WL#7620), mysql_convert_table_format (WL#7034), mysql_fix_extensions (WL#7036), mysql_find_rows.sh (WL#7621), mysql_setpermission (WL#7033), msql2mysql (WL#7035), mysqlbug (WL#7689), mysql_zap and mysql_waitpid (WL#7826), mysqlhotcopy (WL#7854)

Various Removals

The old custom Windows symlink implementation has been removed in MySQL 5.7. Starting from Windows Vista/Server 2008, a native symlink mechanism is supported by Windows (see the mklink command). MySQL will now use the native Windows mechanism going forward. (Remove support for .sym files, WL#6561)

Features that have been deprecated (but not removed)

In 5.7, we have replaced the EOF packet with the new OK packet in the MySQL client-server protocol. The EOF and OK packets serve the same purpose — to mark the end of a query execution result. The protocol is kept backward compatible as clients and servers have one additional capability flag to denote their support for the new OK packet. When this capability flag is in place, any SQL statement which produces a result set will send an OK packet as a marker to indicate the end of data rather than an EOF packet. (Deprecate the EOF packet, WL#7766, see also Morgan Tocker’s blog post)

In 5.6 we deprecated SHOW PROFILE in favor of the Performance Schema instrumentation. In 5.7, we have completed this work by deprecating the INFORMATION_SCHEMA.PROFILING table. See also Bug#67525, reported by Peter Laursen. (Deprecate the INFORMATION_SCHEMA.PROFILING Table, WL#6802)

As part of the increased GIS effort, we’ve started cleaning up the name space for GIS functions. This is done by deprecating, removing, and adding function names (aliases) in order to make the naming scheme consistent and to bring MySQL in line with the OGC standard and other major DBMSs.

With the exception of the constructor functions that have the same names as the data types (Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon and GeometryCollection) and functions that do calculations on minimum bounding rectangles (MBRs), all spatial functions now have names prefixed by the standard “ST_” (spatio-temporal) convention, and function names without that prefix have been deprecated. This also means that it is a lot easier to take your queries from other SQL/MM compliant database systems and run them on your MySQL databases. (Consistent naming scheme for GIS functions, WL#8055, see also the blog post from Erik Frøseth, Spring cleaning in the GIS namespace)

The ENCRYPT(), DES_ENCRYPT() and DES_DECRYPT() functions based on the Data Encryption Standard (DES) have been deprecated in favor of Advanced Encryption Standard (AES) based functions, because AES provides much better security. (Deprecate the ENCRYPT, DES_ENCRYPT and DES_DECRYPT functions, WL#8126)

We now trigger a warning (ER_WARN_DEPRECATED_SYNTAX) upon the execution of the ENCODE() or DECODE() functions. (Deprecate ENCODE()/DECODE() in 5.7, WL#6984)

We have deprecated the SET PASSWORD syntax and the PASSWORD() function. The existing ALTER USER statement is modified to cover the deprecated functionality. The PASSWORD() function was originally introduced as a way to manually update the mysql.user table. This is generally a bad idea and we want to leave the task of managing user authentication properties exclusively to using the ALTER USER statement which automatically determines which authentication plugin should be used and then adjusts the password algorithm accordingly. (Deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes, WL#6409)

We have deprecated the following 4 InnoDB parameters: innodb_large_prefix, innodb_file_format, innodb_file_format_check and innodb_file_format_max.

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.

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, but the innodb_file_format has not been bumped. (InnoDB: Change defaults and deprecate settings in next 5.7 release, WL#7703)

In 5.7, the EXTENDED and PARTITIONS keyword of the EXPLAIN syntax have been deprecated. Currently, EXPLAIN has EXTENDED and PARTITIONS flags that show additional info compared to regular EXPLAIN output. However, EXPLAIN JSON already behaves like those flags and prints all available information. After deprecation regular EXPLAIN will behave exactly as if those flags are given – all additional columns and warnings are printed automatically. (Deprecate EXTENDED and PARTITIONS flags of EXPLAIN, WL#7027, see also Morgan Tocker’s blog post)

We have deprecated the collation_database and character_set_database system variables in 5.7. (Deprecate GLOBAL @@collation_database, @@character_set_database; assignment of SESSION counterparts, WL#3811, see also Bug#35357, Bug#27208, and Bug#27687 as well as Morgan Tocker’s blog post)

The conversion of pre MySQL 5.1 encoded database/schema names has been deprecated in 5.7. (Deprecate the conversion of pre MySQL 5.1 encoded database names, WL#8186).

The sync_frm system variable in 5.7 has been deprecated in 5.7 (Deprecate and remove the sync_frm sysvar, WL#8216)

The @@session.gtid_executed system variable has been deprecated in 5.7. This variable contains the set of GTIDs currently stored in the transaction cache. However, its value can be completely determined from the value of the system variable gtid_next, so it is of limited practical value. Also, it is not very well named, and it was decided to deprecate it. (Deprecate @@session.gtid_executed, WL#7518)