The MySQL Development team is very happy to announce that MySQL 8.0.12, the first 8.0 Maintenance Release, is now available for download at dev.mysql.com. In addition to bug fixes there are a few new features added in this release, most notably the InnoDB instant add column feature. Please download 8.0.12 from dev.mysql.com or from the MySQL Yum, APT, or SUSE repositories. The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.12 Release Notes. Here are the highlights. Enjoy!
Instant Add Column
Support Instant Add Column (WL#11250) This work by Bin Su implements Tencent Games’ Instant Add Column contribution in MySQL/InnoDB. The user can now add a new column specifying ALGORITHM=INSTANT and it will be done immediately as a meta-data change, for example ALTER TABLE t1 ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;. Instant DDL has been one of the most requested InnoDB features for a very long time. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database. Developers constantly need to add new columns to meet the constantly changing business requirements. Thanks to Tencent Games for the contribution! See also blog post by Bin Su here.
BLOB
Optimizing Small Changes to BLOBs (WL#11328) This work by Annamalai Gurusami implements optimizations for small updates to BLOBs in InnoDB. By small, we here mean the size of the modification (up to 100 bytes), and not the size of the BLOB. The core idea is to Undo log the update vector of BLOBs, and thus avoid writing the before and after image to the tablespace, saving time and space. A slightly modified Sysbench test, updating a single integer in a JSON document shows an increase in QPS from doubling to tripling caused by this optimization. See blog post by Annamalai Gurusami here. The optimization is an addition to the partial update feature made by Annamalai in 8.0 (see here, here and here).
Replication
Increase Slave’s Multi-Threaded Event Applier Buffer (WL#11348) This work by Bill Qu changes the default of slave_pending_jobs_size_max from 16M to 128M. It’s not uncommon for users to have some large transactions mixed in with more “normal” smaller ones. If these large transactions are larger than slave_pending_jobs_size_max in size then they clog up the MTS applier pipeline and the slave will begin lagging. For example, see the discussion in forums.mysql.com here.
Group Replication
Instrument memory usage in GCS/XCom (WL#9855) This work by Andre Negrao instruments relevant memory management operations in GCS/XCom and exposes them automatically via the performance schema. This gives users a way to analyze memory usage within the XCom Cache, and to detect memory related problems that may affect the workflow of consensus messages.
Option to shutdown server when dropping out of the group (WL#11568) This work by Ricardo Ferreira allows the user to define the behavior of the server once it drops out of the group. Before this change, the server goes into super read only mode when dropping out of the group. This is not always the desired behavior since users connected to this server will read old data. With this change, the user can specify the server to shut itself down, terminating all user connections and avoiding stale reads.
Query Rewrite Plugin
Support insert, update and delete statements (WL#8776) This work by Sreeharsha Ramanavarapu and David Hovind expands the Query Rewrite Plugin plugin to support INSERT, UPDATE and DELETE statements. Before this change, the plugin only supported SELECT statement patterns.
Add transformed query to EXPLAIN INSERT/UPDATE/DELETE (WL#6033) This work by Sreeharsha Ramanavarapu adds a warning with the transformed INSERT/REPLACE/UPDATE/DELETE statement when EXPLAIN syntax is used. This provides the same behaviour as EXPLAIN SELECT. See Bug#62617.
Windowing Functions
Add BIT_AND, BIT_OR and BIT_XOR window aggregates (WL#11573) This work by Dag Wanvik adds windowing support for the MySQL specific aggregate functions BIT_OR(), BIT_AND() and BIT_XOR().
GIS
ST_Latitude and ST_Longitude (WL#11453) This work by Norvald Ryeng implements two new functions, ST_Latitude() and ST_Longitude(), to get and set the latitude and longitude coordinates of geographic points. This work also defines that ST_X() operates on the first axis and ST_Y() on the second axis, with axes ordered in the order they appear in the SRS definition. This closes Bug#88503.
Code Improvements
Flexible filesort (WL#11590) This work by Steinar Gunderson makes filesort more flexible and less oriented around fixed-size rows by allocating buffers incrementally and by an optimistic approach to sorting and available memory. The primary user-visible benefit of this work is that a user can freely set the default sort buffer size much higher than previously, without having to worry about excessive memory usage for small sorts. This means that large ORDER BY and GROUP BY queries will become much faster, while still keeping small queries at low memory usage.
Test Improvements
MTR: Improvements to –check-testcases option (WL#10928) This work by Mohit Joshi adds more checks to the check-testcases logic in MySQL Test Runner (MTR). MTR check-testcases performs checks just before and after the test execution to ensure there are no changes in the internal state of the Database.
Deprecation and Removals
Make the SPATIAL keyword optional in index creation (WL#11808) This work by Norvald Ryeng makes the SPATIAL keyword optional. As of 8.0.11 the user will not get the expected behavior when omitting the SPATIAL keyword in the following statement CREATE SPATIAL INDEX idx ON table (geometry_column); because a normal B-tree index is created and this index can never be used for geometry calculations. This work fixes this so that a SPATIAL index is created when the column is detected to be a geometry column. There is no change for non-geometry columns. Note that this represents a behavioral change between 8.0.11 and 8.0.12 since it is no longer possible to create a B-tree index over geometry columns (column type GEOMETRY, POINT, LINESTRING, POLYGON, GEOMCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON). This also means that it becomes impossible to use geometry columns in primary keys and unique indexes. It also makes it impossible to create prefix indexes on geometry columns.
Deprecate the syntax for GROUP BY ASC and DESC (WL#11459) This work by Gleb Shchepa adds a deprecation warning for the non-standard, legacy MySQL syntax GROUP BY ASC and DESC. We expect the support for this syntax to be removed in an upcoming 8.0 maintenance release. Applications must be rewritten from using GROUP BY col1 ASC/DESC to GROUP BY col1 ORDER BY col1 ASC/DESC (ok both in 5.7 and 8.0). This is according to the SQL standard and the practice of other databases.
Thank you for using MySQL !