Changes in MySQL 5.7.0 (Not released, Milestone 10)

Note

This is a milestone release, for use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. (Making a backup before the upgrade is a prudent precaution in any case.)

• Deprecation and Removal Notes

• Diagnostics Notes

• Functionality Added or Changed

• Bugs Fixed

Deprecation and Removal Notes

• Microsoft Windows: Windows Vista, Windows Server 2008, and newer support native symbolic linking using the mklink command. This makes the MySQL Server implementation of database symbolic links using .sym files redundant, so that mechanism is now removed. This change has the following implications:

  • Existing .sym files are now ignored. Database symbolic links should be recreated using mklink. See Using Symbolic Links for Databases on Windows.

  • The --symbolic-links and --skip-symbolic-links options and the have_symlink system variable now are meaningful only for Unix systems, and not for Windows.

  (WL #6561)

Diagnostics Notes

• MySQL now supports stacked diagnostics areas. When a push to the diagnostics area stack occurs, 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, but GET 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.) See GET DIAGNOSTICS Statement, and The MySQL Diagnostics Area. (WL #6406)

Functionality Added or Changed

• Important Change; Replication: SHOW SLAVE STATUS when run concurrently with STOP SLAVE can take a long time to execute if the slave SQL thread was in the midst of applying a large update. To fix this problem, a new NONBLOCKING option has been added to the SHOW SLAVE STATUS statement. When this option is used, SHOW SLAVE STATUS does not wait on the SQL or I/O threads but returns immediately. This means that the reported states of these threads may not be completely up to date when the option is used. NONBLOCKING is intended primarily for use by monitoring tools in which obtaining an immediate response is more important than having the most timely data. (Bug #15993588, Bug #67879, WL #6402)

• Important Change; Replication: Added the --idempotent option for mysqlbinlog, which causes the MySQL Server to employ idempotent mode. This causes suppression of all duplicate-key and key-not-found errors when processing updates from the binary log. The mode is in effect for the current mysqlbinlog client and client session only. (WL #6403)

• Important Change: INSERT DELAYED is no longer supported. The server recognizes but ignores the DELAYED keyword, handles the insert as a nondelayed insert, and generates an ER_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. The DELAYED keyword will be removed in a future release.

  In addition, several DELAYED-related options or features were removed:

  • The --delayed-insert option for mysqldump.

  • The COUNT_WRITE_DELAYED, SUM_TIMER_WRITE_DELAYED, MIN_TIMER_WRITE_DELAYED, AVG_TIMER_WRITE_DELAYED, and MAX_TIMER_WRITE_DELAYED columns of the table_lock_waits_summary_by_table Performance Schema table.

    If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

  • mysqlbinlog no longer writes comments mentioning INSERT DELAYED.

  (WL #6073)

• Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit. (Bug #66583, Bug #14554568)

• The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode. (Bug #37520, Bug #11748842)

• The mysql_clear_password cleartext client-side authentication plugin is intended for authentication schemes that require the server to receive the password as entered on the client side, without hashing. Because the password is sent in the clear, this plugin should be used within the context of an encrypted connection, such as an SSL connection, to avoid exposing the password over the network. To make inadvertent use of this plugin less likely, it is now required that clients explicitly enable it. This can be done several ways:

  • Set the LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN environment variable to a value that begins with 1, Y, or y. This enables the plugin for all client connections.

  • The mysql, mysqladmin, and mysqlslap client programs support an --enable-cleartext-plugin option that enables the plugin on a per-invocation basis.

  • The mysql_options() C API function supports a MYSQL_ENABLE_CLEARTEXT_PLUGIN option that enables the plugin on a per-connection basis. Also, any program that uses libmysqlclient and reads option files can enable the plugin by including an enable-cleartext-plugin option in an option group read by the client library.

Bugs Fixed

• Important Change; Replication: Statements involving the Performance Schema tables should not be written to the binary log, because the content of these tables is applicable only to a given MySQL Server instance, and may differ greatly between different servers in a replication topology. The database administrator should be able to configure (INSERT, UPDATE, or DELETE) or flush (TRUNCATE TABLE) performance schema tables on a single server without affecting others. However, when using replication with GTIDs enabled (see Replication with Global Transaction Identifiers), warnings about unsafe statements updating Performance Schema tables were elevated to errors, preventing the use of performance_schema and GTIDs together.

  Similar problems were encountered with replication and system logging tables when GTIDs were enabled.

  This fix introduces the concept of a nonreplicated or local table. Now when MySQL replication encounters a table that is marked as local, updates to this table are ignored.

  This fix defines as local the following tables, which are no longer replicated:

  • All tables in the performance_schema database

  • mysql.general_log

  • mysql.slow_log

  • mysql.slave_relay_log_info

  • mysql.slave_master_info

  • mysql.slave_worker_info

  Before this fix, statements using the performance_schema and other tables just listed were handled by being marked as unsafe for replication, which caused warnings during execution; the statements were nonetheless written to the binary log, regardless of the logging format in effect.

  Existing replication behavior for tables in the INFORMATION_SCHEMA database is not changed by this fix.

  For more information, see MySQL Performance Schema. See also MySQL Server Logs, and Replication Metadata Repositories. For information about general and slow query log tables, see Selecting General Query Log and Slow Query Log Output Destinations. (Bug #14741537)

• Important Change: Formerly, the ExtractValue() and UpdateXML() functions supported a maximum length of 127 characters for XPath expressions supplied to them as arguments. This limitation has now been removed. (Bug #13007062, Bug #62429)

• InnoDB: Creating and altering tables repeatedly would result in a memory leak that was due to a duplicate key error. The duplicate key error occurred because the row_merge_build_indexes function did not call row_fts_psort_info_destroy often enough. As full-text search indexes were created with a unique index, the unique index failed due to the duplicate key error, and full-text search build resource would not be released. (Bug #14759111)

• InnoDB: During an online DDL operation, a duplicate key error could be incorrectly issued if a record was inserted and subsequently updated while the table was being rebuilt. (Bug #14723456)

• InnoDB: InnoDB IO threads within Performance Schema were exposed with the following name: “io_handler_thread”. This fix implements specific keys such as io_read_handler_thread, io_write_handler_thread, io_ibuf_handler_thread to differentiate InnoDB IO threads within Performance Schema. (Bug #14670810)

• InnoDB: If the server crashed at a precise moment during an ALTER TABLE operation that rebuilt the clustered index for an InnoDB table, the original table could be inaccessible afterward. An example of such an operation is ALTER TABLE ... ADD PRIMARY KEY The fix preserves the original table if the server halts during this operation. You might still need to rename the .ibd file manually to restore the original table contents: in MySQL 5.6 and higher, rename from #sql-ib$new_table_id.ibd to table_name.ibd within the database directory; prior to MySQL 5.6, the temporary file to rename is table_name#1 or #2. (Bug #14669848)

• InnoDB: Inserting data of varying record lengths into an InnoDB table that used compression could cause the server to halt with an error. (Bug #14554000, Bug #13523839, Bug #63815, Bug #12845774, Bug #61456, Bug #12595091, Bug #61208)

• InnoDB: This fix addresses an assert condition that would occur when inserting large BLOBs into tablespaces with a 4KB physical page size or into some compressed tables. Extents would not be allocated soon enough for tablespaces with smaller physical page sizes. (Bug #14520559)

• InnoDB: If a table was defined with an index key length very close to the upper length limit of 3072, a query against that table could cause a serious error. (Bug #14500557, Bug #14537695)

• InnoDB: In debug builds, a mismatch in the InnoDB PAGE_FREE list would cause an assertion. (Bug #12701488)

• InnoDB: On Linux systems, certain I/O requests that read or wrote fewer than the requested number of bytes could cause the server to crash. This issue could happen more frequently with asynchronous I/O requests. The messages did not clearly identify what type of error occurred:

  InnoDB: Operating system error number 0 in a file operation.
  InnoDB: Error number 0 means 'Success'.

  With this fix, MySQL retries the operation several times before giving up. (The number of retries is defined by the constant NUM_RETRIES_ON_PARTIAL_IO in the source code, default value 10.) (Bug #11761646, Bug #54160)

• Partitioning: When the server is started with --skip-partition, it should reject DDL or DML statements on partitioned tables. However, for DROP TABLE, the server dropped the .frm file, and for RENAME TABLE, the server renamed the .frm file. (Bug #11763795)

• Replication: mysqlbinlog did not properly decode DECIMAL values in a row-based binary log. This could cause invalid values to be printed out for DECIMAL columns. (Bug #14309019)

  References: See also: Bug #17544169.

• Replication: mysqlbinlog -v -v prints in verbose mode, with comments on data column types, from a binary log file. When mysqlbinlog -v -v encountered a column data value which was NULL, the column's data type was not updated; as a result, the data type of the previous column was printed instead, or—in the case where this was the table's first column—the type was shown as <an integer>. Now in such cases, the data type is shown correctly. (Bug #14171756)

• Replication: When using mysqlbinlog with the --verbose option to read a binary log written by a MySQL server using row-based or mixed-format logging, invalid SQL could be produced when comments appeared inside BINLOG statements. One way in which this could happen was when a function that updated data was used within an INSERT ... SELECT statement. (Bug #12889121)

• Replication: mysql_upgrade on the master broke replication when the slave was run with --log-output equal to FILE or NONE. (Bug #11763447)

• Replication: Issuing STOP SLAVE caused a spurious Error reading packet from server: Lost connection to MySQL server during query message to be written to the error log. (Bug #11761457, Bug #12977988, Bug #53955)

• Replication: When an error occurs in the slave SQL thread, this causes the Slave_SQL_Error and Slave_SQL_Errno columns from SHOW SLAVE STATUS to display the reason for the error. The error number should be one of the usual constants ER_* defined in sql/share/errmsg.txt, and the error message should be the corresponding string. However, in some cases, Slave_SQL_Errno was set to something other than an ER_* number, and Slave_SQL_Error to a hard-coded error message rather than a translatable string from sql/share/errmsg.txt. Now all errors shown by SHOW SLAVE STATUS originate in sql/share/errmsg.txt, as expected. (Bug #11760365, Bug #52768)

• Microsoft Windows: On Microsoft Windows, CMake entries for POSIX API's not found on Microsoft Windows were added to the CMake cache. This decreases the number of expected "Not found" errors while compiling MySQL. (Bug #14790333)

• Microsoft Windows: On Microsoft Windows, queries referring to a table with invalid characters would search the system for invalid file names. The generated system error code (ERROR_INVALID_NAME) was not recognized by MySQL, so this unknown error would be reported to the server log as "ERRNO: 22 - INVALID ARGUMENT". MySQL now recognizes these errors and reports them as the table does not existing, and it no longer logs them to the server error log. (Bug #14642248)

• Microsoft Windows: On Windows, starting the server with --log-error and --console caused the server to write to the log file but not the console. Before MySQL 5.5.3, this occurred only if --log-error was specified after --console. Now, --console overrides --log-error no matter the option order so that --console produces console output in all cases. (Bug #14207773, Bug #65592)

• Microsoft Windows: It was possible to specify a Named Pipe that was already in use. This is no longer allowed, as an error is now emitted and the process is aborted. After mysqld.exe was started in Named Pipe mode with a pipe name that was already used by a different instance, neither instance was able to shut down properly when a shutdown command was received from a TCP socket in any of the processes. Therefore, mysqld.exe was not terminated. (Bug #13891058, Bug #61885)

• Microsoft Windows: On Microsoft Windows, a failed API or function call in mysqld.exe could sometimes report the error code 22, instead of the proper error code. (Bug #11763004)

• NDB Replication: Transactions originating on a replication master are applied on slaves as if using AO_AbortError, but transactions replayed from a binary log were not. Now transactions being replayed from a log are handled in the same way as those coming from a “live” replication master.

  See NdbOperation::AbortOption, for more information. (Bug #14615095)

• Joins of exactly 32 tables and containing a HAVING clause returned an empty result. (Bug #15972635)

• The parser rejected some legal UNION statements. (Bug #14730856)

• Setting thread_cache_size to a negative value at server startup resulted in a value of 16384 rather than 0. (Bug #14683107)

• XA RECOVER displayed nonprintable characters in the XID data. Now such characters are hex encoded. (Bug #14670465)

• There was no warning at startup if the server was started with an invalid query_cache_size value. (Bug #14576423)

• The return value from IS_USED_LOCK() was reported using the wrong data type. (Bug #14575699)

• When resolving outer fields, Item_field::fix_outer_fields() creates new Item_refs for each execution of a prepared statement, so these must be allocated in the runtime memroot. The memroot switching before resolving JOIN::having caused these to be allocated in the statement root, leaking memory for each prepared statement execution. (Bug #14409015)

• Activation of a stored program handler did not preserve the current diagnostics stack. (Bug #14342913, WL #6406)

• In debug builds, killing a HELP statement caused an assertion to be raised. (Bug #14221840)

• If an error occurred during evaluation of the BEFORE expression of a PURGE BINARY LOGS BEFORE statement, the statement did not abort as it should have and later raised an assertion. (Bug #14215847)

• For the index merge access method, the optimizer could make a suboptimal choice of indexes to use. (Bug #14095506)

• An assertion could be raised if the attempt to open the .frm file for a temporary table failed. (Bug #13359247)

• If the state of the Event Scheduler was changed during server shutdown, the server could crash. (Bug #13002460)

• When storing the definition for a view that used the UPPER() or LOWER() function, the function call was replaced by UCASE() or LCASE(), respectively (as shown in the output of SHOW CREATE VIEW). This was in spite of the fact that UPPER() and LOWER() are standard, with UCASE() and LCASE() being MySQL synonyms for these. This made it more difficult to move databases between MySQL and other database systems.

  With this fix, calls to UPPER() and LOWER() within views are no longer rewritten when storing their definitions; instead, UCASE() is now rewritten as UPPER() in stored view definitions, and LCASE() as LOWER(), which increases the portability of the views. (Bug #12844279)

• For queries that accessed an INFORMATION_SCHEMA table in a subquery, an attempt to lock a mutex that had already been locked could cause a server crash. (Bug #11765744)

• A view using INTERVAL() could be created, but it was not possible to select from the view, nor could it be shown with SHOW CREATE VIEW. (Bug #11753832)

• The Range checked for each record optimization is now used for conditions with outer query references. (Bug #11750963)

• For an ALTER TABLE statement that renamed or changed the default value of a BINARY column, the alteration was done using a table copy and not in place. (Bug #67141, Bug #14735373, Bug #69580, Bug #17024290)

• For queries using ref access on string data types, the ref access condition could be evaluated again as part of the query condition or pushed down as an index condition to the storage engine. (Bug #66983, Bug #14682735)

• Concurrent execution of DROP DATABASE and any of CREATE FUNCTION, CREATE PROCEDURE, or CREATE EVENT could be written to the binary log in the wrong order, causing replication failure. (Bug #65428, Bug #14127220)

• For a view defined on a UNION, the server could create an invalid view definition. (Bug #65388, Bug #14117018, Bug #72018, Bug #18405221)

• If read_only is enabled, it is still permitted to create TEMPORARY tables. But in this case, a non-TEMPORARY table with the same name could also be created, which should not be permitted. (Bug #64992, Bug #13969578)

• Enabling the session value of low_priority_updates had no effect for INSERT statements. (Bug #64892, Bug #13939940)

• References to a stored function without a database name qualifier while there was no default database resulted in an ER_SP_DOES_NOT_EXIST error rather than ER_NO_DB_ERROR. (Bug #64692, Bug #13864485)

• The server refused client connections while executing FLUSH PRIVILEGES. (Bug #63178, Bug #13418619)

• A view was created with an incorrect definition if the WHERE clause contained string literals and character_set_client and character_set_connection were set to different character sets. (Bug #63094, Bug #13520710)

• SHOW CREATE VIEW failed if the tables underlying the view were changed. (Bug #61718, Bug #12762393)

• Concurrent inserts were blocked by selects if the inserts were generated from within a stored procedure. (Bug #58689, Bug #11765698)

• An INSERT INTO ... SELECT statement that inserted no rows unnecessarily invalidated statements in the query cache that used the target table. (Bug #50065, Bug #11757947)

• Using ALTER TABLE to rename a table to . resulted in a table with no name. (Bug #49636, Bug #11757569)

• SHOW GLOBAL STATUS caused performance problems on busy servers due to lock contention. (Bug #42930, Bug #11751904)

• INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE and LOAD DATA CONCURRENT REPLACE took too weak a lock, leading to the possibility of concurrent SELECT statements returning inconsistent results. (Bug #38046, Bug #11749055)

• An event was not dropped from the mysql.event table under these circumstances: The event was created while the event scheduler was enabled; the scheduler was disabled and re-enabled; the event expiration time was reached. (Bug #34804, Bug #11748012)