The MySQL 5.7.1 Milestone Release

Due to all of the positive feedback that I received on the MySQL 5.7.3 & 5.7.4 blog posts, I have decided to complete the picture by doing the same for 5.7.1 & 5.7.2. Note that you will find historical releases — including milestone releases — at downloads.mysql.com.

So here is the belated post for the MySQL 5.7.1 Milestone Release, released on April 22, 2013. You can find the full list of changes and bug fixes in the 5.7.1 Release Notes. Enjoy!

InnoDB Online Alter Table

Online Rename Index This work by Marko Mäkelä (WL#6752) and Dmitry Lenev (WL#6555) makes it possible to rename an index as an online operation. Example:

mysql> ALTER TABLE t RENAME INDEX i1 TO i2;

Enlarge VARCHAR column size online (WL#6554) This work by Marko Mäkelä makes it possible to enlarge varchar column sizes as an online operation. This is true as long as the number of bytes required by the VARCHAR column type (VARCHAR/LONGVARCHAR) remains the same, i.e. from 0 to 255 or from 256 to higher. Example:

mysql> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

InnoDB Temporary Table Performance

One of the goals of 5.7 is to optimize InnoDB temp tables for better performance. As a first step toward this goal, we made temp table creation and removal a more light-weight operation by avoiding the unnecessary step of persisting temp table metadata to disk.

Separate tablespace for temp tables (WL#6560). This work by Krunal Bauskar moves temp tables to a separate tablespace so that the recovery process for temp tables becomes a single stateless step by simply re-creating it at start-up. The new separate temp tablespace is named ibtmp1 and resides in the data directory.

Optimize DDL for temp tables (WL#6469) This work by Krunal Bauskar removes unnecessary persistence for temp tables. Temp tables are only visible within the connection/session in which they were created, and they are bound by the lifetime of the server (the temp tablespace is re-created at start-up). Thus, for statements such as CREATE TEMPORARY TABLE we will not make the table definitions persistent. This will eliminate unnecessary disk IO during the table create/drop phases.

InnoDB Spatial Index

One of the goals for 5.7 is to implement spatial indexing in InnoDB. To achieve this InnoDB needs to support GEOMETRY datatypes, so that InnoDB understands and stores them properly.

InnoDB: GEOMETRY datatypes support (WL#6455) This work by Zheng Lai adds a new InnoDB internal datatype called DATA_GEOMETRY, and maps all MySQL GEOMETRY datatypes to this new internal datatype. InnoDB continues to use BLOBs for the underlying storage, but the columns are marked as the proper datatype. Since InnoDB uses BLOBs for the underlying datatype, the prefix index on the GEOMETRY data column can still be used. Also, for the same reason, nothing special needs to be done for recovery related processes.

Partitioning

Add limited SQL HANDLER support for partitioning  (WL#6497) This work by Mattias Jonsson adds step by step index reads for the SQL HANDLER statements. The purpose is to improve the test coverage of index reads in the partition engine.

Triggers

BEFORE triggers are not processed for NOT NULL columns (WL#6030) This work by Dmitry Shulga ensures that we check column constraints at the end of the SQL statement. This is in compliance with the SQL standard. In 5.6 and earlier, MySQL checks the column constraints too soon. For example, if a column was NOT NULL, one could not INSERT a NULL in the original statement (or UPDATE it to NULL), even though an associated trigger later set the column to a NON-NULL value (thus satisfying the NOT NULL column requirement for the final row that is created). See also Bug#6295 reported by Peter Gulutzan and Dmitry Shulga’s blog post  about his implementation.

Error Reporting

Stacked Diagnostic areas (WL#6406) This work by Jon Olav Hauglid implements support for stacked diagnostic areas, according to the SQL standard. This includes updating the current implementation of HANDLER statements, SIGNAL/RESIGNAL, and stored program execution. The GET DIAGNOSTICS statement is also extended to support GET [STACKED] DIAGNOSTICS.

Error Logging

Split info logging and binary logging code (WL#6613) This work by Jon Olav Hauglid splits the binary log code from the general/slow/error log code in order to improve modularity and maintainability. This work also improves the code documentation.

Replication

Non blocking show slave status (WL#6402) This work by Nuno Carvalho adds a non-blocking option to SHOW SLAVE STATUS. The new option makes SHOW SLAVE STATUS non-blocking when run in parallel with STOP SLAVE (the STOP SLAVE command may take a long time to complete when it is waiting for an SQL thread to finish applying large events).

Add idempotent mode to mysqlbinlog (WL#6403) This work by Rohit Kalhans provides an idempotent mode of operation for mysql server. In this mode the server will ignore errors while applying row based events from a binlog file. This mode is useful when a DBA wants to replay binlogs using mysqlbinlog, but against a MySQL server which may not contain all of the data, so suppressing duplicate-key and no-key-found errors can be useful.

Add rewrite-db option to mysqlbinlog for RBR (WL#6404) This work by Manish Kumar adds support for a rewrite-db option to mysqlbinlog, so that a RBR event can be applied to a different database/schema. I.E. replace “from_db” to ‘to_db’ when reading a ROW based event from the binlog.

MySQL Client

Log interactive commands to syslog (WL#6788) This work by Nirbhay Choubey introduces a new client option, --syslog, which enables/disables the logging of attributes like sudo_user (or user), mysql_user, connection_id, db_server, DB, and query entered in an interactive session. This has been requested by some users due to auditing compliance requirements, i.e. requirements to log all interactive commands entered at the mysql command-line prompt to syslog.

Parser refactoring

Use Bison ‘locations’ for accessing statement text elements (WL#5967) This work by Gleb Shchepa replaces a home grown mechanism with standard Bison locations.

Test suite improvements

One of the goals of 5.7 is to improve our automated tests.

Make the MTR binlog suite GTID_MODE agnostic (WL#6663) This work by Nuno Carvalho makes the MTR binlog suite GTID_MODE agnostic, so standard replication test suites can be run with --gtid-mode=ON.

Switch default engine to InnoDB in MTR (WL#6731) This work by Sayantan Dutta changes MySQL Test Run (MTR) such that it uses InnoDB, unless MyISAM is explicitly specified within the test.

Migrate parts of MTR test suite to run with InnoDB storage engine (WL#6734) This work by Viswanatham Gudipati migrates the partitioning (parts) test suite to run with InnoDB as the default storage engine.

Migrate sys_vars MTR test suite to run with the InnoDB storage engine (WL#6735) This work by Akhila Maddukuri migrates the sys_vars test suite to run with InnoDB as the default storage engine.

Deprecation & Removal

Remove support for .sym files (WL#6561) This work by Dmitry Lenev removes the old custom Windows symlink implementation from MySQL 5.7. Starting from Windows Vista/Server 2008, a native symlink mechanism is supported by Windows (see the mklink command). MySQL will now use the native Windows mechanism going forward.

Remove INSERT DELAYED (WL#6073) This work by Martin Hansson removes the support for INSERT DELAYED in 5.7 and onwards. INSERT DELAYED has a lot of limitations and issues attached to it, and with InnoDB as default engine it becomes even less relevant. We decided to keep the INSERT/REPLACE DELAYED syntax, but convert it to INSERT/REPLACE with a warning before execution.

Remove innodb_mirrored_log_groups (WL#6808) This work by Satya Bodapati removes the innodb_mirrored_log_groups option in 5.7. The allowed range of the parameter innodb_mirrored_log_groups was 1..10, but there has always been (at least since 3.23.53) a check within InnoDB startup that refuses any value except 1. This is not meaningful and thus we decided to remove it.