Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / General Information  /  What Is New in MySQL 5.7

1.3 What Is New in MySQL 5.7

This section summarizes what has been added to, deprecated in, and removed from MySQL 5.7. A companion section lists MySQL server options and variables that have been added, deprecated, or removed in MySQL 5.7; seeSection 1.4, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7”.

Features Added in MySQL 5.7

The following features have been added to MySQL 5.7:

  • Security improvements.  These security enhancements were added:

    • In MySQL 8.0,caching_sha2_password is the default authentication plugin. To enable MySQL 5.7 clients to connect to 8.0 servers using accounts that authenticate usingcaching_sha2_password, the MySQL 5.7 client library and client programs support thecaching_sha2_password client-side authentication plugin as of MySQL 5.7.23. This improves compatibility of MySQL 5.7 with MySQL 8.0 and higher servers. SeeSection 6.4.1.4, “Caching SHA-2 Pluggable Authentication”.

    • The server now requires account rows in themysql.user system table to have a nonemptyplugin column value and disables accounts with an empty value. For server upgrade instructions, seeSection 2.10.3, “Changes in MySQL 5.7”. DBAs are advised to also convert accounts that use themysql_old_password authentication plugin to usemysql_native_password instead, because support formysql_old_password has been removed. For account upgrade instructions, seeSection 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    • MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password. For more information, seeSection 6.2.11, “Password Management”.

    • Administrators can lock and unlock accounts for better control over who can log in. For more information, seeSection 6.2.15, “Account Locking”.

    • To make it easier to support secure connections, MySQL servers compiled using OpenSSL can automatically generate missing SSL and RSA certificate and key files at startup. SeeSection 6.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.

      All servers, if not configured for SSL explicitly, attempt to enable SSL automatically at startup if they find the requisite SSL files in the data directory. SeeSection 6.3.1, “Configuring MySQL to Use Encrypted Connections”.

      In addition, MySQL distributions include amysql_ssl_rsa_setup utility that can be invoked manually to create SSL and RSA key and certificate files. For more information, seeSection 4.4.5, “mysql_ssl_rsa_setup — Create SSL/RSA Files”.

    • MySQL deployments installed usingmysqld --initialize are secure by default. The following changes have been implemented as the default deployment characteristics:

      • The installation process creates only a singleroot account,'root'@'localhost', automatically generates a random password for this account, and marks the password expired. The MySQL administrator must connect asroot using the random password and assign a new password. (The server writes the random password to the error log.)

      • Installation creates no anonymous-user accounts.

      • Installation creates notest database.

      For more information, seeSection 2.9.1, “Initializing the Data Directory”.

    • MySQL Enterprise Edition now provides data masking and de-identification capabilities. Data masking hides sensitive information by replacing real values with substitutes. MySQL Enterprise Data Masking and De-Identification functions enable masking existing data using several methods such as obfuscation (removing identifying characteristics), generation of formatted random data, and data replacement or substitution. For more information, seeSection 6.5, “MySQL Enterprise Data Masking and De-Identification”.

    • MySQL now sets the access control granted to clients on the named pipe to the minimum necessary for successful communication on Windows. Newer MySQL client software can open named pipe connections without any additional configuration. If older client software cannot be upgraded immediately, the newnamed_pipe_full_access_group system variable can be used to give a Windows group the necessary permissions to open a named pipe connection. Membership in the full-access group should be restricted and temporary.

  • SQL mode changes.  Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.

    Implementation for theONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

    TheERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE SQL modes are now deprecated but enabled by default. The long term plan is to have them included in strict SQL mode and to remove them as explicit modes in a future MySQL release. SeeSQL Mode Changes in MySQL 5.7.

    The changes to the default SQL mode result in a defaultsql_mode system variable value with these modes enabled:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, andNO_ENGINE_SUBSTITUTION.

  • Online ALTER TABLE. ALTER TABLE now supports aRENAME INDEX clause that renames an index. The change is made in place without a table-copy operation. It works for all storage engines. SeeSection 13.1.8, “ALTER TABLE Statement”.

  • ngram and MeCab full-text parser plugins.  MySQL provides a built-in full-text ngram parser plugin that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese.

    For more information, seeSection 12.9.8, “ngram Full-Text Parser”, andSection 12.9.9, “MeCab Full-Text Parser Plugin”.

  • InnoDB enhancements.  TheseInnoDB enhancements were added:

    • VARCHAR column size can be increased using an in-placeALTER TABLE, as in this example:

      ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

      This is true as long as the number of length bytes required by aVARCHAR column remains the same. ForVARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. ForVARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-placeALTER TABLE only supports increasingVARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-placeALTER TABLE does not support increasing the size of aVARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY).

      DecreasingVARCHAR size using in-placeALTER TABLE is not supported. DecreasingVARCHAR size requires a table copy (ALGORITHM=COPY).

      For more information, seeSection 14.13.1, “Online DDL Operations”.

    • DDL performance forInnoDB temporary tables is improved through optimization ofCREATE TABLE,DROP TABLE,TRUNCATE TABLE, andALTER TABLE statements.

    • InnoDB temporary table metadata is no longer stored toInnoDB system tables. Instead, a new table,INNODB_TEMP_TABLE_INFO, provides users with a snapshot of active temporary tables. The table contains metadata and reports on all user and system-created temporary tables that are active within a givenInnoDB instance. The table is created when the firstSELECT statement is run against it.

    • InnoDB now supports MySQL-supported spatial data types. Prior to this release,InnoDB would store spatial data as binaryBLOB data.BLOB remains the underlying data type but spatial data types are now mapped to a newInnoDB internal data type,DATA_GEOMETRY.

    • There is now a separate tablespace for all non-compressedInnoDB temporary tables. The new tablespace is always recreated on server startup and is located inDATADIR by default. A newly added configuration file option,innodb_temp_data_file_path, allows for a user-defined temporary data file path.

    • innochecksum functionality is enhanced with several new options and extended capabilities. SeeSection 4.6.1, “innochecksum — Offline InnoDB File Checksum Utility”.

    • A new type of non-redo undo log for both normal and compressed temporary tables and related objects now resides in the temporary tablespace. For more information, seeSection 14.6.7, “Undo Logs”.

    • InnoDB buffer pool dump and load operations are enhanced. A new system variable,innodb_buffer_pool_dump_pct, allows you to specify the percentage of most recently used pages in each buffer pool to read out and dump. When there is other I/O activity being performed byInnoDB background tasks,InnoDB attempts to limit the number of buffer pool load operations per second using theinnodb_io_capacity setting.

    • Support is added toInnoDB for full-text parser plugins. For information about full-text parser plugins, seeFull-Text Parser Plugins andWriting Full-Text Parser Plugins.

    • InnoDB supports multiple page cleaner threads for flushing dirty pages from buffer pool instances. A new system variable,innodb_page_cleaners, is used to specify the number of page cleaner threads. The default value of1 maintains the previous configuration in which there is a single page cleaner thread. This enhancement builds on work completed in MySQL 5.6, which introduced a single page cleaner thread to offload buffer pool flushing work from theInnoDB master thread.

    • Online DDL support is extended to the following operations for regular and partitionedInnoDB tables:

    • The Fusion-io Non-Volatile Memory (NVM) file system on Linux providesatomic write capability, which makes theInnoDBdoublewrite buffer redundant. TheInnoDB doublewrite buffer is automatically disabled for system tablespace files (ibdata files) located on Fusion-io devices that support atomic writes.

    • InnoDB supports theTransportable Tablespace feature for partitionedInnoDB tables and individualInnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances. For more information, seeSection 14.6.1.3, “Importing InnoDB Tables”.

    • Theinnodb_buffer_pool_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the newinnodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the newInnodb_buffer_pool_resize_status status variable. For more information, seeConfiguring InnoDB Buffer Pool Size Online.

    • Multithreaded page cleaner support (innodb_page_cleaners) is extended to shutdown and recovery phases.

    • InnoDB supports indexing of spatial data types usingSPATIAL indexes, including use ofALTER TABLE ... ALGORITHM=INPLACE for online operations (ADD SPATIAL INDEX).

    • InnoDB performs a bulk load when creating or rebuilding indexes. This method of index creation is known as a“sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option,innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth. For more information, seeSection 14.6.2.3, “Sorted Index Builds”.

    • A new log record type (MLOG_FILE_NAME) is used to identify tablespaces that have been modified since the last checkpoint. This enhancement simplifies tablespace discovery during crash recovery and eliminates scans on the file system prior to redo log application. For more information about the benefits of this enhancement, seeTablespace Discovery During Crash Recovery.

      This enhancement changes the redo log format, requiring that MySQL be shut down cleanly before upgrading to or downgrading from MySQL 5.7.5.

    • You can truncate undo logs that reside in undo tablespaces. This feature is enabled using theinnodb_undo_log_truncate configuration option. For more information, seeTruncating Undo Tablespaces.

    • InnoDB supports native partitioning. Previously,InnoDB relied on theha_partition handler, which creates a handler object for each partition. With native partitioning, a partitionedInnoDB table uses a single partition-aware handler object. This enhancement reduces the amount of memory required for partitionedInnoDB tables.

      As of MySQL 5.7.9,mysql_upgrade looks for and attempts to upgrade partitionedInnoDB tables that were created using theha_partition handler. Also in MySQL 5.7.9 and later, you can upgrade such tables by name in themysql client usingALTER TABLE ... UPGRADE PARTITIONING.

    • InnoDB supports the creation of general tablespaces usingCREATE TABLESPACE syntax.

      CREATE TABLESPACE `tablespace_name`  ADD DATAFILE 'file_name.ibd'  [FILE_BLOCK_SIZE = n]

      General tablespaces can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.

      Tables are added to a general tablespace usingCREATE TABLEtbl_name ... TABLESPACE [=]tablespace_name orALTER TABLEtbl_name TABLESPACE [=]tablespace_name syntax.

      For more information, seeSection 14.6.3.3, “General Tablespaces”.

    • DYNAMIC replacesCOMPACT as the implicit default row format forInnoDB tables. A new configuration option,innodb_default_row_format, specifies the defaultInnoDB row format. For more information, seeDefining the Row Format of a Table.

    • As of MySQL 5.7.11,InnoDB supports data-at-rest encryption for file-per-table tablespaces. Encryption is enabled by specifying theENCRYPTION option when creating or altering anInnoDB table. This feature relies on akeyring plugin for encryption key management. For more information, seeSection 6.4.4, “The MySQL Keyring”, andSection 14.14, “InnoDB Data-at-Rest Encryption”.

    • As of MySQL 5.7.24, thezlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.

      If you useInnoDB compressed tables, seeSection 2.10.3, “Changes in MySQL 5.7” for related upgrade implications.

  • JSON support.  Beginning with MySQL 5.7.8, MySQL supports a nativeJSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored inJSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as=,<,<=,>,>=,<>,!=, and<=>; for information about supported operators as well as precedence and other rules that MySQL follows when comparingJSON values, seeComparison and Ordering of JSON Values.

    MySQL 5.7.8 also introduces a number of functions for working withJSON values. These functions include those listed here:

    In MySQL 5.7.9 and later, you can usecolumn->path as shorthand forJSON_EXTRACT(column,path). This works as an alias for a column wherever a column identifier can occur in an SQL statement, includingWHERE,ORDER BY, andGROUP BY clauses. This includesSELECT,UPDATE,DELETE,CREATE TABLE, and other SQL statements. The left hand side must be aJSON column identifier (and not an alias). The right hand side is a quoted JSON path expression which is evaluated against the JSON document returned as the column value.

    MySQL 5.7.22 adds the following JSON functions:

    • Two JSON aggregation functionsJSON_ARRAYAGG() andJSON_OBJECTAGG().JSON_ARRAYAGG() takes a column or expression as its argument, and aggregates the result as a singleJSON array. The expression can evaluate to any MySQL data type; this does not have to be aJSON value.JSON_OBJECTAGG() takes two columns or expressions which it interprets as a key and a value; it returns the result as a singleJSON object. For more information and examples, seeSection 12.19, “Aggregate Functions”.

    • The JSON utility functionJSON_PRETTY(), which outputs an existingJSON value in an easy-to-read format; each JSON object member or array value is printed on a separate line, and a child object or array is intended 2 spaces with respect to its parent.

      This function also works with a string that can be parsed as a JSON value.

      See alsoSection 12.17.6, “JSON Utility Functions”.

    • The JSON utility functionJSON_STORAGE_SIZE(), which returns the storage space in bytes used for the binary representation of a JSON document prior to any partial update (see previous item).

      This function also accepts a valid string representation of a JSON document. For such a value,JSON_STORAGE_SIZE() returns the space used by its binary representation following its conversion to a JSON document. For a variable containing the string representation of a JSON document,JSON_STORAGE_FREE() returns zero. Either function produces an error if its (non-null) argument cannot be parsed as a valid JSON document, andNULL if the argument isNULL.

      For more information and examples, seeSection 12.17.6, “JSON Utility Functions”.

    • A JSON merge function intended to conform toRFC 7396.JSON_MERGE_PATCH(), when used on 2 JSON objects, merges them into a single JSON object that has as members a union of the following sets:

      • Each member of the first object for which there is no member with the same key in the second object.

      • Each member of the second object for which there is no member having the same key in the first object, and whose value is not the JSONnull literal.

      • Each member having a key that exists in both objects, and whose value in the second object is not the JSONnull literal.

      As part of this work, theJSON_MERGE() function has been renamedJSON_MERGE_PRESERVE().JSON_MERGE() continues to be recognized as an alias forJSON_MERGE_PRESERVE() in MySQL 5.7, but is now deprecated and is subject to removal in a future version of MySQL.

      For more information and examples, seeSection 12.17.4, “Functions That Modify JSON Values”.

    SeeSection 12.17.3, “Functions That Search JSON Values”, for more information about-> andJSON_EXTRACT(). For information about JSON path support in MySQL 5.7, seeSearching and Modifying JSON Values. See alsoIndexing a Generated Column to Provide a JSON Column Index.

  • System and status variables.  System and status variable information is now available in Performance Schema tables, in preference to use ofINFORMATION_SCHEMA tables to obtain these variable. This also affects the operation of theSHOW VARIABLES andSHOW STATUS statements. The value of theshow_compatibility_56 system variable affects the output produced from and privileges required for system and status variable statements and tables. For details, see the description of that variable inSection 5.1.7, “Server System Variables”.

    Note

    The default forshow_compatibility_56 isOFF. Applications that require 5.6 behavior should set this variable toON until such time as they have been migrated to the new behavior for system variables and status variables. SeeSection 25.20, “Migrating to Performance Schema System and Status Variable Tables”

  • sys schema.  MySQL distributions now include thesys schema, which is a set of objects that help DBAs and developers interpret data collected by the Performance Schema.sys schema objects can be used for typical tuning and diagnosis use cases. For more information, seeChapter 26,MySQL sys Schema.

  • Condition handling.  MySQL now supports stacked diagnostics areas. When the diagnostics area stack is pushed, the first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it. Within a condition handler, executed statements modify the new current diagnostics area, butGET STACKED DIAGNOSTICS can be used to inspect the stacked diagnostics area to obtain information about the condition that caused the handler to activate, independent of current conditions within the handler itself. (Previously, there was a single diagnostics area. To inspect handler-activating conditions within a handler, it was necessary to check this diagnostics area before executing any statements that could change it.) SeeSection 13.6.7.3, “GET DIAGNOSTICS Statement”, andSection 13.6.7.7, “The MySQL Diagnostics Area”.

  • Optimizer.  These optimizer enhancements were added:

  • Triggers.  Previously, a table could have at most one trigger for each combination of trigger event (INSERT,UPDATE,DELETE) and action time (BEFORE,AFTER). This limitation has been lifted and multiple triggers are permitted. For more information, seeSection 23.3, “Using Triggers”.

  • Logging.  These logging enhancements were added:

    • Previously, on Unix and Unix-like systems, MySQL support for sending the server error log tosyslog was implemented by havingmysqld_safe capture server error output and pass it tosyslog. The server now includes nativesyslog support, which has been extended to include Windows. For more information about sending server error output tosyslog, seeSection 5.4.2, “The Error Log”.

    • Themysql client now has a--syslog option that causes interactive statements to be sent to the systemsyslog facility. Logging is suppressed for statements that match the default“ignore” pattern list ("*IDENTIFIED*:*PASSWORD*"), as well as statements that match any patterns specified using the--histignore option. SeeSection 4.5.1.3, “mysql Client Logging”.

  • Generated Columns.  MySQL now supports the specification of generated columns inCREATE TABLE andALTER TABLE statements. Values of a generated column are computed from an expression specified at column creation time. Generated columns can be virtual (computed“on the fly” when rows are read) or stored (computed when rows are inserted or updated). For more information, seeSection 13.1.18.7, “CREATE TABLE and Generated Columns”.

  • mysql client.  Previously,Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. NowControl+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.

  • Database name rewriting with mysqlbinlog.  Renaming of databases bymysqlbinlog when reading from binary logs written using the row-based format is now supported using the--rewrite-db option added in MySQL 5.7.1.

    This option uses the format--rewrite-db='dboldname->dbnewname'. You can implement multiple rewrite rules, by specifying the option multiple times.

  • HANDLER with partitioned tables.  TheHANDLER statement may now be used with user-partitioned tables. Such tables may use any of the available partitioning types (seeSection 22.2, “Partitioning Types”).

  • Index condition pushdown support for partitioned tables.  Queries on partitioned tables using theInnoDB orMyISAM storage engine may employ the index condition pushdown optimization that was introduced in MySQL 5.6. SeeSection 8.2.1.5, “Index Condition Pushdown Optimization”, for more information.

  • WITHOUT VALIDATION support for ALTER TABLE ... EXCHANGE PARTITION.  As of MySQL 5.7.5,ALTER TABLE ... EXCHANGE PARTITION syntax includes an optional{WITH|WITHOUT} VALIDATION clause. WhenWITHOUT VALIDATION is specified,ALTER TABLE ... EXCHANGE PARTITION does not perform row-by-row validation when exchanging a populated table with the partition, permitting database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition.WITH VALIDATION is the default behavior and need not be specified explicitly. For more information, seeSection 22.3.3, “Exchanging Partitions and Subpartitions with Tables”.

  • Source dump thread improvements.  The source dump thread was refactored to reduce lock contention and improve source throughput. Previous to MySQL 5.7.2, the dump thread took a lock on the binary log whenever reading an event; in MySQL 5.7.2 and later, this lock is held only while reading the position at the end of the last successfully written event. This means both that multiple dump threads are now able to read concurrently from the binary log file, and that dump threads are now able to read while clients are writing to the binary log.

  • Character set support.  MySQL 5.7.4 includes agb18030 character set that supports the China National Standard GB18030 character set. For more information about MySQL character set support, seeChapter 10,Character Sets, Collations, Unicode.

  • Changing the replication source without STOP SLAVE.  In MySQL 5.7.4 and later, the strict requirement to executeSTOP SLAVE prior to issuing anyCHANGE MASTER TO statement is removed. Instead of depending on whether the replica is stopped, the behavior ofCHANGE MASTER TO now depends on the states of the replica SQL thread and replica I/O threads; which of these threads is stopped or running now determines the options that can or cannot be used with aCHANGE MASTER TO statement at a given point in time. The rules for making this determination are listed here:

    • If the SQL thread is stopped, you can executeCHANGE MASTER TO using any combination ofRELAY_LOG_FILE,RELAY_LOG_POS, andMASTER_DELAY options, even if the replica I/O thread is running. No other options may be used with this statement when the I/O thread is running.

    • If the I/O thread is stopped, you can executeCHANGE MASTER TO using any of the options for this statement (in any allowed combination)exceptRELAY_LOG_FILE,RELAY_LOG_POS, orMASTER_DELAY, even when the SQL thread is running. These three options may not be used when the I/O thread is running.

    • Both the SQL thread and the I/O thread must be stopped before issuingCHANGE MASTER TO ... MASTER_AUTO_POSITION = 1.

    You can check the current state of the replica SQL and I/O threads usingSHOW SLAVE STATUS.

    If you are using statement-based replication and temporary tables, it is possible for aCHANGE MASTER TO statement following aSTOP SLAVE statement to leave behind temporary tables on the replica. As part of this set of improvements, a warning is now issued wheneverCHANGE MASTER TO is issued followingSTOP SLAVE when statement-based replication is in use andSlave_open_temp_tables remains greater than 0.

    For more information, seeSection 13.4.2.1, “CHANGE MASTER TO Statement”, andSection 16.3.7, “Switching Sources During Failover”.

  • Test suite.  The MySQL test suite now usesInnoDB as the default storage engine.

  • Multi-source replication is now possible.  MySQL Multi-Source Replication adds the ability to replicate from multiple sources to a replica. MySQL Multi-Source Replication topologies can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. SeeSection 16.1.5, “MySQL Multi-Source Replication”.

    As part of MySQL Multi-Source Replication, replication channels have been added. Replication channels enable a replica to open multiple connections to replicate from, with each channel being a connection to a source. SeeSection 16.2.2, “Replication Channels”.

  • Group Replication Performance Schema tables.  MySQL 5.7 adds a number of new tables to the Performance Schema to provide information about replication groups and channels. These include the following tables:

    All of these tables were added in MySQL 5.7.2, except forreplication_group_members andreplication_group_member_stats, which were added in MySQL 5.7.6. For more information, seeSection 25.12.11, “Performance Schema Replication Tables”.

  • Group Replication SQL.  The following statements were added in MySQL 5.7.6 for controlling Group Replication:

    For more information, seeSection 13.4.3, “SQL Statements for Controlling Group Replication”.

Features Deprecated in MySQL 5.7

The following features are deprecated in MySQL 5.7 and may be removed in a future series. Where alternatives are shown, applications should be updated to use them.

For applications that use features deprecated in MySQL 5.7 that have been removed in a higher MySQL series, statements may fail when replicated from a MySQL 5.7 source to a higher-series replica, or may have different effects on source and replica. To avoid such problems, applications that use features deprecated in 5.7 should be revised to avoid them and use alternatives when possible.

Features Removed in MySQL 5.7

The following items are obsolete and have been removed in MySQL 5.7. Where alternatives are shown, applications should be updated to use them.

For MySQL 5.6 applications that use features removed in MySQL 5.7, statements may fail when replicated from a MySQL 5.6 source to a MySQL 5.7 replica, or may have different effects on source and replica. To avoid such problems, applications that use features removed in MySQL 5.7 should be revised to avoid them and use alternatives when possible.

  • Support for passwords that use the older pre-4.1 password hashing format is removed, which involves the following changes. Applications that use any feature no longer supported must be modified.

    • Themysql_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. For instructions on upgrading accounts that use this plugin, seeSection 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    • The--secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated; expect it to 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.

    • Thesecure_auth system variable permits only a value of 1; a value of 0 is no longer permitted.

    • For theold_passwords system variable, a value of 1 (produce pre-4.1 hashes) is no longer permitted.

    • TheOLD_PASSWORD() function is removed.

  • In MySQL 5.6.6, the 2-digitYEAR(2) data type was deprecated. Support forYEAR(2) is now removed. Once you upgrade to MySQL 5.7.5 or higher, any remaining 2-digitYEAR(2) columns must be converted to 4-digitYEAR columns to become usable again. For conversion strategies, seeSection 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”. For example, runmysql_upgrade after upgrading.

  • Theinnodb_mirrored_log_groups system variable. The only supported value was 1, so it had no purpose.

  • Thestorage_engine system variable. Usedefault_storage_engine instead.

  • Thethread_concurrency system variable.

  • Thetimed_mutexes system variable, which had no effect.

  • TheIGNORE clause forALTER TABLE.

  • INSERT DELAYED is no longer supported. The server recognizes but ignores theDELAYED keyword, handles the insert as a nondelayed insert, and generates anER_WARN_LEGACY_SYNTAX_CONVERTED warning. (“INSERT DELAYED is no longer supported. The statement was converted to INSERT.”) Similarly,REPLACE DELAYED is handled as a nondelayed replace. You should expect theDELAYED keyword to be removed in a future release.

    In addition, severalDELAYED-related options or features were removed:

    • The--delayed-insert option formysqldump.

    • TheCOUNT_WRITE_DELAYED,SUM_TIMER_WRITE_DELAYED,MIN_TIMER_WRITE_DELAYED,AVG_TIMER_WRITE_DELAYED, andMAX_TIMER_WRITE_DELAYED columns of the Performance Schematable_lock_waits_summary_by_table table.

    • mysqlbinlog no longer writes comments mentioningINSERT DELAYED.

  • Database symlinking on Windows using.sym files has been removed because it is redundant with native symlink support available usingmklink. Any.sym file symbolic links are now ignored and should be replaced with symlinks created usingmklink. SeeSection 8.12.3.3, “Using Symbolic Links for Databases on Windows”.

  • The unused--basedir,--datadir, and--tmpdir options formysql_upgrade were removed.

  • Previously, program options could be specified in full or as any unambiguous prefix. For example, the--compress option could be given tomysqldump as--compr, but not as--comp because the latter is ambiguous. Option prefixes are no longer supported; only full options are accepted. This is because prefixes can cause problems when new options are implemented for programs and a prefix that is currently unambiguous might become ambiguous in the future. Some implications of this change:

    • The--key-buffer option must now be specified as--key-buffer-size.

    • The--skip-grant option must now be specified as--skip-grant-tables.

  • SHOW ENGINE INNODB MUTEX output is removed. Comparable information can be generated by creating views onPerformance Schema tables.

  • TheInnoDB Tablespace Monitor andInnoDB Table Monitor are removed. For the Table Monitor, equivalent information can be obtained fromInnoDBINFORMATION_SCHEMA tables.

  • The specially named tables used to enable and disable the standardInnoDB Monitor andInnoDB Lock Monitor (innodb_monitor andinnodb_lock_monitor) are removed and replaced by two dynamic system variables:innodb_status_output andinnodb_status_output_locks. For additional information, seeSection 14.18, “InnoDB Monitors”.

  • Theinnodb_use_sys_malloc andinnodb_additional_mem_pool_size system variables, deprecated in MySQL 5.6.3, were removed.

  • Themsql2mysql,mysql_convert_table_format,mysql_find_rows,mysql_fix_extensions,mysql_setpermission,mysql_waitpid,mysql_zap,mysqlaccess, andmysqlbug utilities.

  • Themysqlhotcopy utility. Alternatives includemysqldump and MySQL Enterprise Backup.

  • Thebinary-configure.sh script.

  • TheINNODB_PAGE_ATOMIC_REF_COUNTCMake option is removed.

  • Theinnodb_create_intrinsic option is removed.

  • Theinnodb_optimize_point_storage option and related internal data types (DATA_POINT andDATA_VAR_POINT) are removed.

  • Theinnodb_log_checksum_algorithm option is removed.

  • Themyisam_repair_threads system variable as of MySQL 5.7.39.