Changes in MySQL 5.7.2 (2013-09-21, Milestone 12)

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.)

• Authentication Notes

• Compilation Notes

• Deprecation and Removal Notes

• Diagnostics Notes

• Logging Notes

• Performance Schema Notes

• RPM Notes

• Security Notes

• Semisynchronous Replication Notes

• Trigger Notes

• Functionality Added or Changed

• Bugs Fixed

Authentication Notes

• Incompatible Change: Previously, account rows in the mysql.user table could have an empty plugin column value. In this case, the server authenticated such an account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. With the deprecation of old-format password hashes in MySQL 5.6.5, this heuristic for deciding which authentication plugin to use is unnecessary and it is desirable that user table rows always specify explicitly which authentication plugin applies.

  To that end, the plugin column is now defined to be non-NULL with a default value of 'mysql_native_password', and associated server operations require the column to be nonempty. In conjunction with this plugin column definition modification, several other changes have been made:

  • The --default-authentication-plugin command-line option is reimplemented as the default_authentication_plugin system variable. Its use at server startup is unchanged, but now the default plugin value can be examined at runtime using SHOW VARIABLES or SELECT @@default_authentication_plugin. The variable is read only and cannot be changed at runtime.

  • When mysql_install_db is run, it invokes the server to initialize the mysql database. The server now assigns every user table row a nonempty plugin column value. The value is 'mysql_native_password' unless the default_authentication_plugin system variable is set otherwise at server startup.

  • mysql_upgrade checks user table rows and, for any row with an empty plugin column, sets that column to 'mysql_native_password' or 'mysql_old_password' depending on the hash format of the Password column value.

  • At startup, and at runtime when FLUSH PRIVILEGES is executed, the server checks user table rows. For any row with an empty plugin column, the server writes a warning to the error log of this form:

    [Warning] User entry 'user_name'@'host_name' has an empty plugin
    value. The user will be ignored and no one can login with this user
    anymore.

    To address this issue, execute mysql_upgrade.

  If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate the plugin column change into the mysql system database and assign the appropriate nonempty plugin value to any empty plugin column values. However, because the server now checks for and disables accounts with empty plugin column values, it is necessary to upgrade as follows.

  If you plan to upgrade using the data directory from your existing MySQL installation:

  1. Stop the old server

  2. Upgrade the MySQL binaries in place (replace the old binaries with the new ones)

  3. Restart the server with the --skip-grant-tables option to disable privilege checking

  4. Run mysql_upgrade to upgrade the system tables

  5. Restart the server normally (without --skip-grant-tables)

  If you plan to upgrade by reloading a dump file generated from your existing MySQL installation:

  1. To generate the dump file, run mysqldump without the --flush-privileges option

  2. Stop the old server

  3. Upgrade the MySQL binaries in place (replace the old binaries with the new ones)

  4. Restart the server with the --skip-grant-tables option to disable privilege checking

  5. Reload the dump file (mysql < dump_file)

  6. Run mysql_upgrade to upgrade the system tables

  7. Restart the server normally (without --skip-grant-tables)

  mysql_upgrade runs by default as the MySQL root user. For the preceding procedures, if the root password is expired when you run mysql_upgrade, you will see a message that your password is expired and that mysql_upgrade failed as a result. To correct this, reset the root password to unexpire it and run mysql_upgrade again:

  shell> mysql -u root -p
  Enter password: ****  <- enter root password here
  mysql> SET PASSWORD = PASSWORD('root-password');
  mysql> quit
  
  shell> mysql_upgrade -p
  Enter password: ****  <- enter root password here

  The password-resetting statement normally does not work if the server is started with --skip-grant-tables, but the first invocation of mysql_upgrade flushes the privileges, so when you run mysql, the statement is accepted. (WL #6982)

Compilation Notes

• Work was done to clean up the source code base, including: Removing unneeded CMake checks; removing unused macros from source files; reorganizing header files to reduce the number of dependencies and make them more modular, removing function declarations without definitions, replacing locally written functions with equivalent functions from industry-standard libraries.

Deprecation and Removal Notes

• Previously, program options could be specified in full or as any unambiguous prefix. For example, the --compress option could be given to mysqldump 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.

  (Bug #16996656, WL #6978)

• The deprecated thread_concurrency system variable has been removed. (Bug #16661195)

• The following items are deprecated and will be removed in a future MySQL release. Where alternatives are shown, applications should be updated to use them.

  • The ENCODE() and DECODE() functions. Consider using AES_ENCRYPT() and AES_DECRYPT() instead.

  • The INFORMATION_SCHEMA.PROFILING table. Use the Performance Schema instead; see MySQL Performance Schema.

  (Bug #16463921, WL #6984, WL #6802, WL #6978)

Diagnostics Notes

• Incompatible Change: Per the SQL standard, nondiagnostic statements should clear the diagnostics area when they begin executing. Previously, MySQL differed from this in that some nondiagnostic statements did not do this. MySQL now follows the SQL standard, which affects the content of the diagnostics area for some statements. Consequently, the result from statements such as SHOW WARNINGS that display the diagnostics area now differs somewhat:

  • The previous behavior: SHOW WARNINGS displays information about the conditions (errors, warnings, and notes) resulting from the most recent statement in the current session that generated messages. It shows nothing if the most recent statement used a table and generated no messages. (That is, statements that use a table but generate no messages clear the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

  • The new behavior: SHOW WARNINGS displays information about the conditions resulting from execution of the most recent nondiagnostic statement in the current session.

  The result from other diagnostic statements is affected similarly (SHOW ERRORS, GET DIAGNOSTICS).

  The following example demonstrates the difference in behavior.

  Previously:

  mysql> DROP TABLE test.no_such_table;
  ERROR 1051 (42S02): Unknown table 'test.no_such_table'
  mysql> SELECT @@warning_count;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW WARNINGS;
  +-------+------+------------------------------------+
  | Level | Code | Message                            |
  +-------+------+------------------------------------+
  | Error | 1051 | Unknown table 'test.no_such_table' |
  +-------+------+------------------------------------+
  1 row in set (0.00 sec)

  Here, the SELECT statement does not use tables and does not generate messages, so it does not change the diagnostics area. Consequently, SHOW WARNINGS output pertains to the DROP TABLE statement.

  Now:

  mysql> DROP TABLE test.no_such_table;
  ERROR 1051 (42S02): Unknown table 'test.no_such_table'
  mysql> SELECT @@warning_count;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW WARNINGS;
  Empty set (0.00 sec)

  Here, the SELECT statement clears the diagnostics area because it is a nondiagnostic statement. Consequently, SHOW WARNINGS output pertains to the SELECT statement (and is empty because the SELECT produces no messages).

  An implication of this change in diagnostics area handling is that if you expect to display the warning count as well as the list of messages, you should list the messages first because selecting the warning_count value clears the message list. Alternatively, use SHOW COUNT(*) WARNINGS to display the count; this is recognized as a diagnostic statement and does not clear the diagnostics area. Similar considerations apply to use of error_count.

  For compliance with the SQL standard, which states that diagnostics statements are not preparable, MySQL no longer supports the following as prepared statements:

  • SHOW WARNINGS, SHOW COUNT(*) WARNINGS

  • SHOW ERRORS, SHOW COUNT(*) ERRORS

  • Statements containing any reference to the warning_count or error_count system variable.

  In other words, those statements are now treated, in terms of preparability, the same as GET DIAGNOSTICS, which was already not preparable. (WL #5928)

Logging Notes

• Incompatible Change: Several changes have been made to provide more logging control and more informative log messages:

  • The log_error_verbosity system variable now controls verbosity of the server in writing error, warning, and note messages to the error log. Permitted values are 1 (error messages only), 2 (error and warning messages), 3 (error, warning, and note messages), with a default of 3.

    log_error_verbosity is preferred over, and should be used instead of, the older log_warnings system variable. See the description of log_warnings for information about how that variable relates to log_error_verbosity (Server System Variables). The log_warnings system variable and --log-warnings command-line option now are deprecated and will be removed in a future MySQL release.

    Note

    The effective default verbosity is different now. The previous default (log_warnings=1) corresponds to log_error_verbosity=2, but the default log_error_verbosity is 3. To achieve a logging level similar to the previous default, set log_error_verbosity=2.

  • Default server verbosity is less when invoked with the --bootstrap option (such as is done by mysql_install_db): Only errors are written during the installation process so that they are less likely to be overlooked by the installer.

  • The log_timestamps system variable has been introduced for control of the timestamp time zone of messages written to the error log, and of general query log and slow query log messages written to files. (It does not affect the time zone of general query log and slow query log messages written to log tables, but rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.)

    Note

    The default timestamp time zone is different now (UTC rather than the local system time zone). To restore the previous default, set log_timestamps=SYSTEM.

  • The format of timestamps has changed for messages written to the error log, and for general query log and slow query log messages written to files. Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC). In addition, for the general query log file, timestamps are included in every message, not just when the second changes.

    The format of timestamps has also changed for messages written to the general query log and slow query log tables (mysql.general_log, mysql.slow_log), which now include fractional seconds. (The column type for timestamps has changed from TIMESTAMP to TIMESTAMP(6).)

  • Previously, the ID included in error log messages was the mysqld process ID. Now the ID is that of the thread within mysqld responsible for writing the message. This is more informative with respect to which part of the server produced the message. It is also more consistent with general query log and slow query log messages, which include the connection thread ID.

  For information about log output destinations, see Selecting General Query Log and Slow Query Log Output Destinations. For information about specific logs, see The Error Log, The General Query Log, and The Slow Query Log. (WL #6661)

Performance Schema Notes

• The Performance Schema now provides tables that expose replication information. This is similar to the information available from the SHOW SLAVE STATUS statement, but representation in table form is more accessible and has usability benefits:

  • SHOW SLAVE STATUS output is useful for visual inspection, but not so much for programmatic use. By contrast, using the Performance Schema tables, information about slave status can be searched using general SELECT queries, including complex WHERE conditions, joins, and so forth.

  • Query results can be saved in tables for further analysis, or assigned to variables and thus used in stored procedures.

  • The replication tables provide better diagnostic information. For multithreaded slave operation, SHOW SLAVE STATUS reports all coordinator and worker thread errors using the Last_SQL_Errno and Last_SQL_Error fields, so only the most recent of those errors is visible and information can be lost. The replication tables store errors on a per-thread basis without loss of information.

  • The last seen transaction is visible in the replication tables on a per-worker basis. This is information not avilable from SHOW SLAVE STATUS.

  • Developers familiar with the Performance Schema interface can extend the replication tables to provide additional information by adding rows to the tables.

  These tables provide replication information:

  • replication_connection_configuration and replication_connection_status indicate the configuration parameters used by the slave for connecting to the master and the status of the connection.

  • replication_execute_configuration and replication_execute_status indicate, for aspects of transaction execution on the slave not specific to any given thread, the configuration parameters and the current execution status.

  • replication_execute_status_by_coordinator and replication_execute_status_by_worker contain thread-specific transaction execution information, either about the SQL thread (for a single-threaded slave), or about the coordinator and worker threads (for a multithreaded slave).

  If the slave is multithreaded, the SQL thread is the coordinator for worker threads. In this case, the Last_SQL_Error field of SHOW SLAVE STATUS output now shows exactly what the Last_Error_Message column in the replication_execute_status_by_coordinator Performance Schema table shows. The field value is modified to suggest that there may be more failures in the other worker threads which can be seen in the replication_execute_status_by_worker table that shows each worker thread's status.

  For more information, see Performance Schema Replication Tables.

  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. (WL #3656)

• The Performance Schema now instruments stored program execution and aggregates statistics for them. This includes stored procedures, stored functions, triggers, and Event Scheduler events.

  These specific changes were implemented:

  • The setup_instruments table has new instruments. The statement/scheduler/event instrument tracks all events executed by the Event Scheduler. Instruments with names of the form statement/sp/program_instruction track internal instructions executed by stored programs.

  • The setup_objects table OBJECT_TYPE column now permits values of 'EVENT', 'FUNCTION', 'PROCEDURE', 'TABLE', or 'TRIGGER', not just 'TABLE'.

  • Statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) now have a NESTING_LEVEL column that indicates the event nesting level.

  • The performance_schema_max_program_instances and performance_schema_max_statement_stack system variables configure the maximum number of stored programs and the maximum depth of nested stored program calls for which the Performance Schema maintains statistics.

  • The Performance_schema_program_lost and Performance_schema_nested_statement_lost status variables indicate the number of stored programs for which statistics were lost, and the number of stored program statements for which statistics were lost.

  • The events_statements_summary_by_program summary table aggregates statement events per stored program.

  For more information, see Event Pre-Filtering, and Statement Summary Tables.

  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. (WL #5766)

• The Performance Schema now instruments memory usage and aggregates memory usage statistics, detailed by these factors:

  • Type of memory used (various caches, internal buffers, and so forth)

  • Thread, account, user, host indirectly performing the memory operation

  The Performance Schema instruments the following aspects of memory use

  • Memory sizes used

  • Operation counts

  • Low and high water marks

  Memory sizes help to understand or tune the memory consumption of a server.

  Operation counts help to understand or tune the overall pressure the server is putting on the memory allocator, which has an impact on performance. Allocating a single byte one million times is not the same as allocating one million bytes a single time; tracking both sizes and counts can expose the difference.

  Low and high water marks are critical to detect workload spikes, overall workload stability, and possible memory leaks.

  These specific changes were implemented:

  • The setup_instruments table now has memory instruments. These have names of the form memory/component/instrument_name. Memory instrumentation is disabled by default.

  • The performance_schema_max_memory_classes system variable configures the maximum number of memory instruments.

  • The Performance_schema_memory_classes_lost status variable indicates the number of times a memory instrument could not be loaded.

  • Several summary tables aggregate memory-related events.

  For more information, see Memory Summary Tables.

  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. (WL #3249)

RPM Notes

• It was not possible to upgrade a community RPM to a commercial RPM using rpm -uvh or yum localupdate. To deal with this, the RPM spec file has been updated in MySQL 5.7.2, which has the following consequences:

  • For a non-upgrade installation (no existing MySQL version installed), it possible to install MySQL using yum.

  • For upgrades, it is necessary to clean up any earlier MySQL installations. In effect, the update is performed by removing the old installations and installing the new one.

  Additional details follow.

  For a non-upgrade installation of MySQL 5.7.2, it is possible to install using yum:

  shell> yum install MySQL-server-NEWVERSION.glibc23.i386.rpm

  For upgrades to MySQL 5.7.2, the upgrade is performed by removing the old installation and installing the new one. To do this, use the following procedure:

  1. Remove the existing 5.7.X installation. OLDVERSION is the version to remove.

     shell> rpm -e MySQL-server-OLDVERSION.glibc23.i386.rpm

     Repeat this step for all installed MySQL RPMs.

  2. Install the new version. NEWVERSION is the version to install.

     shell> rpm -ivh MySQL-server-NEWVERSION.glibc23.i386.rpm

  Alternatively, the removal and installation can be done using yum:

  shell> yum remove MySQL-server-OLDVERSION.glibc23.i386.rpm
  shell> yum install MySQL-server-NEWVERSION.glibc23.i386.rpm

  (Bug #16445097, Bug #16445125, Bug #16587285)

Security Notes

• Platform availability, usability, and security of mysql_secure_installation has been improved. Previously, this program was a shell script available for Unix and Unix-like systems. It has been converted to a binary executable program (written in C++) that is available on all platforms. Implementation as a C++ program permits mysql_secure_installation to connect directly to the MySQL server using the client/server protocol, rather than by invoking mysql to do so and communicating with mysql using temporary files.

  This reimplementation of mysql_secure_installation is feature-compatible with previous versions, but the following usability improvements have been made:

  • The validate_password plugin can be used for password strength checking.

  • Standard MySQL options such as --host and --port are supported on the command line and in option files.

  For more information, see mysql_secure_installation — Improve MySQL Installation Security. For more information about validate_password, see The Password Validation Plugin. (WL #6441)

Semisynchronous Replication Notes

• Replication: Semisynchronous replication master servers now use a different wait point by default in communicating wih slaves. This is the point at which the master waits for acknowledgment of transaction receipt by a slave before returning a status to the client that committed the transaction. The wait point is controlled by the new rpl_semi_sync_master_wait_point system variable. These values are permitted:

  • AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

  • AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

    For older versions of MySQL, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT.

  The replication characteristics of these settings differ as follows:

  • With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.

    In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.

  • With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

    If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

  The new wait point is a behavior change, but requires no reconfiguration. The change does introduce a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up. (WL #6355)

Trigger Notes

• 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. Along with that change, several additional modifications were made:

  • By default, triggers for each combination of trigger event and action time execute in the order they were created. To make it possible to specify trigger activation order, CREATE TRIGGER now supports FOLLOWS and PRECEDES clauses. Each clause takes the name of an existing trigger that has the same trigger event and action time.

  • The ACTION_ORDER column in the INFORMATION_SCHEMA.TRIGGERS table is no longer 0 but an integer greater than zero that indicates the order in which triggers activate.

  • Creation time for triggers is now maintained, as a TIMESTAMP(2) value (with a fractional part in hundredths of seconds):

    • The CREATED column in the TRIGGERS table is no longer NULL, for triggers created as of MySQL 5.7.2.

    • The same is true for the Created column of SHOW TRIGGERS output, and for the (new) Created column of SHOW CREATE TRIGGER output.

    • The tbl_name.TRG file that stores trigger information for table tbl_name now contains a created line with trigger creation times.

    For additional information, see Using Triggers, CREATE TRIGGER Statement, SHOW CREATE TRIGGER Statement, SHOW TRIGGERS Statement, The INFORMATION_SCHEMA TRIGGERS Table, and Table Trigger Storage.

  • If run against a table that has triggers, mysql_upgrade and CHECK TABLE ... FOR UPGRADE display this warning for each trigger created before MySQL 5.7.2:

    Trigger db_name.tbl_name.trigger_name does not have CREATED attribute.

    The warning is informational only. No change is made to the trigger.

  These changes have implications for backups, upgrades, and downgrades, as described following. For brevity, “multiple triggers” here is shorthand for “multiple triggers that have the same trigger event and action time.”

  Backup and restore. mysqldump dumps triggers in activation order so that when the dump file is reloaded, triggers are re-created in the same activation order. However, if a mysqldump dump file contains multiple triggers for a table that have the same trigger event and action time, an error occurs for attempts to load the dump file into an older server that does not support multiple triggers. (See the downgrading notes for a workaround; you can convert triggers to be compatible with older servers.)

  Upgrades. Suppose that you upgrade an old server that does not support multiple triggers to MySQL 5.7.2 or newer. If the new server is a replication master and has old slaves that do not support multiple triggers, an error occurs on those slaves if a trigger is created on the master for a table that already has a trigger with the same trigger event and action time. To avoid this problem, upgrade the slaves first, then upgrade the master.

  Downgrades. If you downgrade a server that supports multiple triggers to an older version that does not, the downgrade has these effects:

  • For each table that has triggers, all trigger definitions remain in the .TRG file for the table. However, if there are multiple triggers with the same trigger event and action time, the server executes only one of them when the trigger event occurs. For information about .TRG files, see Table Trigger Storage.

  • If triggers for the table are added or dropped subsequent to the downgrade, the server rewrites the table's .TRG file. The rewritten file retains only one trigger per combination of trigger event and action time; the others are lost.

  To avoid these problems, modify your triggers before downgrading. For each table that has multiple triggers per combination of trigger event and action time, convert each such set of triggers to a single trigger as follows:

  1. For each trigger, create a stored routine that contains all the code in the trigger. Values accessed using NEW and OLD can be passed to the routine using parameters. If the trigger needs a single result value from the code, you can put the code in a stored function and have the function return the value. If the trigger needs multiple result values from the code, you can put the code in a stored procedure and return the values using OUT parameters.

  2. Drop all triggers for the table.

  3. Create one new trigger for the table that invokes the stored routines just created. The effect for this trigger is thus the same as the multiple triggers it replaces.

  (WL #3253)

  References: See also: Bug #28803, Bug #11746800, Bug #37567, Bug #11748861.

Functionality Added or Changed

• Incompatible Change: Previously, the Performance Schema statement instrumentation did not include statements executed on a slave replication server. To address this, a new abstract instrument, statement/rpl/relay_log, has been added to the setup_instruments table. This instrument is used during the early stages of replicated statement classification before the exact statement type is known. (Bug #16750433, Bug #17271055)

• Incompatible Change: Previously, the main loop responsible for accepting client connections also performed initialization of data structures related to each connection. These initialization tasks now are delegated to worker threads to minimize work done by the accept loop and maximize connection acceptance rate.

  As a result of this change, the bind_address, thread_cache_size, and thread_handling system variables are no longer visible to the embedded server (libmysqld). Similarly, the Slow_launch_threads and Threads_cached status variables are not meaningful in the embedded server. These variables are no longer visible within the embedded server and embedded applications that rely on these variables should be modified accordingly. (Bug #62288, Bug #12951536, Bug #62284, Bug #12951595, Bug #62283, Bug #12951605, WL #6606)

• Incompatible Change: The unused --basedir and --datadir options for mysql_upgrade have been removed. (WL #7010)

• Important Change; Partitioning: It is now possible to check and to repair partitions which contain duplicate key violations. This is implemented by allowing the IGNORE keyword in ALTER TABLE statements using the CHECK PARTITION or REPAIR PARTITION option. The keyword has the following effects on the behavior of these statements:

  • ALTER IGNORE TABLE ... REPAIR PARITION removes all rows that cannot be moved due to the presence of duplicate keys.

  • ALTER IGNORE TABLE ... CHECK PARTITION reports the contents of all columns in the partitioning expression for each row.

  Note

  Support for the IGNORE keyword is removed in MySQL 5.7.4.

  (Bug #16900947)

• Important Change; Replication: By default, when promoting integers from a smaller type on the master to a larger type on the slave (for example, from a SMALLINT column on the master to a BIGINT column on the slave), the promoted values are treated as though they are signed. Now in such cases it is possible to modify or override this behavior using one or both of ALL_SIGNED, ALL_UNSIGNED in the set of values specified for the slave_type_conversions server system variable. For more information, see Row-based replication: attribute promotion and demotion, as well as the description of the variable. (Bug #15831300)

• Performance; InnoDB: Multi-version concurrency control (MVCC) in InnoDB requires that each transaction using MVCC be assigned a read view. To improve InnoDB read-only and read-write performance, read view creation has been optimized by reducing mutex contention. (WL #6578)

• Performance; InnoDB: An sx-lock, a new type of rw-lock, optimizes concurrency and improves scalability for read-write workloads. sx-locks reduce locking contention for B-tree index operations by providing write access while permitting inconsistent reads by other threads. (WL #6326, WL #6363)

• Performance; InnoDB: Memory for transaction instances (trx_t) is now allocated in configurable sized blocks that are a multiple of transaction instance size. Transaction instances are also placed in a priority queue and ordered by their address in memory so that when instances are allocated from the pool, they are close together. This enhancement reduces the cost incurred by iterating over transactions instances when allocating instances from the pool. (WL #6906)

• InnoDB: innochecksum functionality has been enhanced with new options and extended capabilities. See innochecksum — Offline InnoDB File Checksum Utility. (Bug #16945722, WL #6045)

• InnoDB: A new CMake option, WITH_INNODB_EXTRA_DEBUG, has been added that enables additional InnoDB debug checks. WITH_INNODB_EXTRA_DEBUG can only be enabled when the WITH_DEBUG option is also enabled. (Bug #16821155)

• InnoDB: When building MySQL from source, you can now define the type of mutex used by InnoDB using the new MUTEX_TYPE CMake option. (WL #6044)

• InnoDB: Refactored mutex code makes selecting the appropriate mutex easier and allows multiple mutex types to be combined in the same instance. The refactored code also removes the distinction between fast_mutex_t and home brew ib_mutex_t types, implements a common interface for both mutex types, and allows new mutex types to be added in the future. Additionally, mutex code is decoupled from InnoDB code so that it can be used as a library, and a “PolicyMutex” interface has been introduced. The new interface uses static inheritance (templates) for mutex implementation making it easier to define policies and customize mutex behavior. (WL #6044)

• InnoDB: Buffer pool list scans and related batch processing have been optimized to reduce scan complexity and the number of pages scanned. (WL #7047)

• InnoDB: InnoDB buffer pool dump and load operations have been 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 by InnoDB background tasks, InnoDB attempts to limit the number of buffer pool load operations per second using the innodb_io_capacity setting. (WL #6504)

• InnoDB: DML operations (INSERT, UPDATE, DELETE) on temporary tables were optimized by disabling redo logging, locking, and change buffering. Redo logging is unnecessary because the lifetime of a temporary table is bounded by the lifetime of the server process, and locking is not required because a temporary table is only visible to the session that created it. The resulting reduction in temporary table size makes change buffering unnecessary, as temporary tables are now more likely to reside in memory. (WL #6470)

• InnoDB: The limit on concurrent data-modifying transactions is now 96 * 1023 transactions that generate undo records. As of MySQL 5.7.2, 32 of 128 rollback segments are allocated to non-redo logs for transactions that modify temporary tables and related objects. This reduces the maximum number of concurrent data-modifying transactions from 128K to 96K. The 96K limit assumes that transactions do not modify temporary tables. If all data-modifying transactions also modify temporary tables, the limit would be 32K concurrent transactions. (WL #6915)

• InnoDB: MySQL 5.7.2 introduces a new type of undo log for both normal and compressed temporary tables and related objects. The new type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. The new undo log resides in the temporary tablespace. The default temporary tablespace file, ibtmp1, is located in the data directory by default and is always recreated on server startup. A user defined location for the temporary tablespace file can be specified by setting innodb_temp_data_file_path. For more information, see Undo Logs. (WL #6915)

• InnoDB: Read-only transactions will no longer be assigned a transaction ID. Conversely, an ID will only be assigned if a transaction is explicitly tagged as “read-write”, if a transaction has acquired an X or IX lock on a table, or if a transaction is a read-only transaction writing to a temporary table. All other transactions are considered “read-only” and are not assigned an ID. Furthermore, read-only transactions are not tagged as “read-only” unless they are explicitly started with START TRANSACTION READ ONLY. For transactions without transaction IDs, SHOW ENGINE INNODB STATUS prints an identifier that is unique but only within the context of the SHOW ENGINE INNODB STATUS invocation. (WL #6047)

• InnoDB: SELECT COUNT(*) FROM t statements now invoke a single handler call to the storage engine to scan the clustered index and return the row count to the Optimizer. Previously, a row count was typically performed by traversing a smaller secondary index and invoking a handler call for each record. A single handler call to the storage engine to count rows in the clustered index improves SELECT COUNT(*) FROM t performance in most cases. For more information, see InnoDB Restrictions and Limitations. (WL #6742)

• InnoDB: Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. (WL #6658)

• InnoDB: The process for converting a transaction's implicit lock to an explicit lock has been optimized to improve performance. The optimization reduces lock_sys_t::mutex contention. (WL #6899)

• InnoDB: A number of internal debug flags in the InnoDB code could only be set at compilation time or from a debugger. As a result, a significant amount of diagnostic information was unused. This enhancement replaces internal debug flags with DBUG labels so that the DBUG package can be used and printouts from various InnoDB subsystems can be enabled using the mysqld --debug command line option. See the Debugging a MySQL Server section for information about configuring MySQL for debugging, creating trace files, and using the mysqld --debug option. (WL #6947)

• Partitioning: The following operations are now supported for individual subpartitions as well as partitions: ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE (see ALTER TABLE Partition Operations).

  Note

  This fix also allows the use of REBUILD with individual subpartitions, even though this is not actually supported by MySQL, and has no effect. This issue is addressed in MySQL 5.7.5 and later by disallowing REBUILD with subpartitions in ALTER TABLE statements.

  (Bug #14028340, Bug #65184)

  References: See also: Bug #19075411, Bug #73130.

• Replication: Previously, transactions could be applied in parallel only if they did not touch the same database. However, the MySQL Server uses a lock-based scheduler, which means that it should be possible to execute in parallel all uncommitted replication threads already in the prepare phase, without violating consistency. Such parallel execution can now be enabled on the slave by starting the slave mysqld with slave_parallel_type=LOGICAL_CLOCK or, if mysqld is already started, by setting the value of the global slave_parallel_type value to 'LOGICAL_CLOCK' on a stopped slave.

  When this feature is enabled, each transaction is marked with a logical timestamp. This timestamp identifies the last transaction committed at the time that the current transaction entered the prepare stage, and all transactions having the same timestamp can execute in parallel.

  To disable this feature without restarting, stop the slave using STOP SLAVE (if it is running as a slave), issue SET @global-slave_parallel_type='DATABASE', then issue START SLAVE when you want the slave to resume. You can also disable the feature by restarting the slave mysqld without setting slave_parallel_type, or by setting it explicitly to DATABASE. When parallel execution of preapred transactions is disabled, the slave follows the old behavior and applies in parallel only those transactions that do not cause changes in the same database. (WL #6314)

• Replication: The master dump thread was refactored to reduce lock contention and improve master throughput. Previously, the dump thread took a lock on the binary log whenever reading an event; now the lock is held only while reading the position at the end of the last successfully written event. This means that multiple dump threads can now read concurrently from the binary log file, and that dump threads can read while clients write to the binary log. (WL #5721)

• Support for LinuxThreads has been removed from the source code. LinuxThreads was superseded by NPTL in Linux 2.6. (Bug #17007529, Bug #72888, Bug #18913935)

• Support for building Apple universal binaries to support PowerPC has been removed from the source code. (Bug #16959103)

• CMake no longer checks for memmove() or memcpy() because they are standard C library functions. Also, implementation of the bmove_upp() function was replaced with calls to memmove(), which may have positive performance implications. (Bug #16839824)

• The C API libmysqlclient shared-library .so files now have version 18.1.0 (up from version 18.0.0 used in MySQL 5.5). 18.1.0 can be used as a replacement for 18.0.0. (Bug #16809055, Bug #59106, Bug #12407476)

• Use of DYNAMIC_ARRAY was reduced, which improves performance of certain range queries by 3-4%. (Bug #16736776, Bug #17030235)

• mysqladmin now supports a --show-warnings option to display warnings resulting from execution of statements sent to the server. (Bug #16517756)

• mysql_upgrade now verifies that the server version matches the version against which it was compiled, and exits if there is a mismatch. In addiion, a --version-check option permits specifying whether to enable version checking (the default), or disable checking if given as --skip-version-checking. (Bug #16500013)

• Invoking CMake with -DWITH_AUTHENTICATION_PAM=1 now causes the build to fail (rather than issue only a warning) if the PAM plugin cannot be built. (Bug #14554639)

• In batch mode, mysql formatted result status messages such as “"Query OK, 1 row affected"” but did not print them. Now these messages are not formatted. (Bug #69486, Bug #16971432)

• Several inefficiencies were corrected:

  • A loop in Item_in_subselect::single_value_transformer() could execute too many times.

  • The myisamchk(), my_test_if_sort_rep(), and recreate_table() functions in MyISAM code could execute too many times.

  Thanks to Po-Chun Chang for the patches to correct these issues. (Bug #69138, Bug #16764131, Bug #69117, Bug #16751784, Bug #69561, Bug #17007268, Bug #69553, Bug #17001703)

• Plugins can now define and expose floating-point system variables of type double using the MYSQL_SYSVAR_DOUBLE() and MYSQL_THDVAR_DOUBLE() accessor macros. See Client Plugin Descriptors. (Bug #68121, Bug #16194302)

• MySQL now supports the use of protocol trace plugins: client-side plugins that implement tracing of communication between a client and the server that takes place using the client/server protocol. Protocol trace plugins use the client plugin API.

  In MySQL source distributions, a test protocol trace plugin is implemented in the test_trace_plugin.cc file in the libmysql directory. This can be examined as a guide to writing other protocol trace plugins.

  For more information, see Writing Plugins; in particular, Writing Protocol Trace Plugins. (WL #6226)

• To make it easier to see the difference between good and bad execution plans, JSON-format EXPLAIN output now includes this additional cost information:

  • query_cost: The total cost of a query block, whether a top-level query or subquery. For a top-level SELECT, this should be equal to the Last_query_cost status variable.

  • sort_cost: The cost of the first sorting operation (GROUP BY or ORDER BY) where and if filesort is used.

  • read_cost: The cost of reading data from each table used in the query block (that is, access method cost).

  • eval_cost: The cost of condition evaluation for each table in the query block.

  • prefix_cost: The cost of executing prefix join in the query block; that is, the cost of joining tables of the query block from the first one to the one (and including it) for which the value is given.

  • data_read_per_join: The estimated amount of data processed by the handler interface per query or subquery execution. This is essentially record width * number of read records.

  • rows_produced_per_join/ rows_examined_per_join: The estimated number of records from the table (per table from the query block) produced or examined per single query block execution.

  • used_columns: The list of columns from the table (per each table in the query block) used for either read or write in the query.

  This cost information is not displayed for INFORMATION_SCHEMA tables. (WL #6510)

• EXPLAIN can now be used to obtain the execution plan for an explainable statement executing in a named connection:

  EXPLAIN [options] FOR CONNECTION connection_id;

  For example, if you are running a statement in one session that is taking a long time to complete, using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay and help you optimize the statement.

  connection_id is the connection identifier, as obtained from the INFORMATION_SCHEMA PROCESSLIST table or the SHOW PROCESSLIST statement. If you have the PROCESS privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections.

  Changes in EXPLAIN output:

  • In the output from EXPLAIN FOR CONNECTION, an Extra value of Plan isn't ready yet means that the optimizer has not finished creating the execution plan for the statement executing in the named connection. (For JSON-format output, this is indicated by planned: false.)

  • In the output from any EXPLAIN used to obtain the execution plan for non-SELECT statements, the select_type value displays the statement type for affected tables. For example, select_type is DELETE for DELETE statements.

  A new status variable, Com_explain_other, indicates how many EXPLAIN FOR CONNECTION statements have been executed.

  For more information, see EXPLAIN Statement, and Obtaining Execution Plan Information for a Named Connection. (WL #6369)

• Semijoin LooseScan strategy now can use ref access and applies to a wider range of queries. (WL #6739)

Bugs Fixed

• Incompatible Change: When used for an existing MySQL account, the GRANT statement could produce unexpected results if it included an IDENTIFIED WITH clause that named an authentication plug differing from the plugin named in the corresponding mysql.user table row.

  Because IDENTIFIED WITH is intended only for GRANT statements that create a new user, it is now prohibited if the named account already exists. (Bug #16083276)

• Incompatible Change: It is possible for a column DEFAULT value to be valid for the sql_mode value at table-creation time but invalid for the sql_mode value when rows are inserted or updated. Example:

  SET sql_mode = '';
  CREATE TABLE t (d DATE DEFAULT 0);
  SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';
  INSERT INTO t (d) VALUES(DEFAULT);

  In this case, 0 should be accepted for the CREATE TABLE but rejected for the INSERT. However, previously the server did not evaluate DEFAULT values used for inserts or updates against the current sql_mode. In the example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.

  The server now applies the proper sql_mode checks to generate a warning or error at insert or update time.

  A resulting incompatibility for replication if you use statement-based logging (binlog_format=STATEMENT) is that if a slave is upgraded, a nonupgraded master will execute the preceding example without error, whereas the INSERT will fail on the slave and replication will stop.

  To deal with this, stop all new statements on the master and wait until the slaves catch up. Then upgrade the slaves followed by the master. Alternatively, if you cannot stop new statements, temporarily change to row-based logging on the master (binlog_format=ROW) and wait until all slaves have processed all binary logs produced up to the point of this change. Then upgrade the slaves followed by the master and change the master back to statement-based logging. (Bug #68041, Bug #16078943)

• Important Change; Performance; InnoDB: InnoDB failed to open a tablespace that has multiple data files. This removes the known limitation that was in MySQL Server 5.6.12. (Bug #17033706, Bug #69623)

• Important Change; Replication: When the server was running with --binlog-ignore-db and SELECT DATABASE() returned NULL (that is, there was no currently selected database), statements using fully qualified table names in dbname.tblname format were not written to the binary log. This was because the lack of a currently selected database in such cases was treated as a match for any possible ignore option rather than for no such option; this meant that these statements were always ignored.

  Now, if there is no current database, a statement using fully qualified table names is always written to the binary log. (Bug #11829838, Bug #60188)

• Performance; InnoDB: A code regression introduced in MySQL 5.6 negatively impacted DROP TABLE and ALTER TABLE performance. This could cause a performance drop between MySQL Server 5.5.x and 5.6.x. (Bug #16864741, Bug #69316)

• Performance; InnoDB: When innodb_thread_concurrency is set to a nonzero value, there was a possibility that all innodb_concurrency_tickets would be released after each row was read, resulting in a concurrency check after each read. This could impact performance of all queries. One symptom could be higher system CPU usage. We strongly recommend that you upgrade to MySQL Server 5.6.13 if you use this setting. This could cause a performance drop between MySQL Server 5.5.x and 5.6.x. (Bug #68869, Bug #16622478)

• InnoDB; Partitioning: Joins involving partitioned InnoDB tables having one or more BLOB columns were not always handled correctly. The BLOB column or columns were not required to be join columns, or otherwise to be named or referenced in the statement containing the join, for this issue to occur. (Bug #16367691)

• InnoDB; Partitioning: Following any query on the INFORMATION_SCHEMA.PARTITIONS table, InnoDB index statistics as shown in the output of statements such as SELECT * FROM INFORMATION_SCHEMA.STATISTICS were read from the last partition, instead of from the partition containing the greatest number of rows. (Bug #11766851, Bug #60071)

  References: See also: Bug #16882435, Bug #69179.

• InnoDB; Replication: Trying to update a column, previously set to NULL, of an InnoDB table with no primary key caused replication to fail on the slave with Can't find record in 'table'.

  Note

  This issue was inadvertently reintroduced in MySQL 5.6.6, and fixed again in MySQL 5.6.12.

  (Bug #11766865, Bug #60091)

  References: See also: Bug #16566658.

• InnoDB: In Windows 64-bit debug builds, read view COPY_TRX_IDS would report a “vector subscript out of range” error to standard error output. (Bug #17320056)

• InnoDB: When logging the delete-marking of a record during online ALTER TABLE...ADD PRIMARY KEY, InnoDB writes the transaction ID to the log as it was before the deletion or delete-marking of the record. When doing this, InnoDB would overwrite the DB_TRX_ID field in the original table, which could result in locking issues. (Bug #17316731)

• InnoDB: An assertion failure would occur while searching an index tree and traversing multiple levels where a block is accessed or pinned at each level. (Bug #17315967)

• InnoDB: The row_sel_sec_rec_is_for_clust_rec function would incorrectly prepare to compare a NULL column prefix in a secondary index with a non-NULL column in a clustered index. (Bug #17312846)

• InnoDB: An incorrect purge would occur when rolling back an update to a delete-marked record. (Bug #17302896)

• InnoDB: The assertion ut_ad(oldest_lsn <= cur_lsn) in file buf0flu.cc failed because the current max LSN would be retrieved from the buffer pool before the oldest LSN. (Bug #17252421)

• InnoDB: InnoDB memcached add and set operations would perform more slowly than SQL INSERT operations. (Bug #17214191)

• InnoDB: As commented in log0log.h, old_lsn and old_buf_free should only be compiled when UNIV_LOG_DEBUG is enabled. (Bug #17160270, Bug #69724)

• InnoDB: When started in read-only mode, InnoDB would assert on a savepoint. (Bug #17086428)

• InnoDB: Before dropping an index, a check is performed to ensure the index root page is free. If the index root page is free, dropping activity is avoided. A transaction would be initialized before the check is performed. If the check evaluated to true, the initialized transaction would be left in a dangling state. (Bug #17076822)

• InnoDB: Adding a foreign key with a constraint name that included the string “_ibfk_” caused InnoDB to create a duplicate constraint with a generated internal name. The generated internal name could also collide with an existing user-defined constraint of the same name, causing a duplicate key error. (Bug #17076737, Bug #69693, Bug #17076718, Bug #69707)

• InnoDB: An InnoDB monitor test would raise an assertion in ha_innodb.cc due to a mutex conflict. (Bug #17027249)

• InnoDB: In debug builds, the trx_sys->rw_max_trx_id variable would sometimes be reversed resulting in an inconsistent CLUST_INDEX_SIZE value. (Bug #17026780)

• InnoDB: The ha_innobase::clone function would incorrectly assert that a thread cannot clone a table handler that is used by another thread, and that the original table handler and the cloned table handler must belong to the same transaction. The incorrect assertions have been removed. (Bug #17001980)

• InnoDB: A regression introduced in the fix for Bug #14606334 would cause crashes on startup during crash recovery. (Bug #16996584)

• InnoDB: Rolling back an INSERT after a failed BLOB write would result in an assertion failure. The assertion has been modified to allow NULL BLOB pointers if an error occurs during a BLOB write. (Bug #16971045)

• InnoDB: SHOW ENGINE INNODB STATUS output referenced a thread in hex format (example: thread handle 0x880), whereas the same thread was referenced in the SHOW ENGINE INNODB STATUS transaction list in decimal format (example: thread id 2176). (Bug #16934269, Bug #69437)

• InnoDB: A full-text search using the IN BOOLEAN MODE modifier would result in an assertion failure. (Bug #16927092)

  References: This issue is a regression of: Bug #16516193.

• InnoDB: When CHECK TABLE found a secondary index that contained the wrong number of entries, it would report an error but not mark the index as corrupt. CHECK TABLE now marks the index as corrupt when this error is encountered, but only the index is marked as corrupt, not the table. As a result, only the index becomes unusable until it is dropped and rebuilt. The table is unaffected. (Bug #16914007)

• InnoDB: InnoDB would attempt to gather statistics on partially created indexes. (Bug #16907783)

• InnoDB: To avoid namespace clashes, usage of 'using namespace std' has been removed from InnoDB. (Bug #16899560)

• InnoDB: When dropping all indexes on a column with multiple indexes, InnoDB failed to block a DROP INDEX operation when a foreign key constraint requires an index. (Bug #16896810)

• InnoDB: Optimized explicit record locking routines. (Bug #16880127)

• InnoDB: The server would crash during a memcached set operation. The failure was due to a padded length value for a utf8 CHAR column. During a memcached update operation, a field from an old tuple would be copied with a data length that was less than the padded utf8 CHAR column value. This fix ensures that old tuples are not copied. Instead, a new tuple is created each time. (Bug #16875543)

• InnoDB: innochecksum would ignore the return value of fwrite which could result in an error or generate warnings and compilation errors when WITH_INNODB_EXTRA_DEBUG CMake is enabled. (Bug #16872677)

• InnoDB: An assertion failure would occur in file row0log.cc on ROW_FORMAT=REDUNDANT tables that contained an unexpected but valid data directory flag. (Bug #16863098)

• InnoDB: An assertion in row0mysql.cc, which ensures that the dictionary operation lock is not taken recursively, failed. (Bug #16862290)

• InnoDB: The two INFORMATION_SCHEMA tables for the InnoDB buffer pool could show an invalid page type for read-fixed blocks. This fix will show the unknown page type for blocks that are I/O-fixed for reading. (Bug #16859867)

• InnoDB: InnoDB record comparison functions have been simplified and optimized. (Bug #16852278)

• InnoDB: Removed invalid compilation warning messages that appeared when compiling the InnoDB memcached plugin. (Bug #16816824)

• InnoDB: During an insert buffer merge, InnoDB would invoke lock_rec_restore_from_page_infimum() on a potentially invalid record pointer. (Bug #16806366)

• InnoDB: The innodb_rwlock_x_spin_waits item in the INFORMATION_SCHEMA.INNODB_METRICS table would show the same value as the innodb_rwlock_x_os_waits item. (Bug #16798175)

• InnoDB: The trx_tables_locked counter in INFORMATION_SCHEMA.INNODB_TRX would not account for all tables with locks. (Bug #16793724)

• InnoDB: This patch removes the UNIV_INTERN function, which was introduced in MySQL 5.1 to help replace static linking in InnoDB with the shared object plugin. UNIV_INTERN is no longer required. (Bug #16781511)

• InnoDB: In debug builds, an online ALTER TABLE operation that performed a full table copy would raise an assertion. The assertion was due to a race condition that would occur during BLOB retrieval, when applying the table modification log to any log block except for the very last one. This fix modifies row_log_table_apply_convert_mrec() to ensure that an index B-tree lock is acquired to protect the access to log->blobs and the BLOB page. (Bug #16774118)

• InnoDB: In debug builds, an assertion could occur in OPT_CHECK_ORDER_BY when using binary directly in a search string, as binary may include NULL bytes and other non-meaningful characters. This fix will remove non-meaningful characters before the search is run. (Bug #16766016)

• InnoDB: The page_zip_validate() debug function, which is enabled when UNIV_ZIP_DEBUG is defined at compilation time, invokes page_zip_decompress(), which in turn would update some compression statistics. This would cause some mysql-test-run tests to fail. (Bug #16759605)

• InnoDB: Valgrind testing returned memory leak errors which resulted from a regression introduced by the fix for Bug #11753153. The dict_create_add_foreign_to_dictionary function would call pars_info_create but failed to call pars_info_free. (Bug #16754901)

• InnoDB: When the function trx_rollback_or_clean_recovered() rolls back or cleans up transactions during a crash recovery, it removes the trx objects from the trx_sys list without freeing up the memory used by those objects. To prevent a memory leak, this fix adds trx_free_for_background() calls to trx_rollback_resurrected(), the function that removes the trx objects. (Bug #16754776)

• InnoDB: A memory leak would occur in dict_check_tablespaces_and_store_max_id() when space_id is equal to zero. (Bug #16737332)

• InnoDB: The page_zip_validate() consistency check failed after compressing a page, in page_zip_compress(). This problem was caused by page_zip_decompress(), which failed to set heap_no correctly when a record contained no user data bytes. A record with no user data bytes occurs when, for example, a primary key is an empty string and all secondary index fields are NULL or an empty string. (Bug #16736929)

• InnoDB: A missing comma in SHOW STATUS output would break MySQL Enterprise Monitor parsing. (Bug #16723686)

• InnoDB: This patch is a code cleanup which may provide a minor performance improvement when keys are not used on columns and when using the default latin1_swedish_ci collation. (Bug #16723431)

• InnoDB: Some characters in the identifier for a foreign key constraint are modified during table exports. (Bug #16722314, Bug #69062)

• InnoDB: After a clean shutdown, InnoDB does not check .ibd file headers at startup. As a result, in a crash recovery scenario, InnoDB could load a corrupted tablespace file. This fix implements consistency and status checks to avoid loading corrupted files. (Bug #16720368)

• InnoDB: A regression introduced with the fix for Bug #11762038 would cause InnoDB to raise an incorrect error message. The message stated that, “InnoDB cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20”. The error message would occur when killing connections reading from InnoDB tables that did not have foreign key constraints. (Bug #16710923)

  References: This issue is a regression of: Bug #11762038.

• InnoDB: Stale InnoDB memcached connections would result in a memory leak. (Bug #16707516, Bug #68530)

• InnoDB: In debug builds, an assertion failure would occur if innodb_log_group_home_dir does not exist. Instead of an assertion, InnoDB now aborts with an error message if innodb_log_group_home_dir does not exist. (Bug #16691130, Bug #69000)

• InnoDB: An existing full-text index would become invalid after running ALTER TABLE ADD FULLTEXT due to an unsynchronized full-text cache. (Bug #16662990, Bug #17373659)

• InnoDB: An INSERT into a temporary table resulted in the following assert: ASSERT ID > 0 IN TRX_WRITE_TRX_ID(). This fix corrects conditions for moving a transaction from a read-only list to a read-write list when the server is running in read-only mode. (Bug #16660575)

• InnoDB: Shutting down and restarting InnoDB with --innodb-force-recovery set to 3 or greater (4, 5, or 6) and attempting to drop a table would result in a crash. With innodb_force_recovery mode set to 3 or greater DML operations should be blocked and DDL operations allowed. This fix ensures that DDL operations are allowed. (Bug #16631778)

• InnoDB: A race condition would occur between ALTER TABLE ... ADD KEY and INSERT statements, resulting in an “Unable to Purge a Record” error. (Bug #16628233)

• InnoDB: Very large InnoDB full-text search (FTS) results could consume an excessive amount of memory. This bug fix reduces memory consumption for FTS results and introduces a new configuration parameter, innodb_ft_result_cache_limit, which places a default size limit of 2000000000 bytes on the InnoDB FTS query result cache. innodb_ft_result_cache_limit has an unlimited maximum value and can be set dynamically. (Bug #16625973)

• InnoDB: This fix addresses a race condition that would occur between the rollback of a recovered transaction and creation of a secondary index in a locked operation. The race condition would corrupt the secondary index. (Bug #16593427)

• InnoDB: DML operations on compressed temporary tables would result in a Valgrind error in the buffer manager stack. (Bug #16593331)

• InnoDB: When ADD PRIMARY KEY columns are reordered in an ALTER TABLE statement (for example: ALTER TABLE t1 ADD PRIMARY KEY(a,b), CHANGE a a INT AFTER b), the log apply for UPDATE operations failed to find rows. (Bug #16586355)

• InnoDB: A code regression resulted in a record lock wait in a dictionary operation. A code modification made to avoid starting a transaction on a temporary table failed to reset the state back to init upon completion of the operation. If a transaction is started, the state is usually reset by trx_commit. To catch similar problems in the future, this fix adds asserts to innobase_commit(), innobase_rollback(), and ha_innobase::update_thd() that trigger when trx->dict_operation and trx->dict_operation_lock_mode are not set. (Bug #16575799)

• InnoDB: In debug builds, the assert_trx_in_list() assert failed, causing a race condition. This fix removes the assert. The same assert is verified in the caller and existing checks are sufficient. (Bug #16567258)

• InnoDB: The MySQL printf facility (my_vsnprintf) did not understand the Microsoft I32 and I64 integer format width specifiers, such as %I64u for printing a 64-bit unsigned integer. As a result, DBUG_PRINT could not be used with the InnoDB UINT64PF format, which is defined as %I64u on Windows. This fix replaces the non-standard “I64” and “I32” length modifiers on Windows with “ll” and “l” so that they will be understood by both my_snprintf() and ut_snprintf(). (Bug #16559119)

• InnoDB: ALTER TABLE operations on InnoDB tables that added a PRIMARY KEY using a column prefix could produce an incorrect result. (Bug #16544336)

• InnoDB: For ALTER TABLE operations on InnoDB tables that required a table-copying operation, other transactions on the table might fail during the copy. However, if such a transaction issued a partial rollback, the rollback was treated as a full rollback. (Bug #16544143)

• InnoDB: The row0purge.h include file contained a self-referencing inclusion. (Bug #16521741)

• InnoDB: During a transaction commit, prepare_commit_mutex is acquired to preserve the commit order. If the commit operation failed, the transaction would be rolled back but the mutex would not be released. Subsequent insert operations would not be able to acquire the same mutex. This fix frees prepare_commit_mutex during innobase_rollback. (Bug #16513588)

• InnoDB: The recv_writer thread would only start after all redo log scans finished. In the case of multiple redo log scans, accumulated redo records would be applied after each scan and before processing the next scan. The absence of the recv_writer thread to help with flushing would slow recovery or result in a server startup timeout. This fix ensures that the recv_writer thread starts before the first scan batch is processed. (Bug #16501172)

• InnoDB: Under certain circumstances, LRU flushing would take a long time possibly affecting all flushing activity and causing a shutdown timeout. (Bug #16500209)

• InnoDB: The InnoDB memcached test.demo_test table failed to work when defined as a utf8 charset table. (Bug #16499038)

• InnoDB: In cases where threads are forced to do single page flushing, fsync() would be triggered for all data files. This fix allows for synchronous single page flushing. (Bug #16477781)

• InnoDB: This fix removes most calls to OS_THREAD_SLEEP from InnoDB. (Bug #16472953, Bug #68588)

• InnoDB: FLUSH TABLES ... FOR EXPORT slept too often while flushing pages from buffer pools. (Bug #16471701)

• InnoDB: Concurrently inserting into a full-text table would cause some inserts to fail. Duplicate values would be generated for full-text search document IDs when performing inserts into a hidden full-text search document ID column. (Bug #16469399)

• InnoDB: An InnoDB memcached file descriptor leak would cause a serious error. (Bug #16466664)

• InnoDB: The page_zip_available function would count some fields twice. (Bug #16463505)

• InnoDB: This fix replaces the IB_ULONGLONG_MAX constant with LSN_MAX where the code refers to log sequence numbers, or with TRX_ID_MAX where trx->no is initialized to an undefined value. This change does not alter the value of the constant. (Bug #16458660)

• InnoDB: This fix corrects the text for InnoDB error 6025, which stated, “InnoDB: read can't be opened in ./ib_logfile0 mode.”. The corrected message states, “InnoDB: ./ib_logfile0 can't be opened in read mode.” The variable and mode in the message construct were transposed. (Bug #16434398)

• InnoDB: When changing the shared tablespace file name using innodb_data_file_path and leaving the current log files in place, InnoDB would create a new tablespace file and overwrite the log files resulting in a mismatch between the data dictionary and tables on disk. This bug fix ensures that InnoDB does not create a new tablespace if there are inconsistent system tablespaces, undo tablespaces, or redo log files. (Bug #16418661)

• InnoDB: Creating a foreign key constraint using the ALTER TABLE INPLACE algorithm requires foreign_key_checks to be set to 0 (SET foreign_key_checks = 0;). As a result, an appropriate duplicate ID check would not be performed. (Bug #16413976)

• InnoDB: When the InnoDB shutdown mode (innodb_fast_shutdown) is set to 2 and the master thread enters the flush loop, the thread would not be able to exit under some circumstances. This could lead to a shutdown hang. (Bug #16411457)

• InnoDB: In debug builds, an insert failed with an invalid assertion: sync_thread_levels_g(array, level - 1, TRUE). (Bug #16409715)

• InnoDB: Crash recovery failed with a !recv_no_log_write assertion when reading a page. (Bug #16405422)

• InnoDB: An ALTER TABLE operation that performed a table copy failed because a temporary tablespace with the same name already existed. This fix makes temporary tables and tablespace names more unique by adding the current log sequence number (LSN) to the end of the previous table or file name. For example, table name “test/#sql-ib21” becomes “test/#sql-ib21-1701208,” where 1701208 is the current LSN. Both the LSN and the table ID are needed to ensure that the name is unique because it is theoretically possible for multiple threads to have the same LSN. Including the table ID allows the temporary name to be associated with the table. (Bug #16403420)

• InnoDB: Multiple concurrent calls to dict_update_statistics() would result in unnecessary server load. (Bug #16400412)

• InnoDB: On 64-bit Windows builds, INNODB_BUFFER_POOL_SIZE would not accept an allocation of more than 32GB. This limitation was due to a bug that truncated the internal value for the InnoDB buffer pool size to 32 bits on 64-bit Windows builds. (Bug #16391722, Bug #68470)

• InnoDB: Restarting InnoDB in read-only mode and running a workload would occasionally return a global_segment < os_aio_n_segments assertion. (Bug #16362046)

• InnoDB: DROP DATABASE failed if the database contained an InnoDB table that had a data file in an external data directory. The external data file had an “InnoDB Symbolic Link” file type (.isl) that was not recognized by MySQL. This fix adds .isl as a known InnoDB file type. (Bug #16338667)

• InnoDB: RENAME TABLE would result in a hang due to a MySQL mutex acquisition deadlock. (Bug #16305265)

• InnoDB: This fix removes dated and incomplete code that is protected by the UNIV_LOG_ARCHIVE macro. (Bug #16296837)

• InnoDB: Under testing, a FLUSH TABLES operation resulted in a timeout due to a missing acknowledgment that the purge thread had stopped. (Bug #16277387)

• InnoDB: For compressed tables, a page reorganize operation would always write an MLOG_ZIP_PAGE_REORGANIZE record to the redo log, which is only correct if innodb_log_compressed_pages=OFF. When innodb_log_compressed_pages=ON, the page reorganize operation should log the compressed page image. (Bug #16267120)

• InnoDB: When tables are linked by foreign key constraints, loading one table would open other linked tables recursively. When numerous tables are linked by foreign key constraints, this would sometimes lead to a thread stack overflow causing the server to exit. Tables linked by foreign key constraints are now loaded iteratively. Cascade operations, which were also performed in a recursive manner, are now performed iteratively using an explicit stack. (Bug #16244691, Bug #65384)

• InnoDB: After disabling foreign key checks with SET foreign_key_checks=0 and performing a DROP INDEX, the table was no longer accessible after restarting the server. This fix allows the table with missing foreign key indexes to be accessed when SET foreign_key_checks=0. When the table is accessible, the user must recreate the missing indexes to fulfill the foreign key constraints. (Bug #16208542, Bug #68148)

• InnoDB: When a transaction is in READ COMMITTED isolation level, gap locks are still taken in the secondary index when a row is inserted. This occurs when the secondary index is scanned for duplicates. The function row_ins_scan_sec_index_for_duplicate() always calls the function row_ins_set_shared_rec_lock() with LOCK_ORDINARY irrespective of the transaction isolation level. This fix modifies the row_ins_scan_sec_index_for_duplicate() function to call row_ins_set_shared_rec_lock() with LOCK_ORDINARY or LOCK_REC_NOT_GAP, based on the transaction isolation level. (Bug #16133801, Bug #68021)

• InnoDB: Persistent statistics would be disabled unnecessarily when running in read-only mode. When running in read-only mode, fetching statistics from disk does not involve any modification of on-disk data except for when ANALYZE TABLE is run. This fix enables persistent statistics for read-only mode. (Bug #16083211)

• InnoDB: Starting mysqld with --innodb-log-buffer-size=50GB failed to allocate memory and returned NULL. For non-debug builds there was no check in place and a segmentation fault occurred. This fix adds a log message stating that memory failed to be allocated, and adds an assertion. (Bug #16069598, Bug #68025)

• InnoDB: When UNIV_DEBUG is enabled in debug builds, buf_validate() is often called which sometimes results in false alarms in tests on semaphore wait timeout. This fix increases counter values to reduce false alarms. (Bug #16068056)

• InnoDB: While printing a UTF-8 table name, InnoDB would truncate the table name, resulting in an incomplete buffer and subsequent Valgrind error. This bug fix also addresses an incorrect debugging error message. (Bug #16066351)

• InnoDB: The explain_filename function, which provides information about a partition by parsing the file name, would return an error when attempting to parse a file name with no partition information. (Bug #16051728)

• InnoDB: Stopping the server, removing a database table (d1.t1) .frm file from the data directory, restarting the server, and dropping the database (d1), would cause an assertion. (Bug #16043216)

• InnoDB: While processing read-write workloads, InnoDB would scan more pages than are required for flushing, unnecessarily consuming CPU resource. (Bug #16037180)

• InnoDB: TRUNCATE TABLE failed to handle the return value from btr_create when btr_create is invoked by TRUNCATE TABLE for creation of a new index. (Bug #16026889)

• InnoDB: An overflow would occur for innodb_row_lock_time_max and innodb_row_lock_current_waits. This fix modifies code logic in storage/innobase/srv/srv0srv.c. (Bug #16005310)

• InnoDB: Attempting to create a table while in innodb_read_only mode resulted in the following error: ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only). (Bug #15963619)

• InnoDB: An active FLUSH TABLES ... FOR EXPORT thread would cause a hang during shutdown. The fix ensures that trx_is_interrupted() is checked during ibuf_merge. (Bug #15953255)

• InnoDB: innochecksum would return an error when run on compressed tables. (Bug #14612872, Bug #66779)

• InnoDB: A multi-row INSERT ... ON DUPLICATE KEY UPDATE insert failure, caused by a duplicate key error, would result in duplicate auto-increment values. (Bug #14483484, Bug #66301)

• InnoDB: A mismatch between .ibd files and the InnoDB data dictionary could occur if TRUNCATE TABLE is interrupted by a crash. The mismatch would be encountered after recovery. To avoid this problem, truncate table information is written to a truncate log file that resides temporarily in the log directory. The truncate log file has the following naming convention: ib_space_id_trunc.log. If the TRUNCATE operation is successful, the truncate log file is removed. If the TRUNCATE operation is interrupted by a crash, information is read from the truncate log file during recovery, the log records are applied, and the truncate log file is removed. (Bug #14174004, Bug #13997329, Bug #17227149, Bug #17238361, WL #6501)

• InnoDB: The documentation incorrectly stated that START TRANSACTION WITH CONSISTENT SNAPSHOT provides a consistent snapshot only if the current isolation level is REPEATABLE READ or SERIALIZABLE. START TRANSACTION WITH CONSISTENT SNAPSHOT only works with REPEATABLE READ. All other isolation levels are ignored. The documentation has been revised and a warning is now generated whenever the WITH CONSISTENT SNAPSHOT clause is ignored. (Bug #14017206, Bug #65146)

• InnoDB: The srv_master_thread background thread, which monitors server activity and performs activities such as page flushing when the server is inactive or in a shutdown state, runs on a one second delay loop. srv_master_thread failed to check if the server is in a shutdown state before sleeping. (Bug #13417564, Bug #63276)

• InnoDB: In the error log, a full-text search index would be reported missing from the data dictionary during a TRUNCATE TABLE operation. After restarting mysqld, the following InnoDB error would be reported: “InnoDB: Error: trying to load index idx13 for table test/g1 but the index tree has been freed..” (Bug #12429565)

  References: See also: Bug #17402002.

• InnoDB: When the value provided for innodb_buffer_pool_size on 32-bit systems is too large, an error message would incorrectly reference the internal variable, innobase_buffer_pool_size, instead of innodb_buffer_pool_size. (Bug #11759578, Bug #51901)

• InnoDB: Compiling InnoDB on Windows Vista 64-bit with Visual Studio 2005 would result in compilation errors. (Bug #11752731, Bug #44004)

• InnoDB: The row_check_index_for_mysql method, which checks for NULL fields during an index scan or CHECK TABLE operation, would iterate unnecessarily. Thanks to Po-Chun Chang for the patch to correct this issue. (Bug #69377, Bug #16896647)

• InnoDB: When running an InnoDB full-text search in boolean mode, prefixing an asterisk (*) to a search string ('*string') would result in an error whereas for MyISAM, a prefixed asterisk would be ignored. To ensure compatibility between InnoDB and MyISAM, InnoDB now handles a prefixed asterisk in the same way as MyISAM. (Bug #68948, Bug #16660607)

• InnoDB: Successive deletes in descending key order would lead to under-filled InnoDB index pages. When an InnoDB index page is under-filled, it is merged with the left or right sibling node. The check performed to determine if a sibling node is available for merging was not functioning correctly. (Bug #68501, Bug #16417635)

• InnoDB: Setting foreign_key_checks=0 and running ALTER TABLE to change the character set of foreign key columns for a database with multiple tables with foreign key constraints would leave the database in an inconsistent state. Subsequent ALTER TABLE operations (using the COPY algorithm) with foreign_key_checks=1 would fail due to the detected inconsistency. Reversion of the partially executed ALTER TABLE operation would also fail, resulting in the loss of the table being altered. When running the same ALTER TABLE operation with a RENAME clause, the inconsistency would not be detected but if the ALTER TABLE operation failed for some other reason, reversion of the partially executed ALTER TABLE failed with the same result.

  The bug fix temporarily disables foreign_key_checks while the previous table definition is restored. (Bug #65701, Bug #14227431)

• InnoDB: Creating a table with a comment or default textual value containing an apostrophe that is escaped with a backslash would sometimes cause the InnoDB storage engine to omit foreign key definitions. (Bug #61656, Bug #12762377)

• InnoDB: The pthread_mutex, commit_threads_m, which was initialized but never used, has been removed from the code base. (Bug #60225, Bug #11829813)

• InnoDB: In many cases InnoDB calls exit(1) when it encounters a fatal error. An exit(1) call does not produce a crash dump or provide information about the process state. Additionally, on Windows, an exit(1) call does not report a crashed process in the Event Viewer. This fix replaces exit(1) calls with ut_error calls in a number of places. (Bug #56400, Bug #11763660)

• Partitioning: Creating a table t1 using CREATE TABLE ... PARTITION BY LIST ... PARTITION ... VALUES IN (NULL), then attempting to execute CREATE TABLE ... LIKE t1 caused the server to fail. (Bug #16860588)

• Partitioning: When upgrading to MySQL 5.5.31 or higher, a message is written into the output of mysql_upgrade when encountering a partitioned table for which the ALGORITHM option is required to maintain binary compatibility with the original; the message includes the ALTER TABLE statement required to make the change. For such a table having a sufficiently large number of partitions, the message was truncated with an error before the complete ALTER TABLE statement could be written. (Bug #16589511)

• Partitioning: When a range was specified in the WHERE condition of a query against a table partitioned by range, and the specified range was entirely within one of the partitions, the next partition was also checked for rows although it should have been pruned away.

  Suppose we have a range-partitioned table t created using the following SQL statement:

  CREATE TABLE t  (
      id INT AUTO_INCREMENT,
      dt DATETIME,
      PRIMARY KEY (dt,id),
      UNIQUE KEY (id,dt)
  )
      PARTITION BY RANGE  COLUMNS(dt) (
          PARTITION p0 VALUES LESS THAN ('2013-01-01'),
          PARTITION p1 VALUES LESS THAN ('2013-01-15'),
          PARTITION p2 VALUES LESS THAN ('2013-02-01'),
          PARTITION p3 VALUES LESS THAN ('2013-02-15'),
          PARTITION pmax VALUES LESS THAN (MAXVALUE)
  );

  An example of a query that exhibited this issue when run against t is shown here:

  SELECT COUNT(*) FROM t
      WHERE dt >= '2013-02-01' AND dt < '2013-02-15';

  In this case, partition pmax was checked, even though the range given in the WHERE clause lay entirely within partition p3. (Bug #16447483)

• Partitioning: When dropping a partitioned table, the table's .par file was deleted first, before the table definition or data. This meant that, if the server failed during the drop operation, the table could be left in an inconsistent state in which it could neither be accessed nor dropped.

  The fix for this problem makes the following changes:

  • Now, when dropping a partitioned table, the table's .par file is not removed until all table data has been deleted.

  • When executing DROP TABLE of a partitioned table, in the event that its .par file is determined to be missing, the table's .frm file is now immediately deleted, in effect forcing the drop to complete.

  (Bug #13548704, Bug #63884)

• Replication; Microsoft Windows: On Windows platforms, issuing SHOW SLAVE STATUS while the slave I/O thread was being terminated due to an error caused the slave to fail. (Bug #16662771)

• Replication: The server attempted to perform an internal truncatation of the slave_worker_info table while resetting it, even though this is a DDL operation and should not be used conccurrently with DML operations. To prevent this from happening, the reset now performs sequential row deletion in place of the truncation operation. (Bug #17286858, Bug #69898)

• Replication: The data size for a table map event created during execution was calculated, but not when the event was created from a network packet. This could later cause problems when the data fields of such events were treated as if they had a length equal to 0 when trying to write the events to a cache, or to the binary log.

  To avoid future problems of this nature, the table map's data size is now calculated in both cases. (Bug #17164074)

• Replication: When the --relay-log-info-file option was used together with slave_parallel_workers set to a value greater than 1, mysqld failed to start. (Bug #17160671)

• Replication: The commit error caused by the failure of binary log rotation failure generated an incident event in the binary log file and interrupted the user session with error messages which did not mention that the slave server would be stopped later when the incident event was replayed.

  Now, when encountering binary log rotation failure, a more helpful error message is instead written to the log, alerting the user to investigate in a timely manner. (Bug #17016017)

• Replication: The condition leading to the issue fixed in Bug #16579083 continued to raise an error even though the condition itself no longer cause the issue to occur. (Bug #16931177, Bug #69369)

  References: See also: Bug #16271657, Bug #16491597, Bug #68251, Bug #68569. This issue is a regression of: Bug #16579083.

• Replication: The mysqlbinlog option --rewrite-db caused USE statements to be ignored, even for databases that were not referenced by the option. (Bug #16914535)

• Replication: When rpl_semi_sync_master_timeout was set to an extremely large value, semisynchronous replication became very slow, especially when many sessions were working in parallel. It was discovered that the code to calculate this timeout was inside the wait loop itself, with the result that an increase in the value of rpl_semi_sync_master_timeout caused repeated iterations. This fix improves the method used to calculate wakeup times, and moves it outside of the wait loop, so that it is executed one time only. (Bug #16878043, Bug #69341)

• Replication: It was possible to cause a deadlock after issuing FLUSH TABLES WITH READ LOCK by issuing STOP SLAVE in a new connection to the slave, then issuing SHOW SLAVE STATUS using the original connection.

  The fix for this includes the addition of the rpl_stop_slave_timeout system variable, to control the time in seconds to wait for slave to stop after issuing STOP SLAVE before returning a warning. (Bug #16856735)

• Replication: It was possible in CHANGE MASTER TO statements to set the MASTER_DELAY option greater than the supported maximum value (231 − 1). In addition, the error resulting from setting MASTER_DELAY to a value greater than 232 was not handled correctly. (Bug #16820156, Bug #16960315, Bug #69249, Bug #69469)

• Replication: Some expressions employing variables were not handled correctly by LOAD DATA. (Bug #16753869)

• Replication: In some circumstances, the message in the Last_Error column from the output of SHOW SLAVE STATUS referred to GTID_NEXT_LIST although this variable is not currently implemented (the name is reserved for possible future use). Now in such cases the error message no longer refers to this variable. (Bug #16742886, Bug #69096)

  References: See also: Bug #16715809, Bug #69045.

• Replication: mysqlbinlog --rewrite-db failed when the name of the destination database contained any underscore (_) characters. (Bug #16737279)

• Replication: Issuing a FLUSH TABLES statement on a GTID-enabled master caused replication to fail. It was found that this misbehavior was introduced by the fix for Bug #16062608, which disallowed statements that perform an implicit commit but whose changes are not logged when gtid_next is set to any value other than AUTOMATIC. The changes made in that fix have been reverted, and such statements are (again) allowed without regard to the value of this variable. (Bug #16715809, Bug #69045)

  References: Reverted patches: Bug #16062608.

• Replication: Point-in-time recovery could fail when trying to restore a single database from a binary log in row-based format using mysqlbinlog with the --database option. (Bug #16698172)

• Replication: A crash-on-commit error caused InnoDB to lose the previous transaction following execution of a RESET MASTER statement. This occurred because the prepare phase caused a flush to disk, while the commit phase did not perform a corresponding flush within InnoDB.

  To fix this problem, RESET MASTER now causes storage engine logs to be flushed on commit. (Bug #16666456, Bug #68932)

• Replication: When processing an Update_rows_log_event or Delete_rows_log_event from the binary log, the before image is hashed and stored in a hash table. Following this, the original table is scanned for the desired records; subsequent processing hashes each record fetched from the original table and performs a lookup for it in the hash table. However, columns read from the image that had originally been set to NULL could instead contain random or “garbage” data, causing the lookup (and thus replication) to fail with an error such as Could not execute Update_rows event on table.... (Bug #16621923)

  References: See also: Bug #11766865. This issue is a regression of: Bug #16566658.

• Replication: When used with the options --dump-slave --include-master-host-port, mysqldump printed the port number within quotation marks, as if it were a string value rather than an integer. (Bug #16615117)

• Replication: Linker errors occurred if the header file log_event.h was included in an application containing multiple source files, because the file rpl_tblmap.cc was included in log_event.h. This fix moves the inclusion of rpl_tblmap.cc into the source files that use log_event.h. (Bug #16607258)

• Replication: The error displayed by SHOW SLAVE STATUS when a worker thread fails to apply an event contained no event coordinate information. The GTID for the event's group was also not shown. Now in such cases, the text shown for Last_SQL_Error is prefixed with the (physical) master binary log coordinates, as well as the value of gtid_next when this has been set. (Bug #16594095)

• Replication: A session attachment error during group commit causes the rollback of the transaction (as intended), but the transaction in which this happened was still written to the binary log and replicated to the slave. Thus, such an error could lead to a mismatched master and slave.

  Now when this error occurs, an incident event is written in the binary log which causes replication to stop, and notifies the user that redundant events may exist in the binary log. An additional error is also now reported to the client, indicating that the ongoing transaction has been rolled back. (Bug #16579083)

• Replication: Due to time resolution issues on some systems, the time to be taken by the dump thread for a reply from the slave could be calculated to be less than zero, leading to Semi-sync master wait for reply fail to get wait time errors. Since this condition does not have a negative impact on replication, errors caused by these conditions have been reduced to warnings. (Bug #16579028)

• Replication: Running the server with --log-slave-updates together with --replicate-wild-ignore-table or --replicate-ignore-table in some cases caused updates to user variables not to be logged. (Bug #16541422)

• Replication: When using mysqlbinlog and the mysql client to roll forward two or more binary logs on a server having GTIDs enabled, the gtid_next variable was not properly reset when switching from the first to the second binary log, causing processing to halt with an error at that point. (Bug #16532543)

• Replication: The mysqlbinlog options --include-gtids, --exclude-gtids, and --skip-gtids did not work correctly when trying to process multiple files. (Bug #16517775)

• Replication: When one or more GTID log events but no previous GTIDs log events were found in the binary log, the resulting error was mishandled and led to a failure of the server. (This is an extremely rare condition that should never occur under normal circumstances, and likely indicates that the binary log file has somehow been corrupted.) Now in such cases, an appropriate error is issued, and is handled correctly. (Bug #16502579, Bug #68638)

• Replication: Attempting to execute START SLAVE after importing new slave_master_info and slave_relay_log_info tables failed with an empty error message. Now an appropriate error and message are issued in such cases. (Bug #16475866, Bug #68605)

• Replication: Restarting the server after the slave_relay_log_info table had been emptied caused mysqld to fail while trying to return an error. (Bug #16460978, Bug #68604)

• Replication: The warning issued when specifying MASTER_USER or MASTER_PASSWORD with CHANGE MASTER TO was unclear for a number of reasons, and has been changed to read, Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see 'START SLAVE Syntax' in the MySQL Manual for more information. (Bug #16460123, Bug #16461303, Bug #68602, Bug #68599)

• Replication: Extra binary log rotations were performed due to concurrent attempts at rotation when the binary log became full, which were allowed to succeed. This could lead to the unnecessary creation of many small binary log files. (Bug #16443676, Bug #68575)

• Replication: When the size of an execution event exceeded the maximum set for the buffer (slave_pending_jobs_size_max), row-based replication could hang with Waiting for slave workers to free pending events. (Bug #16439245, Bug #68462)

• Replication: Following disconnection from the master, the slave could under certain conditions report erroneously on reconnection that it had received a packet that was larger than slave_max_allowed_packet, causing replication to fail. (Bug #16438800, Bug #68490)

• Replication: A slave using row-based replication was unable to read the rows containing columns of type MYSQL_TYPE_DECIMAL properly (old-style decimal, used prior to MySQL 5.0.3). Now the slave throws an error if it receives this type of data. You can convert the old-style DECIMAL format to the binary format used in current MySQL releases with ALTER TABLE; see Upgrading from MySQL 4.1 to 5.0, for more information. (Bug #16416302)

• Replication: An SQL thread error during MTS slave recovery caused the slave to fail. (Bug #16407467, Bug #68506)

• Replication: When using the options --read-from-remote-server --stop-never --base64-output=decode-rows --verbose, mysqlbinlog failed to reset the counter used to store the current position within the file when the binary log on the server was rotated. (Bug #16316123, Bug #68347)

• Replication: When using mysqldump to back up a database created with MySQL 5.6.4 or an earlier version, setting --set-gtid-purged=AUTO caused the backup to fail, because pre-5.6.5 versions of MySQL did not support GTIDs, and it could not be determined if GTIDs were enabled for the database. This fix makes sure mysqldump does not attempt to output a SET @@GLOBAL.gtid_purged statement when backing up any pre-5.6.5 databases. (Bug #16303363, Bug #68314)

• Replication: DROP TEMP TABLE IF EXISTS statements could lead to failures in applying the binary log during point-in-time recovery operations. This is due to the fact that, when using row-based replication, the server appends IF EXISTS to any DROP TEMPORARY TABLE statements written to the binary log, and that the slave SQL thread does not check * wildcard filter rules for DROP TEMPORARY TABLE IF EXISTS. If --log-slave-updates was also enabled on the slave, such a statement was preceded by a USE statement. If the database referred by the USE statement did not exist, the statement failed, and stopped replication.

  Now, when writing DROP TEMPORARY TABLE IF EXISTS into the binary log, no USE statement is written, and the table name in the DROP TEMPORARY TABLE statement is a fully qualified table name. (Bug #16290902)

• Replication: Deadlocks could sometimes occur on group commits with a high number of concurrent updates, as well as when one client held a lock from a commit while another client imposed a lock while rotating the binary log. (Bug #16271657, Bug #16491597, Bug #68251, Bug #68569)

• Replication: After a transaction was skipped due to its GTID already having been logged, all remaining executed transactions were incorrectly skipped until gtid_next was pointed to a different GTID.

  To avoid this incorrect behavior, all transactions—even those that have been skipped—are marked as undefined when they are commited or rolled back, so that an error is thrown whenever a second transaction is executed following the same SET @@SESSION.gtid_next statement. (Bug #16223835)

• Replication: When semisynchronous replication was enabled, the automatic dropping on the master of an event created using ON COMPLETION NOT PRESERVE caused the master to fail. (Bug #15948818, Bug #67276)

• Replication: Modifying large amounts of data within a transaction can cause the creation of temporary files. Such files are created when the size of the data modified exceeds the size of the binary log cache (max_binlog_cache_size). Previously, such files persisted until the client connection was closed, which could allow them to grow until they exhausted all available disk space in tmpdir. To prevent this from occurring, the size of a temporary file created in this way in a given transaction is now reset to 0 when the transaction is committed or rolled back. (Bug #15909788, Bug #18021493, Bug #66237)

• Replication: When the master had more than one table with an auto-increment column, and the slave ignored at least one of these tables due to --replicate-ignore-table rules, but at least one them was replicated, even so—the replicated table or tables having at least one trigger updating one or more tables existing only on the slave—updates to any of the auto-increment tables on the master caused replication to fail. (Bug #15850951, Bug #67504)

• Replication: Setting a SET column to NULL inside a stored procedure caused replication to fail. (Bug #14593883, Bug #66637)

• Replication: The binary log contents got corrupted sometimes, because the function MYSQL_BIN_LOG::write_cache always thought it had reached the end-of-cache when the function my_b_fill() reported a '0,' while that could also mean an error had occurred. This fix makes sure that whenever my_b_fill() returns a '0,' an error check is performed on info->error. (Bug #14324766, Bug #60173)

• Replication: The internal function MYSQL_BIN_LOG::open_binlog() contained an unneeded variable, which has been removed. (Bug #14134590, Bug #60188)

• Replication: PURGE BINARY LOGS by design does not remove binary log files that are in use or active, but did not provide any notice when this occurred. Now, when log files are not removed under such conditions, a warning is issued; this warning includes information about the file or files were not removed when the statement was issued. (Bug #13727933, Bug #63138)

• Replication: It was possible for the multithreaded slave coordinator to leak memory when the slave was stopped while waiting for the next successful job to be added to the worker queue. (Bug #13635612)

• Replication: When replicating to a BLACKHOLE table using the binary logging format, updates and deletes cannot be applied and so are skipped. Now a warning is generated for this whenever it occurs.

  Note

  binlog_format=STATEMENT is recommended when replicating to tables that use the BLACKHOLE storage engine.

  (Bug #13004581)

• Replication: Temporary files created by LOAD DATA were not removed if the statement failed. (Bug #11763934, Bug #56708)

• Replication: After the client thread on a slave performed a FLUSH TABLES WITH READ LOCK and was followed by some updates on the master, the slave hung when executing SHOW SLAVE STATUS. (Bug #68460, Bug #16387720)

• Microsoft Windows: On Microsoft Windows, passing in --local-service to mysqld.exe when also passing in a service name could cause a crash at startup. (Bug #16999777, Bug #69549)

• The execution time of a query involving a stored function was affected by the number of warnings generated by the previous statement in the same session. (Bug #23031008, Bug #80922)

• The contents of SQL condition items such as TABLE_NAME, CONSTRAINT_NAME, an so forth were lost if resignaled by a stored routine condition handler. (Bug #17280703)

• AES_ENCRYPT() and AES_DECRYPT() failed to work correctly when MySQL was built with an AES_KEY_LENGTH value of 192 or 256. (Bug #17170207)

• SELECT * from performance_schema.events_statements_current could raise an assertion due to a race condition under load. (Bug #17164720)

• InnoDB full-text searches failed in databases whose names began with a digit. (Bug #17161372)

• A successful connection failed to reset the per-IP address counter used to count successive connection failures. This could possibly cause a host to be blocked, when the max_connect_errors limit was reached. (Bug #17156507)

• With the thread pool plugin enabled and SSL in use, an error in one connection might affect other connections, causing them to experience a lost connection. (Bug #17087862)

• Under load, truncating the accounts Performance Schema table could cause a server exit. (Bug #17084615)

• my_pthread.h unconditionally included pfs_thread_provider.h, a noninstalled header file, resulting in compilation failure when compiling MySQL applications against the installed header files. (Bug #17061480)

• Indexed lookups on POINT columns was slower for InnoDB tables in MySQL 5.7 compared to 5.6. (Bug #17057168)

• The Performance Schema was built for embedded server builds. This no longer occurs. (Bug #17041705)

• Reads from message buffers for closed connections could occur. (Bug #17003702)

• The server could exit while using a cursor to fetch rows from a UNION query. (Bug #16983143)

• The range optimizer incorrectly assumed that any geometry function on a spatial index returned rows in ROWID order, which could result in incorrect query results. (Bug #16960800)

• mysql_secure_installation did not properly clean up the mysql.proxies_privs table for removed accounts. (Bug #16959850)

• A race condition in the thread pool plugin could cause status variables such as Aborted_connects not to be incremented and permitting concurrent kills to happen for the same thread ID. (Bug #16959022)

• At server startup, it was possible to set the validate_password_length system variable to a value smaller than permitted by the values of other password-length variables related to it. (Bug #16957721)

• Initialization of keycache_* variables (see Multiple Key Caches) during server startup could write to incorrect memory. (Bug #16945503)

• For debug builds, improper use of SAFE_MUTEX within dbug.c caused different code areas to have different ideas about size and contents of a mutex. This could result in out-of-bounds memory writes. (Bug #16945343)

• The Performance Schema could spawn a thread using incorrect instrumentation information. (Bug #16939689)

• The server did excessive locking on the LOCK_active_mi and active_mi->rli->data_lock mutexes for any SHOW STATUS LIKE 'pattern' statement, even when the pattern did not match status variables that use those mutexes (Slave_heartbeat_period, Slave_last_heartbeat, Slave_received_heartbeats, Slave_retried_transactions, Slave_running). Now attempts to show those variables do not lock those mutexes. This might result is slightly stale data, but better performance. (Bug #16904035)

• Full-text phrase search in InnoDB tables could read incorrect memory. (Bug #16885178)

• It was not possible to keep several major versions of MySQL in the same yum repository. (Bug #16878042)

• Excessive memory consumption was observed for multiple execution of a stored procedure under these circumstances: 1) The stored procedure had an SQL statement that failed during validation. 2) The stored procedure had an SQL statement that required repreparation. (Bug #16857395)

• The Batched Key Access method could return incorrect results on big-endian machines if a table buffered in the BKA join cache used a storage engine such as InnoDB or MyISAM with little-endian storage format, and then the next table used a storage engine such as NDB with native-endian storage format. (Bug #16853897)

• The error string for ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE string contained a hardcoded database name ('mysql.%s'), which is incorrect when the error referred to a table in a different database. (Bug #16813605)

• An assertion could be raised when the optimizer considered pushing down an index condition containing an updatable user variable and did not contain fields from the index. (Bug #16804581)

• If a SET statement containing a subquery caused a deadlock inside InnoDB, InnoDB rolled back the transaction. However, the SQL layer did not notice this and continued execution, resulting eventually in an assertion being raised inside InnoDB. (Bug #16802288)

• Removing a server RPM package did not shut down the existing server if it was running. (Bug #16798868)

• Some errors in MySQL 5.7 had different numbers than in MySQL 5.6. (Bug #16780120)

• A race condition in the server could cause issues with the mysqld process ID file when startup was aborted. As part of the fix for this issue, mysqld_safe now creates its own PID file mysqld_safe.pid in the server's data directory. (Bug #16776528, Bug #70308)

• HAVE_REPLICATION now is set from CMake rather than in my_global.h so that it is not dependent on my_global.h having been included. (Bug #16768511)

• INSERT ... ON DUPLICATE KEY UPDATE could cause a server exit if a column with no default value was set to DEFAULT. (Bug #16756402)

  References: This issue is a regression of: Bug #14789787.

• CMake now assumes the existence of standard C header files such as stdlib.h and stdarg.h. (Bug #16748528)

• In a prepared statement or stored routine, if the HAVING clause of a subquery referenced some column of the GROUP BY of the parent query, the server could exit. (Bug #16739050)

• Compiling failed with -DMY_ATOMIC_MODE_RWLOCKS=1 or on platforms on which MySQL did not support lockless atomic operations (such as ARM). (Bug #16736461)

• Password rewriting in the general query log now also applies to prepared statements. (Bug #16732621)

• The code base was modified to account for new warning checks introduced by gcc 4.8. (Bug #16729109)

• The function fill_locks_row(), which is responsible for providing data for the INFORMATION_SCHEMA.INNODB_LOCKS table, would try to look up the B-tree page in the buffer pool for INFIMUM and SUPREMUM records, both of which have a predefined heap_no. This generated unnecessary buffer pool contention and caused information to be omitted when a page was not available in the buffer pool. This fix removes the buffer pool lookup for PAGE_HEAP_NO_INFIMUM (heap_no=0) and PAGE_HEAP_NO_SUPREMUM (heap_no=1) from fill_locks_row(). (Bug #16684523)

• The runtime open_files_limit system variable did not show the actual number of file descriptors the mysqld process could have, but instead the number that was requested after adjusting the value specified at startup. (Bug #16657588)

• Kill handling in the thread pool plugin was subject to timeout problems and Valgrind warnings. (Bug #16633880)

• Overhead for setting PROCESSLIST_STATE values in the THREADS Performance Schema table has been reduced. (Bug #16633515)

• Within a stored procedure, repeated execution of a prepared CREATE TABLE statement for a table with partitions could cause a server exit. (Bug #16614004)

• The server could make the wrong decision about whether an account password was expired. (Bug #16604641)

• The Windows authentication plugin failed to free a context buffer for each connection. (Bug #16591288)

• Some rows for a session could be missing sporadically from the session_connect_attrs Performance Schema table while the session was executing a workload. (Bug #16576980)

• The DBUG_PRINT() macro unnecessarily evaluated arguments when debugging was not enabled. (Bug #16556597)

• Some problems compiling on Solaris in 64-bit mode with gcc and g++ were corrected. (Bug #16555106)

• SHOW WARNINGS and SHOW ERRORS did not properly reset the warning count. (Bug #16522662)

• Clients could determine based on connection error message content whether an account existed. (Bug #16513435, Bug #17357528, Bug #19273967)

• Geometry methods that worked with WKB data performed insufficient input data validation, which could cause Valgrind errors or a server exit. (Bug #16510712, Bug #12772601)

• The server could attempt a filesort operation for a zero-size sort length, causing it to exit. (Bug #16503160)

• Opening a cursor on a SELECT within a stored procedure could cause a segmentation fault. (Bug #16499751)

  References: This issue is a regression of: Bug #14740889.

• CREATE TABLE or ALTER TABLE can fail if the statement specified unsupported options or something was missing. Previously, such errors were returned as ER_ILLEGAL_HA. Now they are returned as the new ER_MISSING_HA_CREATE_OPTION error. (Bug #16498740)

• Enabling the query cache could cause repeatable-read transactions to return incorrect results. (Bug #16497925)

• my_load_defaults() was modified to accommodate some problems under compilation with gcc 4.7.2 that could cause a client crash during option processing. (Bug #16497125)

• Missing variable initialization led to incorrectly returning an error from st_select_lex_unit::explain and led to a failed assertion. (Bug #16484966)

• When index condition pushdown was used on a descending range scan and the first range interval did not contain any qualifying records, the result of the range scan could be empty even if other range intervals contained qualifying records. (Bug #16483273)

• The WKB reader for spatial operations could fail and cause a server exit. (Bug #16451878)

• The ER_OUTOFMEMORY error was used in some places without the proper parameter being passed, resulting in incorrect diagnostic output. (Bug #16449659)

• Failure to handle a full-text search wildcard properly could cause the server to exit. (Bug #16446108)

• Optimizer heuristics inappropriately preferred range access over ref access in cases when the ref access referred to a column of a table earlier in the join seqence. (Bug #16437940)

• For queries using ref access on CHAR and VARCHAR 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 #16437630)

• If the optimizer was using a Loose Index Scan, the server could exit while attempting to create a temporary table. (Bug #16436567)

• Incorrect results or a server exit could be caused by a reference to an aggregated expression inside a nested subquery, where the aggregated expression was evaluated in a query block more than two levels outer to the reference. (Bug #16436383)

• If a table has been marked as containing only NULL values for all columns if it is a NULL-complemented row of an outer join, then rollup on the column which cannot be nullable results in a server exit. (Bug #16436014)

• Unlike MyISAM, InnoDB does not support boolean full-text searches on nonindexed columns, but this restriction was not enforced, resulting in queries that returned incorrect results. (Bug #16434374)

• Performance Schema parameter autosizing at startup did not take into account later autosizing changes to other startup parameters on which the Performance Schema parameters depended. (Bug #16430532)

• A full-text search syntax error failed to print to standard output. (Bug #16429688, Bug #16765397)

• Some INFORMATION_SCHEMA queries that used ORDER BY did not use a filesort optimization as they did in MySQL 5.5. (Bug #16423536)

• Debugging flags used to set the debug system variable were ignored if they were a prefix of keywords already in the debugging list. (Bug #16415978)

• Manually-created accounts (using INSERT) with a malformed password effectively had no password. (Bug #16414396)

• For debug builds, DBUG_EXPLAIN resulted in a buffer overflow when the debug system variable value was more than 255 characters. (Bug #16402143)

• Several scripts in the sql-bench directory that were supposed to be executable did not have the executable access bit set. (Bug #16395606)

• For debug builds, with an XA transaction in IDLE or PREPARED status, execution of a query with the query cache enabled could cause a server exit. (Bug #16388996)

• If the primary key for the mysql.proc system table was removed (an unsupported and not-recommended operation), the server exited for subsequent stored procedure invocation. Similar problems could occur for other system tables. Now an error occurs instead. (Bug #16373054)

• A server exit could occur for queries of the form SELECT (SELECT 1 FROM t1) IN (SELECT a FROM t1) when attempting to evaluate the constant left-hand argument to the IN subquery predicate. (Bug #16369522)

• An assertion could be raised when creating a index on a prefix of a TINYBLOB or GEOMETRY column in an InnoDB column. (Bug #16368875, Bug #18776592, Bug #17665767)

• If a lock timeout resulted from an UPDATE with a nested SELECT being unable to access rows being accessed by another thread, the error could go unchecked and cause an assertion to be raised later. (Bug #16367039)

• In debug builds, failure in the range optimizer for an ER_LOCK_DEADLOCK or ER_LOCK_WAIT_TIMEOUT error could go undetected and cause an assertion to be raised when a response was sent to the client. In release builds, this problem manifested as clients receiving an OK for a statement that had failed. (Bug #16366994, Bug #16247110)

• In debug builds, failure in the range optimizer for an ER_LOCK_DEADLOCK or ER_LOCK_WAIT_TIMEOUT error could go undetected and cause the filesort code to raise an assertion. In release builds, this problem manifested as clients receiving an ER_FILSORT_ABORT rather than the correct error code. (Bug #16366881)

• For debug builds, set_field_to_null() could raise an assertion for attempts to insert NULL into a NOT NULL column. (Bug #16362246)

• An assertion could be raised if, in greedy search mode, the optimizer identified join orders but was unable to choose one. (Bug #16361170)

• A race condition in vio_shutdown() could cause a server exit. (Bug #16354789)

• For debug builds, GROUP_CONCAT(... ORDER BY) within an ORDER BY clause could cause a server exit. (Bug #16347426)

• A GROUP_CONCAT() invocation containing subquery having an outer reference caused the server to exit. (Bug #16347343)

• The validate_password plugin did not always enforce appropriate constraints against assigning empty passwords. (Bug #16346443)

• Transforming some subqueries that select temporal or BIGINT types or to a semijoin caused a server exit on the second execution of prepared statements or stored programs. (Bug #16319671)

• Re-execution of a stored procedure could cause a server exit in Item_field::fix_outer_field. (Bug #16317443)

• For debug builds, the server could exit for queries involving a nested subquery, a subquery transformed into a semijoin and using a view. (Bug #16317076)

• The server could exit in do_copy_not_null() due to an improper NULL-value check. (Bug #16316564)

• No warning was generated if a duplicate index existed after dropping a column associated with a multiple-column index. (Bug #16315351)

• SELECT DISTINCT with WITH ROLLUP could result in a Duplicate entry 'NULL' for key '<auto_key>' error. (Bug #16314835)

• Oracle RPM packages were unusable by yum due to issues with the obsoletes line in the .spec file causing yum to interpret the package as obsoleting itself. (Bug #16298542)

• The range optimizer could set up incorrect ranges for queries that used XOR operations. (Bug #16272562)

• mysql_secure_installation could not connect to the server if the account used had an expired password. It invoked mysql noninteractively, resulting in that program failing to connect. Now mysql supports a --connect-expired-password option that indicates to the server that it can handle sandbox mode for expired-password accounts even if invoked noninteractively, and mysql_secure_installation invokes mysql with this option. (Bug #16248315)

• The usual failed-login attempt accounting was not applied to failed COM_CHANGE_USER commands. (Bug #16241992, Bug #17357535)

• For debug builds, an assertion could be raised if a failed LOAD DATA statement will followed by an INSERT for the same table within the same session. (Bug #16240526)

• If Loose Index Scan was used on a query that used MIN(), a segmentation fault could occur. (Bug #16222245)

• For debug builds, an assertion was incorrectly raised for queries executed using eq_ref access and filesort. (Bug #16164885)

• A user variable referenced during execution of a prepared statement is set to memory that is freed at the end of execution. A second execution of the statement could result in Valgrind warnings when accessing this memory. (Bug #16119355)

• Misoptimization of left expressions in prepared statements could cause a server exit. (Bug #16095534)

• If my_write() encountered a disk-full condition, it could return an incorrect error value. (Bug #16078792, Bug #19984788)

• The server could exit the second time a stored routine was invoked that performed an UPDATE or DELETE using an invalid column in the join clause. (Bug #16078466)

• Certain queries containing ORDER BY or SQL_CALC_FOUND_ROWS could cause a server exit for JSON-format EXPLAIN statements. (Bug #16077396, Bug #16078113)

• A prepared statement that used GROUP_CONCAT() and an ORDER BY clause that named multiple columns could cause the server to exit. (Bug #16075310)

• ORDER BY MATCH ... AGAINST could cause a server exit. (Bug #16073689)

• Creating a FEDERATED table without specifying a connection string caused a server exit. (Bug #16048546)

• When a partition is missing, code in ha_innodb.cc would retry 10 times and sleep for a microsecond each time while holding LOCK_open. The retry logic for partitioned tables was introduced as a fix for Bug#33349 but did not include a test case to validate it. This fix removes the retry logic for partitioned tables. If the problem reported in Bug#33349 reappears, a different solution will be explored. (Bug #15973904)

• Client programs from MySQL 5.6.4 and up could confuse older servers during the connection process by using newer protocol features not understood by older servers. (Bug #15965409)

• The mysql.server script exited with an error if the status command was executed with multiple servers running. (Bug #15852074)

• In some cases, REVOKE could fail to revoke the GRANT OPTION privilege. (Bug #14799187)

• Use of the VALUES() function in the VALUES() clause of an INSERT statement could result in Valgrind warnings or an unstable server, possibly leading to a server exit. (Bug #14789787)

• The Debug Sync facility could lose a signal, leading to a spurious ER_DEBUG_SYNC_TIMEOUT error. (Bug #14765080, Bug #18221750)

• The mysql client allocated but did not free a string after reading each line in interactive mode, resulting in a memory leak. (Bug #14685362)

• The optimizer trace could print ranges for key parts that were not usable for range access. (Bug #14615536)

• Killing a connection while it was in the process of disconnecting could lead to an assertion being raised, Valgrind warnings, and general unstability. (Bug #14560522)

• Passwords in statements were not obfuscated before being written to the audit log. (Bug #14536456)

• When running a query on INFORMATION_SCHEMA.INNODB_BUFFER_PAGE that requested table_name and index_name values, query results would include index pages without table_name or index_name values. (Bug #14529666)

• Several COM_xxx commands in the client-server protocol did not have length checks for incoming network packets, which could result in various problems for malformed input. (Bug #14525642)

• If used to process a prepared CALL statement for a stored procedure with OUT or INOUT parameters, mysql_stmt_store_result() did not properly set the flags required to retrieve all the result sets. (Bug #14492429, Bug #17849978)

• INSERT ... ON DUPLICATE KEY UPDATE on a view could cause a server exit. (Bug #14261010)

• With the thread pool plugin in use, normal connection termination caused the Aborted_clients status variable to be incremented. (Bug #14081240)

• A build failure occurred if HAVE_CRYPT was 0. (Bug #14036425)

• Grouping by an outer BLOB column in a subquery caused a server exit. (Bug #13966809, Bug #14700180)

• On Windows, command-line options of the form --opt_name="opt_value" worked but --opt_name='opt_value' did not.

  On all platforms, for Performance Schema options of the form --performance_schema_instrument="instrument=value", invalid instrument names now are rejected. (Bug #13955232)

• The server could exit after failing to handle an out-of-memory condition in open_normal_and_derived_tables(). (Bug #13553905)

• The server could exit due to improper handling of the error from an invalid comparison. (Bug #13009341)

• MySQL Installer, if run in custom install or change mode, offered installation options that had no effect. (Bug #12928601)

• Metadata returned for a prepared SELECT statement that had outer joins could indicate that columns containing NULL values were NOT NULL. (Bug #12818811)

• The thread pool plugin produced an error message containing an incorrect maximum thread_pool_prio_kickup_timer value. (Bug #12817590)

• For debug builds, the server could exit as a result of a series of statements that used a user variable such that its character set/collation changed from statement to statement. (Bug #12368577)

• Incorrect results could be returned from queries that used several aggr_func(DISTINCT) functions (where aggr_func() is an aggregate function such as COUNT()) when these referred to different columns of the same composite key. (Bug #12328597)

• Queries of the form SELECT ... UNION SELECT ... ORDER BY were parsed incorrectly, with the ORDER BY applied to the final SELECT rather than to the statement as a whole. (Bug #11886060)

• An identifier containing special characters could become too long after conversion of such characters to encoded format, resulting in SQL errors or failure to find files. (Bug #11766880)

• The CMake check for unsigned time_t failed on all platforms. (Bug #11766815)

• mysql_convert_table_format ignored --engine or -e as a synonym for the --type option. (Bug #11756950)

• mysqladmin debug causes the server to write debug information to the error log. On systems that supported mallinfo(), the memory-status part of this output was incorrect in 64-bit environments when mysqld consumed more than 4GB memory.

  Now the server uses malloc_info() to obtain memory-status information. malloc_info() does not report the memory that the glibc malloc() implementation internally allocates using mmap(). However, it does provide the memory usage information in all the memory arenas.

  This bug fix also involves a change of output format. The server now writes memory information in XML format rather than as plain text. Example:

  Memory status:
  <malloc version="1">
  <heap nr="0">
  <sizes>
  <size from="33" to="33" total="1056" count="32"/>
  <size from="65" to="65" total="65" count="1"/>
  <size from="113" to="113" total="226" count="2"/>
  <size from="129" to="129" total="2451" count="19"/>
  <size from="145" to="145" total="290" count="2"/>
  <size from="161" to="161" total="1288" count="8"/>
  <size from="209" to="209" total="418" count="2"/>
  </sizes>
  <total type="fast" count="0" size="0"/>
  <total type="rest" count="66" size="5794"/>
  <system type="current" size="10833920"/>
  <system type="max" size="10833920"/>
  <aspace type="total" size="10833920"/>
  <aspace type="mprotect" size="10833920"/>
  </heap>
  <total type="fast" count="0" size="0"/>
  <total type="rest" count="66" size="5794"/>
  <system type="current" size="10833920"/>
  <system type="max" size="10833920"/>
  <aspace type="total" size="10833920"/>
  <aspace type="mprotect" size="10833920"/>
  </malloc>

  (Bug #11746658)

• The DBUG_ENTER string for the THD::increment_questions_counter() function incorrectly named the THD::increment_updates_counter() function. (Bug #69989, Bug #17297266)

• RPM packages did not provide lowercase tags for their contents. For example, a server RPM indicated that it provided MySQL-server, but not mysql-server. (Bug #69830, Bug #17211588)

• If the WITH_SSL CMake option was specified with an incorrect path to the SSL installation or the path to an unsupported (too old) SSL installation, the option was implicitly changed to the bundled value and yaSSL was used instead. Now CMake exits with an error so the user knows that the option value must be changed. (Bug #69744, Bug #17162055)

• When selecting a union of an empty result set (created with WHERE 1=0 or WHERE FALSE) with a derived table, incorrect filtering was applied to the derived table. (Bug #69471, Bug #16961803)

  References: This issue is a regression of: Bug #15848521.

• For queries with ORDER BY ... LIMIT, the optimizer could choose a nonordering index for table access. (Bug #69410, Bug #16916596)

• If query_cache_type was disabled at startup to prevent the query cache from being enabled at runtime, disabling query_cache_type at runtime generated a warning even though it was already disabled. (Bug #69396, Bug #16906827)

• When an internal buffer was too small for the workload, the Performance Schema could spend a lot of time in an internal spin loop attempting to allocate a memory buffer, and fail. (Bug #69382, Bug #16945618)

• In the absence of SQL_CALC_FOUND_ROWS in the preceding query, FOUND_ROWS() should return the number of rows in the result set, but this did not always happen if the query contained ORDER BY. (Bug #69271, Bug #16827872)

• Full-text search on InnoDB tables failed on searches for words containing apostrophes. (Bug #69216, Bug #16801781)

• The libmysql.dll library was missing several symbols: my_init, mysql_client_find_plugin, mysql_client_register_plugin, mysql_load_plugin, mysql_load_plugin_v, mysql_options4, and mysql_plugin_options. (Bug #69204, Bug #16797982, Bug #62394)

• If an UPDATE containing a subquery caused a deadlock inside InnoDB, the deadlock was not properly handled by the SQL layer. The SQL layer then tried to unlock the row after InnoDB rolled back the transaction, raising an assertion inside InnoDB. (Bug #69127, Bug #16757869)

• FOUND_ROWS() could return an incorrect value if the preceding query used filesort. (Bug #69119, Bug #16760474)

  References: This issue is a regression of: Bug #68458.

• The optimizer could choose a poor execution plan for queries with ORDER BY ... LIMIT. (Bug #69013, Bug #16697792)

• Some possible cases of memory use after being freed were fixed. Thanks to Jan Staněk for the patch. (Bug #68918, Bug #16725945)

• Some LEFT JOIN queries with GROUP BY could return incorrect results. (Bug #68897, Bug #16620047)

  References: This issue is a regression of: Bug #11760517.

• Some errors could be handled by condition handlers only if they were raised by particular statements, such as INSERT, but not if they were raised by other statements, like UPDATE. An example would be the foreign-key error ER_NO_REFERENCED_ROW_2 which could be treated differently, depending on which statement raised it. (Bug #68831, Bug #16587369)

• When specified in an option file, the plugin-dir client option was ignored. (Bug #68800, Bug #16680313)

• Comparison of a DATETIME value and a string did not work correctly for the utf8_unicode_ci collation. (Bug #68795, Bug #16567381)

• When only counting events but not timing them, Performance Schema would report MIN_TIMER_WAIT values as a large number instead of 0. (Bug #68768, Bug #16552425)

• Using range access with an index prefix could produce incorrect results. (Bug #68750, Bug #16540042)

• Full-text search on InnoDB tables failed on searches for literal phrases combined with + or - operators. (Bug #68720, Bug #16516193)

• For debug builds, metadata locking for CREATE TABLE ... SELECT could raise an assertion. (Bug #68695, Bug #16503173)

• Compilation on Solaris using gcc produced incorrect builds for 32-bit systems. (Bug #68675)

• mysqld --help and mysqld --verbose --help performed unnecessary logging. (Bug #68578, Bug #16442113)

• A new CMake option, WITH_EDITLINE, is provided to indicate whether to use the bundled or system libedit/editline library. The permitted values are bundled (the default) and system.

  WITH_EDITLINE replaces WITH_LIBEDIT, which has been removed. (Bug #68558, Bug #16430208)

• Overhead for the skip_trailing_space() function was reduced. (Bug #68477, Bug #16395778)

• If Loose Index Scan was used to evaluate a query that compared an integer column to an integer specified as a quoted string (for example, col_name = '1'), the query could return incorrect results. (Bug #68473, Bug #16394084)

• Optimizations that used extended secondary keys (see Use of Index Extensions) worked only for InnoDB, even for storage engines with the requisite underlying capabilities. (Bug #68469, Bug #16391678)

• mysql_install_db incorrectly tried to create the mysql.innodb_table_stats and mysql.innodb_index_stats tables if InnoDB was not available. (Bug #68438, Bug #16369955)

• BIT(0) is not a valid data type specification but was silently converted to BIT(1). Now an ER_INVALID_FIELD_SIZE error occurs and the specification is rejected. (Bug #68419, Bug #16358989)

• In a MySQL server newer than MySQL 5.5 using a nonupgraded mysql.user table (for which mysql_upgrade had not been run), statements to set passwords caused a server exit due to a faulty check for the password_expired column. (Bug #68385, Bug #16339767)

• Indexes on derived tables that were used during the first invocation of a stored procedure were not used in subsequent invocations. (Bug #68350, Bug #16346367)

• If a function such as AES_DECRYPT() that requires SSL support failed, the error could affect later calls to functions that require SSL support. (Bug #68340, Bug #16315767)

• For DELETE and UPDATE statements, EXPLAIN displayed NULL in the ref column for some cases where const is more appropriate. (Bug #68299, Bug #16296268)

• The mysql client incorrectly used latin1 for certain comparisons even if started with a multibyte default character set, resulting in a client crash. (Bug #68107, Bug #16182919)

• In option files, the server could misinterpret option settings if the value was given after the option name with no = sign in between. (Bug #67740, Bug #15930031)

• Performance of prepared DML statements containing ? parameter substitution markers was improved under row-based logging format: Since the binary log in this case need not include the statement text, and since the statement will not be forced to statement-based logging as some DDL statements might be, there is no need to substitute ? markers to produce a statement suitable for logging. (Bug #67676, Bug #16038776)

• ELT(LEAST(...),..) could return a non-NULL value even if LEAST() returned NULL. (Bug #67578, Bug #16171537)

• If the server could not find the errmsg.sys file at startup, the resulting error message did not indicate which configuration parameter to check. (Bug #67576, Bug #15880018)

• mysqldump wrote SET statements as SET OPTION, which failed when reloaded because the deprecated OPTION keyword has been removed from SET syntax. (Bug #67507, Bug #15844882)

• For failure to create a new thread for the event scheduler, event execution, or new connection, no message was written to the error log. This could lead to the impression that the event scheduler was running normally when it was not. (Bug #67191, Bug #14749800, Bug #16865959)

• Configuring with cmake -DWITHOUT_SERVER to build clients without the server failed for builds outside of the source tree. (Bug #66000, Bug #14367046)

• mysqldump assumed the existence of the general_log and slow_log tables in the mysql database. It failed if invoked to dump tables from an older server where these tables do not exist. (Bug #65670, Bug #14236170)

• If an account had a nonzero MAX_USER_CONNECTIONS value, that value was not always respected. (Bug #65104, Bug #14003080)

• Attempts to build from a source RPM package could fail because the build process attempted to refer to a pb2user that might not exist. (Bug #64641, Bug #13865797, Bug #69339, Bug #16874980)

• When an ALTER TABLE operation was performed with an invalid foreign key constraint, the error reported was ER_CANT_CREATE_TABLE rather than ER_CANNOT_ADD_FOREIGN. (Bug #64617, Bug #13840553)

• If one session had any metadata lock on a table, another session attempting CREATE TABLE [IF NOT EXISTS] for the same table would hang. This occurred due to an attempt in the second session to acquire an exclusive metadata lock on the table before checking whether the table already existed. An exclusive metadata lock is not compatible with any other metadata locks, so the session hung for the lock timeout period if another session had the table locked.

  Now the server attempts to acquire a shared metadata lock on the table first to check whether it exists, then upgrade to an exclusive lock if it does not. If the table does exist, an error occurs for CREATE TABLE and a warning for CREATE TABLE IF NOT EXISTS. (Bug #63144, Bug #13418638)

• sql-common/client_plugin.c contained a nonportable use of a va_list parameter. (Bug #62769, Bug #13252623)

• InnoDB does not support full-text parser plugins, but failed to report an error if they were specified. Now an ER_INNODB_NO_FT_USES_PARSER error is returned. (Bug #62004, Bug #12843070)

• The url columns in the mysql datatbase help tables were too short to hold some of the URLs in the help content. These columns are now created as type TEXT to accommodate longer URLs. (Bug #61520, Bug #12671635)

• A typo in cmake/dtrace.cmake prevented DTrace support from being enabled by -DENABLE_DTRACE-on. (Bug #60743, Bug #12325449)

• The Turbo Boyer-Moore algorithm used for LIKE pattern matches failed to handle some patterns. The server now uses the original Boyer-Moore algorithm. (Bug #59973, Bug #11766777)

• Boolean plugin system variables did not behave well on machines where char is unsigned; some code attempted to assign a negative value to these. (Bug #59905, Bug #11864205)

• Some subquery transformations were not visible in EXPLAIN output. (Bug #59852, Bug #11766685)

• Configuring MySQL with -DWITH_EXTRA_CHARSETS=none caused a build failure. (Bug #58672, Bug #11765682)

• Two problems adding or subtracting keyword from the current debug system variable setting were corrected:

  • A debug value of 'd' means “all debug macros enabled”. The following sequence left the value in an incorrect state:

    mysql> SET debug = 'd';SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | d       |
    +---------+
    
    
    mysql> SET debug = '+d,M1';SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | d,M1    |
    +---------+

    The first SET statement enables all debug macros. The second SET should add the M1 macro to the current set, which should result in no change because the current set is already “all macros”. Instead, the second SET reset the current set to only the M1 macro, effectively disabling all others. The server now correctly leaves debug set to 'd'.

  • A debug value of '' means “no debug macros enabled”. The following sequence left the value in an incorrect state:

    mysql> SET debug = 'd,M1';SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | d,M1    |
    +---------+
    
    
    mysql> SET debug = '-d,M1';SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | d       |
    +---------+

    The first SET statement sets debug to the M1* macro. The second SET should subtract the M1 macro from the current set, leaving no debug macros enabled. Instead, the second SET reset the current set to 'd' (all macros enabled). The server now correctly sets debug to ''.

  (Bug #58630, Bug #11765644)

• It is now possible to suppress installation of the mysql-test directory after compiling MySQL from source by invoking CMake with the INSTALL_MYSQLTESTDIR option explicitly set to empty:

  cmake . -DINSTALL_MYSQLTESTDIR=

  Previously, attempts to do this resulted in an error. (Bug #58615, Bug #11765629)

• On 64-bit OS X systems, CMake used x86 rather than x86_64 when determining the machine type. (Bug #58462, Bug #11765489)

• Long table or column names could cause mysqlshow to exit. (Bug #53956, Bug #11761458)

• With big_tables enabled, queries that used COUNT(DISTINCT) on a simple join with a constant equality condition on a non-duplicate key returned incorrect results. (Bug #52582, Bug #11760197)

  References: See also: Bug #18853696.

• The !includedir directive in option files did not read .cnf or .ini files that included a dot in the file name preceding the extension. (Bug #51609, Bug #11759306)

• Successful queries served from the query cache did not clear warnings. (Bug #49634, Bug #11757567)

• If ALTER TABLE was used to set the default value for a TIMESTAMP or DATETIME column that had CURRENT_TIMESTAMP as its default when it was created, the new default was not shown by SHOW CREATE TABLE, and incorrect values could be inserted into the column. (Bug #45669, Bug #11754116, Bug #76610, Bug #20848203)

• IF() function evaluations could produce different results when executed in a prepared versus nonprepared statement. (Bug #45370, Bug #11753852)

• The range optimizer used the wrong prerequisite for concluding that a table is the inner table of an outer join. This led to incorrect cost estimates and choice of the wrong index for query processing. (Bug #37333, Bug #11748775)

• For better robustness against stack overflow, the server now accounts for the size of the guard area when making thread stack size requests. (Bug #35019, Bug #11748074)

• If mysqld crashed during a shutdown initiated by /etc/init.d/mysql stop, mysqld_safe restarted mysqld when it should not have. (Bug #34084, Bug #13864548)

• mysql.h no longer defines __WIN__ on Windows, and the MySQL sources have been changed to test for _WIN32 instead. (Bug #20338, Bug #11745828)