MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
The MySQL 8.0.4 Release Candidate is available

The MySQL Development team is very happy to announce that MySQL 8.0.4, the second 8.0 Release Candidate (RC2), is now available for download at dev.mysql.com (8.0.4 adds features to 8.0.3, 8.0.2, 8.0.1 and 8.0.0). The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.4 Release Notes. Here are the highlights. Enjoy!

Security

Make caching_sha2_password default authentication mechanism (WL#11057) – This work by Harin Vadodaria changes the default authentication plugin from mysql_native_password to caching_sha2_password. The caching_sha2_password plugin was developed in WL#9591 and delivered in MySQL 8.0.3. An upgrade from 5.7 will not change authentication method for existing users. But after an upgrade to 8.0, new users will get the new caching_sha2_password by default. See SHA-2 Pluggable Authentication.

Dynamic Linking of OpenSSL in MySQL Server (WL#10524) – This work by Tor Didriksen implements dynamic linking of OpenSSL for MySQL 8.0.  This means that customers can choose which OpenSSL library to use with MySQL, e.g. due to security requirements imposed by the customer organization. MySQL Community now defaults to using OpenSSL library for TLS/SSL and other cryptography functions, which previously were implemented using yaSSL.

JSON Table Functions

Add JSON table functions (WL#8867) – This work by Evgeny Potemkin implements the ability to view JSON data as SQL tables. This is very powerful as it enables the use of the SQL machinery for JSON data. JSON_TABLE() creates a relational view of JSON  data. It maps the result of a JSON data evaluation into relational rows and columns. The user can query the result returned by the function as a regular relational table using SQL, e.g. join, project, and aggregate.

RLIKE/REGEXP

Add the ICU library to handle RLIKE/REGEXP (WL#8987) – This work by Martin Hansson changes the library used by MySQL’s REGEXP()/RLIKE() from Henry Spencer to ICU. Main motivators for this are to support regular expressions for UTF8MB4 as well as new functions like REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR().  This work also adds the system variables regexp_stack_limit and regexp_time_limit to control the execution. The REGEXP_REPLACE()  function is one of the most requested features by the MySQL community, for example see feature request reported as BUG #27389 by Hans Ginzel.

SQL DIGEST

SQL Function to return DIGEST of given SQL (WL#9637) – This work by Martin Hansson adds two new native functions called STATEMENT_DIGEST() and STATEMENT_DIGEST_TEXT(). For strings containing valid SQL statements, STATEMENT_DIGEST() will return the digest that performance_schema would assign to the statement, and STATEMENT_DIGEST_TEXT() will return the normalized statement that performance_schema would use. This functionality is useful to people who want to correlate a server digest with their application-side query capturing.

Tablespaces

InnoDB: Provide offline database portability without ISL files (WL#8619) – This work by Sunny Bains ensures that ibd and system tablespace files can be moved from one location to another while the server is offline. The server must be restarted with --innodb-directories="dir1<;dir2;...;dirN>", so that the server will know the new locations. Implicitly created undo tablespace files named “undo_001”, etc must be located in --innodb-undo-directory, just as they have always been.  The *.isl (InnoDB Symbolic Link) files introduced in 5.6 have been removed by WL#6416.

BLOB

InnoDB: Partial Fetch and Update of BLOB (WL#8960) – This work by Annamalai Gurusami implements partial fetch and update of large object (LOB) data stored in external fields outside of clustered index pages. This improves efficiency and performance when working with JSON documents, for example when using JSON_SET() and JSON_REPLACE() functions. 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.

Upgrade

Support InnoDB tablespace version in se_private_data of mysql.tablespaces (WL#5989) – This work by Jimmy Yang introduces two new version numbers in the InnoDB tablespace headers. The two new version numbers are the server version number and the tablespace version number and both will be used for upgrade purposes in the future.

Update server version information in InnoDB tablespaces (WL#11063) – This work by Abhishek Ranjan ensures that the server version information in InnoDB tablespace headers are initialized when a tablespace is created,  imported, or upgraded. Hence, MySQL will have a way to associate user-data with a given server version.

Upgrading the transactional data dictionary tables (WL#9553) – This work by Sivert Sørumgård implements upgrade for the data dictionary. MySQL will support changing the definition of the data dictionary tables between two server versions. At server restart, the server checks if upgrade is needed based on persistent version information. If upgrade is not needed, the server continues with an ordinary restart. Otherwise, the server creates a new set of DD tables with the desired table definitions and copies the persisted (meta) data from the old DD tables (which need to be upgraded) to the new ones (which have the desired definition). Finally, the server swaps the old and new DD tables atomically, and perform DD initialization over again, this time using the desired table definitions.

Remote Management

RESTART command (WL#9809) – This work by Thayumanavar Sachithanantha implements an SQL RESTART command. The purpose is to enable remote management of a MySQL server over an SQL connection, for example to set a non-dynamic configuration variable by SET PERSIST followed by a RESTART.

Error Logging

Logging services: log writers: add error ID to traditional error log (WL#11009) – This work by Tatjana Nurnberg and Praveenkumar Hulakund introduces error numbers (<error_ID>) to messages sent to the MySQL Error Log (stderr/file).  The format is a number in the 10000 series preceded by “MY-“, for example “MY-10001”. Error numbers will be stable in a GA release, but the corresponding error texts are allowed to change (i.e. improve) in maintenance releases.

Logging services: force-print certain non-error messages to error log (WL#10942) – This work by Tatjana Nurnberg and Praveenkumar Hulakund introduces System messages to the error log. System messages are written to the error log as [System] instead of [Error], [Warning], [Note]. [System] and [Error] messages are printed regardless of verbosity and cannot be suppressed.  [System] messages are only used in a few places, mainly associated with major state transitions such as starting or stopping the server.

Logging services: log_filter_dragnet component (WL#9651) – This work by Tatjana Nurnberg implements a log filter component called log_filter_dragnet. This component enables log filtering based on user-defined rules of the form IF <conditions> THEN <action>. The rules are specified as text in the dragnet.log_error_filter_rules system variable. In this way error log messages can be suppressed, projected, throttled, and changed. See Error Log Filtering documentation.

Group Replication

MySQL GCS: Instrument locking structures in GCS/XCom (WL#9856) – This work by Filipe Campos instruments mutex and condition synchronization objects in GCS and exposes them automatically as Performance Schema tables metrics. The main purpose is to give MySQL GCS developers a tool to look for abnormal wait events like bottlenecks or deadlocks. MySQL XCom is currently single threaded and not affected by this work.

MySQL GCS: Support name resolution in white list (WL#10803) – This work by Tiago Jorge implements support for hostnames in the GCS/XCom connection whitelisting feature. Users will be able to configure whitelist parameter using hostnames instead of IP Addresses to allow connections from outside hosts, which do not have a fixed address.

GIS

CREATE/DROP SPATIAL REFERENCE SYSTEM (WL#9059) – This work by Aleksander Wasaznic adds CREATE/DROP SPATIAL REFERENCE SYSTEM statements to manipulate the spatial reference systems in the data dictionary. For example: CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1234 NAME 'foo' DEFINITION 'PROJCS[...]';

Forbid using undefined SRIDs (WL#9450) – This work by Hans Melby enforces that new geometries have to be in one of the known spatial reference systems. I.e. MySQL can’t do spatial operations on geometries in unknown spatial reference systems.

Don’t do Cartesian computations on geographic geometries (WL#11096) – This work by Norvald Ryeng modifies the behavior of all spatial functions that don’t yet support geographic computations to raise an error if given geographic input, effectively restricting the functions to Cartesian input. As we implement geographic support in more functions, this error will gradually go away. The old behavior can still be achieved by temporarily casting the input parameters to SRID 0, and, if necessary, casting the result back to a geographic SRID.

Restrict range of geographic coordinates in import functions (WL#9449) – This work by Hans Melby restricts input to a valid longitude and latitude range. Geographic coordinates are longitudes and latitudes, which have known bounds. Now we raise an error if a geographic SRS is used and the coordinates are outside the legal range of (-180, 180] for longitude and [-90,90] for latitude.

ST_Distance_Sphere for geographic geometries (WL#10999) – This work by Aleksander Wasaznic extends the function ST_Distance_Sphere() to also accept parameters in geographic SRSs. This way, ST_Distance_Sphere() can be used as a performance optimization for geographical distance, if the user can accept less accurate results.

Ellipsoidal ST_IsSimple (WL#10927) – This work by Aleksander Wasaznic extends  the function ST_IsSimple() to detect that its parameter is in a geographic (ellipsoidal) SRS and to compute on the ellipsoid.

Ellipsoidal ST_IsValid (WL#10926) – This work by Torje Digernes extends the function ST_IsValid() to detect that its parameter is in a geographic (ellipsoidal) SRS and to compute on the ellipsoid.

Ellipsoidal ST_Length (WL#10416) – This work by Jens Even Blomsøy extends the function ST_Length() to detect that its parameter is in a geographic (ellipsoidal) SRS and to compute the length on the ellipsoid.

X Plugin

SHA256 challenge response against in-memory sha256-storage (WL#10992) – This work by Tomasz Stepniak implements caching SHA2 authentication for the X Plugin. Essentially, it implements the same functionality for the X Protocol as WL#9591 did for the classic MySQL protocol. The purpose of this functionality is to provide fast authentication with improved strength.

Mysqlx connection timeout (WL#9267) – This work by Tomasz Stepniak detects and drops idle connections. The definition of an idle connection is governed by the following configuration variables: mysqlx_wait_timeout (default 28800 seconds), mysqlx_interactive_timeout (default 28800 seconds), mysqlx_read_timeout (default 30 seconds), and mysqlx_write_timeout (default 60 seconds).

X Protocol Crud.Insert with Upsert, behavior redefined (WL#11434) – This work by Grzegorz Szwarc corrects the behavior of UPSERT operations introduced in WL#9807 (MySQL 8.0.2). The idea of an UPSERT is that an INSERT translates to an UPDATE if the document/row already exists. But in the original implementation in WL#9807 there were cases where an UPSERT of a new document/row affected already existing documents. This has now been fixed.

Refactoring

Reimplement the password validation plugin API and plugin as a component (WL#6667) – This work by Venkata Sidagam moves the password validation plugin introduced in 5.6 over to the new component service infrastructure.

Parser Refactoring: Refactoring of the HANDLER statement (WL#8066), the EXPLAIN statement (WL#8065), and the LOAD DATA/XML statement (WL#8063) – This work by Gleb Shchepa refactors the statement grammar rules in a pure bottom-up style to make it context-independent for the better maintainability and extendability.

Changes to Defaults

Increase default value of optimizer_trace_max_mem_size to a reasonable size (WL#11345) – This work by Roy Lyseng changes the default of optimizer_trace_max_mem_size from 16KB to 1MB. The old default caused the the optimizer trace to be truncated for any non-trivial query.

Increase default value of table_open_cache to a reasonable size (WL#9703) – This work by  Abhishek Ranjan changes the default of table_open_cache from 2000 to 4000. The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors.

Change default log_error_verbosity to 2 (WL#11143) – This work by Praveenkumar Hulakund changes the default of log_error_verbosity from 3 (Notes) to 2 (Warning). The purpose is to make MySQL 8.0 error log less verbose by default.

Switch GCC optimization from -O3 to -O2 (WL#10343) – This work by Steinar Gunderson changes the compile time default GCC optimization level from -O3 to -O2. Performance testing has shown 0-6% performance improvement in Sysbench by switching from -O3 to -O2, likely due to the reduction in binary size with -O2.

Deprecation and Removal

Deprecate innodb_undo_tablespaces in 5.7 (WL#10473) – This work by Kevin Lewis marks innodb_undo_tablespaces as deprecated in 5.7 (and to be removed in 8.0). The current default of 2 will be hard coded in the future. Adding new UNDO tablespaces will be done by means of SQL CREATE UNDO TABLESPACE commands.

Remove performance_schema.setup_timers in 8.0 (WL#10985 and WL#10986) – This work by Marc Alff deprecates performance_schema.setup_timers in 5.7 and removes the functionality in 8.0. This functionality is mostly unused and not needed. Removing this functionality allows us to reduce the performance schema runtime overhead.

Remove group_replication_allow_local_disjoint_gtids_join option (WL#11139) – This work by Nuno Carvalho removed the option in 8.0 because it is considered to be unsafe. See also the explanation for the deprecation in 5.7 found in WL#11138.

Deprecate group_replication_primary_member status variable (WL#10958) – This work by Nuno Carvalho adds a deprecation warning to the documentation regarding the global status variable group_replication_primary_member in 8.0. The status variable will be removed in a later version. This option has been superseded by the column MEMBER_ROLE in performance_schema.replication_group_members.

That’s it for now. Thank you for using MySQL!