MySQL Community Server 8.0.17 has been released (part 1/3)
Posted by: Balasubramanian Kandasamy
Date: July 22, 2019 03:04AM
Date: July 22, 2019 03:04AM
[Due to size limitation, this announcement is split in three parts. This is part 1] Dear MySQL users, MySQL Server 8.0.17, a new version of the popular Open Source Database Management System, has been released. MySQL 8.0.17 is recommended for use on production systems. For an overview of what's new in MySQL 8.0, please see http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html For information on installing MySQL 8.0.17 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/8.0/en/installing.html MySQL Server 8.0.17 is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/ MySQL Server 8.0.17 is also available from our repository for Linux platforms, go here for details: http://dev.mysql.com/downloads/repo/ Windows packages are available via the Installer for Windows: http://dev.mysql.com/downloads/installer/ along with .ZIP (no-install) packages for more advanced needs. 8.0.17 also comes with a web installer as an alternative to the full installer. The web installer doesn't come bundled with any actual products and instead relies on download-on-demand to fetch only the products you choose to install. This makes the initial download much smaller but increases install time as the individual products will need to be downloaded. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://bugs.mysql.com/report.php The following link lists the changes in the MySQL 8.0 since the release of MySQL 8.0.16. It may also be viewed online at http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html Enjoy! Changes in MySQL 8.0.17 (2019-07-22, General Availability) * Audit Log Notes * C API Notes * Character Set Support * Component Notes * Configuration Notes * Debugging Notes * Deprecation and Removal Notes * Installation Notes * Keyring Notes * Packaging Notes * Performance Schema Notes * Plugin Notes * X Plugin Notes * Functionality Added or Changed * Bugs Fixed Audit Log Notes * Encryption and decryption operations for encrypted MySQL Enterprise Audit log files use a password stored in the MySQL keyring. Previously, only a single password was stored. Generating a new password made the old password inaccessible, rendering MySQL Enterprise Audit unable to read log files encrypted with the old password. MySQL Enterprise Audit now archives old passwords to maintain a password history in the keyring, and includes in each encrypted log file name the ID of the password required to read the file. To enable expiration and cleanup of old archived passwords in the keyring, the new audit_log_password_history_keep_days system variable is available. See Audit Log Logging Configuration (https://dev.mysql.com/doc/refman/8.0/en/audit-log-logging-configuration.html). C API Notes * These C API changes were made: + HOSTNAME_LENGTH was changed from 60 to 255 and moved from include/mysql_com.h to include/my_hostname.h. + USER_HOST_BUFF_SIZE was moved from include/mysql_com.h to sql/auth/auth_common.h. (Bug #29590300) Character Set Support * The utf8mb4 character set has a new binary collation, utf8mb4_0900_bin, which differs from the existing utf8mb4_bin binary collation as follows: + For collating weights, utf8mb4_bin uses code points, possibly with leading zero bytes added, whereas utf8mb4_0900_bin uses the utf8mb4 encoding bytes. The sort order is the same for both collations, but sorting for utf8mb4_0900_bin is much faster. + The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations. For more information, see Unicode Character Sets (https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html). Component Notes * A new mysql_current_thread_reader component service is available to enable server components to obtain a handle to the current thread. For example, the service enables components to access properties of the current session by passing its thread handle to other services. For information about this service, see the Component Subsystem section of the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html. Configuration Notes * The source files in the mysys_ssl directory have been moved to the mysys directory and the mysys_ssl library is no longer built. (Bug #29488066) * MySQL configuration now requires a minimum CMake version of 3.5.1. (Bug #29337090) * The maximum permitted length of host names throughout MySQL has been raised to 255 ASCII characters, up from the previous limit of 60 characters. This applies to, for example, host name-related columns in the data dictionary, mysql system schema, Performance Schema, INFORMATION_SCHEMA, and sys schema; the MASTER_HOST value for the CHANGE MASTER TO statement; the Host column in SHOW PROCESSLIST statement output; host names in account names (such as used in account-management statements and in DEFINER attributes); and host name-related command options and system variables. Caveats: + The increase in permitted host name length can affect tables with indexes on host name columns. For example, tables in the mysql system schema that index host names now have an explicit ROW_FORMAT attribute of DYNAMIC to accommodate longer index values. + Some file name-valued configuration settings might be constructed based on the server host name. The permitted values are constrained by the underlying operating system, which may not permit file names long enough to include 255-character host names. This affects the general_log_file, log_error, pid_file, relay_log, and slow_query_log_file system variables and corresponding options. If host name-based values are too long for the OS, explicit shorter values must be provided. + Although the server now supports 255-character host names, connections to the server established using the --ssl-mode=VERIFY_IDENTITY option are constrained by maximum host name length supported by OpenSSL. Host name matches pertain to two fields of SSL certificates, which have maximum lengths as follows: Common Name: maximum length 64; Subject Alternative Name: maximum length as per RFC#1034. Applications that expect host names to be a maximum of 60 characters should be adjusted to account for this change. (Bug #13548245, Bug #63814, Bug #27925782, Bug #90601, Bug #27955121, Bug #29584642, Bug #29602081, Bug #94907) Debugging Notes * The MySQL server is a multithreaded application that uses numerous internal locking primitives such as mutexes. To enable detection of lock-acquisition deadlocks and enforcement that runtime execution is free of them, MySQL now supports LOCK_ORDER tooling. This enables a lock-order dependency graph to be defined as part of server design, and server runtime checking to ensure that lock acquisition is acyclic and that execution paths comply with the graph. LOCK_ORDER support includes: + A lock_order_dependencies.txt file that defines the server lock-order dependency graph. + A WITH_LOCK_ORDER CMake option that configures whether MySQL is built with LOCK_ORDER tooling. + A set of system variables that configure LOCK_ORDER tool operation during server execution. + A --lock-order option for mysql-test-run.pl that controls whether to enable the LOCK_ORDER tool during test case execution. To use the LOCK_ORDER tool, you must build MySQL from source with tooling enabled. See The LOCK_ORDER Tool (https://dev.mysql.com/doc/refman/8.0/en/lock-order-tool.html). It is intended for debugging the server, not for production use. Deprecation and Removal Notes * FLOAT(M,D) and DOUBLE(M,D) syntax to specify the number of digits for columns of type FLOAT and DOUBLE (and any synonyms) is a nonstandard MySQL extension. This syntax is deprecated and support for it will be removed in a future MySQL version. (Bug #25328973, Bug #84363) * For string data types, the BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary (_bin) collation of the column character set (or of the table default character set if no column character set is specified). In MySQL 8.0, this nonstandard use of BINARY is ambiguous because the utf8mb4 character set has multiple _bin collations, so the BINARY attribute is deprecated and support for it will be removed in a future MySQL version. Applications should be adjusted to use an explicit _bin collation instead. The use of BINARY to specify a data type or character set remains unchanged. * The nonstandard C-style &&, ||, and ! operators that are synonyms for the standard SQL AND, OR, and NOT operators, respectively, are deprecated and support for them will be removed in a future MySQL version. Applications that use the nonstandard operators should be adjusted to use the standard operators. Note Use of || is deprecated unless the PIPES_AS_CONCAT SQL mode is enabled. In that case, || signifies the SQL-standard string concatenation operator). * The ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. Support for ZEROFILL and display widths for integer data types will be removed in a future MySQL version. Consider using an alternative means of producing the effect of these attributes. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns. * The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms) and will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns. * AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms) and will be removed in a future MySQL version. Consider removing the AUTO_INCREMENT attribute from such columns, or convert them to an integer type. * The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are now deprecated and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows. For example, instead of these queries: SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10; SELECT FOUND_ROWS(); Use these queries instead: SELECT * FROM tbl_name WHERE id > 100 LIMIT 10; SELECT COUNT(*) WHERE id > 100; COUNT(*) is subject to certain optimizations. SQL_CALC_FOUND_ROWS causes some optimizations to be disabled. Installation Notes * An automatic upgrade at server startup can take some time to complete. For better status notification in systemd environments, the server now sends messages to the system notification socket when upgrades start and end. (Status can be monitored with server mysqld status.) (Bug #29493201) Keyring Notes * The keyring_aws plugin has been updated to use the latest AWS SDK and so that it works with OpenSSL 1.1. Packaging Notes * To reduce the download size and disk footprint of mysql-community-server Linux packages, debug binaries and plugins have been moved to separate packages for these platforms: + EL8, Fedora: The mysql-community-server package with debug binaries and associated plugins is now split into a mysql-community-server package without debug binaries or plugins and a mysql-community-server-debug package with debug binaries and plugins. + Debian: The mysql-community-server package with debug binaries and associated plugins is now split into a mysql-community-server package without debug binaries or plugins, a mysql-community-server-debug package with debug binaries, and a mysql-community-test-debug package with debug plugins. In all cases, the debug packages are dependent on the corresponding mysql-community-server package. (Bug #29769061, Bug #28647754, Bug #92415, Bug #29702765, Bug #95169, Bug #29681301) Performance Schema Notes * Performance Schema version checking at compile time was improved to prevent incompatibilities with the server version. (Bug #29550156) * Previously, the Performance Schema instrumentation for RWLOCK named priority read/write locks as rwlock (thus failing to distinguish plain from priority locks) and did not collect information about the kind of unlock operation performed. Priority read/write locks now are named prlock, so that events for them begin with wait/synch/prlock. Also, information about unlock operations is provided. (Bug #29270712) Plugin Notes * Not all plugins can operate properly if loaded "early" in the server startup sequence using the --early-plugin-load option (that is, before InnoDB is initialized). However, InnoDB requires keyring backend plugins to operate on encrypted tables. To enable plugins to indicate to the server whether they can be loaded early, a new PLUGIN_OPT_ALLOW_EARLY flag is available for use in the plugin descriptor. See Server Plugin Library and Plugin Descriptors (https://dev.mysql.com/doc/refman/8.0/en/plugin-data-structures.html#server-plugin-descriptors). Keyring plugins included in MySQL distributions now have the PLUGIN_OPT_ALLOW_EARLY flag enabled because InnoDB requires them, but the flag is not limited to keyring plugins. It can be set for other plugins that are able to initialize successfully early in the server startup sequence. This flag has no effect on whether a plugin can be loaded at server startup with the --plugin-load or --plugin-load-add option, or at runtime with the INSTALL PLUGIN statement. All plugins compiled using MySQL distributions prior to 8.0.17 do not have this flag set. When loading these into pre-8.0.17 servers this does not matter, but attempts to use --early-plugin-load to load plugin binaries compiled using pre-8.0.17 MySQL distributions into a 8.0.17 or higher server will fail. The plugins must be recompiled against MySQL 8.0.17 or higher. (Bug #29040456, Bug #93550) X Plugin Notes * The createIndex() method did not support the DOUBLE(M,D) syntax for specifying double-precision values due to a regression in MySQL 8.0.16. (Bug #29748841) * X Protocol's handling of messages with arguments encoded as octets was corrected to support non-scalar data such as an array of strings. (Bug #29721046) * When host name identity verification was active for SSL connections (--ssl-mode=VERIFY_IDENTITY), X Protocol did not check for matches to Subject Alternative Names (SANs) in the server Certificate Authority (CA) certificate. This could lead to connection requests being rejected unnecessarily because they used a valid host name that was specified as an SAN rather than as the certificate's Common Name value. (Bug #29691694) * When prepared statements were used with X Plugin, using IN or NOT IN in a modify or find operation produced invalid JSON, resulting in an error. (Bug #29259501) * On Windows, X Plugin logged some messages that were unnecessary or insufficiently informative. The messages have been removed or improved as appropriate. (Bug #27839153) * X Plugin's list of SQL functions was out of date, and has been updated to add new functions and remove functions that are no longer available. (Bug #26574971) Functionality Added or Changed * InnoDB; JSON: InnoDB now supports multi-valued indexes on JSON arrays. A multi-valued index is an index in which multiple index records can point to the same data record. This can be useful for indexing JSON documents such as {user:"Bob",zipcode:[94477,94536]} in which, if we wish to search all zip codes, it is necessary to have two index records for each zip code in the document. We can create such an index on the zipcode array using a CREATE INDEX statement such as this one: CREATE INDEX zips ON t1( (CAST(data->'$.zipcode' AS UNSIGNED ARRAY)) ) Effectively, this is a functional index using the CAST() function, which has been extended with the ARRAY keyword to enable casting of JSON arrays to SQL data type arrays. The path expression must be a valid JSON path, and must point to an array in the JSON document in order to be effective. All type specifiers supported by CAST() can be used except for BINARY. Such usage of the CAST() function is supported only by InnoDB, and only for creating multi-valued indexes on JSON arrays. As part of this work, MySQL adds a new function JSON_OVERLAPS() as well as a new MEMBER OF() operator for working with JSON documents, as described here: + JSON_OVERLAPS() compares two JSON documents. If they contain any key-value pairs or array elements in common, the function returns TRUE (1); otherwise it returns FALSE (0). If both values are scalars, the function performs a simple test for equality. If one argument is a JSON array and the other is a scalar, the scalar is treated as an array element. Thus, JSON_OVERLAPS() acts as a complement to JSON_CONTAINS(). + MEMBER OF() tests whether the first operand (a scalar or JSON document) is a member of the JSON array passed as the second operand, returning TRUE (1) if it is, and FALSE (0) if it is not. No type conversion of the operand is performed. The MySQL optimizer uses multi-valued indexes automatically for any suitable query---that is, a query using in its WHERE clause any of JSON_CONTAINS(), JSON_OVERLAPS(), or MEMBER OF() on an array within a JSON column. You can see whether such an index is actually used by checking the output of EXPLAIN for the given query. Multi-Valued Indexes (https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued), provides more detailed information about multi-valued indexes, including examples. For more information about JSON_OVERLAPS() and MEMBER OF(), with examples of use, see Functions That Search JSON Values (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html). * Microsoft Windows: A new warning message now reminds DBAs that connections made using the MySQL named pipe on Windows has limited the permissions a connector can request on the named pipe. Previously, the named_pipe_full_access_group system variable was set to a value that maps to the built-in Windows Everyone group (SID S-1-1-0) by default. However, this group is not ideal and should be replaced with a group that restricts its membership for connectors that are unable to request fewer permissions on the MySQL named pipe. The new warning is written to the error log at startup if the string value assigned to named_pipe_full_access_group is '*everyone*' (or the Windows System Language equivalent) and named pipes are enabled. In addition, the warning is written to the error log and raised to the client if the system variable is reset to the Everyone group at runtime. * X DevAPI: For Collection objects, the following methods have been deprecated and are scheduled to be removed in a future release: + Collection.find().where() + Collection.modify().where() + Collection.remove().where() Any Collection code relying on the .where() method should be updated and the expression in the .where() method should be provided directly in the appropriate .find(), .remove(), and .modify() method. * JSON: MySQL now supports JSON schema validation using two functions JSON_SCHEMA_VALID() and JSON_SCHEMA_VALIDATION_REPORT(), both of which validate a JSON document against a JSON schema conforming to Draft 4 of the JSON Schema specification. JSON_SCHEMA_VALID() returns true if the document validates against the schema and false if it does not. JSON_SCHEMA_VALIDATION_REPORT() returns a JSON document containing detailed information about the results of the validation. For both of these functions, the following statements apply: + required attributes are supported. + Regular expressions are supported (invalid expressions are silently ignored). + External resources in schemas and the $ref keyword are not supported. For more information, including examples, see JSON Schema Validation Functions (https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html). * The time_zone session variable is now hintable using the SET_VAR optimizer hint. (Bug #29776464) * The minor version of the libmysqlclient.so C client library has been increased to 1 (21.0 to 21.1), to signal that new symbols have been added. This was done to correct an oversight in the MySQL 8.0.16 release. To address compatibility concerns, the version of all symbols is unchanged. This means the filename of the library is libmysqlclient.so.21.1.17, whereas all symbols inside the library are tagged as 21_0 (unchanged from the 8.0.16 release). (Bug #29584073, Bug #29642146) * Thanks to Josh Braden, Daniël van Eeden, Simon Mudd, and Zhou Mengkang, who contributed corrections to comments and messages in the MySQL source code. (Bug #29403708, Bug #94464, Bug #29428435, Bug #94527, Bug #29262200, Bug #94049, Bug #29468128, Bug #94625) * The mysql client program now sends os_user and os_sudouser connection attributes, when available, to indicate the name of the operating system user running the program and the value of the SUDO_USER environment variable, respectively. For general information about connection attributes, see Performance Schema Connection Attribute Tables (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.html). Thanks to Daniël van Eeden for the contribution on which this feature was based. (Bug #29210935, Bug #93916) * Password hash values displayed in the IDENTIFIED WITH clause of output from SHOW CREATE USER may contain unprintable characters that have adverse effects on terminal displays and in other environments. Enabling the new print_identified_with_as_hex system variable causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled. For compatibility with this change, CREATE USER and ALTER USER now accept hash values specified either as regular string literals or as hexadecimal strings. (Bug #28053446, Bug #90947) * In MySQL 8.0, the lower_case_table_names variable can only be configured when the MySQL server is initialized. Because a MySQL server installation on Debian and Ubuntu performed using APT initializes the MySQL server for you, there was no opportunity to enable lower_case_table_names. To work around this issue, you can now use the debconf-set-selection utility to enable lower_case_table_names (set lower_case_table_names=1) prior to installing MySQL using APT. To enable lower_case_table_names prior to installing MySQL using APT, execute the following command: shell> sudo debconf-set-selections <<< "mysql-server mysql-server/lowe rcase-table-names select Enabled (Bug #27948395, Bug #90695) * The server now checks the SSL server certificate better at startup and writes a warning to the error log if it finds a problem. (Bug #25945005) * The umask for files created using SELECT ... INTO OUTFILE or SELECT ... INTO DUMPFILE was changed from 0666 to 0640. The LOAD_FILE() function no longer requires files to be world-readable, just readable by the server. (Bug #24513720) * The mysqldump option --set-gtid-purged controls whether or not a SET @@GLOBAL.gtid_purged statement is added to the mysqldump output. The statement updates the value of gtid_purged on a server where the dump file is reloaded, to add the GTID set from the source server's gtid_executed system variable. A new choice --set-gtid-purged=COMMENTED is now available. When this value is set, if GTIDs are enabled on the server you are backing up, SET @@GLOBAL.gtid_purged is added to the output (unless gtid_executed is empty), but it is commented out. This means that the value of gtid_executed is available in the output, but no action is taken automatically when the dump file is reloaded. With COMMENTED, you can control the use of the gtid_executed set manually or through automation. For example, you might prefer to do this if you are migrating data to another server that already has different active databases. Thanks to Facebook for this contribution. (Bug #94332, Bug #29357665) * MySQL now supports explicit casts to DOUBLE, FLOAT, and REAL using either of the functions CAST() or CONVERT(). For more information, see Cast Functions and Operators (https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html). (Bug #30524, Bug #11747058) * InnoDB now supports redo log archiving. Backup utilities that copy redo log records may sometimes fail to keep pace with redo log generation while a backup operation is in progress, resulting in lost redo log records due to those records being overwritten. The redo log archiving feature addresses this issue by sequentially writing redo log records to an archive file. Backup utilities can copy redo log records from the archive file as necessary, thereby avoiding the potential loss of data. For more information, see Redo Log Archiving (https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-redo-log-archiving). * To provide additional indexing options for JSON data, InnoDB now supports multi-value indexes. A multi-value index is a secondary index defined on a column that contains an array of values. * MySQL now provides a clone plugin that permits cloning InnoDB data locally or from a remote MySQL server instance. A local cloning operation stores cloned data on the same server or node where the MySQL instance runs. A remote cloning operation transfers cloned data over the network from a donor MySQL server instance to the recipient server or node where the cloning operation was initiated. The clone plugin supports replication. In addition to cloning data, a cloning operation extracts and transfers replication coordinates from the donor and applies them on the recipient, which enables using the clone plugin for provisioning Group Replication members and replication slaves. Using the clone plugin for provisioning is considerably faster and more efficient than replicating a large number of transactions. Group Replication members can also be configured to use the clone plugin as an alternative method of recovery, so that members automatically choose the most efficient way to retrieve group data from seed members. For more information, see The Clone Plugin (https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html), and Cloning for Distributed Recovery (https://dev.mysql.com/doc/refman/8.0/en/group-replication-cloning.html). * The compatibility policies that Group Replication implements for member versions in groups now consider the patch version of a member's MySQL Server release. Previously, only the major version was considered. Using the patch version means Group Replication can better maintain replication safety for mixed version groups during group reconfigurations and upgrade procedures. The compatibility policies are applied when a member joins the group, either for the first time or following its upgrade, when a donor is selected for state transfer, and when a primary member election takes place. Members running MySQL 8.0.16 or lower, or MySQL 5.7, only take into account the major version in these situations. For a primary member election, so that all members come to the same decision, members running releases from MySQL 8.0.17 adjust their policies to match members running lower releases if any are in the group. In a multi-primary mode group where members running multiple MySQL Server versions are online, for example during a rolling online upgrade procedure, Group Replication now automatically manages the read-write and read-only status of members running releases from MySQL 8.0.17. If a member leaves the group, the members running the version that is now the lowest are automatically set to read-write mode. When you change a mixed version group that was running in single-primary mode to run in multi-primary mode, using the group_replication_switch_to_multi_primary_mode() UDF, Group Replication automatically sets members to read-write or read-only mode depending on their MySQL server version. The improved compatibility policies influence the behavior of group members during an online upgrade procedure from one patch version to another, in the same ways as the behavior during upgrades from one major version to another was influenced previously. For a multi-primary mode group, the number of members in read-write mode is reduced during the upgrade procedure, but Group Replication now automatically manages their read-write status when the upgrade is complete. For a single-primary mode group, if you want the primary to remain as the primary, it must be upgraded last. * Group Replication can now use a remote cloning operation for state transfer to a joining member during distributed recovery. The remote cloning operation lets you add new members to the group without transferring the group's data to the server manually beforehand. To use this functionality, you must install the Clone plugin on the donor and joining member, grant the BACKUP_ADMIN permission to the replication user for distributed recovery, and set the new group_replication_clone_threshold system variable to an appropriate level. Group Replication automatically configures the required Clone plugin settings and manages the remote cloning operation. When cloning is complete and the joining member has restarted, the transactions that the group applied while the remote cloning operation was in progress are transferred to the joining member by replication from a donor's binary log, to complete distributed recovery. * Data that is held in the binary log transaction and statement caches during a transaction is in unencrypted format in the memory buffer that stores the cache. The data is written to a temporary file on disk if it exceeds the space available in the memory buffer. From MySQL 8.0.17, when binary log encryption is active on the server (binlog_encryption=ON), the temporary files used for the binary log cache are now encrypted using AES-CTR (AES Counter mode) for stream encryption. Because the temporary files are volatile and tied to a single process, they are encrypted using single-tier encryption, using a randomly generated file password and initialization vector that exist only in memory and are never stored on disk or in the keyring. After each transaction is committed, the binary log cache is reset: the memory buffer is cleared, any temporary file used to hold the binary log cache is truncated, and a new file password and initialization vector are randomly generated for use with the next transaction. This reset also takes place when the server is restarted after a normal shutdown or an unexpected halt. * An incomplete SQL predicate has the form WHERE value, in which value is a column name or constant expression and no comparison operator is used. MySQL now rewrites any predicate of this type internally as WHERE value != 0 during the contextualization phase, so that the query resolver, query optimizer, and query executor need work only with complete predicates. The principal visible effect of this change is that, for Boolean values, EXPLAIN output now shows true and false, rather than 1 and 0. * The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery. This is similar to the existing IS NULL (Not exists) outer join optimization; see EXPLAIN Extra Information (https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information), for further information. In addition, the semijoin materialization can now be used for a WHERE condition having IN (subquery) IS TRUE, or EXISTS (subquery) IS TRUE, or when the IN condition belongs to a left join such as SELECT * FROM t1 LEFT JOIN t2 ON t2.x IN (SELECT * FROM t3). See Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations (https://dev.mysql.com/doc/refman/8.0/en/semijoins.html). Also as a result of this work, MySQL is now able to simplify a condition of the form (x IS TRUE) IS FALSE as x IS NOT TRUE, which can be tested more quickly and optimized more easily than the condition as originally written. * InnoDB parallel read thread performance for large data sets was improved through better utilization of read threads, through a reduction in read thread I/O for prefetch activity that occurs during parallel scans, and through support for parallel scanning of partitions. The parallel read thread feature is controlled by the innodb_parallel_read_threads variable. The maximum setting is now 256, which is the total number of threads for all client connections. If the thread limit is reached, connections fall back to using a single thread. * mysqlbinlog now supports the --compress (or -C) option to enable compression in the client/server protocol. [To be continued]
Subject
Views
Written By
Posted
MySQL Community Server 8.0.17 has been released (part 1/3)
2802
July 22, 2019 03:04AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.