Changes in MySQL 5.7.23 (2018-07-27, General Availability)

Note

This release includes a change to the innodb_index_stats and innodb_table_stats system tables. When upgrading to this release, be sure to run mysql_upgrade in order to include these changes.

• Audit Log Notes

• Compilation Notes

• Deprecation and Removal Notes

• Firewall Notes

• Security Notes

• Functionality Added or Changed

• Bugs Fixed

Audit Log Notes

• For new MySQL installations, the USER and HOST columns in the audit_log_user table used by MySQL Enterprise Audit now have definitions that better correspond to the definitions of the User and Host columns in the mysql.user system table.

  For upgrades to an installation for which MySQL Enterprise Audit is already installed, it is recommended that you alter the table definitions as follows:

  ALTER TABLE mysql.audit_log_user
    DROP FOREIGN KEY audit_log_user_ibfk_1;
  ALTER TABLE mysql.audit_log_filter
    ENGINE=InnoDB;
  ALTER TABLE mysql.audit_log_filter
    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
  ALTER TABLE mysql.audit_log_user
    ENGINE=InnoDB;
  ALTER TABLE mysql.audit_log_user
    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
  ALTER TABLE mysql.audit_log_user
    MODIFY COLUMN USER VARCHAR(32);
  ALTER TABLE mysql.audit_log_user
    ADD FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME);

  (Bug #23706056)

Compilation Notes

• Solaris: MySQL now can be compiled on Solaris using gcc. (Bug #27802681)

Deprecation and Removal Notes

• Explicit ASC or DESC qualifiers for GROUP BY clauses are now deprecated and will be removed in a future MySQL version. (WL #11459)

Firewall Notes

• The MySQL Enterprise Firewall firewall_whitelist table now contains a primary key column named ID. (Bug #27164826)

Security Notes

• The linked OpenSSL library for the MySQL Commercial Server has been updated to version 1.0.2o. Issues fixed in the new OpenSSL version are described at http://www.openssl.org/news/vulnerabilities.html.

  This change does not affect the Oracle-produced MySQL Community build of MySQL Server, which uses the yaSSL library instead. (Bug #28025379)

• In MySQL 5.7, the default authentication plugin is mysql_native_password. As of MySQL 8.0, the default authentication plugin is changed to caching_sha2_password. To enable MySQL 5.7 clients to connect to 8.0 and higher servers using accounts that authenticate with caching_sha2_password, the MySQL 5.7 client library and client programs now support the caching_sha2_password client-side authentication plugin. This improves MySQL 5.7 client connect-capability compatibility with respect to MySQL 8.0 and higher servers, despite the differences in default authentication plugin. For more information, see Caching SHA-2 Pluggable Authentication. (WL #11645)

Functionality Added or Changed

• Previously, for the --ssl-mode=VERIFY_IDENTITY or --ssl-verify-server-cert option, the client checked whether the host name that it used for connecting matched the Common Name value in the certificate but not the Subject Alternative Name value. Now, if the client uses OpenSSL 1.0.2 or higher, the client checks whether the host name matches either the Subject Alternative Name value or the Common Name value in the server certificate. Thanks to Daniël van Eeden for a patch on which this change was based. (Bug #16211011, Bug #68052, Bug #27511233, Bug #89578)

Bugs Fixed

• Important Change; Partitioning: After creating partitioned InnoDB tables with very long names, the table_name columns in the corresponding entries in the mysql.innodb_index_stats and mysql.innodb_table_stats system tables were truncated. To fix this issue, the length of the table_name column in each of these tables has been increased from 64 to 199 characters. In both cases, this is now the same as the lengths of these columns in MySQL 8.0.

  When upgrading to this release, use mysql_upgrade to effect these changes in your MySQL installation. In the event that you fail to do this, MySQL generates the warning Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade in the error log.

  Note

  Some platforms, such as Microsoft Windows, may restrict path lengths (MAX_PATH) to a maximum of 260, which can cause creation of partitioned tables with long names to fail. You can avoid this problem on Windows systems by enabling NTFS long path names; see your system documentation for information on how to do this.

  (Bug #86926, Bug #26390736)

• InnoDB: The ngram full-text search parser permitted comma and period characters to be tokenized as words, which caused an inconsistency between boolean and natural language mode search results. Comma and period characters are no longer tokenized. (Bug #27847697)

• InnoDB: An I/O error returned by an fsync() operation is now treated as a hard error. (Bug #27805553, Bug #90296)

• InnoDB: A schema mismatch error reported during an import tablespace operation failed to print mismatched table flags in a readable format. (Bug #27542720)

• InnoDB: A DDL operation failed to wait for a FULLTEXT index optimization operation to finish. (Bug #27326796)

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

• InnoDB: An unnecessary check for read-only transactions was removed from the trx_set_rw_mode() function. Thanks to Sandeep Sethia for the patch. (Bug #27211287, Bug #88739)

• InnoDB: A DDL operation that added a foreign key constraint raised an assertion when it accessed a stale memory object that belonged to the parent table. (Bug #27208858)

• InnoDB: A DDL operation on a table with a FULLTEXT index during full-text index cache synchronization caused an assertion failure. (Bug #27082268, Bug #27095935)

• InnoDB: A failing assertion occurred after initiating a memcached get operation. (Bug #26876594)

• InnoDB: A corrupt index ID encountered during a foreign key check raised an assertion. (Bug #26654685)

• InnoDB: An internal deadlock during a DDL operation resulted in a long semaphore wait followed by a server exit. (Bug #26225783)

• InnoDB: A DDL operation encountered a serious error due to an invalid lock upgrade. (Bug #26225783)

• InnoDB: On a Windows 64-bit system, invalid buffer pool configuration values caused the server to exit on startup. (Bug #26100239, Bug #86370)

• Partitioning: For a partitioned table, partition update time could be incorrect after rebuilding the table or restarting the server. (Bug #27073100)

• Partitioning: An extraneous row lock was imposed by an update to a partitioned InnoDB table. (Bug #87253, Bug #26553164)

• Replication: The log messages generated when a member fails to join the group have been improved, for example when group_replication_group_name on the member joining the group does not match the seed's group_replication_group_name this is now described in the log message. (Bug #27628695)

• Replication: The use of replication filters or binary log filters can cause issues when they are applied to tables that are updated with XA transactions. Filtering of tables could cause an XA transaction to be empty on a replication slave, and empty XA transactions are not supported. Also, with the settings master_info_repository=TABLE and relay_log_info_repository=TABLE on a replication slave, which became the defaults in MySQL 8.0, the internal state of the data engine transaction is changed following a filtered XA transaction, and can become inconsistent with the replication transaction context state.

  Due to these issues, the use of replication filters or binary log filters in combination with XA transactions is not supported. This fix adds the new error ER_XA_REPLICATION_FILTERS, which is logged whenever an XA transaction is impacted by a replication filter, whether or not the transaction was empty as a result. If the transaction is not empty, the replication slave is able to continue running, but you should take steps to discontinue the use of replication filters with XA transactions in order to avoid potential issues. If the transaction is empty, the replication slave stops. In that event, the replication slave might be in an undetermined state in which the consistency of the replication process might be compromised. In particular, the gtid_executed set on a slave of the slave might be inconsistent with that on the master. To resolve this situation, isolate the master and stop all replication, then check GTID consistency across the replication topology. Undo the XA transaction that generated the error message, then restart replication. (Bug #27442477)

• Replication: When a transaction larger than the binary log transaction cache size (binlog_cache_size) was flushed to a temporary file during processing, and the flush failed due to a lack of space in the temporary directory, the flush error was not handled correctly. No message was written to the error log, and the binary log cache was not cleared after the transaction was rolled back. Now, in this situation, the server takes an appropriate action based on the binlog_error_action setting (shut down the server or halt logging), and writes a message to the error log. When the transaction is rolled back, the server checks for flush errors and clears the binary log cache if any occurred. (Bug #27399620, Bug #89272)

• Replication: When GTIDs are in use for replication, replicated transactions that are filtered out on the slave are persisted. If binary logging is enabled on the slave, the filtered-out transaction is written to the binary log as a Gtid_log_event followed by an empty transaction containing only BEGIN and COMMIT statements. If binary logging is disabled, the GTID of the filtered-out transaction is written to the mysql.gtid_executed table. This process ensures that there are no gaps in the set of executed GTIDs, and that the filtered-out transactions are not retrieved again if the slave reconnects to the master. Previously, this process was not done for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements, but it is now carried out for those statements as well as for others. (Bug #27308751, Bug #88891)

• Replication: On a multithreaded slave, when a STOP SLAVE statement is executed on the slave, followed by a START SLAVE statement, the error log can report a different position in the binary log for the slave SQL thread when exiting, compared to the position reported for the slave SQL thread at the subsequent initialization.

  For a multithreaded slave, the position reported for the SQL thread on exit is a low water mark, up to which the replication stream is consistent and has no gaps. Transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not. However, this low water mark was being reported before the process to stop the worker threads was actually carried out, and the low water mark was subsequently updated by a checkpoint routine during that process. The timing of the log message has now been changed so that the final low water mark is reported as the position for the SQL thread on exit. (Bug #27300658)

• Replication: An online upgrade of a replication group that was running a version of MySQL earlier than 5.7.23 was not possible. When a member running a version earlier than either MySQL 5.7.22 or MySQL 8.0.11 tried to join a group with members running MySQL 5.7.21 or earlier, it failed to join the group because MySQL 5.7.21 does not send the value of it lower_case_table_names system variable. This mean that the later version compared the lower_case_table_names variable with an invalid value and exited the group. The work around is to take the group offline, upgrade to version 5.7.23 or 8.0.12 and later. Subsequent upgrades of the group can then be carried out without taking the group offline. See Upgrading Group Replication. (Bug #90794, Bug #27991334)

• Replication: In certain situations, such as during distributed recovery procedure, the certification info garbage collection was purging more data than it should, resulting in conflicts not being detected. The garbage collection procedure has been improved to take this case in consideration. (Bug #89938, Bug #27652526)

• Replication: The ER_GRP_RPL_SQL_SERVICE_FAILED_TO_RUN_SQL_QUERY error was being logged incorrectly. (Bug #89788, Bug #27590534)

• Group Replication: Using an IP address or hostname in any Group Replication related configuration on macOS was failing. (Bug #27376511, Bug #89123, Bug #27604471)

• Group Replication: When the group_replication_applier channel's applier thread encountered an error, the master_log_name and end_log_pos values shown in the resulting error message were incorrect, since the events in a transaction are replicated before they are written to the binary log of the member where the transaction originated, with the result that the final values are unknown at the time they are applied on the replica by the applier thread. To avoid confusion, any such error messages encountered by a group_replication_applier channel no longer contain the binary log name or position. (Bug #89146, Bug #27368735)

• A heap overflow vulnerability in the MySQL client library was fixed. (Bug #27980823)

• For generated columns that used the INTERVAL() function, incorrect behavior could occur. (Bug #27881102)

• The exec_in_background command for mysqltest is now available in MySQL 5.7. (Bug #27858055)

• An unencrypted connection could result from a client connection attempt specifying that an encrypted connection was required, if the server was not configured to support SSL. (Bug #27759871)

• A BETWEEN clause comparing negative values could lead to erroneous results. (Bug #27691347)

• Audit log filter rules did not permit class names to be specified as an array of strings. That is now possible. Example:

  {
    "filter": {
      "class": [
        { "name": [ "connection", "general", "table_access" ] }
      ]
    }
  }

  (Bug #27628325)

• On Windows, if the Visual C++ Redistributable for Visual Studio had been removed, MySQL uninstallation using the MSI installer failed. (Bug #27621546)

• The audit_log_read() loadable function could deadlock. In consequence of the fix for this issue, audit_log_read() no longer uses a shared global buffer. Each call allocates its own buffer, which exists for the duration of the call. To control the buffer size, set the audit_log_read_buffer_size system variable. Previously, this variable existed only in global scope with a default of 1MB. Now, the variable also exists in session scope, has a smaller default of 32KB, and can be set at runtime. Each client should set its session value of audit_log_read_buffer_size appropriately for its use of audit_log_read(). (Bug #27545962)

• Within stored procedures, it was not possible to write a condition handler to catch foreign key failures if the table involved used the FEDERATED storage engine. (Bug #27509959)

• With the NO_ENGINE_SUBSTITUTION SQL mode disabled, an error (rather than substitution) occurred for CREATE TABLE and ALTER TABLE if the desired engine was disabled using the disabled_storage_engines system variable. (Bug #27502530)

• If MySQL Enterprise Firewall was configured with a large number of rules, server shutdown could take a long time. (Bug #27492122)

• A memory leak in the pfs-t unit test was fixed. Thanks to Yura Sorokin for the patch. (Bug #27440735, Bug #89384)

• With automatic_sp_privileges enabled, the EXECUTE and ALTER ROUTINE privileges were not correctly granted to routine creators. (Bug #27407480)

• Some queries that used ordering could access an uninitialized column during optimization and cause a server exit. (Bug #27389294)

• For debug builds, an assertion was raised (rather than an error) for ALTER TABLE for an ARCHIVE table with a GEOMETRY NOT NULL column containing empty strings. (Bug #27330634, Bug #89088)

• If system tables could not be read or initialized, the server wrote an incomplete error message. Thanks to Daniël van Eeden for a patch related to the fix. (Bug #27302337, Bug #89001)

• The keyring_okv plugin did not always fail over to the standby OKV server when the primary server was unavailable. (Bug #27244099)

• The Performance Schema could attempt to access invalid SOURCE column values. (Bug #27231036)

• Mishandling of internal privilege structures could cause a server exit. (Bug #27230925)

• The UNION of a user-defined variable and a DECIMAL column produced an incorrect result when using a UTF8 character set. (Bug #27197235)

• Setting max_execution_time sometimes had no effect when used with full-text search. (Bug #27155294)

• For attempts to increase the length of a VARCHAR column of an InnoDB table using ALTER TABLE with the INPLACE algorithm, the attempt failed if the column was indexed.

  If an index size exceeded the InnoDB limit of 767 bytes for COMPACT or REDUNDANT row format, CREATE TABLE and ALTER TABLE did not report an error (in strict SQL mode) or a warning (in nonstrict mode). (Bug #26848813)

• In MySQL 5.7.19, the mysql.gtid_executed table was excluded from dumps. This table is no longer excluded but its data is not dumped. (Bug #26643180, Bug #87455)

  References: This issue is a regression of: Bug #82848, Bug #24590891.

• The slave_rows_search_algorithms system variable controls how rows are searched for matches when preparing batches of rows for row-based logging and replication. Specifying INDEX_SCAN as one of the search algorithms performs an index scan if an index is present. In the situation where a different primary key is used on the master and the slave, and a unique key is present on the slave, a bug in the code meant that the index scan was not being performed as it should be, and a slower table scan was being performed instead. The issue has now been corrected so that an index scan is used. (Bug #26450129, Bug #23311892, Bug #81500, Bug #81501)

• Certain cases of subquery materialization could cause a server exit. These queries now produce an error suggesting that materialization be disabled. (Bug #26402045)

• mysqldump exited abnormally for large --where option values. (Bug #26171967, Bug #86496, Bug #27510150)

• For MyISAM tables, particular sequences of INSERT and DELETE statements could cause table corruption. (Bug #25541037)

• The SHOW CREATE USER statement requires the SELECT privilege for the mysql database, except to see information for the current user. In addition, for the current user, the statement requires the SELECT privilege for the mysql.user system table for display of the password hash in the IDENTIFIED AS clause; otherwise, the hash displays as <secret>. (Bug #24911117)

• CREATE FUNCTION produced an error if there was no default database and the function body called another function. (Bug #24357244, Bug #82350)

• For debug builds, a SELECT statement executed within a stored program could raise an assertion if derived table resolution failed. (Bug #23221336)

• If mysqldump or mysqlpump were used to dump binary data without the --hex-blob option, reloading the dump file could produce spurious warnings (values were inserted correctly regardless of the warnings). Such values are now written preceded by the _binary introducer to silence the warnings. (Bug #22601255, Bug #80150)