The MySQL 5.7.5 Milestone Release is Available

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

Scalability

Improve scalability by not using thr_lock locks for InnoDB tables (WL#6671) : This work by Dmitry Lenev improves InnoDB scalability by not using thr_lock locks for InnoDB tables. For InnoDB tables we now rely on MDL + InnoDB row locks. This patch shows good performance/scalability improvements in the single table Sysbench OLTP_RO/ POINT_SELECT tests for InnoDB on multi-core machines.

Truncation of Undo Log

InnoDB: Truncate UNDO logs/tablespace (WL#6965) : This work by Krunal Bauskar implements automatic truncation of undo logs when separate UNDO tablespaces have been configured. InnoDB operates with several undo tablespaces that are periodically truncated, one at a time. While one UNDO tablespace is being truncated the other UNDO tablespaces will be available to service transaction management to ensure minimal impact on transaction processing. The purpose of this work is to avoid ever growing UNDO log file sizes that could occur in some usage scenarios. See also Bug#1341 reported by Scott Ellsworth.

Partitioning

Allow WITHOUT VALIDATION on EXCHANGE PARTITION (WL#5630) : This work by Mattias Jonsson adds support for the [{WITH|WITHOUT} VALIDATION] clause to the EXCHANGE PARTITION WITH TABLE command in order to skip the default row-by-row validation step. When we implemented EXCHANGE PARTITION WITH TABLE (WL#4445) the option to skip the row-by-row validation step was provided via the IGNORE clause. However, this lead to bugs like Bug#55944, and the IGNORE clause was not a good choice of wording since it conflicts with how the reserved IGNORE word was already used in MySQL (e.g. Bug#57708). Therefore, in order to properly allow the DBA to ensure that all rows would fit into the partition, this worklog is adds the new [{WITH|WITHOUT} VALIDATION] clause in order to control whether or not to skip the row-by-row validation step.

Online Buffer Pool Resizing

InnoDB: resize the InnoDB Buffer Pool online (WL#6117) : This work by Yasufumi Kinoshita adds the capability to change the value of innodb_buffer_pool_size dynamically. This provides the ability to tune the buffer pool size—without incurring any downtime—as your database usage patterns evolve over time. Note: this work does not include the possibility of altering the number of buffer pool instances.

Bulk Data Load Improvements

InnoDB: bulk load for create index (WL#7277) : This work by Shaohua Wang implements sorted index builds, thus making CREATE INDEX operations much faster. Prior to this work InnoDB looped through the base table and created one record in the index table for each record in the base table. After this work InnoDB reads many records from the base table, sorts the records using the index key, and then inserts the chunked set of rows into the index table.

Proper Connection ID Handling

Ensure that 32-bit connection-IDs roll over safely (WL#7293) : This work by Tatjana Nurnberg and Jon Olav Hauglid avoids the reuse of any connection IDs that are still in use. Even for 64-bit builds, the wire-protocol still only uses 32 bits for the connection ID for backward compatibility reasons. Some customers have experienced conflicts with old and new connection IDs when the ID value rollover occurs and new connections are created. See also Bug#44167 reported by Jan Kneschke.

Syslog Support

Add native support for syslog on Unixoid platforms (WL#7793) : This work by Tatjana Nurnberg adds the option to redirect the general log output to the system’s syslog facility. This new option can be set at start-up as well as viewed and set at runtime using the new log_syslog system variable (ON/OFF, defaults to OFF). See also Bug#55370 reported by Kyle Joiner and later enhanced by Simon Mudd and Marc Alff.

Multiple User Level Locks

Allow multiple locks in GET_LOCK() (WL#1159) : User-level locks are often used to organize mutual exclusion when accessing some resource in cases when table or row-level locks are not appropriate. This work by Dmitry Lenev – based on a contribution by Konstantin Osipov (Bug#67806) – allows for multiple user level locks per connection. The user is now allowed to request multiple user locks by issuing a series of GET_LOCK statements. GET_LOCK no longer releases all user-level locks previously acquired. The work replaces the original custom user-level lock implementation with one that is based on the MDL lock manager. If there are any resulting deadlocks between different connections acquiring the same user-level locks, then metadata locks (MDLs) are used and any threads waiting for table flushes are detected and errors reported.

Dtrace Support

Improve Dtrace support on Oracle Linux (WL#7894) : This work by Praveenkumar Hulakund adds full Dtrace support to MySQL in the Server 5.6+ packages that we ship for Oracle Linux 6+.

IGNORE Clause

Define and reimplement IGNORE (WL#6614) : This work by Abhishek Ranjan properly defines the meaning and handling of the IGNORE clause. It reimplements IGNORE so that it is consistent across all supported SQL statements while also making it much easier to maintain. The IGNORE clause is a MySQL extension to the SQL standard, the purpose of which is to INSERT as many records as possible, while skipping records that violate various constraints (i.e. taking no action on those records). The same principle applies to DELETE IGNORE, REPLACE IGNORE, and UPDATE IGNORE. However, until now this principle has not been enforced consistently. See also Bug#30191, Bug#49539, Bug#55421, Bug#54543, Bug#54106, Bug#49534, Bug#47788, Bug#46539, and Bug#46425.

STRICT Mode

Define and reimplement STRICT mode (WL#6891) : This work by Raghav Kapoor implements a consistent behaviour of STRICT MODE across all supported SQL statements. This work is similar to what we did for IGNORE mode, but here it is applied to STRICT MODE. The IGNORE clause downgrades errors to warnings to allow statements to skip row(s) which would have otherwise have caused the entire statement to abort. STRICT MODE does just the opposite—it upgrades warnings to errors. Similar to IGNORE, STRICT MODE has not previously been clearly and consistently defined, thus the implementation has been the source of many bugs like Bug#42910, Bug#5929, Bug#43880, Bug#48637, Bug#5912, and Bug#5913. These issues have now been fixed and address by this new work. Note: we have also deprecated ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE SQL MODES as part of this work and made all of that functionality part of STRICT MODE. See WL#7467 for those details.

Make STRICT mode for transactional SEs the default in 5.7 (WL#7764) : This work by Raghav Kapoor makes STRICT MODE the default for all transactional storage engines. This change does not affect non-transactional storage engines such as MyISAM.  In MySQL 5.6 we introduced the my.cnf/my.ini files provided during install which included the the STRICT_TRANS_TABLES setting which enabled STRICT MODE for transactional storage engines. In MySQL Server 5.7 it now becomes the compiled-in default.

The MySQL Upgrade Tool

Refactor mysql_upgrade (WL#7308) : This work by Marcin Babij is a rewrite of the mysql_upgrade tool which fixes many reported bugs while also making mysql_upgrade more robust and easier to maintain. The mysql_upgrade tool has been rewritten using the C API for queries and mysqlcheck functions are now linked directly into the mysql_upgrade executable. This fixes problems it previously had with handling options, logins, special server startup modes, etc. For example, this work fixes Bug#65288 reported by Nicholas Bamber and Bug#71579 reported by Florian Weimer.

The MySQL Install DB Tool

Deprecate mysql_install_db and create a new program in C++ (WL#7688) : This work by Kristofer Pettersson rewrites the mysql_install_db tool from perl/bash to C/C++. At the same time we redesigned it in order to provide a better user experience, cover more functionality, and improve security (all while still offering all legacy features).

Offline Mode

Method to bring servers off line (WL#3836) : This work by Atanu Ghosh introduces the new offline_mode global server variable which is settable by a user with SUPER privilege. The intended usage is for upgrade purposes. Setting the server to offline mode will gracefully disconnect all connected clients except those with the SUPER privilege. “Super” users are allowed to connect and manage the system while in offline mode; this also allows replication slave threads to continue applying changes to the server.

Temporary Tables

Optimizing temporary tables (WL#7682) : This work by Krunal Bauskar introduces a sub-class of temporary tables called “intrinsic temporary tables”. An intrinsic temporary table is like a normal temporary table but with relaxed ACID and MVCC semantics. The purpose is to support internal use cases where internal modules (such as the optimizer) demand light weight and ultra-fast tables for quick intermediate operations.

Support InnoDB as an additional storage engine for tmp tables (WL#6711) : This work by Benny Wang makes it possible to switch between using MyISAM or InnoDB for storage of internal temporary tables created by the optimizer as part of the query execution. The switch is in the form of a new global system variable called INTERNAL_TMP_DISK_STORAGE_ENGINE.

Fulltext Search Optimizations

Additional query optimizations for Full Text Search (WL#7123) : This work by Sergey Gluhov implements hints passed on to InnoDB about a query so that InnoDB may skip part of the full text search processing, thus improving  performance. For example, it no longer computes the ranking values if they are not needed such as: SELECT COUNT(*) FROM test.wp WHERE MATCH(text) AGAINST (‘+for +the +this+that’ in boolean mode) ;  Moreover, it adds hint to InnoDB that we can use later to further improve some other query performance. The hint information it adds includes the following: 1) No need to sort on ranking. When the ranking is not important for the result set, sorting on ranking is not needed. 2) No ranking needed. In the cases where the ranking values are not needed InnoDB can skip calculating and holding ranking values.

Optimizer – ONLY_FULL_GROUP_BY mode

Better ONLY_FULL_GROUP_BY mode (WL#2489) : This work by Guilhem Bichot improves the behavior of ONLY_FULL_GROUP_BY mode and makes it a default in 5.7. This work makes this SQL mode far less strict about selected/order expressions because the server now properly recognizes functional dependencies. The user can also now specify the ANY_VALUE clause which then relaxes the rules for the HAVING, DISTINCT, and ORDER BY clauses. This work addresses many user complaints as illustrated by Bug#51058 and by Roland Bouman’s blog Debunking GROUP BY myths.

Security

System account auth plugin (WL#7726) : This work by Todd Farmer implements a mysql_no_login authentication plugin. Accounts defined as using the mysql_no_login authentication plugin will reject all client connections. Albeit rejecting client connections, such accounts may be assigned privileges like any user and they may be used as the DEFINER or INVOKER for stored programs, events and views, and they may be used as a base user for proxy users.

Remove the deprecated old_password plugin (WL#8006) : This work by Georgi Kodinov removes the mysql_old_password authentication plugin, thus removing support for passwords using the older unsafe pre-4.1 password hashing format. Accounts that use this plugin are now disabled at startup and the server writes an “unknown plugin” message to the error log.

Performance Schema

We are continuing to deliver on our monitoring roadmap. In 5.7.5 we reduce overhead, facilitate progress reporting for long running statements, expose user variables associated with a thread, instrument the newly introduced “SX lock” and instrument memory allocation in InnoDB.

Batch Table IO stats (WL#7802) : This work by Haixiang Li and Marc Alff implements changes in the table IO instrumentation to record statistics in batches of N operations. This work significantly reduces the overhead for table IO instrumentation.

USER VARIABLES (WL#6884) : This work by Marc Alff introduces a new table called performance_schema.user_variables_by_thread. This table exposes user variables and values associated with each thread. Only SELECT statements are supported as this is a read-only table.

Statement progress (WL#7415) : This work by Marc Alff improves the stage instrumentation by keeping track of a progress indicator. This allows users to monitor the progress of long running statements such as ALTER TABLE.

Instrument SX-lock for rw_lock (WL#7445) :  This work by Marc Alff enhances the performance schema instrumentation for read/write locks to support the new SX-lock operation.

Integrate memory instrumentation with InnoDB (WL#7777) : This work by Vasil Dimov implements memory instrumentation within InnoDB using the interface provided by Performance Schema. To support this a new mechanism for allocating memory in InnoDB has been introduced. This work complements the memory instrumentation previously done at the Server layer in 5.7.3.

InnoDB GIS Support

We have implemented spatial indexing in InnoDB. InnoDB spatial index can be used with all existing syntax that has been developed for MyISAM spatial indexes. In addition, InnoDB spatial index supports full transaction properties, as well as isolation levels. It employs predicate lock to prevent the phantom scenario. See also Jimmy Yang’s article “InnoDB Spatial Indexes in 5.7.4 LAB release“.

R-tree index support (WL#6968) : This work by Jimmy Yang implements spatial indexes within InnoDB. The R-tree search and traversal is different from that of a B-tree in the sense that a search criteria could be met in multiple leaf pages of different search paths. A search/query bounding box can also intersect with or contain multiple leaf and non-leaf bounding boxes. Because of this we also refactored the row_search_for_mysql() and btr_cur_search_to_nth_level() functions to do this work properly.

Support DML operations for InnoDB R-tree indexes (WL#6745) : This work by Zheng Lai adds R-tree index support in InnoDB for the Geometry datatype (R-tree split and shrink operations). This work adds InnoDB support for all MBR (Minimum Bounding Rectangle) manipulation functions and 2 dimensional GIS datatypes that MyISAM previously had.

Support predicate locking for spatial indexes (WL#6609) : This work by Jimmy Yang implements predicate locks to enforce consistent reads for the spatial (R-tree) indexes. For InnoDB spatial indexes we cannot use next key locking to support the REPEATABLE READ or SERIALIZABLE isolation levels as there is no such absolute ordering concept in multi-dimensional data. So it is difficult to define the “next” key. Instead, this work uses predicate locks to enforce consistent reads for spatial indexes. In this case we might simply lock the Minimum Bounding Rectangle (MBR) which was used for the query, and other transactions cannot insert or modify a row that would have matched the search condition. The predicate in this case is limited to the MBR used in the spatial query (e.g. ST_Within or ST_Contains).

Store Geometry POINT datatype as fixed length column rather than BLOB (WL#6942) : This work by Bin Su implements an optimization for POINTs; a commonly used GeoSpatial datatype. The optimization is to avoid storing POINTs as a BLOB and instead use a fixed length datatype. This increases overall performance, while also saving on disk space and CPU time.

GeoSpatial / GIS

Now that InnoDB is the default storage engine for MySQL, our user base is rapidly transitioning to InnoDB. One capability that they have been demanding is a performant and scalable GIS implementation. Along with adding R-tree index support to InnoDB, we decided to replace the original GIS algorithms with a more powerful, reliable, effective and efficient geometric engine.  See also Manyi Lu’s article “Why Boost.Geometry in MySQL?” and David Zhao’s article “Making Use of Boost Geometry in MySQL GIS“.

Spatial relation check functions (WL#7220) : This work by David Zhao implements MySQL GIS relation check functions using Boost. Geometry. Many type combinations which were not previously supported before are now supported, e.g. functions like within, intersects, equals, disjoint, cross, and overlaps.

Geometry set operations (WL#7221) : This work by David Zhao implements MySQL GIS geometry set operations using Boost.Geometry. Many type combinations which were not previously supported are now supported, e.g. set operations like intersection, union, and difference.

Spatial analysis functions (WL#7236) : This work by David Zhao refactors the spatial analysis functions including area(), centroid(), convexhull(), distance() and envelope() using Boost.Geometry functions. The semantics of these functions is now implemented according to the OGC standard specifications.

WKB geometry container (WL#7280) : This work by David Zhao implements WKB (well known binary) containers which conform to the Boost.Range concept, so as to be used
as adapters between the existing WKB Geometry data and the Boost.Geometry algorithms. The aim of this work is to avoid unnecessary conversions between WKB encoded byte strings and Boost.Geometry objects as such conversions can be expensive.

GeoHash support (WL#7928) : This work by Erik Frøseth adds functions to encode and decode GeoHashes. GeoHash is a system for encoding latitude and longitude coordinates of arbitrary precision into a text string. The added functions will make it possible for applications to import and export data from MySQL using the GeoHash format. It will also make it possible to index and search for geographical data in one-dimensional (B-tree) indexes.

GeoJSON support (WL#7444) : This work by Erik Frøseth adds functions for parsing and generating GeoJSON [1,2] documents for GIS data types: ST_AsGeoJSON and ST_GeomFromGeoJSON. GeoJSON is an open standard for encoding geometric/ geographical features. GeoJSON supports the same geometric/geographic datatypes that are already supported by MySQL. GeoJSON also includes the possibility to declare which coordinate reference system  is used for the feature (WKT and WKB lack this).

Parser Refactoring

We are in the process of refactoring the SQL parser in an incremental way. The old parser had critical limitations because of its grammar complexity and top-down parsing style which caused poor maintainability and extensibility. See article by Gleb Shchepa “SQL parser refactoring in 5.7.4 LAB release“.

True bottom-up server parser—a common framework for the refactoring (WL#7199) : This work by Gleb Shchepa provides a base framework of classes and a methodology to transform the current SQL parser to a pure bottom-up parser in subsequent small, separate, and safe steps.

True bottom-up server parser—refactoring of the SELECT statement (WL#7200) : This work by Gleb Shchepa refactors the SELECT statement related parser grammar rules in a pure bottom-up style to make the grammar context-independent for improved maintainability and extendability.

True bottom-up server parser—refactoring of the SET statement (WL#7203) : This work by Gleb Shchepa refactors SET statement related parser grammar rules in a pure bottom-up style to make the grammar context-independent for improved maintainability and extendability.

Optimizer Refactoring – New Layered Architecture for the Optimizer

Currently, the phases of parsing, optimizing and execution are intermixed. Almost every module is spread over different optimizer parts. As a consequence, the cost of maintaining the codebase is high and extensibility is poor.  We started out an optimizer refactoring project with the goal of clear separation of these phases. In 5.7.5 we deliver yet another two increments:

Split JOIN_TAB structure in two parts: optimizer’s and executor’s (WL#6042) : This work by Guilhem Bichot splits the internal JOIN_TAB data structure into one structure used during optimization (“the optimization plan”) and one used for execution (“the execution plan”). It also gets rid of redundant allocations of SQL_SELECTs and JOIN_TABs. This work improves maintainability and reduces memory usage.

Move properties from TABLE to TABLE_LIST: table number and map (WL#7540) : This work by Roy Lyseng represents a step in refactoring and improving prepared statements. The lifetime of a TABLE object is one execution, whereas a TABLE_LIST object lasts as long as the prepared statement it is used for. Hence, it may be reasonable to collect table specific properties that are constant throughout all executions of a query in the TABLE_LIST object. Examples of such properties are table number and table map. This will also focus the TABLE_LIST object as the optimizer’s view of a table throughout query preparation and optimization.

Work towards a new Cost Model

We want to improve the cost based optimizer and replace existing heuristics with cost based decisions. We want to produce better cost estimates which also take into account new hardware architectures (larger buffers, caches, SSDs, etc.). Better cost estimates will lead to better decisions by the optimizer and thus to better performance.  See also Jørgen Løland’s article “The MySQL Optimizer Cost Model Project“.

Use Cost_estimate values within the range Optimizer (WL#5869) : This work by Benny Wang changes the internal cost representations used within the range optimizer from double values to Cost_estimate objects. The old double value was the sum of disk lookup cost, CPU comparison costs, etc. With the new Cost_estimate object the CPU, memory, and disk costs are kept separate. This work does not introduce any functional changes.

Make use of condition filtering in the Optimizer (WL#6635) : This work by Jørgen Løland improves join ordering. It provides a much better prefix rows estimate by taking into account not only conditions that are used by the chosen access method but all other relevant conditions as well. See also “A New Dimension to MySQL Query Optimization” part1 and part2 by Jørgen Løland.

Remove the InnoDB rec_per_keys hack (WL#6068) : This work by Vasil Dimov removes the historical InnoDB hard coded “correction” of index selectivity. At some point in the past, InnoDB judged MySQL to favor table scans too much over index searches, and decided to report index selectivity to be 2 times better than the actual estimate.

Use improved records per key estimate interface in the Optimizer (WL#7339) : This work by Olav Sandstå changes the optimizer to use the new interface for providing “records per key” values using float values (instead of integers). Using this new interface instead of using the previously used rec_per_key values will give more correct record estimates for ref access against an indexed column.

Configuration tables for the Optimizer Cost Model (WL#7276) : This work by Olav Sandstå introduces tables for storing “cost constants”. Until now the Optimizer cost model has been using “cost constants” that were hard-coded in the source code. These are now replaced by “cost constants” that can be changed without making changes to the source code and can be adjusted by the server administrator. This work defines the tables that will be used for storing the “cost constants” to be used by the new Cost Model. These tables will then be read and used by the Optimizer.

Optimizer Cost Model—main memory management of cost constants (WL#7315) : This work by Olav Sandstå implements reading cost constants from the new cost constant tables and maintaining these in a main memory cache, providing the current version of them to the Optimizer’s Cost Model. This “cost constant cache” is shared by all running queries and it supports concurrent access and sharing of the cached cost constants.

Optimizer cost model—command for online updating of cost model constants (WL#7316) : This work by Olav Sandstå implements the statement and methods used for reloading the cost constants in a running server, re-loading the data into the cost constant cache.

Replication

So far in 5.7 the most prominent replication features are  semisync-replication and improved muti-threaded slaves. In 5.7.5 we deliver a few improvements to multi-threaded slaves and we deliver some preparatory work for multi-source replication (in Labs).

Support SLAVE_TRANSACTION_RETRIES in multi-threaded slave mode (WL#6964) : This work by Libing Song enables Multi-Threaded Slave (MTS) replication slaves to retry a transaction after a temporary failure. Before this work only non-MTS replication slave servers attempted to retry a transaction after a temporary failure. See Bug#68465 reported by Yoshinori Matsunobu.

Slave performance—waiting for more transactions to enter the Binlog Group Commit (BGC) queue (WL#7742) : This work by Luis Soares adds two new options to introduce an artificial delay in order to increase the binary log group commit size. This gives a chance for more transactions to be flushed and synced together to disk, thus reducing the overall time spent in committing the transactions (the bigger the groups the fewer sync operations). With the correct tuning, this can make the slave perform several times faster without compromising the master’s throughput. These options are named: binlog-group-commit-sync-delay and binlog-group-commit-sync-no-delay-count.

Move status variables to replication Performance Schema tables (WL#7817) : This work by Shivji Jha moves replication system variables to Performance Schema tables so they can be monitored per source and not as global variables. The replication variables affected are: Slave_running, Slave_retried_transactions, Slave_last_heartbeat, Slave_received_heartbeats, and Slave_heartbeat_period. This work is a requirement for multi-source replication.

WAIT_FOR_EXECUTED_GTID_SET (WL#7796) : This work by Manish Kumar adds a new sql function WAIT_FOR_EXECUTED_GTID_SET which makes the master-slave syncing option independent of the slave threads. If the slave thread is not running the WAIT_FOR_EXECUTED_GTID_SET(GTID_SET [, TIMEOUT]) function keeps waiting until success (0) or timeout (1).

Optimize GTIDs for passive slaves—store GTIDs in a table (WL#6559) : This work by Daogang Qu adds the ability to store GTIDs in a table instead of in the binary log. A common use case is when having a slave that is only used for read scale-out and is never going to become a master and thus may not have any use for the transactions in the binary log, but may have a use for the GTIDs. E.g. to allow fail over to another new master.

Multi-threaded slaves—ordered commits (sequential consistency) (WL#6813) : This work by Libing Song ensures that the commits by slave applier/worker threads running in parallel will be done using the same execution order as on the master. This also means that the slave will never externalize a database state which was never externalized by the master. This is a requirement when the applications reading from the slave must observe the same set of states that existed on the master due to some application enforced constraint. This has become a necessity after WL#6314 implemented in 5.7.2 which enables multiple transactions to be executed in parallel by the slave threads, few of which may be modifying a single database.

Refactoring

We are continuing to refactor the source code to pave the way for new features, improve reliability, improve security, improve maintainability, utilize newer compiler features, and in some cases fix historical flaws. Here are the things we did in 5.7.5:

Refactoring for WL#6835 (WL#8023) This work by Sunny Bains adds the infrastructure to track what a transaction is doing at any given time. This is pure refactoring only, no functional change.

Repurpose FIL_PAGE_FLUSH_LSN (WL#7990) : This work by Marko Mäkelä  will stop writing FIL_PAGE_FLUSH_LSN to any files except for the first file of the InnoDB system tablespace (page number 0:0).

Simplify tablespace discovery during crash recovery  (WL#7142) : This work by Marko Mäkelä eliminates the use of the file system as a “data dictionary” during redo log processing (before applying redo log). See also “InnoDB Crash Recovery Improvements in MySQL 5.7” by Marko Makela.

Memroot allocator for C++ STL (WL#6074) : This work by Jon Olav Hauglid implements two memory allocators: one that takes memory from a MySQL MEM_ROOT, and one that uses heap (via my_malloc/my_free). The latter is important for Boost.Geometry as it allows for Performance Schema tracking of its memory usage.

Remove custom rwlock windows implementation (WL#7914) : This work by Jon Olav Hauglid removes the custom rwlock implementation on Windows as this function is now provided natively on all of our supported Windows platforms (Windows 7+, Server 2008 R2+).

Remove MY_ATOMIC_MODE_RWLOCKS (WL#7655) : This work by Jon Olav Hauglid removes our homegrown atomics implementation (using mutexes) as it is no longer needed. We are now using either GCC builtins, Windows interlocked operations, or Solaris atomics.

New data dictionary—don’t hold the LOCK_open mutex while reading table definitions (WL#7593) : This work by Sivert Sørumgård makes it possible to read more than one table definition from the new DD concurrently (or also to read it from .FRMs concurrently).

Refactor strings—use of (const char *) in LEX_USER (WL#7582) : This work by Thayumanavar Sachithanantham changes (char *) and LEX_STRING to (const char *) and LEX_CSTRING respectively in the LEX_USER structure and related functions. This work is part of an effort to move to a more reliable string implementation.

Refactor strings—use of (const char *) in THD (WL#7583) : This work by Thayumanavar Sachithanantham changes (char *) and LEX_STRING to (const char *) and LEX_CSTRING respectively in the THD class and related functions. This work is part of an effort to move to a more reliable string implementation.

Move time zone and help tables from MyISAM to transactional storage (WL#7159) : This work by Dmitry Shulga moves time zone and help tables from MyISAM to InnoDB. The purpose of this work is to increase reliability by storing system tables in a transactional engine.

Server support for attachable transactions (WL#8003) : This work by Alexander Nozdrin updates the server code so that it is able to use InnoDB attachable transactions. This work is also a first step to full server support of nested transaction contexts. This work is motivated by the work on the new Data Dictionary.

Attachable transaction support in InnoDB (WL#7828) : This work by Alexander Nozdrin implements attachable transactions in InnoDB. An attachable transaction is always a AC-RO-RC-NL transaction (AutoCommit-ReadOnly-ReadCommitted-NonLocking). This work is motivated by work on the new Data Dictionary. The new Data Dictionary needs read-write access to system tables (dictionary tables) stored in InnoDB. Reading data should be always performed in the READ COMMITTED isolation level no matter which isolation level is active within the user transaction.

Improved Test Coverage

We are continuing to improve the public testsuites, to ensure that users who make changes and compile from source can do so with the knowledge that test coverage is good. In 5.7.5 added 229 new MTR tests and we also harmonized testing related aspects between MySQL Server and MySQL Cluster by making include/sync_slave_sql_with_master.inc work as a replacement to sync_slave_with_master for NDB in MTR tests (WL#7774).

Deprecation and Removal

We are continuing to deprecate and remove old options and behavior that is no longer relevant or have been replaced with better alternatives. In 5.7.5 we did the following:

Remove limited YEAR(2) support (WL#6263) : This work by Haixiang Li removes all remnants of YEAR(2) related code in 5.7. Overall we removed the support for YEAR(2) in 5.6, but we kept some limited support for old YEAR(2) columns to allow for upgrade procedures to MySQL 5.6. This limited support has now been removed.

Deprecate skip-innodb in 5.6 and remove it in 5.7 (WL#7976) : This work by Alexander Nozdrin removes the server options which disable InnoDB. The system tables are being moved from MyISAM to InnoDB, so in 5.7+ it will be not possible to run the MySQL server without InnoDB (which is the default SE since 5.5).

Remove the storage_engine system variable in 5.7 (WL#7148) : This work by Jon Olav Hauglid removes the storage_engine system variable (deprecated in 5.5 and removed in 5.7). You should instead use the default_storage_engine server option.

Deprecate and remove the timed_mutexes system variable (WL#7436) : This work by Jon Olav Hauglid removes the timed_mutexes system variable (deprecated in 5.5/5.6 and removed in 5.7). The timed_mutexes system variable has been disconnected from any underlying InnoDB code, so it no longer serves any purpose.

Deprecate and remove mysqlbug (WL#7689) : This work by Erlend Dahl removes the mysqlbug script because it is obsolete (deprecated in 5.5/5.6 and removed in 5.7).

Deprecate and remove mysql_zap and mysql_waitpid (WL#7826) : This work by Erlend Dahl removes the mysql_zap and mysql_waitpid scripts because they are obsolete (deprecated in 5.6 and removed in 5.7).

Deprecate and remove mysqlhotcopy (WL#7854) : This work by Erlend Dahl removes the mysqlhotcopy perl script because it is obsolete (deprecated in 5.6 and removed in 5.7).

Deprecate the EOF packet (WL#7766) : This work by Bharathy Satish replaces the EOF packet with the new OK packet in the MySQL client-server protocol. The EOF and OK packets serve the same purpose — to mark the end of a query execution result. The protocol is kept backward compatible as clients and servers have one additional capability flag to denote their support for the new OK packet. When this capability flag is in place, any SQL statement which produces a result set will send an OK packet as a marker to indicate the end of data rather than an EOF packet.

About Geir Hoydalsvik

Geir Høydalsvik has been working with MySQL Database team since 2008. He is currently employed by Oracle, based in Norway. He is Senior Software Development Director and responsible for the development and maintenance of MySQL Database. He has a background in the database industry, working for the database startup company Clustra Inc. on the Clustra database and for Sun Microsystems on Java DB. He has a Master degree in Computer Science and a PhD in Software Engineering from the Norwegian University of Science and Technology.

16 thoughts on “The MySQL 5.7.5 Milestone Release is Available

  1. Frankly, was not sure what to expect after Oracle came in, but all expectations met and also mind blown. So many news and features, great effort from all team members. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter *