Changes in MySQL 5.7.20 (2017-10-16, General Availability)

• Audit Log Notes

• Deprecation and Removal Notes

• Installation Notes

• Packaging Notes

• Security Notes

• X Plugin Notes

• Functionality Added or Changed

• Bugs Fixed

Audit Log Notes

• Event-matching filter rules for the audit_log plugin now support an abort element, which can be used to prevent qualifying events from executing. For more information, see Audit Log Filtering. This capability can be used, for example, to augment the capabilities of MySQL Enterprise Firewall, which blocks SQL statements on a per-user basis, by writing audit filtering rules that match statements and block them based on characteristics of the statements themselves.

Deprecation and Removal Notes

• Previously, the --transaction-isolation and --transaction-read-only server startup options corresponded to the tx_isolation and tx_read_only system variables. For better name correspondence between startup option and system variable names, transaction_isolation and transaction_read_only have been created as aliases for tx_isolation and tx_read_only. The tx_isolation and tx_read_only variables are now deprecated and will be removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation and transaction_read_only instead.

• The query cache is now deprecated and is removed in MySQL 8.0. Deprecation includes these items:

  • The FLUSH QUERY CACHE and RESET QUERY CACHE statements.

  • The SQL_CACHE and SQL_NO_CACHE SELECT modifiers.

  • These system variables: have_query_cache, ndb_cache_check_time, query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

  • These status variables: Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks.

• The mysql client by default strips comments in statements sent to the server, and this behavior is controlled using --skip-comments (strip comments), and --comments (preserve comments).

  Comment stripping is now deprecated. This feature and the options to control it will be removed in a future MySQL release.

• These syntax constructs for table and column references are now deprecated and will be removed in a future version of MySQL. Instances of these constructs should be changed to remove the leading period.

  • .col_name

  • .tbl_name

  • .tbl_name.col_name

Installation Notes

• For platforms that use systemd (see Managing MySQL Server with systemd), the data directory is initialized if empty at server startup. This might be a problem if the data directory is a remote mount that has temporarily disappeared: The mount point would appear to be an empty data directory, which then would be initialized as a new data directory. It is now possible to suppress this automatic initialization behavior. Specify the following line in the /etc/sysconfig/mysql file (create the file if it does not exist):

  NO_INIT=true

  (Bug #26595288, Bug #87287)

Packaging Notes

• mysqlcheck was missing in the MySQL Server Docker image, which prevented mysql_upgrade from running. (Bug #26400146, Bug #86968)

Security Notes

• Certificates automatically generated by mysqld and mysql_ssl_rsa_setup now use X509 v3 rather than v1. (Bug #26521654)

• The keyring_okv plugin now supports password-protecting the key file used for secure connections. See Using the keyring_okv KMIP Plugin.

X Plugin Notes

• A Mysqlx.Connection.CapabilitiesGet request using MySQL X Protocol did not return the complete list of available authentication mechanisms. (Bug #26044113)

• For mixed case or uppercase schema names, the statement list_objects could incorrectly report a collection as a table. (Bug #25769683)

• The X Plugin was omitted from the list of plugins to include for testing data directory permissions. (Bug #24823999)

Functionality Added or Changed

• Replication: In previous versions issuing STOP GROUP_REPLICATION stopped the plugin but the server still accepted transactions. This meant the transactions were not transmitted to the group. To make STOP GROUP_REPLICATION safer, now super_read_only is set to ON immediately upon issuing STOP GROUP_REPLICATION, which ensures no transactions are accepted. (Bug #25495067, Bug #84795)

• Replication: The group_replication_member_weight variable has been added which enables you to control the election of new primaries in single-primary mode. In previous versions primary election was based on the member's UUID, with the lowest UUID elected as the new primary in the event of fail over. Use this variable to assign numeric weights to members to ensure that specific members are elected, for example during scheduled maintenance of the primary or to ensure certain hardware is prioritized.

Bugs Fixed

• InnoDB: Invalid error handling code was removed from a function related to tablespace import. (Bug #26595476)

• InnoDB: A check for discarded partitions during a DML operation only checked the first partition. Failure to check for other discarded partitions caused an assertion failure. (Bug #25942592)

• InnoDB: Replication lag occurred on slave instances during large update operations on tables with many partitions. (Bug #25687813, Bug #85352)

• InnoDB: A failure occurred during an end range comparison. (Bug #25669686)

• InnoDB: Enabling the innodb_buffer_pool_load_now setting failed in read-only mode. The event that signals the buffer pool load thread was not initialized. (Bug #25586766)

• InnoDB: Test-related code intended to simulate a random read on a nonexistent page raised an invalid assertion. (Bug #25479538)

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

• InnoDB: A long wait for a dictionary operation lock held by a full-text search synchronization operation caused a server exit. (Bug #24938374, Bug #26376681, Bug #26376239)

• Partitioning: In certain cases when fetching heap records a partition ID could be set to zero. (Bug #86255, Bug #26034430)

• Partitioning: Queries involving NULL were not always handled correctly on tables that were partitioned by LIST. (Bug #76418, Bug #20748521)

  References: See also: Bug #86255, Bug #26034430.

• Replication: MySQL internal administration commands that update replication-specific repository tables, for example during a replication synchronization check using the mysqlrplsync utility, can now bypass read locks. This enables such commands to execute regardless of the settings for the read_only and super_read_only system variables and the autocommit mode. (Bug #26414532, Bug #86224)

• Replication: When the Group Replication plugin Delayed_initialization_thread failed to start due to unavailable resources, a locked mutex was being kept behind which would cause issues on Delayed_initialization_thread destructor. The fix ensures that the mutex is unlocked when the thread fails to start. (Bug #26394678)

• Replication: If the options file contained Group Replication related settings the server could stop unexpectedly on start up. (Bug #26314472)

• Replication: FLUSH LOGS attempted to send an OK message after having already sent an error response during the commit phase. Thanks to Laurynas Biveinis for the patch. (Bug #26272158, Bug #25363745, Bug #84437)

• Replication: With some workloads that contained large inserts, the XCOM transaction cache could consume a large amount of memory. The fix limits the size of the cache to reduce the impact to memory usage. (Bug #26241291)

• Replication: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE was set to an incorrect value when group_replication_recovery_complete_at="transactions_certified" on a recovering member. (Bug #26180350)

• Replication: The values of the group_replication_recovery_use_ssl and group_replication_recovery_ssl_verify_server_cert variables were not being updated when configured for the Group Replication recovery channel. (Bug #26142801)

• Replication: When replicating a partitioned table with an index, on a replication slave where HASH_SCAN was specified as part of the slave_rows_search_algorithms setting, the slave I/O thread sometimes stopped with an error HA_ERR_KEY_NOT_FOUND. (Bug #26137159)

• Replication: It was possible to set server_uuid to the same value as group_replication_group_name. Doing so could result in unexpected behavior because GTIDs are identified by a UUID. Now it is not possible to set server_uuid to the same value as group_replication_group_name. (Bug #26035931)

• Replication: The system variable pseudo_slave_mode, which is for internal server use, sometimes raised an assertion when it was changed inside a transaction. The server no longer changes this variable inside a transaction. (Bug #26034192, Bug #86250)

• Replication: A misleading warning was issued when the command FLUSH LOGS or PURGE LOGS BEFORE was used on a binary log file with an expiry time set, and the binary log file was in use. The warning related to the file being in use, and implied that a purge attempt had taken place, even if the expiry time had not yet been reached. Now, MySQL checks the expiry time of the binary log file first, and only then checks whether the file is in use. So the warning is only issued for an in-use binary log file that is old enough to be purged. (Bug #25973525)

• Replication: Group Replication stopped unexpectedly when more than 1024 file descriptors were in use. (Bug #25892493)

• Replication: Now that XA transactions are prepared and committed in two parts, an issue with statement-based replication has been identified. If two XA transactions committed on the master are being prepared on the slave in the inverse order, locking dependencies can occur that cannot be safely resolved. The issue is not present with row-based replication.

  XA transactions are therefore now considered unsafe for statement-based replication.

  • When binlog_format = STATEMENT, a warning is issued for DML statements inside XA transactions, and replication might fail with deadlock on slaves.

  • When binlog_format = MIXED, DML statements inside XA transactions are logged using row-based replication.

  • When binlog_format = ROW, DML statements inside XA transactions are logged as before.

  (Bug #25786490, Bug #85639)

• Replication: Replication clients no longer enable LOCAL capability for LOAD DATA statements, because they do not use LOAD DATA LOCAL statements. (Bug #24763131)

• Replication: In case of a failure while creating multiple slave applier worker threads, some threads would be left orphaned and their resources would not be collected. Thanks to Laurynas Biveinis for his contribution to fixing this bug. (Bug #24679056, Bug #82980)

• Replication: The binlog_checksum option cannot be changed within a transaction. MySQL cannot log this statement, as would be required inside a transaction, while the requested function is being performed on the binary log. (Bug #22914463)

• Replication: If hostname resolution was not working for a member in Group Replication, the error returned when attempting to connect was referring to credentials. The error message has been improved to describe the problem with hostname resolution. (Bug #86858, Bug #26368004)

• Replication: Group Replication partition threads were not visible in the Performance Schema tables. (Bug #86626, Bug #26241008)

• Replication: group_replication_force_members could be used in situations where the group was working properly, in other words a majority was reachable. This incorrect use could cause instability in the group. Therefore, its use has been restricted to the scenario for which it was created, for forming a new membership from a subset of a previous group's membership when a majority of the members are unreachable. (Bug #86359, Bug #26093967)

• Replication: The delayed initialization mechanism used for server starts has been improved. Now, it only blocks connections until the server is in read mode. (Bug #86271, Bug #26037344)

  References: See also: Bug #84731, Bug #25475132.

• Replication: When a primary member, for example the primary in single-primary group or in a multi-primary group, which also had asynchronous replication channels feeding data into it was stopped, the asynchronous channels would continue applying changes. Although super_read_only was being set when STOP GROUP_REPLICATION was issued, this did not stop any running asynchronous replication channels which were already running. This meant that changes could be made locally on the member, and that the asynchronous replication channels had to be stopped manually. Now when Group Replication stops, either due to an error or when STOP GROUP_REPLICATION is issued, all asynchronous replication channels are stopped. (Bug #86222, Bug #26024253)

• Replication: If the Group Replication plugin was uninstalled while a group was running, the plugin was marked for uninstall. This could cause the server shutdown to not complete successfully. The fix ensures that the Group Replication plugin cannot be uninstalled if the majority of the group cannot be reached. In such a case it is necessary to issue STOP GROUP_REPLICATION before uninstalling the plugin. (Bug #85322, Bug #25673788)

• Replication: The logging of Group Replication has been improved. Now logging includes information when a member joins or leaves, when the view changes, and so on. (Bug #84798, Bug #25495393)

  References: See also: Bug #26422857.

• Compiling with -DWITHOUT_SERVER=1 resulted in my_symlink.c compilation failure due to missing #include for my_dir.h. Thanks to Christian Hesse for the patch. (Bug #26495816, Bug #87137)

• yaSSL could incorrectly perform TLS cipher negotiation. (Bug #26482173)

• The Linux RPM spec file for RHEL6 and higher is updated with comments that recommend installing the redhat-rpm-config package to add convenience macros that make rebuilding the RPM package easier. Thanks to Simon Mudd for the patch. (Bug #26474153, Bug #87098)

• If the error log was misconfigured and the server could not start, no output describing the problem was produced. (Bug #26447825, Bug #87087)

• mysqld_multi was modified to use mysqld --initialize rather than the deprecated mysql_install_db. Thanks to Zhan Shi for the patch. (Bug #26446321, Bug #87080)

• Adding an ORDER BY to a query that included an outer join and a subquery caused a constant value defined for a column in the subquery to be incorrectly promoted to a constant value in the case when the subquery returns 0 rows. (Bug #26432173)

• The server failed to check the maximum path length for partition names. (Bug #26390632)

• Identifiers containing a mixt of backslashes and backticks could be parsed incorrectly. (Bug #26372491)

• Source packages for Debian platforms contained prebuilt debug binaries, causing build failures on any architectures other than the one on which those binaries were built. (Bug #26186911)

• The mysqld_pre_systemd script in RPM packages found the error log setting in option files if specified as log-error but not as log_error, though both are permitted. (Bug #26148391, Bug #86466)

• When running mysqlbinlog with the --read-from-remote-server option, rewrite rules specified using the --rewrite-db option were ignored, so data was not written to the target database. (Bug #26117735, Bug #86288)

• REFERENCES privilege checking could use the incorrect database in some cases. (Bug #26106655)

• Uninstalling the daemon_memcached plugin caused a serious error. (Bug #25909540)

• The rpl_diff.inc test case file did not find the data difference between servers. Thanks to Yura Sorokin for the patch. (Bug #25860138, Bug #85838)

• An ngram fulltext parser search query returned incorrect results and raised an assertion. (Bug #25851975)

• Selecting from a view could yield different results with materialization enabled versus materialization disabled. (Bug #25782811, Bug #85622)

• The Performance Schema failed to check the maximum host length for client connections. (Bug #25510805)

• mysqlpump displayed incorrect progress information about the number of tables dumped. (Bug #25432850)

• Some mysqldump warnings went to the standard output rather than the standard error output and consequently were written to the dump file. (Bug #25380000, Bug #82992)

• LOAD DATA failed to accept multibyte characters that followed an escape sequence. (Bug #25147988, Bug #83950, Bug #25865525)

• A server error occurred when a full text search result exceeded the innodb_ft_result_cache_limit setting. The patch for this bug also backports a related patch (Bug #21140111). (Bug #25033538)

• If a stored function was considered a constant by the optimizer, calling it from a subquery in a NOT IN condition in the WHERE clause could cause a server exit. (Bug #23577867)

• A mysqldump memory leak was fixed. Thanks to Yura Sorokin for the patch. (Bug #23531150, Bug #81714)

• Incorrect results or a server exit could result when a query used Batched Key Access optimization and a virtual generated column was part of the join buffer. (Bug #23169112)

• If a session rolled back to a savepoint and then was killed, the statements up to the point of the savepoint could be committed. (Bug #22350047, Bug #79596)

• For clients that used Connector/Python and authenticated using the sha256_password plugin, the server could handle connections incorrectly. (Bug #21421642)