The MySQL Development team is happy to announce our 8.0.2 development milestone release (DMR), now available for download at dev.mysql.com. (8.0.2 adds features to 8.0.1and 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.2 Release Notes. Here are the highlights. Enjoy!

SQL Window Functions

Add first batch of SQL window functions (WL#9236) – This work by Dag Wanvik implements SQL window functions in MySQL. A window function performs a calculation across a set of table rows that are somehow related to the current row. Support for window functions (a.k.a. analytic functions) is a frequent user request. Window functions have long been part of standard SQL (SQL 2003).

Add remaining non-aggregate window functions (WL#9603) – This work by Dag Wanvik and Chaithra Gopalareddy adds the non-aggregate window functions missing from WL#9236above. These functions are PERCENT_RANK, CUME_DIST, NTH_VALUE, LEAD, and LAG.

Additional aggregate window functions (WL#9727) – This work by Dag Wanvik and Chaithra Gopalareddy adds aggregate window functions missing from WL#9236  above. These functions are MIN, MAX, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, and VAR_SAMP.

JSON

Ranges in JSON path expressions (WL#9831) – This work by Knut Anders Hatlen extends the syntax for ranges in JSON path expressions. The new syntax introduced is a subset of the SQL standard syntax, described in SQL:2016, 9.39 SQL/JSON path language: syntax and semantics. See also Bug#79052 reported by Roland Bouman.

Add JSON_STORAGE_SIZE / JSON_STORAGE_FREE functions (WL#9192) – This work by Knut Anders Hatlen adds JSON functions related to space usage for a given JSON object. The JSON_STORAGE_SIZE returns the required storage size in bytes for JSON stings. For a native-type it returns the actual size in bytes. The JSON_STORAGE_FREE returns the free space of a JSON binary type in bytes (including fragmentation and padding saved for inplace update). For a JSON string, it creates an intermediate binary representation and returns the padding that is taken for this object.

Support for partial update of JSON in the optimizer (WL#8963) – This work by Knut Anders Hatlen speeds up update of JSON documents, which are stored as BLOBs. If only some parts of a JSON document are updated, we want to give information to the handler about what was changed, so that the storage engine and replication don’t need to write the full document. This work is for the optimizer’s part only.

Provide logical diffs for partial update of JSON values (WL#10570) – This work by Knut Anders Hatlen adds support for partial update with the JSON_REMOVE function, in addition to the JSON_SET and JSON_REPLACE functions which got support for partial update in WL#8963. In a replicated environment, it cannot be guaranteed that the layout of a JSON document is exactly the same on the slave and the master, so the physical diffs cannot be used to reduce the network I/O for row-based replication. This work will provides logical diffs that row-based replication can send over the wire and reapply on the slave.

GIS

Ellipsoidal relational operations and R-tree support functions (WL#8685) – This work by Norvald Ryeng extends spatial relational functions such as ST_Contains and ST_Crosses to detect that their parameters are in a geographical (ellipsoidal) SRS and to compute their results on the ellipsoid. The behavior of each ST function is as defined in SQL/MM Part 3 Spatial.

Character Sets

Add Japanese kana sensitive collation to utf8mb4 (WL#10480) – This work by Xing Zhang implements utf8mb4_ja_0900_as_cs collation which sorts characters by using three levels’ weight. But users also wants to have a collation which has additional kana sensitive feature. The new collation is named utf8mb4_ja_0900_as_cs_ks, where ‘ks’ stands for ‘kana sensitive’.

Add utf8mb4 accent sensitive and case insensitive collation (WL#10818) – This work by Xing Zhang Add adds the utf8mb4 accent sensitive and case insensitive collation utf8mb4_0900_as_ci.

Varlen keys for sorting multibyte String values (WL#9554) – This work by Steinar Gunderson introduces variable-length keys when sorting NO PAD collations. PAD collations still need to be fixed length, because they are conceptually extended to infinity. It builds on the existing semantics for sorting JSON using variable-length keys.

Use utf8mb4 in libmysql and command tools (WL#10353) – This work by Bharathy Satish changes the default charset for client tools from latin1 to utf8mb4. Client tools include following: mysql, mysqladmin, mysqldump, mysqlcheck, mysqlpump, mysqlimport, mysql_upgrade, mysqltest, mysqlshow, and mysqlslap.

Change MTR tests to run with new default charset (WL#10297, WL#10300, WL#10418, WL#10319, WL#10298, WL#10296) – This work by Deepa Dixit adopts optimizer test cases to the new default character set, i.e. from latin1 to utf8mb4.

Histograms

Extend ANALYZE TABLE with histogram support (WL#8943) – This work by Erik Froseth manages histograms statistics. This includes adding histogram statistics to one or more columns, as well as removing existing histogram statistics. The syntax for ANALYZE TABLEhas been extended to accept two new clauses: UPDATE HISTOGRAM ON column [, column] WITH n BUCKETS and DROP HISTOGRAM ON column [, column]. The histogram statistics are stored in the dictionary table “column_statistics” and accessible through the view information_schema.COLUMN_STATISTICS. However, this work does not cover how histograms are used by the optimizer.

Performance Schema

PERFORMANCE_SCHEMA, TABLE PLUGIN (WL#8879) – This work by Mayank Prasad allows dynamic plugins to provide their own performance schema tables. Before this work, all performance schema tables have been hard coded in the core server.

Configuration Variables

Allow SET PERSIST to set read-only variables too (WL#9787) – This work by Bharathy Satish allows SET PERSIST to set read-only variables. The new values will take effect at the next server restart. Before this work SET PERSIST could only set variables that was settable as SET GLOBAL. Note that a small subset of read-only variables are left intentionally not settable.

Tablespaces

Replace MLOG_FILE_NAME with MLOG_FILE_OPEN (WL#9499) – This work changes the way open tablespaces are tracked and logged to the redo log. Instead of tracking table space use in every mini-transaction the table spaces in use are tracked when they are opened / closed / dropped. This work reverts some of the code changes done by WL#7142 and WL#7806 as these worklogs caused performance regressions due to the high frequency of checking and tracking the table space use in mini-transactions.

Undo Tablespaces

Make the number of undo tablespaces and rollback segments dynamic (WL#9507) – This work by Kevin Lewis redesigns the way rollback segments are found in undo tablespaces. Furthermore, this work allows the setting innodb_undo_tablespaces to be increased or decreased at runtime or at startup. If increased, new undo tablespaces will be created and then atomically added to the list of active undo tablespaces. If decreased, the list of active undo tablespaces is atomically reduced in size. Existing undo tablespaces will continue to be used by existing transactions and eventually will become inactive.

In-Memory Storage Engine

Compact In-Memory Temporary Tables (WL#8117) – This work by Vasil Dimov implements a MEMORY-like storage engine that supports variable length columns in an efficient way – that is, if a VARCHAR(100) column contains ‘abcd’ it must be stored in about 4 bytes, not in about 100 bytes. This engine is used as a replacement for the MEMORY engine by server internals.

Error Logging

Logging services: Improved error logging in 8.0 (WL#9323) – This work by Tatjana Nurnberg outlines the changes to error logging in 8.0. The high level view is that error logging becomes pluggable in 8.0 using the new service infrastructure. We introduce error numbers for error log messages and we separate between creating an error event and how it is reported. The new error logging can be thought of as a sequence of log filters followed by parallel log writers (log sinks).

Logging services: log filter (filtering engine) (WL#9342) – This work by Tatjana Nurnberg implements a filtering service (API) and a default filtering service implementation (component). Filtering here means to suppress certain log messages (selection) and/or fields within a given log message (projection).

Logging services: log writers (WL#9343) – This work by Tatjana Nurnberg implements a log writer service (API) and a default log writer service implementation (component).  Log writers accept a log event and writes it to a log. This log can be a classic file, syslog, EventLog and a new JSON log writer.

Logging services: error messages (WL#9344) – This work by Tatjana Nurnberg updates the calls to error-logging in the server code to leverage the new features introduced by WL#9323.

Replication

Improve usability when receiver thread is waiting for disk space (WL#10406) – This work by Joao Gramacho makes the replication monitoring work even when the receiver thread is waiting for disk space. This work also makes the receiver thread safely stoppable even when waiting for disk space.

Add transaction length to gtid_log_event (WL#10493) – This work by Joao Gramacho adds the transaction length to the Gtid_log_event in the binary log. This work is a stepping stone for optimizing the applier, optimizing the protocol, and optimizing the binary log.

Group Replication

Flow-control fine tuning (WL#9838) – This work by Vitor Oliveira enables the user to fine tune the flow-control mechanism in GR. Three new options are introduced: group_replication_flow_control_min_quota, group_replication_max_quota, and group_replication_member_quota_percent. These options can be used to optimize the lag between members and to keep the throughput as stable as possible.

Monitoring improvements (WL#10380) – This work by Jaideep Karande improves Group Replication by introducing additional columns in the existing performance schema tables replication_group_member and replication_group_member_stats. The work adds additional member related information like member role, version and additional stats.

Disallow writes after leave group (WL#10611) – This work by Anibal Pinto enables super read only when STOP GROUP_REPLICATION is executed preventing clients to execute writes after it. After this feature, clients applications will not be able to change data when the server disconnects from the group (it automatically sets super_read_only). Thus the system as a whole is now more resilient when it comes to dealing with situations where accidental updates are routed to disconnected servers.

Member weight for automatic primary election on failover (WL#10433) – This work by Hemant Dangi enables the user to influence the primary member election in single-primary mode by providing a member weight value for each member node. This member weight value will be used for electing primary member instead of lowest server uuidwhich has been used until now.

Notify listener services on relevant group events (WL#10412) – This work by Luis Soares implements a notification framework in the group replication plugin. On view changes, recovery state updates, network partitioning and primary election, the plugin will call out to listeners registered in the service registry and notify them that an event has occurred.

MySQL GCS – Minor Refactoring: Information on nodes are duplicated in several modules and classes (WL#9834) – This work by Alfranio Correia refactors code to eliminate some duplicated code related to node’s properties and improves tracking and handling of information of nodes that get back online and rejoin a group.

MySQL GCS: Update XCom in Group Replication (WL#10632) – This work by Tiago Jorge brings the latest XCom code to the latest version of Group Replication.

Security

Allow grants and revokes for PUBLIC (WL#924) – This work by Kristofer Petterson introduces the configuration variable mandatory-roles which can be used for automatic assignment and granting of default roles when new users are created.

Define and implement authorization model to manage XA-transactions (WL#7194) – This work by Dmitry Shulga introduces a new system privilege XA_RECOVER_ADMIN which controls the capability to execute the statement XA RECOVER. An attempt to do XA RECOVERby a user whom wasn’t granted the new system privilege XA_RECOVER_ADMIN will cause an error.

Add mysqld_safe-functionality to server (WL#10441) – This work by Dyre Tjeldvoll implement parts of the logic currently found in the mysqld_safe script inside the server. The work improves server usability in some scenarios for example when using the --daemonize startup option. The work also make users less dependent upon the mysqld_safe script, which we hope to remove in the future. It also fixes Bug#75343 reported by Peter Laursen.

Service Infrastructure

Add a UDF registration service (WL#8020) – This work by Georgi Kodinov defines a new plugin service which allows e.g. plugin_init() to dynamically register UDF functions and e.g. plugin_deinit() to de-register them. This allows for defining all the functions with a single INSTALL PLUGIN command.

MySQL string functionalities as a service for mysql_server component (WL#9503) – This work by Venkata Sidagam implements string functions as a general service in the new service infrastructure. The motivation behind this work is to convert the existing password validation plugin into a component in the new service infrastructure.

Parser Refactoring

True bottom-up server parser: refactoring of the ALTER TABLE statement (WL#8657) – This work by Gleb Shchepa refactors ALTER TABLE|VIEW statement-related parser grammar rules in a pure bottom-up style to make it (grammar) context-independent for the better maintainability and extendability.

X Protocol

IN Operator for JSON (WL#10612) – This work by Grzegorz Szwarc introduces new CONTAINSand NOT_CONTAINS operators. These operators can be used in any valid expression in CRUD operations. CONTAINS accept JSON values at the left and right sides of the operator, including expressions that generate a JSON value.

Crud.Insert with upsert (WL#9807) – This work by Grzegorz Szwarc introduces an INSERT ... ON DUPLICATE KEY UPDATE ... like command for collections. The UPSERT command allows performing an insert or update, depending on whether the document already exists or not, in a single step, atomically.

DATA DICTIONARY – INNODB

Update InnoDB specific metadata into new DD for DDL (WL#9525) – This work by Bin Su propogates InnoDB specific metadata into the new data dictionary for DDL operations. More specifically, it adds storage engine private data to dictionary tables for CREATE TABLE, CREATE TABLESPACE, ALTER TABLE etc.

Instantiate InnoDB in-memory metadata with new DD objects (WL#9534) – This work by Jimmy Yang instantiates the InnoDB in-memory metadata from the newDD objects.

FTS index support for new DD (WL#9530) – This work by Shaohua Wang enables Full Text Search functionality along with the new data dictionary.

Enable table encryption and transparent compression (WL#9531) – This work by Zheng Lai enables encryption and transparent compression for the new data dictionary project.

Move data dictionary tables to a single tablespace (WL#9532) – This work by Bin Su creates tablespace mysql and datafile mysql.ibd and uses this tablespace for data dictionary tables.

Support in-place ALTER PARTITION (WL#9559) – This work by Bin Su implements add, drop, coalesce, reorganize, rebuild, exchange partition by native partitioning.

Upgrade steps for new data dictionary (WL#9357) – This work by Satya Bodapaty outlines the upgrade steps for the new data dictionary.

Support InnoDB table import/export for new DD (WL#9537) – This work by Zheng Lai ensures that IMPORT and EXPORT works correctly with the new data dictionary.

DATA DICTIONARY – RUNTIME

Support flexible creation and versioning of virtual P_S tables (WL#7900) – This work by Dmitry Shulga allows the performance schema engine to create it’s own tables, and versioning is added to help handle changes in these table definitions. This work is a prerequisite performance schema table plugin, see WL#8879 above.

Upgrade for InnoDB Internal Dictionary Migration (WL#9461)- This work by Abhishek Ranjan migrates the content of the InnoDB internal dictionary to the Data Dictionary. With the implementation of Data Dictionary for Server, InnoDB will remove its internal Data Dictionary. InnoDB will store its metadata in Server Data dictionary. Dictionary provides se_private_id and se_private_data columns to be used by Storage Engines. Upgrade from older MySQL versions needs to migrate the content of the InnoDB internal dictionary onto the Data Dictionary.  This provide a way to migrate the contents of InnoDB internal dictionary to the Data Dictionary. This includes all the innodb internal metadata related to tablespaces, tables, indexes, and foreign keys, currently stored by InnoDB dictionary only.

Extend SHOW statements to list hidden columns and index information (WL#9570) – This work by Praveenkumar Hulakund implements SHOW EXTENDED COLUMNS and SHOW EXTENDED INDEX. The former list hidden columns of a table. The latter lists hidden indexes and index elements. Aside from the SHOW EXTENDED command, the INFORMATION_SCHEMA and SHOWcommands will not list hidden columns.

Update schema tables of dynamic plugins into data dictionary (WL#9495) – This work by Gopal Shankar deals with various aspects of how dynamic plugins interact with the data dictionary. For example, when a plugin is loaded or unloaded it will need to register or unregister plugin meta-data in data dictionary tables.

MTR Tests

Remove all references to embedded server from tests (WL#10185) – This work by Parakh Agarwal removes all references to embedded server. It removes tests that can only run on embedded server and removes checks for embedded on tests that cannot run on embedded server. On removal of the embedded server see MySQL 8.0: Retiring support for libmysqld.

Migrate main suite to run with innodb (part 15) (WL#9490) – This work by Parakh Agarwal is a continuation of the work to migrate MTR tests from using the MyISAM storage engine to using the InnoDB storage engine. Several tests has been migrated in previous work. This work migrate tests that need functional changes to work with InnoDB.

Changes to Defaults

Change default for explicit_defaults_for_timestamp to ON (WL#9687) – This work by Abhishek Ranjan changes the default value of explicit_defaults_for_timestamp from “0” to “1”. The option explicit_defaults_for_timestamp was introduced in 5.6. We expect this option to be removed in the future, making explicit defaults for timestamps mandatory.

Enable PFS MDL instrumentation by default (WL#9629) – This work by Marc Alff changes the compiled default from OFF to ON for performance-schema-instrument='wait/lock/metadata/sql/%=ON'. This is an enabler for adding MDL oriented views in SYS. MDL instrumentation is useful for understanding lurking locking problems, which are not clear in SHOW PROCESSLIST etc. See also Morgan Tocker’s suggestion in Bug#80823.

Enable PFS memory instrumentation by default (WL#9625) – This work by Marc Alff changes the compiled default from OFF to COUNTED for performance-schema-instrument='memory/%=COUNTED'. This is important because the accounting is incorrect if instrumentation is enabled after server start, i.e. you could get a negative balance from missing an allocation, but catching a free.

Enable PFS transaction instrumentation by default (WL#9628) – This work by Marc Alff changes the compiled default from OFF to ON for the following settings: performance-schema-consumer-events-transactions-current=ON, performance-schema-consumer-events-transactions-history=ON, and performance-schema-instrument='transaction%=ON'.

Change default for innodb_undo_tablespaces from 0 to 2 (WL#10498) – This work by Kevin Lewis changes the default value for innodb_undo_tablespaces from “0” to “2”.

Change default for innodb_undo_log_truncate from OFF to ON (WL#10499) – This work by Kevin Lewis changes the default value for innodb_undo_log_truncate from “OFF” to “ON”.

Enable Binary Log Expiration (WL#10478) – This work by Dhruthi K.V changes the default value for variable expire_logs_days from “0” to “30”. The new default (30) causes mysqld to periodically purge unused binary logs that are older than 30 days. The old value “0” will disable any automatic binary log purges.

Store Replication Metadata In InnoDB (WL#10474) – This work by Deepthi E.S ensures that replication meta-data is stored in InnoDB by default. The work changes the default values of master-info-repository and relay-log-info-repository from FILE to TABLE.

Enable Transaction Write Sets (WL#10477) – This work by Dhruthi K.V changes the default value transaction-write-set-extraction from “OFF” to “XXHASH64”. By using Transaction Write Sets, the master has to do slightly more work to generate the write sets which is helpful in conflict detection. This allows users to easily move into GR since transaction-write-set is a requirement for GR. Also, the new default will make users to easily enable binary log writeset parallelization on master to speed up replication.

Defaults: Enable Hash Scans In RBR (WL#10476) – This work by Parveez Baig changes the default value of slave_rows_search_algorithms from INDEX_SCAN,TABLE_SCAN to INDEX_SCAN,HASH_SCAN. The work enables the use of a hash table to avoid repeated table scans when no Primary Key or Primary Key Equivalent (non-null unique secondary index) exists on a table.

Deprecation and Removal

Deprecate libmysqld embedded server in 5.7 (WL#9722) – This work by Marc Alff ensures that the libmysqld embedded server is documented as being deprecated in 5.7. No code change, this task is to update the documentation only.

Deprecate innodb_undo_logs in 5.7 (WL#10322) – This work by Kevin Lewis adds a deprecation warning for innodb_undo_logs in 5.7. The setting innodb_undo_logs has been an alias for innodb_rollback_segments since it was introduced in 5.6. In 8.0 we will no longer use the words “undo log” to refer to a rollback segment, but the original setting, innodb_rollback_segments, will remain. In 8.0 each undo tablespace to contain this number of rollback segments. In 8.0, the meaning of the innodb_rollback_segmentssetting will change to “number of rollback segments per undo tablespace”. The undo tablespaces that this applies to are the system tablespace, the shared temporary tablespace, and any other undo tablespace.

Deprecate and remove “Delphi style” column and table syntax (WL#8662) – This work by Gleb Shchepa removes support for the old Deplhi style syntax for columns and table names in 8.0. Deplhi style syntax, a dot sign followed by the column/table name, will give an SQL syntax error in 8.0. 5.7 will give deprecation warnings for this syntax (.column_name, .table_name.column_name, and .table_name).

Disable symbolic links as compiled default + deprecate variable (WL#8392) – This work by Dyre Tjeldvoll changes the compiled default of symbolic-links from “ON” to “OFF” (aligning with the defacto default used in most sample configuration files). This work also adds a deprecation warning for the option symbolic-links in 5.7. We expect this option to be removed in a future release.

Remove disabling of strict aliasing optimization (GCC) (WL#10344) – This work by Jon Olav Haugli re-enabling the strict aliasing optimization for the GCC compiler. Performance testing has shown 0-4% performance improvement in Sysbench by using the strict aliasing optimization. The largest improvement has been seen for single-threaded performance.

Remove cross compatibility code for binlog V1 and V3 (WL#9219) – This work by Libing Song removes the compatibility code for supporting binlog v1 (used in MySQL 3.23) and v3 (used in MySQL 4.0.2 through 4.1). With this change, both slave and mysqlbinlog will refuse to connect to masters older than 5.0 which generate binary logs of v1 and v3.

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