Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)
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.)
Beginning with MySQL 8.0.4, macOS 10.13 is a supported platform.
Also beginning with MySQL 8.0.4, Ubuntu 14.04 and Debian 8 are no longer supported platforms for MySQL 8.0.
* Compilation Notes
* Component Notes
* Configuration Notes
* Deprecation and Removal Notes
* Function Notes
* InnoDB Notes
* Installation Notes
* Logging Notes
* Packaging Notes
* Performance Schema Notes
* Security Notes
* Server Administration
* Spatial Data Support
* Test Suite Notes
* X Plugin Notes
* Functionality Added or Changed
* Bugs Fixed
* Linux: Binary packages on Linux platforms now are built using GCC 6. The optimization level has changed from -O3 to -O2.
* macOS; Microsoft Windows: For compiling MySQL from source, the -DWITH_SSL=system CMake option now works on Windows and macOS. (Bug #26907731, Bug #87938)
* On platforms for which the GNU gold linker is used, removal of unused functions is now enabled, reducing the size of binaries. (Bug #26612067, Bug #87372)
* #include directives in source files were rewritten and reorganized to be unambiguous. (Bug #26597243, Bug #87358, Bug #26897738)
* The BUILD directory containing compilation scripts is no longer maintained and has been removed from MySQL source trees. (Bug #26576219, Bug #87323)
* The minimum version of the Boost library for server builds is now 1.65.0. (Bug #26574924, Bug #87317)
* MySQL can now be linked against OpenSSL 1.1 on Unix and Unix-like systems. (Bug #25094892, Bug #83814)
* 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.
* The validate_password plugin has been reimplemented to use the server component infrastructure. To install and uninstall the validate_password component, use these statements: INSTALL COMPONENT 'file://component_validate_password'; UNINSTALL COMPONENT 'file://component_validate_password';
INSTALL PLUGIN and UNINSTALL PLUGIN still work to install and uninstall the validate_password plugin as before, but generate warnings. The plugin form of validate_password is deprecated and will be removed in a future version of MySQL. MySQL installations that use the plugin should transition to the component instead. See Transitioning to the Password Validation Component (http://dev.mysql.com/doc/refman/8.0/en/validate-password-transitioning.html). A usage difference between the component and plugin implementations of validate_password is that, whereas the plugin exposes system and status variables with names that begin with validate_password_, the component uses the convention of exposing variables with names that begin with the component name and a period. For example, the plugin exposes the validate_password_policy system variable and validate_password_dictionary_file_words_count status variable, whereas the corresponding variables exposed by the component are validate_password.policy and validate_password.dictionary_file_words_count. The plugin variables, like the plugin itself, are deprecated and will be removed in a future version of MySQL.
* Replication: When calculating the binary log expiration period, any value that you specify for the deprecated expire_logs_days system variable is now ignored if binlog_expire_logs_seconds is set, and only the value of binlog_expire_logs_seconds is used. Previously, the effects of binlog_expire_logs_seconds and expire_logs_days were cumulative. If you set a value for both system variables at startup, a warning message is issued stating that the value of expire_logs_days is ignored. You cannot set or change the value of one option dynamically while the other is set, and an error message is issued in this situation. The default binary log expiration period of 30 days (as changed in MySQL 8.0.2) applies if neither binlog_expire_logs_seconds nor expire_logs_days has a value set at startup. If a value for either binlog_expire_logs_seconds or expire_logs_days is set at startup, this value overrides the default binary log expiration period. To disable automatic purging of the binary log, you must set both binlog_expire_logs_seconds and expire_logs_days explicitly to 0 at startup. (Bug #26274274, Bug #86698)
* For RHEL, SLES, and Fedora RPMs, the default plugin directory for debug builds has been changed from /usr/lib64/mysql/plugin to /usr/lib64/mysql/plugin/debug. (Bug #27072155, Bug #88363)
* The table_open_cache system variable default variable has been increased from 2000 to 4000. This additionally changes the default for the autosized table_definition_cache system variable from 1400 to 2000.
* The optimizer_trace_max_mem_size system variable default value was changed from 16KB to 1MB to lessen the likelihood of optimizer trace truncation.
* The log_error_verbosity system variable default value was changed from 3 (Errors, Warnings, Notes) to 2 (Errors, Warnings) to make mysqld error logging less verbose by default.
Deprecation and Removal Notes
* InnoDB: The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release.
* Replication: The group_replication_primary_member status variable has been deprecated and will be removed in a future version.
* Replication: The group_replication_allow_local_disjoint_gtids_join system variable has been removed.
* When the libmysqld embedded server library was removed in MySQL 8.0.1, the following mysql_options() options used only for libmysqld were not removed. They have now been removed. MYSQL_OPT_GUESS_CONNECTION MYSQL_OPT_USE_EMBEDDED_CONNECTION MYSQL_OPT_USE_REMOTE_CONNECTION MYSQL_SET_CLIENT_IP
* Generated columns no longer permit deprecated functions in the generation expression, to avoid problems when MySQL is upgraded to a version in which the deprecated functions have been removed. (Bug #26279884, Bug #86712)
* Incompatible Change: Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators (REGEXP, RLIKE). Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR(), REGEXP_REPLACE(), and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively. The regexp_stack_limit and regexp_time_limit system variables provide control over resource consumption by the match engine. MySQL source distributions bundle the ICU library, and the WITH_ICU CMake option controls whether to compile with the bundled or system ICU library. The ICU_VERSION() function returns the ICU library version. For more information, see Regular Expressions (http://dev.mysql.com/doc/refman/8.0/en/regexp.html). For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations (http://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility).
* Two new SQL functions are available to provide digest information about SQL statements. Given an SQL statement as a string, STATEMENT_DIGEST() returns the statement digest hash value, and STATEMENT_DIGEST_TEXT() returns the normalized statement digest. See Encryption and Compression Functions (http://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html).
* The restriction enacted in MySQL 8.0.3 against renaming of columns in a parent foreign key has been lifted. (Bug #26659110, Bug #87490, Bug #25722221) References: See also: Bug #26334071.
* For platforms that use systemd (see Managing MySQL Server with systemd (http://dev.mysql.com/doc/refman/8.0/en/using-systemd.html)), 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)
* A new error log filter component, log_filter_dragnet, enables control over events written to the error log, based on user-defined rules specified as the value of the dragnet.log_error_filter_rules system variable. This new filtering capability is more flexible than is achievable using the built-in log_filter_internal filter component and log_error_verbosity system variable. For more information, see Error Log Filtering (http://dev.mysql.com/doc/refman/8.0/en/error-log-filtering.html).
* Messages written to the error log by the log_sink_internal log writer component now contain an error-ID indicator. This ID has a format of [error_id]. It follows the severity indicator and precedes the message text. For more information, see Error Log Message Format (http://dev.mysql.com/doc/refman/8.0/en/error-log-format.html).
* When the log_error_verbosity system variable for error logging is set to filter out notes and warnings, selected important messages about non-error situations are now printed to error logs as system messages. These messages were previously handled and labeled as errors so that they were logged when log_error_verbosity=1 was set. The labeling function has now been decoupled from the handling function for these messages, so that the messages can be given an appropriate label. System messages now have the label "System" in the MySQL error log. Other log writers that support custom labels might also use this convention. In logs that do not support custom labels, system messages are printed with the label used for the information level of severity. However, they are included even if the log_error_verbosity setting would normally exclude messages at this level. If your system has additional configuration to discard messages labeled as information level, or to redirect them to a different destination from messages labeled as errors, system messages do not override this behavior. If the log writer involved does not use the "System" label, system messages will now be discarded or redirected along with other messages labeled as information level. The messages that are now printed with the "System" label where supported include startup and shutdown messages, and some significant changes to settings, as follows:
* All MySQL binary distributions now are linked against OpenSSL, including Community distributions, which previously were linked against yaSSL. In addition, OpenSSL is linked dynamically rather than statically, which enables substitution of alternative SSL libraries for use with MySQL if desired. For some platforms, binary distributions bundle OpenSSL libraries to ensure library availability:
+ Windows: Distributions bundle libeay32.dll and ssleay32.dll, which are installed in the same directory as MySQL binaries. Other libraries can be used by replacing those library files with alternatives, or by using some other library-selection method supported on Windows. (See https://msdn.microsoft.com/en-us/library/7d83bc18.as px.)
+ macOS: Distributions bundle libssl.dylib and libcrypto.dylib. MySQL binaries are linked to expect the libraries in the same directory, and symbolic links there point to the actual library locations.
+ Linux (for generic compressed tar file distributions only): Distributions bundle libssl.so and libcrypt.so,which are installed in the same directory as the libmysqlclient.so client library. (Bug #26272084, Bug #26134893, Bug #26927607, Bug #87996)
Performance Schema Notes
* The Performance Schema now uses SHA-256 hashes for statement digests rather than MD5 hashes. To accommodate the increased storage required for SHA-256 values, DIGEST columns in Performance Schema tables are widened from VARCHAR(32) to VARCHAR(64). (Bug #26727443)
* The Performance Schema setup_timers table has been removed, as has the TICK row in the performance_timers table.
* Incompatible Change: Passwords are now restricted to a maximum of 256 characters for the caching_sha2_password and sha256_password authentication plugins, and for the PASSWORD() function when old_passwords=2. Also, the number of password hashing rounds is capped to limit CPU time used. (Bug #27099029, Bug #27194270)
* Incompatible Change: The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is now the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library:
+ For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.
+ The libmysqlclient library treats caching_sha2_password as the default authentication plugin rather than mysql_native_password. This change has the following implications:
+ The change affects only the authentication plugin used for creating new MySQL accounts. For accounts already existing in an upgraded installation, their authentication plugin remains unchanged.
+ Clients that use an account that authenticates with caching_sha2_password must use either a secure connection (made using TCP using TLS/SSL credentials, a Unix socket file, or shared memory), or an unencrypted connection that supports password exchange using an RSA key pair. This security requirement does not apply to mysql_native_passsword, so the switch to caching_sha2_password may require additional configuration (see Caching SHA-2 Pluggable Authentication (http://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)). However, client connections in MySQL 8.0 prefer use of TLS/SSL by default, so clients that already conform to that preference may need no additional configuration.
+ Because caching_sha2_password is also now the default authentication plugin in the libmysqlclient client library, authentication requires an extra round trip in the client/server protocol for connections from MySQL 8.0 clients to accounts that use mysql_native_password (the previous default authentication plugin), unless the client program is invoked with a --default-auth=mysql_native_password option.
+ Incompatibility: Clients and connectors that have not been updated to know about caching_sha2_password cannot connect to accounts that authenticate with caching_sha2_password because they do not recognize this plugin as valid. To work around this issue, relink clients against libmysqlclient from MySQL 8.0.4 or higher, or obtain an updated connector that recognizes caching_sha2_password.
+ Incompatibility: Clients and connectors that have not been updated to know about caching_sha2_password may have trouble connecting to a MySQL 8.0 server configured with caching_sha2_password as the default authentication plugin, even to use accounts that do not authenticate with caching_sha2_password. This issue occurs because the server specifies the name of its default authentication plugin to clients. If a client or connector is based on a client/server protocol implementation that does not gracefully handle an unrecognized default authentication plugin, it may fail with an error. For more information about the more prominent role of caching_sha2_password, including discussion of potential compatibility issues and solutions, see caching_sha2_password as the Preferred Authentication Plugin (http://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password). Additionally, replication slaves are now able to connect to masters using RSA key pair-based password exchange. This RSA capability applies for accounts that authenticate with the caching_sha2_password or sha256_password authentication plugin. (Previously, slaves could authenticate with those plugins, but required a secure connection and could not use RSA password exchange.) The following changes enable use of this new slave RSA capability:
+ The CHANGE MASTER TO statement has clauses for specifying RSA public key information.
+ For Group Replication, the group_replication_recovery_public_key_path and group_replication_recovery_get_public_key system variables serve the same purpose.
+ The SHOW SLAVE STATUS statement and the Performance Schema replication_connection_configuration table display replication slave RSA public key information. More programs now support a --server-public-key-path option to enable specifying a client-side file containing the public key for RSA key pair-based password exchange, for accounts that authenticate with the caching_sha2_password or sha256_password authentication plugin: mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, mysql_upgrade. Previously, only mysql and mysqltest supported --server-public-key-path. The --server-public-key-path option is an alternative to --get-server-public-key, and may be used when a client-side file is available that contains a copy of the RSA public key required by the server, rather than sending a request for the public key to the server. The new mysql_reset_server_public_key() C API function clears any cached copy of the server RSA public key from the client library. See mysql_reset_server_public_key() (http://dev.mysql.com/doc/refman/8.0/en/mysql-reset-server-public-key.html). The new caching_sha2_password_auto_generate_rsa_keys system variable enables automatic RSA private/public key-pair file generation, similar to the sha256_password_auto_generate_rsa_keys system variable. See Automatic SSL and RSA File Generation (http://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files-using-mysql.html#creating-ssl-rsa-files-using-mysql-automatic). The new Caching_sha2_password_rsa_public_key status variable exposes the public key used by the caching_sha2_password authentication plugin for RSA key pair-based password exchange. (Bug #26751594)
* The linked OpenSSL library for MySQL Server has been updated to version 1.0.2n. Issues fixed in the new OpenSSL version are described at http://www.openssl.org/news/vulnerabilities.html. (Bug #27212666, Bug #27236394)
* Certificates automatically generated by mysqld and mysql_ssl_rsa_setup now use X509 v3 rather than v1. (Bug #26521654)
* MySQL now supports key migration between underlying keyring keystores. This enables DBAs to switch a MySQL installation from one keyring plugin to another. See Migrating Keys Between Keyring Keystores (http://dev.mysql.com/doc/refman/8.0/en/keyring-key-migration.html).
* A new RESTART SQL statement is available that enables restarting a MySQL server instance from within a client session. This feature is available for platforms on which a monitoring process is able to detect a server shutdown performed for restart purposes: Windows (mysqld started as a Windows service or standalone), and Unix and Unix-like systems that use systemd or mysqld_safe to manage mysqld. See RESTART Syntax (http://dev.mysql.com/doc/refman/8.0/en/restart.html).
Spatial Data Support
* Incompatible Change: These spatial import functions now return an error for data in a geographic spatial reference system for which the coordinates are outside the legal range of values: ST_GeomCollFromText(), ST_GeomCollFromTxt(), ST_GeomCollFromWKB(), ST_GeometryCollectionFromText(), ST_GeometryCollectionFromWKB(), ST_GeometryFromText(), ST_GeometryFromWKB(), ST_GeomFromGeoJSON(), ST_GeomFromText(), ST_GeomFromWKB(), ST_LineFromText(), ST_LineFromWKB(), ST_LineStringFromText(), ST_LineStringFromWKB(), ST_MLineFromText(), ST_MLineFromWKB(), ST_MPointFromText(), ST_MPointFromWKB(), ST_MPolyFromText(), ST_MPolyFromWKB(), ST_MultiLineStringFromText(), ST_MultiLineStringFromWKB(), ST_MultiPointFromText(), ST_MultiPointFromWKB(), ST_MultiPolygonFromText(), ST_MultiPolygonFromWKB(), ST_PointFromText(), ST_PointFromWKB(), ST_PolyFromText(), ST_PolyFromWKB(), ST_PolygonFromText(), ST_PolygonFromWKB(). (ST_LatFromGeoHash(), ST_LongFromGeoHash(), and ST_PointFromGeoHash() do not have this behavior because they cannot represent out-of-range values.) ST_GeomFromGeoJSON() also now returns an error if a crs member that specifies an SRID different from the top-level object SRID is found at a lower level of the GeoJSON document. Note If spatial data contains SRID values that refer to a geographic spatial reference system and the data coordinates are out of range, existing queries using these functions will return different results, compared to previous MySQL versions. (Bug #26941370, Bug #88031)
* Incompatible Change: Previously, these spatial functions ignored the spatial reference system (SRS) for geometry arguments and computed results on a Cartesian plane. They now support computations for geometry arguments that specify a geographic SRS: ST_Distance_Sphere(), ST_IsSimple(), ST_IsValid(), ST_Length(). Previously, these spatial functions ignored the SRS for any geometry arguments and computed results on a Cartesian plane. They now produce an error when invoked with geometry arguments that specify a geographic SRS: ST_Area(), ST_Buffer(), ST_Centroid(), ST_ConvexHull(), ST_Difference(), ST_Envelope(), ST_Intersection(), ST_IsClosed(), ST_MakeEnvelope(), ST_Simplify(), ST_SymDifference(), ST_Union(), ST_Validate(). Previously, these spatial functions permitted geometry arguments with an undefined SRS. They now produce an error when invoked with geometry arguments that have an undefined SRS: ST_Dimension(), ST_Distance_Sphere(), ST_EndPoint(), ST_ExteriorRing(), ST_GeometryN(), ST_GeometryType(), ST_InteriorRingN(), ST_IsEmpty(), ST_IsSimple(), ST_IsValid(), ST_Length(), ST_NumGeometries(), ST_NumInteriorRing(), ST_NumInteriorRings(), ST_NumPoints(), ST_PointN(), ST_StartPoint(), ST_SwapXY(), ST_X(), ST_Y(). Previously, the ST_GeoHash() spatial function accepted points with any SRID. ST_GeoHash() now accepts only points with SRID 0 or 4326. Note If spatial data contains geometry values that now are interpreted differently by the functions just listed, existing queries using these functions will return different results, compared to previous MySQL versions.
* Incompatible Change: For the following spatial functions that take an SRID argument, they now return an error if the SRID does not exist: ST_GeomCollFromText(), ST_GeomCollFromTxt(), ST_GeomCollFromWKB(), ST_GeometryCollectionFromText(), ST_GeometryCollectionFromWKB(), ST_GeometryFromText(), ST_GeometryFromWKB(), ST_GeomFromGeoJSON(), ST_GeomFromText(), ST_GeomFromWKB(), ST_LineFromText(), ST_LineFromWKB(), ST_LineStringFromText(), ST_LineStringFromWKB(), ST_MLineFromText(), ST_MLineFromWKB(), ST_MPointFromText(), ST_MPointFromWKB(), ST_MPolyFromText(), ST_MPolyFromWKB(), ST_MultiLineStringFromText(), ST_MultiLineStringFromWKB(), ST_MultiPointFromText(), ST_MultiPointFromWKB(), ST_MultiPolygonFromText(), ST_MultiPolygonFromWKB(), ST_PointFromGeoHash(), ST_PointFromText(), ST_PointFromWKB(), ST_PolyFromText(), ST_PolyFromWKB(), ST_PolygonFromText(), ST_PolygonFromWKB(). (ST_SRID() already had this behavior.) Note If spatial data contains SRID values that refer to a nonexistent spatial reference system, existing queries using these functions will return different results, compared to previous MySQL versions.
* The EPSG Dataset containing spatial reference system data for spatial calculations has been upgraded from version 9.0 to 9.2. (Bug #26711258, Bug #87564, Bug #26990896)
* MySQL now supports CREATE SPATIAL REFERENCE SYSTEM and DROP SPATIAL REFERENCE SYSTEM statements for creating and removing spatial reference system (SRS) definitions in the data dictionary. See CREATE SPATIAL REFERENCE SYSTEM Syntax (http://dev.mysql.com/doc/refman/8.0/en/create-spatial-reference-system.html), and DROP SPATIAL REFERENCE SYSTEM Syntax (http://dev.mysql.com/doc/refman/8.0/en/drop-spatial-reference-system.html). With the implementation of these statements, direct access to the mysql.st_spatial_reference_systems data dictionary table using statements such as SELECT, INSERT, and UPDATE is not needed, and is no longer permitted. Read access to SRS definitions remains available through the INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS table, as before.
Test Suite Notes
* Documentation for the MySQL Test Suite is now maintained in the MySQL source tree using Doxygen (see http://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_MYSQL_TEST_RUN.html). The related Unix man pages that previously were produced from the old test suite manual are no longer updated and have gone out of date. Consequently, they are no longer included in MySQL distributions. (Bug #27021754)
* The MySQL test suite now includes CRC32() tests. Thanks to Daniel Black for the patch. (Bug #26495791, Bug #87136)
X Plugin Notes
* X Plugin could incorrectly remove quotes from parameters that were passed to functions expecting JSON strings, causing an error. (Bug #26906519)
* After an index was created on a collection, X Plugin would list the collection as a relational table, and no longer list it as a collection. (Bug #26906487)
* X Protocol displayed the DATE type in the same way as the DATETIME type. The two data types are now treated differently. (Bug #26647488)
* mysqlxtest did not display fractional seconds in DATETIME values correctly. (Bug #26638422)
* X Plugin returned a column metadata field showing fractional digits for a JSON column, for which that information was irrelevant. The field is no longer returned. (Bug #26258481)
* X Plugin handled user account lookup incorrectly if the IP address resolved to an empty host name. (Bug #26042786)
* X Plugin can now be configured to abort any authenticated connections which are considered idle. The following variables have been added:
* X Plugin now supports Caching SHA-2 Pluggable Authentication (http://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html). To enable the support, issue: INSTALL PLUGIN mysqlx_cache_cleaner SONAME 'mysqlx.so'; This plugin maintains the cache and removes all entries for accounts that were modified. References: See also: Bug #27213213, Bug #27042109.
Functionality Added or Changed
* InnoDB: Obsoleted InnoDB system tables that are no longer required after upgrading to MySQL 8.0.3 or later are now dropped after a successful upgrade. (Bug #26757171)
* InnoDB: Support was added to automatically upgrade data dictionary table definitions when starting the MySQL server after upgrading MySQL binaries to a new version. At startup, the data dictionary version of the server is compared to the version information stored in the data dictionary to determine if data dictionary tables should be upgraded. If an upgrade is necessary and supported, the server creates data dictionary tables with updated definitions, copies persisted metadata to the new tables, atomically replaces the old tables with the new ones, and reinitializes the data dictionary. If an upgrade is not necessary, startup continues without updating the data dictionary tables. The mysqld --no-dd-upgrade option can be used to prevent automatic upgrade of data dictionary tables at startup.
* InnoDB: Moving or restoring tablespace files to a new location while the server is offline is supported by the new --innodb-directories option, which defines directories to scan at startup for tablespace files. For more information, see Moving Tablespace Files While the Server is Offline (http://dev.mysql.com/doc/refman/8.0/en/innodb-moving-data-files-offline.html). With the introduction of the --innodb-directories feature, the location of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to the innodb_directories argument value. Otherwise, InnoDB is not able to locate these files during recovery. To view tablespace file locations, query the INFORMATION_SCHEMA.FILES table: mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
The CREATE TABLESPACE ... ADD DATAFILE statement now requires that a specified directory is known to InnoDB. Known directories include those implicitly and explicitly defined by the innodb_directories option.
* InnoDB: InnoDB now supports partial fetch and update of large object (LOB) data stored in external fields outside of clustered index pages, including JSON documents updated using JSON_SET() and JSON_REPLACE(). Previously, LOB values could only be read or modified in full, and updates of JSON column values were done by completely removing the previous document and writing the new one in its place. Partial fetch and update uses an internal LOB index, or ZLOB index in the case of compressed LOB data, which is created and stored in one or more LOB pages when a row containing LOB data is inserted or updated. Thus, partial fetch and update of LOB data is supported for newly inserted rows, but existing rows containing LOB data must be updated to add partial fetch and update support. The addition of LOB index data increases the storage space required by LOB values by a small percentage. This feature adds the following InnoDB page types for storing uncompressed and compressed LOB data:
* InnoDB: The storage engine private field of the mysql.tablespaces data dictionary table and page 0 of InnoDB tablespace files now store MySQL server version and tablespace version information. The MySQL server version is the version that created the tablespace, the version into which the tablespace was imported, or the version of the last major MySQL upgrade. The tablespace version tracks tablespace format changes. SERVER_VERSION and SPACE_VERSION fields were added to the INFORMATION_SCHEMA.INNODB_TABLESPACES table to provide access to this data.
* Replication: Host names can now be specified as part of a whitelist for group replication connections, using the group_replication_ip_whitelist system variable. Host names support CIDR notation. Host names that resolve to IPv6 addresses are not supported. For host names, name resolution takes place only when a connection request is made by another server. A host name that cannot be resolved is not considered for whitelist validation, and a warning message is written to the error log. Forward-confirmed reverse DNS (FCrDNS) verification is carried out for resolved host names. Warning Host names are inherently less secure than IP addresses in a whitelist. FCrDNS verification provides a good level of protection, but can be compromised by certain types of attack. Specify host names in your whitelist only when strictly necessary, and ensure that all components used for name resolution, such as DNS servers, are maintained under your control. You can also implement name resolution locally using the hosts file, to avoid the use of external components.
* JSON: Added the JSON_TABLE() function, which accepts JSON data and returns it as a relational table whose columns are as specified. This virtual table can be accessed using standard SQL mechanisms This function has the syntax JSON_TABLE(expr, path COLUMNS column_list) [AS] alias), where expr is an expression that returns JSON data, path is a JSON path applied to the source, and column_list is a list of column definitions. Columns can be of the types FOR ORDINAL, PATH, EXISTS PATH, and NESTED PATH or NESTED, as described in the following list:
+ FOR ORDINAL: The column is a counter, similar to an AUTO_INCREMENT column.
+ PATH: The column holds a scalar value using the specified JSON path. ON ERROR and ON EMPTY options are supported for handling illegal values (such as nonscalars) and empty values, respectively.
+ EXISTS PATH: The column value is 1 if a match exists for the specified JSON path, and 0 otherwise.
+ NESTED PATH: Nested objects or arrays in JSON data found in the given JSON path are flattened into a single row along with the JSON values from the parent object or array. The PATH keyword is optional. Two simple examples are shown here: mysql> SELECT * -> FROM -> JSON_TABLE( -> '[1, 3, 5, 7, 9]', -> "$[*]" COLUMNS(rowid FOR ORDINALITY, col VARCHAR(50) PATH " $") -> ) AS t1; +-------+------+ | rowid | col | +-------+------+ | 1 | 1 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 9 | +-------+------+
The JSON source expression can be any expression that yields a valid JSON document, including a JSON literal, a table column, or a function call that returns JSON such as JSON_EXTRACT(t1, data, '$.post.comments'). For complete syntax and other information about this function, see JSON Table Functions (http://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html).
* The -DWITH_ASAN_SCOPE CMake option enables the AddressSanitizer -fsanitize-address-use-after-scope Clang flag for use-after-scope detection. The default is off. To use this option, -DWITH_ASAN must also be enabled. (Bug #27095089)
* Handling of empty JSON documents has been made more robust. (Bug #26787468, Bug #87734)
* MySQL 8.0.3 imposed a restriction against ALTER TABLE RENAME on tables in a foreign key relationship if a LOCK TABLES was active. This restriction has been lifted. (Bug #26647340, Bug #87467)
* If the server PID file is configured to be created in a world-writable location, the server now issues a warning suggesting use of a more secure location. (Bug #26585560)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!