The MySQL Development team is happy to announce our 5.7.6 development milestone release (DMR), now available for download at dev.mysql.com! The source code is available at GitHub. You can find the full list of changes and bug fixes in the 5.7.6 release notes. Here are the highlights. Enjoy!

InnoDB

Refactoring needed for general tablespace (CREATE TABLESPACE) support (WL#8109) : This work by Kevin Lewis refactors and cleans up InnoDB code in preparation for supporting general tablespaces in WL#6205.

Implement CREATE TABLESPACE for general use (WL#6205) : This work by Kevin Lewis adds the ability for InnoDB to create a general tablespace using the CREATE TABLESPACE syntax. This work gives the user the freedom to choose their own mapping between tables and tablespaces, i.e. which tablespaces to create and what tables they should contain. This provides the means for doing things such as grouping all tables for a given user or customer within a single tablespace, and thus having all of their data within a single file on the filesystem.

Separate partitioning interface from handler to Partition_handler (WL#4807) : This work by Mattias Jonsson separates the partitioning specific parts of the handler class into an own partitioning interface. This is refactoring done to modularize and decouple the partitioning engine from the rest of the server code base.

Add native partitioning support to InnoDB (WL#6035) : This work by Mattias Jonsson adds native partitioning support to InnoDB. Until now, partitioning support for InnoDB has relied on the ha_partition handler, which created a new handler for each partition, which in turn wasted a lot of resources when using many partitions. By supporting native partitioning within InnoDB one can use far fewer overall resources (see also: bug#62536, bug#37252, and bug#70641). InnoDB native partitioning also paves the way for better overall partitioning. That would include things such as parallel query processing, improved partition pruning (due to more accurate index statistics), foreign key support, global secondary indexes, and full-text searches on partitioned tables.

Add InnoDB events to Performance Schema’s Event Stage table (WL#5889) : This work by Vasil Dimov introduces InnoDB stages in the performance_schema.events_stages_* tables (named ‘stage/innodb/%’). The purpose is to enable progress reporting for long running transactions. Some tasks inside InnoDB may take a significant amount of time (more than a few minutes) to complete, and the users need a way to check on the progress of such a task. For example, if somebody has already waited 3 hours for an ALTER TABLE command to complete, they will want to know if it will likely complete in another 10 minutes, or in another 7 hours.

Improvements around flushing for proper performance (WL#7868) : This work by Yasufumi Kinoshita improves the adaptive flushing algorithm and related mechanisms in order to achieve more consistent or smooth throughput. Please see the WorkLog entry for all of the details.

Make InnoDB fill factor settable (WL#6747) : This work by Yasufumi Kinoshita makes the merge_threshold settable per index. Until now it has been fixed at 50%. If the  amount of data within the page falls below the merge_threshold, e.g. when deleting a row or shortening it via an update, InnoDB will attempt to merge it with a neighbor page. This offers a way to decrease InnoDB’s overall disk footprint, at the expense of some additional work due to the additional page merges.

Make InnoDB the default engine used for internal disk based temporary tables (WL#6737) : This work by Krunal Bauskar makes InnoDB the default engine used for internal disk based temporary tables created by the Optimizer during query processing and execution (e.g. when it needs to produce intermediate results). Until now MyISAM has been the default.

Enhance Check Table for InnoDB Spatial indexes (WL#7740) : This work by Allen Lai extends CHECK TABLE so that it is able to determine whether any spatial indexes are valid. It now checks for a valid R-tree structure and ensures that the entry count matches up with the clustered index.

InnoDB FULLTEXT SEARCH: CJK support (WL#6607) : This work by Shaohua Wang implements CJK (Chinese, Japanese, and Korean) support for InnoDB FULLTEXT search. This work provides a default fulltext parser for CJK (N-GRAM support) and an alternative plugin which provides MeCab support for Japanese. Users can also implement their own pluggable fulltext parser for CJK using the pluggable parser support.

Support for 32k and 64k pages (WL#5757) : This work by Shaohua Wang adds support for 32k and 64k page sizes (the default remains 16k). Bigger page sizes will help to improve compression ratios (the bigger the page size, the more redundant bits), thus offering much improved data compression in conjunction with the new transparent page compression. Furthermore, bigger page sizes allow more “on page” or “inline” storage of BLOBs and large VARCHAR/TEXT fields, thus improving I/O performance when such fields are used.

High Priority Transactions in InnoDB (WL#6835) : This work by Sunny Bains implements high priority transactions within InnoDB, i.e. transactions that shall never be chosen to be aborted in a deadlock scenario. The motivation is to better support MySQL Group Replication, where a transaction cannot be aborted in one replica and committed in another.

Runtime

Introduce a separate error code range for 5.7 (WL#8206) : This work by Jon Olav Hauglid implements a separate range of numbers for new server error messages introduced in 5.7. These codes are currently numbered consecutively, starting at 1000. We also have a second range of error codes used by the client, starting at 2000. The problem is that the first range of codes is currently up to 1974 which means that the two ranges would soon overlap, making it hard to differentiate between server and client errors. The new 5.7 server error codes will now instead start at 3000.

Add MDL for tablespaces (WL#7957) : This work by Sivert Sørumgård defines and implements meta data locking (MDL) for general tablespaces. This is specifically needed for the new general tablespaces in InnoDB, and for the already existing NDB general tablespaces.

Move the plugin and servers tables from MyISAM to transactional storage (WL#7160) : This work by Dmitry Shulga makes InnoDB the default storage engine used for the plugin and server system tables (MyISAM remains optional).

Refactor low-level thread handling (WL#8190) : This work by Jon Olav Hauglid improves the low-level code for handling OS threads. For example, by harmonizing the APIs for Windows and non-Windows platforms (thread create & join), and removes the use of macros.

Non-intrusive refactoring of the Security_context class code (WL#8048) : This work by Praveenkumar Hulakund refactors the Security_context code to ensure proper data encapsulation. This work removes the following issues found in previous versions: 1. some of the security context members were public and the code accessing and modifying these members was spread across many files within the MySQL source code 2. access to these members were not guarded, and thus threads could potentially read stale data when accessing them from a particular thread.

Multi-Source Replication

Multi-source replication  (WL#1697) : This work by Ritheesh Vedire implements Multi-Source Replication. It allows a slave to replicate from multiple sources/masters, but without any additional conflict detection and resolution. One use case is to commission a “fan in slave” for data aggregation or backups. This version takes into consideration the great feedback we got from the community when we first released this via MySQL Labs some time ago.

Replication – Multi Threaded Slaves (MTS)

Optimize MTS scheduling by increasing the parallelization window on master (WL#7165) : This work by Andrei Elkin improves the performance of Multi-Threaded Slaves (MTS) by using a more precise algorithm to determine which transactions are non-conflicting. This allows more transactions to execute in parallel when using --slave-parallel-type=LOGICAL_CLOCK, thus greatly improving the overall replication performance.

Replication – GTID

Support setting gtid_mode=ON online (WL#7083) : This work by Sven Sandberg provides a way to turn on GTIDs online, so that reads and writes are allowed during the procedure, and servers do not need to synchronize. Prior to this work, the user had to stop all updates, synchronize all servers, and then restart all of them simultaneously. This previously meant that enabling GTIDs implied several minutes of planned downtime. See Bug#69059. Now GTIDs can be enabled as an online operation.

Always generate Gtid_log_event and Previous_gtids_log_event (WL#7592) : This work by Sven Sandberg ensures that similar events are generated regardless of the current value for GTID_MODE. Without ensuring this consistent behavior, there were cases where you could potentially lose GTID_EXECUTED and GTID_PURGED values (see the details in the WorkLog entry). (This was originally part of WL#7083).

Session Tracker: Add GTIDs context to the OK packet (WL#6128) : This work by Luis Soares adds a tracker to the response packet of the MySQL protocol. This is done to be able to pass the connector information about the session state collected which can then be used to implement session consistency. This work will be collecting, packing, and sending the data provided by WL#6972 below.

Collect GTIDs to include in the protocol’s OK packet (WL#6972) : This work by Luis Soares implements a mechanism to collect the necessary set of GTIDs to be sent over the wire in the response packet.

Replication – Refactoring

Group Replication: Server Interfaces (WL#8007 and WL#8202) : This work by Tiago Jorge and Nuno Carvalho create stable interfaces useful for MySQL Group Replication and other server plugins. Server plugins can now be informed of server events in the form of server callbacks.

Moving binlog event decoding into a separate module (WL#7440) : This work by Neha Kumari separates out the deserialization of events in a MySQL replication stream into the a separate module. The goal of this refactoring is to break the dependencies between the server core and the replication framework.

Performance Schema

PERFORMANCE_SCHEMA, STATUS VARIABLES (WL#6629) : This work by Chris Powers exposes the GLOBAL and SESSION level STATUS and VARIABLES from within Performance Schema. The corresponding SHOW commands will be preserved for backward compatibility. This work represents an important step in our effort to improve the monitoring framework and to improve MySQL observability.

PERFORMANCE SCHEMA, configurable statement text size (WL#7270) : This work by Mayank Prasad makes the two entities related to Query Text Length—SQL text and digest—configurable. The default for both remains at 1024 bytes. This is a much requested feature from users who commonly have SQL queries larger than 1024 bytes.

PERFORMANCE SCHEMA, SCALABLE MEMORY ALLOCATION (WL#7794) : This work by Marc Alff changes the way performance schema does memory allocations. Previously, the Performance Schema has allocated all of the memory it needs up front when the server starts. This work relaxes the memory constraints to increase ease-of-use (less configuration) and to decrease the overall memory footprint. We are now automatically scaling the memory consumption to match the actual server load.

PERFORMANCE SCHEMA, SETUP_ACTORS ENABLED COLUMN (WL#7800) : This work by Mayank Prasad improves ease-of-use by allowing you to enable all user/host combinations except “this specific one” (exclusion). To allow this, a new column named ENABLED has been added to the setup_actors table to provide users with the ability to specific exclusion as well as inclusion rules.

PERFORMANCE SCHEMA, REDUCE MEMORY USAGE FOR TABLE IO / TABLE LOCK (WL#7698) : This work by Mayank Prasad refactors Performance Schema’s internal buffers so that the memory overhead for the table instrumentation scales better when the number of tables in the database is large.

Security

Limit the scope of the FILE privilege in the default installation – secure deployment (WL#6782) : This work by Harin Vadodaria restricts the scope of the FILE privilege to a secure default value for --secure-file-priv. The server will also warn users of insecure configurations for --secure-file-priv at start-up time.

SSL cert and key generation for MySQL Community – secure deployment (WL#7706) : This work by Harin Vadodaria provides a “best effort” SSL setup process during installation. If successful, the MySQL server configuration file is also modified to reference the newly generated SSL certs and keys.

Temporary disablement of users (WL#6054) : This work by Marek Szymczak makes it possible to enable (unlock) or disable (lock) a user account (login permission) by extending the CREATE USER and ALTER USER commands.

Extend mysql_real_escape_string() to be aware of the string type it’s escaping for (WL#8077) : This work by Marek Szymczak adds an additional argument of a “quote type” to the function mysql_real_escape_string_quote(). This provides the means to explicitly distinguish between single-quoted and double-quoted strings. This breaks the ABI, so it will not be backported to 5.6.

Fold mysql_install_db into the server binary (WL#7307) : This work by Georgi Kodinov simplifies the server bootstrap process and removes the need for the mysql_install_db script. Simple specifying --initialize on the server command line is now enough to create a database directory if it’s absent, and then initialize it.

ENSURE 5.7 SUPPORTS SMOOTH LIVE UPGRADE FROM 5.6 (WL#8350) : This work by Robert Golebiowski ensures a smooth upgrade fom 5.6 to 5.7 without having to specify --skip-grant-tables as part of an intermediate step.

GIS

Spatial Relation Check Functions Now Use Boost.Geometry Extensions  (WL#7224) : This work by David Zhao replaces the old geometry relation check algorithms with new and improved ones. Boost.Geometry 1.56.0 now contains all of the needed type combinations for all used geometry relation check functions.

Geometry Collection Support (WL#7420) : This work by David Zhao implements support for the geometry_collection type in all GIS functionalty—including spatial relationship checking, geometry set operations, and spatial analysis functions—using Boost.Geometry’s functionality for the six basic types (point, linestring, polygon, multipoint, multilinestring, multipolygon).

More user friendly GIS functions (WL#8034) : This work by David Zhao adds some new GIS functions which, while not being defined by the OGC, make MySQL’s GIS much more user friendly. The added functions are: ST_Distance_Sphere(), ST_MakeEnvelope(), ST_IsValid(),  ST_Validate(), ST_Simplify().

GIS MBR spatial operations enhancement (WL#7541) : This work by David Zhao implements various improvements in the GIS Minimum Bounding Rectangle (MBR) support, see the worklog entry for the details.

Consistent naming scheme for GIS functions – Deprecation (WL#8055) : This work by Erik Frøseth cleans up the GIS function namespace by deprecating, removing, and adding function names (aliases) in order to make the naming scheme consistent and to bring MySQL in line with the OGC standard and other major DBMSs.

Parser

True bottom-up server parser: refactoring of the INSERT/REPLACE statement (WL#7201) : This work by Gleb Shchepa refactors the parser grammar rules for INSERT and REPLACE statements. This work makes the parser more maintainable and extendable.

True bottom-up server parser: refactoring of the DELETE statement (WL#7202) : This work by Gleb Shchepa refactors parser grammar rules for DELETE and TRUNCATE statements. This work makes the parser more maintainable and extendable.

True bottom-up server parser: refactoring of the UPDATE statement (WL#8062) : This work by Gleb Shchepa refactors parser grammar rules for the UPDATE statement. This work makes the parser more maintainable and extendable.

Optimizer

Generated columns (WL#411) : This work by Benny Wang adds support for Generated Columns (GC). The values of such columns, unlike a regular field’s value, isn’t set by the user but instead computed by the server when the row is created or updated, using the expression specified by the user as part of the table definition.

Query Rewrite Plugins (WL#7589) : This work by Martin Hansson implements a Query Rewrite Plugin, as well as a framework (APIs) for people to write their own custom query rewrite plugins. A query rewrite plugin specifies how certain queries that arrive at the server should be rewritten before they are processed and executed by the server. One usage is to enforce/prevent a certain query plan by adding hints to a query. Our query rewrite plugin is a superb tool for handling such problematic queries when users cannot rewrite the query within the application itself, e.g. because it originates from an external tool (like an Object Relational Mapping tool such as Hibernate) or from some other 3rd party application.

Process subqueries in FROM clause in the same way as view (WL#5275) : This work by Roy Lyseng unifies handling of derived tables and views as much as possible. Until now, subqueries in the FROM clause (derived tables) were unconditionally materialized, while views created from the same query expressions were sometimes materialized and sometimes merged into the outer query. This behavior, beside being inconsistent, can lead to a serious performance penalty. This work fixes Bug#59203, Bug#67631, and Bug#12755.

Make switching of index due to small limit cost-based (WL#6986) : This work by Chaithra Gopalareddy makes the decision in make_join_select() of whether to switch to a new index in order to support "ORDER BY ... LIMIT N" cost-based. This work fixes Bug#73837.

API for estimates on how much of table and index data that is in a memory buffer (WL#7168) : This work by Olav Sandstå extends the handler interface with a new API for providing estimates for where table and index data is currently available. It also contains a default implementation that will be used if the storage engine does not provide this information.

Create JOIN object after query preparation (WL#7870) : This work by Roy Lyseng makes it possible to prepare a query without having a JOIN object available. The JOIN object is hereafter created just before a query is optimized, and can thus be initialized directly from a prepared SELECT_LEX object. See also: Bug#70553.

Deprecation and Removal

Deprecate the ENCRYPT, DES_ENCRYPT and DES_DECRYPT functions (WL#8126) : This work by Harin Vadodaria deprecates the encryption and decryption functions based on the Data Encryption Standard (DES) in favor of Advanced Encryption Standard (AES) based functions, because AES provides much better security.

Deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes (WL#6409) : This work by Bharathy Satish deprecates SET PASSWORD and the PASSWORD() function. The existing ALTER USER statement is modified to cover the deprecated functionality. The PASSWORD() function was originally introduced as a way to manually update the mysql.user table. This is generally a bad idea and we want to leave the task of managing user authentication properties exclusively to using the ALTER USER statement which automatically determines which authentication plugin should be used and then adjusts the password algorithm accordingly.

Deprecate and remove the sync_frm sysvar (WL#8216) : This work by Jon Olav Hauglid deprecates the sync_frm system variable in 5.7 (removed in 5.8).

Deprecate GLOBAL @@collation_database, @@character_set_database; assignment of SESSION counterparts (WL#3811) : This work by Jon Olav Hauglid deprecates the GLOBAL collation_database and character_set_database variables in 5.7 (removed in 5.8) and deprecates the SESSION collation_database and character_set_database variables in 5.7 (making them read-only in 5.8). See also: Bug#35357, Bug#27208, and Bug#27687.

Deprecate the conversion of pre MySQL 5.1 encoded database names (WL#8186) : This work by Jon Olav Hauglid deprecates the conversion of pre MySQL 5.1 encoded database/schema names in 5.7 (removed in 5.8). See also: ALTER DATABASE … UPGRADE DATA DIRECTORY NAME.

Remove the innodb_file_io_threads variable in 5.7 (WL#7149) : This work by Sunny Bains removes the innodb_file_io_threads system variable, which was deprecated in 5.5.

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