Appendix D. MySQL Release Notes

Important Change: Replication: The lettercasing used for displaying UUIDs in global transaction identifiers was inconsistent. Now, all GTID values use lowercase, including those shown in the Retrieved_Gtid_Set and Executed_Gtid_Set columns from the output of SHOW SLAVE STATUS. (Bug #15869441)

Replication: When GTIDs were enabled, the automatic dropping of a temporary table when a client disconnected did not always generate a GTID. Now each logged DROP TABLE statement, including any generated by the server, is guaranteed to have its own GTID. (Bug #15907504)

Replication: When a binary log is replayed on a server (for example, by executing a command like mysqlbinlog binlog.000001 | mysql), it sets a pseudo-slave mode on the client connection used, so that the server can read binlog and apply binary log events correctly. However, the pseudo-slave mode was not disabled after the binary log dump was read, which caused unexpected filtering rules to be applied to SQL statements subsequently executed on the same connection. (Bug #15891524)

Replication: Issuing START SLAVE concurrently with setting sql_slave_skip_counter or slave_net_timeout could cause a deadlock. (Bug #14236151)

Replication: When using statement-based replication, and where the master and the slave used table schemas having different AUTO_INCREMENT columns, inserts generating AUTO_INCREMENT values logged for a given table on the master could be applied to the wrong table on the slave. (Bug #12669186)

AES_DECRYPT() and AES_ENCRYPT() had memory leaks when MySQL was compiled using OpenSSL. (Bug #15909183)

Several OpenSSL-related Valgrind warnings were corrected. (Bug #15908967)

If the server shut down unexpectedly, the presence of an InnoDB table with 1018 columns (very close to the upper limit of 1020 columns) could cause an assertion error during server restart:

InnoDB: Failing assertion: table->n_def == table->n_cols - 3

(Bug #15834685)

MySQL could encounter an error during shutdown on Windows XP or earlier systems. This issue did not affect systems running Windows Vista or higher, which use atomic condition variables to represent Windows Events. (Bug #14822849)

Queries that used grouping failed when executed using a cursor if the optimizer processed the grouping using a temporary table. (Bug #14740889)

Autosizing of Performance Schema parameters could result in settings that caused excessive CPU use. (Bug #67736, Bug #15927744)

The --random-passwords option for mysql_install_db is now supported for MySQL install operations (not upgrades) using Solaris PKG packages.

Incompatible Change: Replication: A number of variable and other names relating to GTID-based replication have been changed, with a view to making these names more appropriate and meaningful. The old names are no longer supported.

The features so renamed are shown in the following list:

For more information, see Section 16.1.3, “Replication with Global Transaction Identifiers”, and Section 16.1.4.5, “Global Transaction ID Options and Variables”. (Bug #14775984)

Windows Vista, Windows Server 2008, and newer support native symlinking using the mklink command. This makes the MySQL Server implementation of database symbolic links using .sym files redundant, so that mechanism is now deprecated and will be removed in a future MySQL release. See Section 8.11.3.1.3, “Using Symbolic Links for Databases on Windows”.

Performance: InnoDB: The timing values for low-level InnoDB read operations were adjusted for better performance with fast storage devices, such as SSD. This enhancement primarily affects read operations for BLOB columns in compressed tables. (Bug #13702112, Bug #64258)

Incompatible Change: The THREAD_ID column in Performance Schema tables was widened from INT to BIGINT to accommodate 64-bit values.

If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change to the performance_schema database. (Bug #14664453)

Incompatible Change: Connection ID (thread ID) values greater than 32 bits can occur on some systems (such as busy or long-running 64-bit systems), causing these problems:

Connection IDs now are permitted to be 64-bit values when the server supports them (when built with 64-bit data types), which has these effects:

(Bug #19806, Bug #11745768, Bug #65715, Bug #14236124, Bug #44728, Bug #11753308)

Important Change: InnoDB: A DML statement using the index merge access method could lock many rows from the table, even when those rows were not part of the final result set. This fix reduces the excessive locking by releasing the locks of unmatched rows. This optimization affects only transactions with isolation level equal to or less strict than READ COMMITTED; it does not apply to transactions using REPEATABLE READ or SERIALIZABLE isolation level. (Bug #14226171)

Important Change: Replication: Because running the server with GTIDs enabled prevented changes to nontransactional tables, programs such as mysql_upgrade and mysql_install_db were unable to operate on system tables that used the MyISAM storage engine and therefore could not function correctly. Now, when running with --enforce-gtid-consistency (required whenever --gtid-mode=ON), the server allows single statements on nontransactional tables. (Bug #14722659)

Important Change: Replication: Formerly, the value of the Seconds_Behind_Master column in the output of SHOW SLAVE STATUS was always set to NULL whenever the SQL thread or the I/O thread was stopped. Now, this column is set to NULL only if the SQL thread is not running, or if the I/O thread is not running following a check to determine whether or not the SQL thread has processed all of the relay log. (If the SQL thread has finished processing and the I/O thread is running, Seconds_Behind_Master is 0.) (Bug #12946333)

Partitioning: InnoDB: Previously, when attempting to optimize one or more partitions of a partitioned table that used a storage engine that does not support partition-level OPTIMIZE, such as InnoDB, MySQL reported Table does not support optimize, doing recreate + analyze instead, then re-created the entire table, but did not actually analyze it. Now in such cases, the warning message is, Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. In addition, the entire table is analyzed after first being rebuilt. (Bug #11751825)

InnoDB: An online DDL operation for an InnoDB table incorrectly reported an empty value ('') instead of the correct key value when it reported a duplicate key error for a unique index using an index prefix. (Bug #14729221)

InnoDB: InnoDB tables with FULLTEXT indexes could allocate memory for thread handles that was never released, possibly leading to resource issues on Windows systems. (Bug #14759163)

InnoDB: During an online DDL operation that copies the table, the secondary index of the table could become corrupted. (Bug #14753701)

InnoDB: If an ALTER TABLE statement failed while attempting to create a FULLTEXT index for an InnoDB table, the server could halt with an assertion error while dropping the incomplete index. (Bug #14504174)

Replication: If a table to be replicated had a FULLTEXT index, this index was not ruled out when selecting the type of scan to be used in finding the next row, even though it cannot be used to find the correct one. The row applier subsequently tried unsuccessfully to employ an index scan, causing replication to fail. Now in such cases, indexes which do not provide for sequential access (such as FULLTEXT) are not considered when determining whether to use a table, index, or hash scan for this purpose. (Bug #14843764)

Replication: When using the GTID-aware master-slave protocol, the slave I/O thread used the wrong position. When using GTIDs, the position is not normally used, but as a special case, the position was used in addition to the GTID when the slave reconnected to the same master (even though this was not necessary). This problem is fixed by making the GTID-aware master-slave protocol not use positions at all any longer. (Bug #14828028)

Replication: Given a stored routine R in which the GTID_SUBTRACT() function was invoked: Once GTID_SUBTRACT() returned NULL when called inside R, it continued to return NULL every time it was called within R, for the remainder of the client session. (Bug #14838575)

Replication: MySQL Enterprise Backup, mysqldump, and mysqlhotcopy could not be used with a GTID-enabled MySQL Server, because they were unable to restore the server's GTID state and so could not restore from any point in the binary log other than the very beginning.

As part of the fix for this problem, the gtid_purged system variable (formerly named gtid_lost) is no longer read-only; now it is possible to add GTIDs to it when gtid_executed (formerly gtid_done) is empty. (Bug #14787808)

Replication: Restarting replication after the first binary log file was purged resulted in the error Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' This led GTID-based replication to fail. (Bug #14756691)

Creating an InnoDB table with a FULLTEXT index could encounter a serious error if the table name contained non-alphanumeric characters. (Bug #14835178)

Invalid memory reads could occur for queries that selected from a zero-length table name. (Bug #14780820)

With LOCK TABLES in effect, CREATE TABLE IF NOT EXISTS ... LIKE could raise an assertion. (Bug #14788976)

An assertion was raised if ALTER TABLE was used to rename a column to same name as an existing column while also reordering the renamed column using AFTER or FIRST. (Bug #14756089)

An assertion could be raised if semi-join materialization was used to evaluate a NOT IN subquery. (Bug #14751858)

For some continuation handler nestings, continuation could occur at the wrong location. (Bug #14724836)

SHOW PROCESSLIST output was not sorted in Id order. (Bug #14771006)

For UPDATE statements, EXPLAIN showed the total key length in the key_len column rather than the length of the used key parts. (Bug #14682438)

Starting the server with --bind-address and then setting host_cache_size to 0 could result in the server stopping for certain kinds of client connections. (Bug #14689561)

With index condition pushdown enabled, the optimizer could produce incorrect results for derived tables. (Bug #14640176)

The optimizer could incorrectly use a nonspatial index to optimize spatial operations, causing an assertion to be raised. (Bug #14600994)

CHECK TABLE and REPAIR TABLE could crash if a MyISAM table had a corrupt key (.MYI) file. Now the server produces an error. (Bug #13556441)

CHECK TABLE and REPAIR TABLE could crash if a MyISAM table had a corrupt key (.MYI) file. Now the server produces an error. (Bug #13556107, Bug #13556000)

For dumps of the mysql database, mysqldump skipped the event table unless the --events option was given. To skip this table if that is desired, use the --ignore-table option instead (Bug #55587, Bug #11762933)

mysqld_safe ignored the value of the UMASK environment variable, leading to behavior different from mysqld with respect to the access mode of created files. Now mysqld_safe (and mysqld_multi) attempt to approximate the same behavior as mysqld. (Bug #57406, Bug #11764559)

LAST_INSERT_ID(expr) did not work for expr values greater than the largest signed BIGINT value. (Bug #20964, Bug #11745891)

This release continues the process begun in MySQL 5.6.6 of making changes to the default values of server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are subject to revision in future releases as we gain feedback.

In some cases, a parameter has a different fixed default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a fixed value. For example, the setting for host_cache_size is its previous default of 128, adjusted up by an amount proportional to the value of max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.

The following table summarizes changes to defaults. For variables that are autosized, the main variable description provides additional detail about the sizing algorithm. See Section 5.1.4, “Server System Variables”, and Section 14.2.7, “InnoDB Startup Options and System Variables”. Any of these default settings can be overridden by specifying an explicit value at server startup.

On Unix platforms, mysql_install_db supports a new option, --random-passwords, that provides for more secure MySQL installation. Invoking mysql_install_db with --random-passwords causes it to perform the following actions in addition to its normal operation:

As a result of these actions, it is necessary after installation to start the server, connect as root using the password written to the .mysql.secret file, and assign a new root password. Until this is done, root cannot do anything else. This must be done for each root account you intend to use. To change the password, you can use the SET PASSWORD statement (for example, with the mysql client). You can also use mysqladmin or mysql_secure_installation.

New RPM install operations (not upgrades) invoke mysql_install_db with the --random-passwords option. As a consequence, RPM installs from this version onward will have their root accounts secured, and will have no anonymous-user accounts. (Install operations using RPMs for Unbreakable Linux Network are unaffected because they do not use mysql_install_db.)

For install operations using a binary .tar.gz distribution or a source distribution, you can invoke mysql_install_db with the --random-passwords option manually to make your MySQL installation more secure. This is recommended, particularly for sites with sensitive data.

On Unix platforms, mysql_install_db now creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started using mysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.

With one exception, the settings in the default option file are commented and have no effect. The exception is that the file changes the sql_mode system variable from its default of NO_ENGINE_SUBSTITUTION to also include STRICT_TRANS_TABLES. This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Section 5.1.7, “Server SQL Modes”.

The my-default.cnf template replaces the older sample option files (my-small.cnf, my-medium.cnf, and so forth), which are no longer distributed.

mysql_install_db is now a Perl script and can be used on any system with Perl installed. Previously, it was a shell script and available only on Unix platforms.

In addition, mysql_install_db is more strict about the --datadir option value. Only the last component of the path name is created if it does not exist; the parent directory must already exist or an error occurs. Previously, it created any nonexistent directories in the path name.

InnoDB: The InnoDB transportable tablespace feature was enhanced to allow ALTER TABLE ... IMPORT TABLESPACE to succeed in some cases where the corresponding .cfg file was not available. This enhancement allows recovery of data even in some cases where the system tablespace is corrupted or deleted. (Bug #14589582, Bug #66715)

The number of atomic operations performed by the Performance Schema was reduced. (Bug #14658739)

ALTER USER now can be used as a prepared statement. (Bug #66874, Bug #14646014)

On Unix systems, the mysql client logs executed statements to a history file when run in interactive mode (see Section 4.5.1.3, “mysql History File”). mysql now ignores for logging purposes statements that match any pattern in the “ignore” list. By default, the pattern list is "*IDENTIFIED*:*PASSWORD*", to ignore statements that refer to passwords. Pattern matching is not case sensitive. Within patterns, two characters are special:

To specify additional patterns, use the --histignore command option or set the MYSQL_HISTIGNORE environment variable. (If both are specified, the option value takes precedence.) The value should be a colon-separated list of one or more patterns, which are appended to the default pattern list.

Patterns specified on the command line might need to be quoted or escaped to prevent your command interpreter from treating them specially. For example, to suppress logging for UPDATE and DELETE statements in addition to statements that refer to passwords, invoke mysql like this:

shell> mysql --histignore="*UPDATE*:*:DELETE*"

(Bug #48287, Bug #11756377)

On Windows, many MySQL executables depend on the libeay32.dll and ssleay32.dll SSL libraries at runtime. To ensure that the proper versions of these libraries are found, the install process copies them into the same directory as the executables.

The SHOW AUTHORS and SHOW CONTRIBUTORS statements have been removed.

Important Change: Replication: When running the slave with the --slave-skip-errors option, successive skipped events (errors logged as warnings) were found to contain information from previous warnings, which caused an excessive amount of redundant information to be written to the error log. This problem could occur when using row-based or mixed-format binary logging.

The fix for this issue is to clear these warnings prior to processing the next skipped event. In addition, the skipped events are now handled in the same way regardless of the value of binlog_format, and a skipped error always causes a warning to be written to the error log, as long as the value of the log_warnings system variable is greater than 1. (Bug #12776842)

Important Change: The server system variables profiling, have_profiling, and profiling_history_size are now deprecated, and are subject to removal in a future release of the MySQL Server. (Bug #14658683)

InnoDB: When a CREATE INDEX operation failed for an InnoDB FULLTEXT index due to a duplicate key error, some allocated memory was not freed. (Bug #14759111)

InnoDB: An online DDL operation to create a unique index could fail to detect duplicate index values, when the duplicate values were caused by DML operations while the index was being created. (Bug #14733674)

InnoDB: When using the transportable tablespace feature, the ALTER TABLE ... IMPORT TABLESPACE statement could crash if the InnoDB table being flushed contained a FULLTEXT index. With this fix, the table data can be imported, although you must drop and re-create the FULLTEXT index after the import operation. (Bug #14712962, Bug #67081)

InnoDB: During a brief time window while creating an InnoDB unique index, MySQL could print a spurious warning message:

WARNING: CANNOT FIND INDEX ?index_name IN INNODB INDEX TRANSLATION TABLE

The cause was that MySQL started enforcing the uniqueness constraint before the existence of the index was fully registered. The fix suppresses the incorrect message during this final stage of index creation. (Bug #14735988)

InnoDB: An assertion failure occurred when a bogus duplicate key error was flagged during online ALTER TABLE. This issue only occurred for a table that lacked a primary key and any secondary indexes. This patch fixes the assertion failure, but not the bogus duplicate key error, which is reported as Bug#14723456. (Bug #14712710)

InnoDB: The InnoDB memcached plugin can now work with tables where the underlying character set is multi-byte. (Bug #14711015, Bug #67076)

InnoDB: An ALTER TABLE operation on an InnoDB table containing a FULLTEXT index could cause make the server halt with an assertion error. The fix causes all ALTER TABLE operations for such tables to use the table-copying behavior of the ALGORITHM=COPY clause. (Bug #14681198)

InnoDB: If an InnoDB table containing a FULLTEXT index was being modified by a TRUNCATE TABLE statement and on online DDL operation simultaneously, the server could end up with inconsistent internal locks or could crash. (Bug #14676329)

InnoDB: If the server crashed while executing TRUNCATE TABLE for an InnoDB table containing a FULLTEXT index, further errors could occur during crash recovery, preventing the server from restarting. (Bug #14676345)

InnoDB: If the MySQL server crashed while XA transactions were in PREPARED state, inconsistent data could be produced during crash recovery if the query cache was enabled. The fix allows MySQL to disable the query cache during crash recovery if required. (Bug #14658648)

InnoDB: With the innodb_file_per_table setting enabled, a DROP TABLE operation could cause a crash, due to a race condition that depended on the timing of pending I/O requests. (Bug #14594600, Bug #66718)

InnoDB: If an online DDL operation failed due to a duplicate key error, caused by DML changes being made concurrently to the table, the server could crash with an assertion error. (Bug #14591797)

InnoDB: If a FULLTEXT index was dropped from an InnoDB table, and the server crashed later for an unrelated reason, an additional error could occur while attempting to access nonexistent FULLTEXT data structures. (Bug #14586855)

InnoDB: MySQL could crash while creating an InnoDB table if the disk became full at a specific moment: after the .frm file was created but before the corresponding .ibd file was created. (Bug #14645935)

InnoDB: If the server crashed at the specific point when a change buffer entry was being merged into a buffer pool page, the transaction log and the change buffer were left in an inconsistent state. After a restart, MySQL could crash after reading the corresponding secondary index page. The problem was more likely to occur in MySQL 5.5 or later, where the original insert buffering mechanism was generalized to cover other operations. (Bug #14636528, Bug #66819, Bug #58571, Bug #61104, Bug #65443)

InnoDB: If a crash occurred during a CREATE TABLE operation, the InnoDB data dictionary could be left in an inconsistent state, causing a crash if the partially created table was accessed later. (Bug #14601290)

InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550)

InnoDB: A query against an InnoDB table with a FULLTEXT index could crash, if the AGAINST clause contained a character sequence that was encoded incorrectly for the character set of the table. (Bug #14588091)

InnoDB: The server could crash with a confusing message if it ran out of space for temporary files during index creation.

InnoDB: Assertion failure in thread thread_num in file mtr0mtr.cc line 306 
InnoDB: Failing assertion: mtr->state == 12231

(Bug #14586256)

InnoDB: An ALTER TABLE on an InnoDB table that dropped the primary key and then re-created it with columns in a different order could cause an error. The issue affected tables where the swapped columns referenced each other in a single-table foreign key relationship. The data dictionary could be left in an inconsistent state, where the table was listed in SHOW TABLES output but could not be queried or dropped. For example, if the table was declared with primary key columns (c1,c2) and a foreign key with c1 REFERENCES c2:

ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY (c2, c1); 
ERROR 1030 (HY000): Got error 38 from storage engine 

(Bug #14548753)

InnoDB: Table names containing non-ASCII characters were displayed incorrectly when the MYSQL.INNODB_TABLE_STATS.TABLE_NAME column was queried. (Bug #14404879)

InnoDB: A race condition could cause a crash during an online CREATE INDEX statement for an InnoDB table. This bug only affected very small tables. It required a DML operation to be in progress for the table, affecting the primary key columns, at the same time the CREATE INDEX statement was issued. (Bug #14117641)

InnoDB: If a transaction was started with a consistent snapshot, then new indexes were added to the table while the transaction was in progress, a subsequent UPDATE statement could incorrectly encounter the error:

HA_ERR_TABLE_DEF_CHANGED: insufficient history for index

This issue could cause an assertion error in debug builds. (Bug #14036214)

InnoDB: The server could crash with an assertion error during operations on tables with ROW_FORMAT=COMPRESSED. (Bug #14001972)

InnoDB: In rare circumstances, during operations on an InnoDB table containing foreign keys, pages in the buffer pool could be evicted but not written to disk, leading to data inconsistency. (Bug #13688491)

InnoDB: In rare circumstances, MySQL could apply InnoDB undo records out of order during a ROLLBACK of an operation that modified a BLOB column. This issue could cause an assertion error in debug builds:

!bpage->file_page_was_freed 

(Bug #13249921)

Partitioning: The server now skips pruning of tables (see Section 17.4, “Partition Pruning”) that use a storage engine which handles its own partitioning internally. The server now also explicitly rejects attempts to use explicit partitioning for such tables. (Bug #14672885)

Partitioning: When used with a table having multiple columns in its primary key, but partitioned by KEY using a column that was not part of the primary key as the partitioning column, a query using an aggregate function and DISTINCT such as SELECT SUM(DISTINCT pk_column_1) FROM table WHERE pk_column_2 = constant was not handled correctly. (Bug #14495351)

Replication: When using a multithreaded slave, if all worker threads were kept busy, it was possible for cleanup of an internal MTS circular buffer to fail, resulting in a full buffer and failure of the slave. (Bug #14710881)

Replication: When invoked while while gtid_mode was set to OFF, the SQL_THREAD_WAIT_AFTER_GTIDS() function waited indefinitely, unless a timeout was specified. In the latter case, the function could return incorrect values. Now, when gtid_mode is OFF, SQL_THREAD_WAIT_AFTER_GTIDS() always returns NULL, as expected. (Bug #14640065)

Replication: Partially-failed GRANT and REVOKE statements were not always handled the same way on the master and the slave. We now log an incident event whenever an error occurs, even if it is only a partial error, with a message stating that manual reconciliation is required. (Bug #14598585)

Replication: Executing FLUSH LOGS in parallel with COMMIT could cause the server to hang. (Bug #14640486)

Replication: Backtick (`) characters were not always handled correctly in internally generated SQL statements, which could sometimes lead to errors on the slave. (Bug #14548159)

Replication: There existed a gap in time between the appending of the current GTID to the server's list of logged GTIDs and the commit of the transaction by the storage engine. On slow platforms, or when using profiling, this could cause SELECT SQL_THREAD_WAIT_AFTER_GTIDS(gtid) to return before the data actually reached the database.

Now the current GTID is appended to the logged GTIDs following the commit, which removes this gap and so eliminates a possible source of inconsistency. (Bug #14116526)

Replication: The error shown when a relay log file was missing from the relay log index file informed the user only that the log file was not found, but did not specify the exact reason. Now in such cases, the error message returned is Could not find target log file mentioned in relay log info in the index file 'index_file_name' during relay log initialization. (Bug #11758505)

Replication: Following an insert into a nontransactional table that failed due to insufficient disk space, the server did not properly clean up all pending events, leading to an assert or possibly to other errors. (Bug #11750014)

A DELETE statement for an InnoDB table could write incorrect transaction metadata into a record, causing the server to halt with an error. To work around this issue, reduce the specified length of the primary key to less than 1K bytes. (Bug #14731482)

For an in-place ALTER TABLE operation on an InnoDB table that produced a duplicate-key error for NULL values, the error message displayed the column default value rather than NULL. (Bug #14723364)

mysql_secure_installation could not change the password for an account that had password_expired='Y' in the mysql.user table row for that account. (Bug #14726722)

In debug builds, the server could crash because db_suicide() failed to handle SIGABRT signals. (Bug #14649493)

With the optimizer tracing enabled, the INFORMATION_SCHEMA.OPTIMIZER_TRACE table can be queried to find tracing information about the last statements. However, for queries for which the results were retrieved from the query cache, this information was not available. (Bug #14665052)

Patches for materialized semi-joins caused failures of the query plan interface used by NDBCLUSTER. (Bug #14704659)

Queries that used a nested join with a subquery in the FROM clause and an ORDER BY ... DESC clause could return too few rows. (Bug #14678404)

EXPLAIN DELETE ... WHERE impossible_condition could function incorrectly when it was used in a stored routine. (Bug #14601802)

References: This bug was introduced by Bug #11752097.

An incomplete result could be stored in the query cache when a query failed with an error (providing that the query cache was enabled, and was set to a nonzero size). This fix ensures that it is no longer possible for queries that finish with an error to be cached. (Bug #14621700)

References: This bug was introduced by Bug #40264.

USE dbname could fail with Unknown database when dbname contained multiple backtick (`) characters. (Bug #14645196)

The configure.pl script that converts GNU configure options to CMake equivalents generated erroneous output for the --with-client-ldflags and --with-mysqld-ldflags options. It now ignores those options. (Bug #14593123)

Attempts to insert, update, delete from, or lock unknown Performance Schema tables failed with an ER_TABLEACCESS_DENIED_ERROR error rather than ER_NO_SUCH_TABLE. (Bug #14633008)

Outer joins could execute inefficiently and return incorrect results if joins were pushed down to the storage engine. (Bug #14644936)

Index condition pushdown in conjunction with descending index range scan could return incorrect results if there were multiple ranges in the range scan. (Bug #14604223)

The server could crash when registering tables in the query cache for queries that selected from views. (Bug #14619935)

Small values of max_sort_length could produce incorrect results for integer, decimal, floating-point, or temporal data types. Now max_sort_length is ignored for those data types. (Bug #14596888)

With semi-join and materialization optimizations enabled, a query that materialized a const table returned incorrect results when STRAIGHT_JOIN was added. (Bug #14609394)

A prepared statement that referenced views in an IN subquery could return different results for different executions. (Bug #14641759)

References: See also Bug #13773979.

In-source builds modified the source file sql/share/dictionary.txt. (Bug #14562699)

The server printed excessive Got error 159 when reading table messages to the error log when one transaction attempted to access a table that had been modified by another. (Bug #14579877)

Materialization of a subquery in the FROM clause could return the wrong number of rows if the subquery included a LIMIT clause. (Bug #14576727)

The optimizer could choose an incorrect execution plan for updates to InnoDB tables based on indexes that use column prefixes. (Bug #14578060)

A query with a subquery and ORDER BY and LIMIT clauses returned fewer rows than expected when executed using semi-join materialization. (Bug #14580874)

On Windows, mysql_plugin could not find my_print_defaults. (Bug #14471052)

A query with a subquery in the JOIN ... ON clause with an outer reference to a field that was out of scope could cause the server to crash. (Bug #14498914)

When used in GRANT statements, quoted user name or host name values containing leading or trailing spaces caused privileges to be assigned incorrectly until a FLUSH PRIVILEGES statement was issued.

Now, as a result of this fix, quoted name and host identifiers used in a GRANT statement are automatically trimmed of any leading and trailing spaces, before privileges are assigned. (Bug #14328259)

CREATE USER and DROP USER could fail to flush the privileges, requiring FLUSH PRIVILEGES to be used explicitly. (Bug #13864642)

On Mac OS X, the version_compile_machine system variable did not include the value 64 for server binaries compiled on a 64-bit system. (Bug #13859866)

Access to INFORMATION_SCHEMA tables through a view could leak memory. (Bug #13734987)

On Microsoft Windows with CMake 2.6, the build process would not stop if the create_initial_db step failed. (Bug #13713525)

The test in mysqld_safe for the presence of the --plugin_dir option and assignment of a default value to it were performed before the actual argument parsing took place. (Bug #13548161)

The number of connection errors from a given host as counted by the server was periodically reset, with the result that max_connect_errors was never reached and invalid hosts were never blocked from trying to connect. (Bug #11753779)

References: See also Bug #38247, Bug #43006, Bug #45584, Bug #45606.

The Range checked for each record optimization is now used for conditions with outer query references. (Bug #11750963)

A cached query result was not empty at the end of statement execution as expected. This could occur when executing queries (with the query cache enabled and set to a nonzero size) where the result was not sent to the client such as those executed by the Event Scheduler, or when executing stored routines containing queries while the server was running in bootstrap mode. (Bug #11755580, Bug #14609893)

Metadata locking resulted in excessive contention in read-only workloads involving InnoDB tables and a low number of connections.

Now the set of metadata locks can be partitioned into separate hashes to permit connections accessing different objects to use different locking hashes and reduce contention. The new metadata_locks_hash_instances system variable can be used to specify the number of hashes. (Bug #66473, Bug #14569140)

ST_Contains() and ST_Within() incorrectly reported that a polygon did not contain itself. ST_Equals() incorrectly returned 0 for polygons that differed only in shifted vertices. (Bug #64653, Bug #13864679)

ST_Difference() could incorrectly produce empty polygons in the result. (Bug #64649, Bug #13865773)

For some queries involving ORDER BY, the optimizer chose the wrong index for accessing the table. (Bug #45969, Bug #11754370, Bug #14338686)

On Windows, the Perl version of mysql_install_db created system tables in the mysql database that were not populated properly. (Bug #65584, Bug #14181049)

Random number generation during client authentication consumed excessive CPU. (Bug #66567, Bug #14555434)

In debug builds, vio_read() printed errno rather than socket_error to the debug trace. (Bug #28775, Bug #11746795)

Important Change: Partitioning: The maximum number of partitions for a user-partitioned table is increased from 1024 to 8192. (Bug #11755685)

InnoDB: The --innodb-read-only option lets you run a MySQL server in read-only mode. You can access InnoDB tables on read-only media such as a DVD or CD, or set up a data warehouse with multiple instances all sharing the same data directory. See Section 14.2.6.1, “Support for Read-Only Media” for usage details. (Bug #14143600)

InnoDB: You can now select the compression level for InnoDB compressed tables, from the familiar range of 0-9 used by zlib. The compression level is controlled by the innodb_compression_level configuration option, with a default value of 6:

You can also control whether compressed pages in the buffer pool are stored in the redo log when an update operation causes pages to be compressed again. This behavior is controlled by the innodb_log_compressed_pages configuration option. Turning off logging for compressed pages reduces the amount of redo data that is generated, possibly improving throughput. If the compressed page is required during crash recovery, it is compressed again at that time.

InnoDB: Each data block in an InnoDB compressed table contains a certain amount of empty space (padding) to allow DML operations to modify the row data without re-compressing the new values. Too much padding can increase the chance of a compression failure, requiring a page split, when the data does need to be re-compressed after extensive changes. The amount of padding can now be adjusted dynamically, so that DBAs can reduce the rate of compression failures without re-creating the entire table with new parameters, or re-creating the entire instance with a different page size. The associated new configuration options are innodb_compression_failure_threshold_pct, innodb_compression_pad_pct_max

InnoDB: New information_schema tables, innodb_cmp_per_index and innodb_cmp_per_index_reset, provide statistics on InnoDB tables that use compression. The statistics at the index level let DBAs measure whether the proportion of successful or failed compression operations is acceptable for a particular combination of table, index, page size, and workload. Typically, the compression failure rate should be less than 10%, particularly when using a compressed table to handle an OLTP-style workload with frequent INSERT, UPDATE, or DELETE operations.

Because gathering those statistics could be very time consuming and would hurt performance negatively, the new tables are enabled only when the new configuration option innodb_cmp_per_index_enabled is set to ON. (It is OFF by default.)

When MySQL is configured with -DWITH_SSL=system to build with OpenSSL, CMake now produces an error if OpenSSL is older than version 1.0.1 (Bug #14167227)

The default has changed from false to true for the --secure-auth option for mysql and the MYSQL_SECURE_AUTH option for the mysql_options() C API function. (Bug #13789417)

The server now issues a Note diagnostic if an index is created that duplicates an existing index. (Bug #37520, Bug #11748842)

The WITH_SSL option for CMake now accepts a path_name value that indicates the path name to the OpenSSL installation to use. This can be useful instead of a value of system when the CMake code detects an older or incorrect installed OpenSSL version. (Another permitted way to do the same thing is to set the CMAKE_PREFIX_PATH option to path_name.) (Bug #61619, Bug #12762891)

The mysql_clear_password cleartext client-side authentication plugin is intended for authentication schemes that require the server to receive the password as entered on the client side, without hashing. Because the password is sent in the clear, this plugin should be used within the context of a secure connection, such as an SSL connection, to avoid exposing the password over the network. To make inadvertent use of this plugin less likely, it is now required that clients explicitly enable it. This can be done several ways:

The unused multi_range_count system variable is now deprecated, and will be removed in a future release.

The following items are deprecated and will be removed in a future MySQL release. Where alternatives are shown, applications should be updated to use them.

Performance: InnoDB: The OPTIMIZE TABLE statement now updates the InnoDB persistent statistics for that table when appropriate. (Bug #14238097)

Performance: InnoDB: This fix removes redundant checksum validation on InnoDB pages. The checksum was being verified both when a compressed page was read from disk and when it was uncompressed. Now the verification is only performed when the page is read from disk. (Bug #14212892, Bug #64170)

Performance: Replication: On Solaris systems, enabling slave_parallel_workers could lead to a slowdown in event executions on the slave. (Bug #14641110)

References: See also Bug #13897025.

Performance: Replication: When slave_parallel_workers was enabled, an internal multiplier representing the number of events above a certain overrun level in the worker queue was never reset to zero, even when the excess had been taken care of; this caused the multiplier to grow without interruption over time, leading to a slowdown in event executions on the slave. (Bug #13897025)

Performance: View definitions (in .frm files) were not cached and thus every access to a view involved a file read. Definitions now are cached for better performance. (Bug #13819275)

Performance: Certain instances of subquery materialization could lead to poor performance. Subquery materialization now is chosen only if it is less costly than the EXISTS transformation. (See Section 8.13.15.2, “Optimizing Subqueries with Subquery Materialization”, and Section 8.13.15.4, “Optimizing Subqueries with EXISTS Strategy”.) (Bug #13111584)

Important Change: Replication: When issued during an ongoing transaction, any of the following statements that are used to control MySQL Replication now cause the transaction to be committed:

For more information, see Section 13.3.3, “Statements That Cause an Implicit Commit”. (Bug #13858841)

References: See also Bug #14298750, Bug #13627921.

Important Change: Formerly, the ExtractValue() and UpdateXML() functions supported a maximum length of 127 characters for XPath expressions supplied to them as arguments. This limitation has now been removed. (Bug #13007062, Bug #62429)

Partitioning: InnoDB: A SELECT from a partitioned InnoDB table having no primary key sometimes failed to return any rows where a nonempty result was expected. In such cases the server also returned the error Can't find record in table_name or Incorrect key file for table table_name. (Bug #13947868)

InnoDB: Inserting data of varying record lengths into an InnoDB table that used compression could cause the server to halt with an error. (Bug #14554000, Bug #13523839, Bug #63815, Bug #12845774, Bug #61456, Bug #12595091, Bug #61208)

InnoDB: On Windows systems, a file access error due to an incorrect value for MYSQL_DATADIR could cause an InnoDB assertion error. The error could persist after restarting MySQL. (Bug #14558324)

InnoDB: The default for the innodb_checksum_algorithm, which was briefly changed to crc32 during the MySQL 5.6 development cycle, was switched back to innodb for improved compatibility of InnoDB data files during a downgrade to an earlier MySQL version. (Bug #14525151)

InnoDB: In an ALTER TABLE that rebuilds a table, and in particular, ADD COLUMN, DROP COLUMN, there were some assertion failures related to FULLTEXT indexes, particularly for tables containing more than one FULLTEXT index. The fix makes the ALTER TABLE correctly use or not use online DDL depending on the presence of FULLTEXT indexes. If a table had a FULLTEXT index that was dropped, any restrictions on online DDL for that table remain, due to the hidden FTS_DOC_ID column. (Bug #14488218)

InnoDB: The default value for the innodb_io_capacity_max configuration option has been updated to use a formula given by the expression:

innodb_io_capacity_max = max(2000, innodb_io_capacity * 2)

This default takes effect at server startup, and only applies if you do not set any value for innodb_io_capacity at server startup. (Bug #14469086)

InnoDB: The syntax ALTER TABLE ... DROP FOREIGN KEY ... ALGORITHM=COPY incorrectly considered the names of foreign keys to be case-sensitive. (Bug #14394071)

InnoDB: Under heavy load of concurrent DML and queries, an InnoDB table with a unique index could return non-existent duplicate rows to a query. (Bug #14399148, Bug #66134)

InnoDB: When an error (such as a duplicate key error) was detected during an online DDL operation, while applying changes made to the table while an index was being built, MySQL could encounter an assertion error if the same ALTER TABLE statement also contained any DROP INDEX clauses. (Bug #14392805)

InnoDB: A heavy query workload against an InnoDB table with a FULLTEXT index could cause a crash. The issue only occurred with some number of queries per second and some number of concurrent connections. (Bug #14347352)

InnoDB: When an InnoDB table had a system-chosen primary key, based on a unique index on non-nullable columns, an error was issued if one of the primary key columns was altered to be nullable. The message was:

Warning	1082	InnoDB: Table table_name has a primary key in InnoDB data 
dictionary, but not in MySQL!

This issue only affected ALTER TABLE statements using the online DDL mechanism, that is, with the ALGORITHM=INPLACE clause specified or implied. (Bug #14353985)

InnoDB: If an online CREATE INDEX operation failed, there was a brief period of time when concurrent DML operations could fail because the table was considered to be in an error state. (Bug #14341099)

InnoDB: The mysql_install_db command could crash with an assertion error:

InnoDB: Assertion failure in thread thread_num in file trx0rseg.cc line 326 

The size of the InnoDB system tablespace was being capped at 10MB, but during the 5.6 development cycle, the minimum size of a system tablespace became slightly larger than 10MB. (Bug #14315223)

InnoDB: The server could crash if a read-only transaction was killed in a session that contained an InnoDB temporary table. (Bug #14213784)

InnoDB: When more than one InnoDB temporary table was created and accessed within the same transaction, queries on those temporary tables could fail with an ER_TABLE_DEF_CHANGED error. (Bug #14234581)

InnoDB: This fix addresses several issues regarding AUTO_INCREMENT columns when adding a column using online DDL (that is, with ALGORITHM=INPLACE). Now the AUTO_INCREMENT_OFFSET value is used properly, the calculation for the next value is corrected, FLOAT, DOUBLE, and unsigned INTEGER auto-increment values are handled correctly, and overflow conditions are detected. (Bug #14219624)

InnoDB: A SHOW ENGINE...STATUS command could crash if an XA transaction was created using the statement START TRANSACTION READ ONLY. (Bug #14218867)

InnoDB: This fix prevents online DDL operations from conflicting with foreign key operations happening simultaneously on the same table. Updates or deletes based on CASCADE or SET NULL clauses in the foreign key definition are blocked while the online DDL is in progress, because the information needed in case of a ROLLBACK would not be available after the ALTER TABLE statement completes. (Bug #14219233)

InnoDB: A race condition could cause assertion errors during a DROP TABLE statement for an InnoDB table. Some internal InnoDB functions did not correctly determine if a tablespace was missing; other functions did not handle the error code correctly if a tablespace was missing. (Bug #14251529)

InnoDB: With the MySQL 5.6 online DDL feature, an ALTER TABLE statement to add a primary key to an InnoDB table could succeed, even though the primary key columns contained duplicate values. (Bug #14219515)

InnoDB: The server could crash with a combination of a transaction with SERIALIZABLE isolation level, FLUSH TABLES ... WITH READ LOCK, and a subsequent query. The error message was:

InnoDB: Failing assertion: prebuilt->stored_select_lock_type != LOCK_NONE_UNSET 

(Bug #14222066)

InnoDB: An online DDL operations to add a foreign key could incorrectly leave some memory allocated if the DDL encountered an error. (Bug #14156259)

InnoDB: An INSERT into a table after a failed online DDL operation could cause an erroneous assertion error:

InnoDB: Failing assertion: prebuilt->trx_id == 0 || prebuilt->trx_id <= 
last_index->trx_id 

(Bug #14176821)

InnoDB: The server could hang at startup, during crash recovery, if the rollback of previously active transactions conflicted with the dropping of temporary tables. With this fix, persistent statistics do not apply to InnoDB temporary tables. (Bug #14175080)

InnoDB: The configuration option innodb_max_io_capacity was renamed to innodb_io_capacity_max, to emphasize its relationship to the existing innodb_io_capacity option. (Bug #14175020)

InnoDB: The server could crash with a signal 8 (division by zero error) due to a race condition while computing index statistics. (Bug #14150372)

InnoDB: Deleting from an InnoDB table containing a prefix index, and subsequently dropping the index, could cause a crash with an assertion error. (Bug #13807811)

InnoDB: The value of the NUMBER_PAGES_CREATED and NUMBER_PAGES_WRITTEN columns of the INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table were set to incorrect values, and the NUMBER_PAGES_GET column was not being set at all. (Bug #13639187)

InnoDB: The server could crash when updating very large BLOB values, typically 16MB or more. (Bug #13450566)

InnoDB: When a SELECT ... FOR UPDATE, UPDATE, or other SQL statement scanned rows in an InnoDB table using a < or <= operator in a WHERE clause, the next row after the affected range could also be locked. This issue could cause a lock wait timeout for a row that was not expected to be locked. The issue occurred under various isolation levels, such as READ COMMITTED and REPEATABLE READ. (Bug #11765218)

InnoDB: Various inconsistent behaviors, including tables becoming inaccessible, were cleaned up for ALTER TABLE statements involving InnoDB tables involved in foreign key relationships. (Bug #11744929, Bug #5670)

Partitioning: For tables using PARTITION BY HASH or PARTITION BY KEY, when the partition pruning mechanism encountered a multi-range list or inequality using a column from the partitioning key, it continued with the next partitioning column and tried to use it for pruning, even if the previous column could not be used. This caused partitions which possibly matched one or more of the previous partitioning columns to be pruned away, leaving partitions that matched only the last column of the partitioning key.

This issue was triggered when both of the following conditions were met:

An example of a statement creating a partitioned table and a query against this for which the issue described above occurred is shown here:

CREATE TABLE t1 (
  c1 INT,
  c2 INT,
  PRIMARY KEY(c2, c1)
) PARTITION BY KEY()  # Use primary key as partitioning key 
  PARTITIONS 2;

SELECT * FROM t1 WHERE c2 = 2 AND c1 <> 2;

This issue is resolved by ensuring that partition pruning skips any remaining partitioning key columns once a partition key column that cannot be used in pruning is encountered. (Bug #14342883)

Partitioning: The buffer for the row currently read from each partition used for sorted reads was allocated on open and freed only when the partitioning handler was closed or destroyed. For SELECT statements on tables with many partitions and large rows, this could cause the server to use excessive amounts of memory.

This issue has been addressed by allocating buffers for reads from partitioned tables only when they are needed and freeing them immediately once they are no longer needed. As part of this fix, memory is now allocated for reading from rows only in partitions that have not been pruned (see Section 17.4, “Partition Pruning”). (Bug #13025132)

References: See also Bug #11764622, Bug #14537277.

Replication: Updates writing user variables whose values were never set on a slave while using --replicate-ignore-table could cause the slave to fail. (Bug #14597605)

References: This bug was introduced by Bug #14275000.

Replication: When the number of multithreaded slave workers (as determined by setting the slave_parallel_workers server system variable) was changed when using relay_log_info_repository=TABLE, the mysql.slave_worker_info table did not reflect the change. (Bug #14550905)

References: See also Bug #13804728, Bug #14550945, Bug #14578740.

Replication: With relay_log_info_repository=FILE and slave_parallel_workers greater than 0, changing the relay log info repository type to TABLE and restarting the slave mysqld caused a subsequent START SLAVE statement to crash the slave. (Bug #14550945)

References: See also Bug #13804728, Bug #14550905, Bug #14578740.

Replication: A manually created file named slave_worker_info in the MySQL Server's data directory could be mistaken for the actual relay log info file. In addition, when the number of workers (slave_parallel_workers server system variable) was decreased, the corresponding info files were not removed as expected. (Bug #14578740)

References: See also Bug #13804728, Bug #14550905, Bug #14550945.

Replication: Executing the SQL_THREAD_WAIT_AFTER_GTIDS() function without binary logging enabled could cause the server to crash. (Bug #14457883)

Replication: Using COM_BINLOG_DUMP_GTID with incorrect data could cause the server to crash. (Bug #14509140)

Replication: An internal routine in the MySQL Replication code removed elements from a hash used to store a mapping between databases and worker threads at the same time that the hash was being iterated over. This could cause an unintended reordering of the has elements and thus possibly to incorrect results from routines using this hash. (Bug #14381701)

References: See also Bug #13864642.

Replication: The names of the binary log and relay log Performance Schema mutexes were mistakenly changed to names that differed from the MySQL 5.5 names. The names have been reverted to those used in MySQL 5.5. (Bug #14366314)

Replication: When setting up replication between a master and a slave which was using --master-info-repository=TABLE, the mysql.slave_master_info table was not updated the first time that START SLAVE was issued. (Bug #14298750)

References: See also Bug #13858841.

Replication: The --disable-gtid-unsafe-statements option caused any nontransactional DML statement involving temporary tables to be rejected with an error even with binlog_format set explicitly to ROW, in spite of the fact that they are not written to the binary log in this case. Now, such statements are allowed when using row-based logging, as long as any nontransactional tables affected by the statements are also temporary tables. (Bug #14272627)

Replication: When using multithreaded slaves, --replicate-rewrite-db rules were not honored while assigning databases to slave worker threads, which could cause statements to be executed out of order when this option was used. This could result in a slave that was inconsistent with the master. (Bug #14232958)

Replication: mysql_upgrade failed when the server was running with gtid_mode=ON and --disable-gtid-unsafe-statements because the MySQL system tables are stored using MyISAM. This problem is fixed by changing the default logging behavior for mysql_upgrade; logging is now disabled by default. (Actions taken by mysql_upgrade depend on the server version, and thus should not be replicated to slaves.) To enable logging, you can execute mysql_upgrade using the --write-binlog option. (Bug #14221043, Bug #13833710)

Replication: The initialization and usage of a number of internal programming objects relating to GTIDs did not work properly with PERFORMANCE_SCHEMA. (Bug #14152637)

Replication: The scheduler for multi-threaded slaves did not take into account databases implicitly involved in operations through foreign key dependencies, which could lead to a temporary loss of consistency on the slave. To avoid this problem, replication events on the master that invoke foreign key relationships between table is different databases are now marked in such a way that they can be scheduled sequentially to avoid race conditions and thereby inconsistency. However, this can adversely affect performance. (Bug #14092635)

Replication: On 64-bit Windows platforms, values greater than 4G for the max_binlog_cache_size and max_binlog_stmt_cache_size system variables were truncated to 4G. This caused LOAD DATA INFILE to fail when trying to load a file larger than 4G in size, even when max_binlog_cache_size was set to a value greater than this. (Bug #13961678)

Replication: When using a multithreaded slave, the repository type employed for the relay log info log was not always used automatically for worker repositories as expected. (Bug #13804728)

References: See also Bug #14550905, Bug #14550945, Bug #14578740.

Replication: It was possible for the multithreaded slave coordinator to leak memory when the slave was stopped while waiting for the next successful job to be added to the worker queue. (Bug #13635612)

Replication: The Master_id column of the mysql.slave_master_info and mysql.slave_relay_log_info tables showed the slave's server ID instead of the master's server ID. (Bug #12344346)

Replication: Statements such as UPDATE ... WHERE primary_key_column = constant LIMIT 1 are flagged as unsafe for statement-based logging, despite the fact that such statements are actually safe. In cases where a great many such statements were run, this could lead to disk space becoming exhausted do to the number of such false warnings being logged. To prevent this from happening, a warning suppression mechanism is introduced. This warning suppression acts as follows: Whenever the 50 most recent ER_BINLOG_UNSAFE_STATEMENT warnings have been generated more than 50 times in any 50-second period, warning suppression is enabled. When activated, this causes such warnings not to be written to the error log; instead, for each 50 warnings of this type, a note is written to the error log stating The last warning was repeated N times in last S seconds. This continues as long as the 50 most recent such warnings were issued in 50 seconds or less; once the number of warnings has decreased below this threshold, the warnings are once again logged normally.

The fix for this issue does not affect how these warnings are reported to MySQL clients; a warning is still sent to the client for each statement that generates the warning. This fix also does not make any changes in how the safety of any statement for statement-based logging is determined. (Bug #11759333, Bug #51638)

References: See also Bug #11751521, Bug #42415.

In-place ALTER TABLE operations for InnoDB tables could raise an assertion attempting to acquire a lock. (Bug #14516798)

ALTER TABLE ... DROP FOREIGN KEY that did not name the foreign key to be dropped caused a server crash. Now the foreign key name is required. (Bug #14530380)

In mysql_com.h, the CLIENT_CONNECT_ATTRS and CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA symbols incorrectly were defined as the same value. (Bug #14482472)

With a password policy of STRONG and a password of 100 characters or more, VALIDATE_PASSWORD_STRENGTH() could cause a server crash. (Bug #14458293)

PASSWORD(NULL) and OLD_PASSWORD(NULL) could cause a server crash. (Bug #14458217)

Polygons with holes could cause a server crash for spatial operations. (Bug #14497827)

The Threads_running status variable was not updated properly. (Bug #14471011)

mysql_secure_installation did not work if old_passwords was set to 2 (use the sha256_password authentication plugin). (Bug #14506073)

GROUP_CONCAT() with DISTINCT or ORDER BY on GEOMETRY values caused a server crash. (Bug #14468106)

For complex conditions, the optimizer could produce an incorrect range construction and return incorrect query results. (Bug #14497598)

Item_cache_str::save_in_field() dereferenced a null pointer if the cached value was NULL. (Bug #14501403)

The optimizer could raise an assertion when grouping and sorting in descending order on an indexed column. (Bug #14498999)

A query with GROUP BY ... WITH ROLLUP comparing a grouping column using the IN operator caused an assertion to be raised. (Bug #14500792)

In debug builds, with semi-join enabled, GROUP BY ... WITH ROLLUP that did not use a temporary table could cause a server crash. (Bug #14499409)

An assertion was raised when using the join cache for a query that contained an IN subquery query with a subquery that is expected to return a single row but returned more than one. (Bug #14499331)

Index condition pushdown in conjunction with descending index range scan caused a performance regression. (Bug #14503142)

The explicit_defaults_for_timestamp system variable was not visible (for example, with SHOW VARIABLES), so it was not possible to make runtime decisions based on its value. (Bug #14409088)

An ALTER TABLE for an InnoDB table that attempted to add an index and also change the nullability of a column participating in that index raised an assertion. (Bug #14404635)

The --server-public-key option for mysql and mysqltest has been renamed to --server-public-key-path to reflect that it refers to a file and for consistency with related server-side variable naming. Also, this option now is available only if MySQL was built with OpenSSL (not yaSSL) because yaSSL does not support the necessary RSA encryption. (Bug #14348721)

The result set could contain extra rows for queries on MyISAM tables that used the SQL_BUFFER_RESULT modifier and a subquery. (Bug #14348858)

For debug builds, if one session used a DDL statement to alter an InnoDB table, another session could raise an assertion failure if it had a pre-alter consistent snapshot of the table. (Bug #14365043)

The Performance Schema used listed the nanosecond timer by default for stages and statements in the setup_timers table. But if this timer was not available on a given platform (such as Windows), timing for stages and statements failed to work. Now the idle, stage, and statement timers used the preferred timers if they are available, but alternate timers if not. (Bug #14298586)

The Performance Schema generated different digests for a statement before and after selecting a database. (Bug #14256311)

The RPM spec file now also runs the test suite on the new binaries, before packaging them. (Bug #14318456)

Inside a stored program, references to stored program variables in XML functions such as ExtractValue() failed after the first execution of the stored program. (Bug #14317442)

A LooseScan semi-join could return duplicate rows from the outer table. (Bug #14271594)

Some queries for which the optimizer used index condition pushdown in conjunction with ref access could be very slow if the index was read in descending order. (Bug #14287654, Bug #14503142)

Queries executed using MaterializeScan semi-join strategy and a materialized subquery could return too many rows. (Bug #14272788)

ALTER SERVER, CREATE SERVER, and DROP SERVER with an empty server name caused a server crash. (Bug #14220942)

The server did not build with gcc 4.7. (Bug #14238406)

If a call to socket() failed, the Performance Schema created instrumentation for it anyway. (Bug #14209598)

REQUIRE ISSUER clauses for GRANT statements were not rewritten properly for logging and caused a server crash. (Bug #14211069)

For queries that used range access, the optimizer could read uninitialized data, resulting in Valgrind warnings. (Bug #14200538)

The Performance Schema digest-generation code could fail with a race condition. (Bug #14250296)

With semi-join optimization enabled, subqueries in the WITH CHECK OPTION clause of view definitions were evaluated incorrectly. (Bug #14230177)

WEIGHT_STRING() could crash if given a bad flags argument. (Bug #14211236)

An optimizer trace could crash attempting to print freed subquery items. (Bug #14238404)

mysql_upgrade did not set the STATS_PERSISTENT=0 table option for InnoDB tables in the mysql database. (Bug #14195056)

ALTER TABLE with DISCARD TABLESPACE or IMPORT TABLESPACE did not acquire a sufficiently strong metadata lock to prevent a concurrent ALTER TABLE statement with ADD or DROP from modifying the tablespace. This could result in warnings or raise an assertion. (Bug #14213236)

Some queries with a HAVING clause with a function that referred to a function in the WHERE list with a subquery as parameter caused an assertion to be raised. (Bug #14209318)

String allocation could cause Valgrind warnings. (Bug #14201818)

For JSON-format EXPLAIN statements, derived tables were not handled properly and caused a server crash. (Bug #14167499)

Incorrect internal conversion of string-format dates could cause a server crash. (Bug #14167911)

In debug builds, comparisons for strings that had the ucs2_unicode_520_ci collation could raise an assertion. (Bug #14161973)

Join processing could attempt to clean up a temporary table that had not been instantiated, causing a server crash. (Bug #14168270)

In debug builds, the optimizer raised an unnecessary (too strict) assertion about MyISAM key lengths. (Bug #14179461)

In-place ALTER TABLE did not work for a table with a GEOMETRY column, even if the alteration did not involve that column. (Bug #14140927)

Negative values could be erroneously reported for some columns in the buffer_pool_pages_in_flush row in the information_schema.innodb_metrics table. (Bug #14090287)

Improper error handling for CREATE SERVER, DROP SERVER, and ALTER SERVER could raise an assertion. (Bug #14061851)

RELEASE SAVEPOINT did not have sufficient checks for the XA transaction state to prevent a savepoint from being released while the transaction was in a prepared state. (Bug #14062726)

The libmysqlclient_r client library exported symbols from yaSSL that conflict with OpenSSL. If a program linked against that library and libcurl, it could crash with a segmentation fault. (Bug #14068244)

The FirstMatch strategy for semi-joins produced incorrect results for some queries with multiple inner tables. (Bug #14081638)

With materialization and semi-joins enabled, some queries with an OR condition could produce incorrect results. (Bug #14075016)

In-place ALTER TABLE did not handle autopartitioning storage engines such as NDB. (Bug #14063233)

JSON-format EXPLAIN statements could raise an assertion or cause the server to hang for statements with an impossible-WHERE clause and subqueries in ORDER BY or GROUP BY clauses. (Bug #14084642)

For nonexistent files, the Performance Schema file I/O instrumentation sometimes did extra work or was subject to instrumentation leaks. (Bug #14113704)

Within a trigger, references to a temporary table used during the query execution process could end up pointing to nonexisting fields on subsequent executions, causing a server crash. (Bug #14105951)

Small sort_buffer_size values could result in a server crash. (Bug #14111180)

Improper initialization by spatial functions could cause a server crash the first time they were invoked following server startup. (Bug #14015762)

Polygon sorting by spatial functions could be done incorrectly and cause a server crash. (Bug #13938850)

For JSON-format EXPLAIN statements, improper handling of subqueries could cause an assertion to be raised. (Bug #13956275)

SELECT on a partitioned table that used a join buffer could cause a server crash. (Bug #13949549)

The argument for LIMIT must be an integer, but if the argument was given by a placeholder in a prepared statement, the server did not reject noninteger values such as '5'. (Bug #13868860)

For DELETE statements, WHERE clause row retrieval that should access only the index tree could raise an assertion. (Bug #13919180)

Some arguments could cause ST_Buffer() to crash. (Bug #13832749, Bug #13833019)

Queries that used the ST_Contains and Within() functions yielded incorrect results when argument columns had a spatial index. (Bug #13813064)

CHECK TABLE and REPAIR TABLE could crash if a key definition differed in the .frm and .MYI files of a MyISAM table. Now the server produces an error. (Bug #13555854)

The optimizer used a full index scan for cases for which a loose index scan was preferable. (Bug #13464493)

References: This bug is a regression of Bug #12540545.

COUNT(DISTINCT(SELECT 1)) could be evaluated incorrectly if the optimizer used a loose index scan. (Bug #13444084)

A query for a FEDERATED table could return incorrect results when the underlying table had a compound index on two columns and the query included an AND condition on the columns. (Bug #12876932)

mysqlhotcopy failed for databases containing views. (Bug #62472, Bug #13006947, Bug #12992993)

“Illegal mix of collation” errors were returned for some operations between strings that should have been legal. (Bug #64555, Bug #13812875)

mysqldump could dump views and the tables on which they depend in such an order that errors occurred when the dump file was reloaded. (Bug #44939, Bug #11753490)

The ST_Contains() and Within() functions yielded an incorrect result when used on a column with a SPATIAL index. (Bug #65348, Bug #14096685)

Adding a LIMIT clause to a query containing GROUP BY and ORDER BY could cause the optimizer to choose an incorrect index for processing the query, and return more rows than required. (Bug #54599, Bug #11762052)

If the server was started with secure_auth disabled, it did not produce a warning that this is a deprecated setting. (Bug #65462, Bug #14136937)

The GeomFromWKB() function did not return NULL if the SRID argument was NULL, and non-NULL SRID values were not included in the converted result. (Bug #65094, Bug #13998446)

With statement-based binary logging, stored routines that accessed but did not modify tables took too strong a lock for the tables, unnecessarily blocking other statements that also accessed those tables. (Bug #62540, Bug #13036505)

ALTER TABLE operations that changed a column definition could cause a loss of referential integrity for columns in a foreign key. (Bug #46599, Bug #11754911)

For some queries, the optimizer used index_merge access method when this was more work than ref access. (Bug #65274, Bug #14120360)

In prepared statements, MYSQL_TYPE_DATE parameters when converted to an integer were handled as MYSQL_TYPE_DATETIME values and the conversion produced incorrect results. (Bug #64667, Bug #13904869)

mysqlbinlog did not accept input on the standard input when the standard input was a pipe. (Bug #49336, Bug #11757312)

The argument to the --ssl-key option was not verified to exist and be a valid key. The resulting connection used SSL, but the key was not used. (Bug #62743, Bug #13115401)

In-place ALTER TABLE incorrectly handled indexes using key prefixes by using a copy algorithm. (Bug #65865, Bug #14304973)

Starting the server with --bind-address=* is supposed to cause the the server to accept TCP/IP connections on all server host IPv6 and IPv4 interfaces if the server host supports IPv6, or TCP/IP connections on all IPv4 addresses otherwise. But the server sometimes did not correctly detect when IPv6 was not supported, and failed to start. (Bug #66303, Bug #14483430)

Internal temporary MyISAM tables were unnecessarily registered in an open-table list protected by a global mutex, causing excessive mutex contention. (Bug #65077, Bug #14000697)

Queries with ALL over a UNION could return an incorrect result if the UNION result contained NULL. (Bug #65902, Bug #14329235)

There was a performance regression for queries that used GROUP BY and COUNT(DISTINCT). (Bug #49111, Bug #11757108)

In debug builds, an InnoDB assertion was overly aggressive about prohibiting an open range. (Bug #66513, Bug #14547952)

The ALTER USER statement cleared the user password in the mysql.user table. It no longer does this.

Performance: The server now implements group commit for the binary log: Multiple commits are grouped in memory, then written and flushed to disk as a group rather than individually. This reduces the number of writes and flushes, improving performance of binary logging. Group commit works for all storage engines. InnoDB implements some optimizations to take advantage of group commit capability.

These system variables were added in conjunction with group commit:

This release of MySQL implements changes to the default values of several server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are subject to revision in future releases as we gain feedback.

In some cases, a parameter has a different fixed default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a fixed value. For example, the setting for back_log is its previous default of 50, adjusted up by an amount proportional to the value of max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.

The following table summarizes changes to defaults. For variables that are autosized, the main variable description provides additional detail about the sizing algorithm. See Section 5.1.4, “Server System Variables”, and Section 14.2.7, “InnoDB Startup Options and System Variables”. Any of these default settings can be overridden by specifying an explicit value at server startup.

With regard to compatibility with previous releases, the most important changes are:

Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:

[mysqld]
innodb_file_per_table=0
innodb_checksum_algorithm=INNODB
binlog_checksum=NONE

Those settings preserve compatibility as follows:

The Performance Schema is now enabled by default (the performance_schema system variable is enabled by default). To disable it, set performance_schema=off at server startup.

In addition, the Performance Schema now automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. To see which parameters are sized under this policy, use mysqld --verbose --help and look for those with a default value of –1, or see Section 20.11, “Performance Schema System Variables”.

For each autosized parameter that is not set at server startup (or is set to –1), the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:

max_connections
open_files_limit
table_definition_cache
table_open_cache

To override autosizing for a given parameter, set it a value other than –1 at startup. In this case, the Performance Schema assigns it the specified value.

At runtime, SHOW VARIABLES displays the actual values that autosized parameters were set to.

If the Performance Schema is disabled, its autosized parameters remain set to –1 and SHOW VARIABLES displays –1.

These security improvements were implemented:

(Bug #65461, Bug #14136939)

Performance: InnoDB: Many DDL operations on InnoDB tables can now be performed online, without making the tables unavailable for queries. Some operations, such as creating or dropping indexes, even allow DML statements (INSERT, UPDATE, DELETE) on the table while the operation is in progress. A single online DDL operation can also take the place of a sequence of statements, such as several DROP INDEX statements, ALTER TABLE ... ADD COLUMN, and then several CREATE INDEX statements. See Section 14.2.2.6, “Online DDL for InnoDB Tables” for full details. (Bug #58368, Bug #11765404, Bug #11872643, Bug #12325508, Bug #11765266, Bug #60689)

Performance: InnoDB: The MySQL server now includes the widely used memcached in-memory caching system, and a plugin that allows fast NoSQL-style access to InnoDB tables through the memcached protocol. This access method avoids the overhead of SQL parsing and constructing a query optimization plan. You can store the underlying data in a single InnoDB table, or spread it across multiple tables. You can read and write data through both memcached and SQL. For example, you can do fast single-key lookups through memcached get calls, and do statistical reports across all the data through SQL.

Several configuration options let you fine-tune this system, in particular to balance raw performance against durability and consistency of data. The main new configuration options are daemon_memcached_option, daemon_memcached_r_batch_size, daemon_memcached_w_batch_size, innodb_api_trx_level, innodb_api_enable_mdl, and innodb_api_enable_binlog.

See Section 14.2.10, “InnoDB Integration with memcached” for full details.

Performance: InnoDB: The persistent statistics feature for InnoDB tables is now enabled by default, and can be controlled at the level of individual tables. This feature involves the configuration options innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_persistent_sample_pages, and the clauses STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES of the CREATE TABLE and ALTER TABLE statements. See Section 14.2.5.2.9, “Persistent Optimizer Statistics for InnoDB Tables” for usage details.

Incompatible Change: The --safe-mode server option has been removed.

Incompatible Change: It is now explicitly disallowed to assign the value DEFAULT to stored procedure or function parameters or stored program local variables (for example with a SET var_name = DEFAULT statement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertantly used this construct. It remains permissible to assign DEFAULT to system variables, as before, but assigning DEFAULT to parameters or local variables now results in a syntax error.

After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.

Important Change: Partitioning: MySQL nows supports partition lock pruning, which allows for many DDL and DML statements against partitioned tables using MyISAM (or another storage engine that employs table-level locking) to lock only those partitions directly affected by the statement. These statements include (but are not limited to) many SELECT, SELECT ... PARTITION, UPDATE, REPLACE, INSERT, and other statements. This enhancement improves especially the performance of many such statements when used with tables having many (32 or more) partitions. For a complete list of affected statements with particulars, and other information, see Section 17.6.4, “Partitioning and Locking”. (Bug #37252, Bug #11748732)

Important Change: Replication: It is now possible, in the event that a multi-threaded slave fails while running with the --relay-log-recovery option, to switch it safely to single-threaded mode despite the presence of any gaps with unprocessed transactions in the relay log. To accomplish this, you can now use START SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS to cause the slave SQL threads to run until no more such gaps are found in the relay log. Once this statement has completed, you can change the slave_parallel_workers system variable, and (if necessary) issue a CHANGE MASTER TO statement before restarting the slave. (Bug #13893363)

References: See also Bug #13893310.

Important Change: Replication: INSERT ON DUPLICATE KEY UPDATE is now marked as unsafe for statement-based replication if the target table has more than one primary or unique key. For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”. (Bug #58637, Bug #11765650, Bug #13038678)

Important Change: Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS) may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege was necessary to use either form of this statement.)

Important Change: INSERT DELAYED is now deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead. (Bug #13985071)

Important Change: In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

Those nonstandard behaviors remain the default for TIMESTAMP but now are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp system variable at server startup. With this variable enabled, the server handles TIMESTAMP as follows instead:

To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp:

(Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)

Important Change: The YEAR(2) data type is now deprecated because it is problematic. YEAR(2) columns in existing tables are treated as before, but YEAR(2) in new or altered tables are converted to YEAR(4). Support for YEAR(2) will be removed entirely in a future release of MySQL. For more information, see Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.

InnoDB: InnoDB tables now support the notion of “transportable tablespaces”, allowing .ibd files to be exported from a running MySQL instance and imported into another running instance. The FOR EXPORT clause of the FLUSH TABLE command writes any unsaved changes from InnoDB memory buffers to the .ibd file. After copying the .ibd file and a separate metadata file to the other server, you can use the DISCARD TABLESPACE and IMPORT TABLESPACE clauses of the ALTER TABLE statement to bring the table data into a different MySQL instance.

For more information, see Section 13.7.6.3, “FLUSH Syntax”.

InnoDB: For systems with constant heavy workloads, or workloads that fluctuate widely, several new configuration options let you fine-tune the flushing behavior for InnoDB tables: innodb_adaptive_flushing_lwm, innodb_max_dirty_pages_pct_lwm, innodb_max_io_capacity (changed in subsequent point releases to innodb_io_capacity_max), and innodb_flushing_avg_loops. These options feed into an improved formula used by the innodb_adaptive_flushing option. For full details about improvements to flushing algorithms and options, see Section 14.2.5.2.8, “Improvements to Buffer Pool Flushing”.

Replication: The STOP SLAVE option SQL_BEFORE_GTIDS did not function correctly, and the SQL_AFTER_GTIDS option for the same statement did not function at all. (Bug #13810456)

Replication: Added the --slave-rows-search-algorithms option for mysqld, which determines the search algorithms used for finding matches for slave updates when slave_allow_batching is enabled, including whether or not table or index hashing is used with searches employing a primary or unique key, some other key, or no key.

The Performance Schema has a new system variable, performance_schema_session_connect_attrs_size, and new status variable, Performance_schema_session_connect_attrs_lost. The system variable is the amount of preallocated memory per thread used to hold connection attribute strings. If the connection attribute strings are larger than the reserved storage, the status variable is incremented. (Bug #14076427)

yaSSL was upgraded from version 1.7.2 to 2.1.4.

The generic “procedure API” has been removed from the server. This was formerly present as a means of writing server procedures, but went unused except for PROCEDURE ANALYSE(). Removing the interface simplifies aspects of the internal procedure representation that were related to code no longer in the server but had a negative effect on its operation, in the sense that these aspects hindered the ability of the optimizer to perform better on more common query types. In addition, this code hindered future optimizer development and its removal will have benefit that development.

PROCEDURE ANALYSE() remains available, but is no longer implemented using a public interface. (For information, see Section 8.4.2.4, “Using PROCEDURE ANALYSE”.) One consequence of removing the procedure interface is that EXPLAIN SELECT ... PROCEDURE ANALYSE() now works where previously it produced an error.

To improve scalability by reducing contention among sessions for the global lock on the open tables cache, the cache now can be partitioned into several smaller cache instances. A session now need lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that need to use the cache when many there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)

A new system variable, table_open_cache_instances, permits control over the number of cache instances. Each instance has a size of table_open_cache / table_open_cache_instances. By default, the number of instances is 1.

Three new status variables provide information about the operation of the open tables cache. Table_open_cache_hits and Table_open_cache_misses indicate the number of hits and misses or lookups in the cache. Table_open_cache_overflows indicates how many times, after a table is opened or closed, an instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.

Previously, for semi-join processing the outer query specification was limited to simple table scans or inner joins using comma syntax, and view references were not possible. Now outer join and inner join syntax is permitted in the outer query specification, and the restriction that table references must be base tables has been lifted.

It is now possible for client programs to pass connection attributes to the server in the form of key/value pairs. Attributes are manipulated using the MYSQL_OPT_CONNECT_ATTR_RESET and MYSQL_OPT_CONNECT_ATTR_DELETE options for the mysql_options() C API function, and the MYSQL_OPT_CONNECT_ATTR_ADD option for the new mysql_options4() function. Connection attributes are exposed through the session_connect_attrs and session_account_connect_attrs Performance Schema tables.

If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

For more information, see Section 21.9.3, “C API Function Descriptions”, and Chapter 20, MySQL Performance Schema.

Previously, the default value for the --bind-address option was 0.0.0.0, which causes the server to accept TCP/IP connections on all server host IPv4 interfaces. To make it easier to use IPv6 connections without special configuration, the default --bind-address value now is *. This is similar to 0.0.0.0, but causes the server to also accept TCP/IP connections on all IPv6 interfaces if the server host supports IPv6. (Another way to to accept IPv4 and IPv6 connections is by using --bind-address=::, but in this case an error occurs if the server host does not support IPv6.)

The optimizer's cost model for disk-sweep Multi-Read Range (DS-MRR) has been improved. The improved cost model makes it more likely that DSMRR will be used for queries that read much data from disk.

For the WITH_SSL CMake option, no is no longer a permitted value or the default value. The default is now bundled. Consequently, MySQL now is always built with SSL support.

Performance: Partitioning: InnoDB: The statistics used by the optimizer for queries against partitioned InnoDB tables were based only on the first partition of each such table, leading to use of the wrong execution plan. (Bug #13694811)

References: This bug was introduced by Bug #11756867.

Performance: InnoDB: Improved the efficiency of the system calls to get the system time to record the start time for a transaction. This fix reduces potential cache coherency issues that affected performance. (Bug #13993661)

Performance: InnoDB: Improved the algorithm related to adaptive flushing. This fix increases the rate of flushing in cases where compression is used and the data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061)

Performance: InnoDB: The order in which flushes are performed when the innodb_flush_neighbors configuration option is enabled was improved. The algorithm makes the neighbor-flushing technique faster on HDD storage, while reducing the performance overhead on SSD storage. (innodb_flush_neighbors typically is not needed for SSD hardware.) (Bug #13798956)

Performance: InnoDB: This fix improves the speed of DROP TABLE for InnoDB tables by removing a scan of the buffer pool to remove entries for the adaptive hash index. This improvement is most noticeable on systems with very large buffer pools and the innodb_adaptive_hash_index option enabled. (Bug #13704145, Bug #64284)

Performance: Replication: All changes made as part of a given transaction are cached; when the transaction is committed, the contents of this cache are written to the binary log. When using global transaction identifiers, the GTID identifying this transaction must be the first event among all events in the cache belonging to the transaction.

Previously, a portion of the cache was preallocated as a buffer when the transaction began; upon commit it was completed with a valid GTID. However, because it was not possible to perform a seek in the cache, it was necessary to flush it to a temporary file, and then seek within this file. When the cache buffer is not big enough to accommodate all changes comprising a given transaction, it swapped the data to disk, then reinitialized the cache to have the buffer properly filled with the correct data again. The buffer was actually flushed and the cache reinitialized every time a GTID event was written, even in those cases in which all events making up a given transaction fit within the cache buffer, which could negatively impact the performance of binary logging (and thus replication) when using GTIDs.

Now the cache is reinitialized only when it is actually necessary—in other words, only when the cache is in fact swapped to disk.

In addition, the fix for this issue addresses a missing unlock operation when the server failed to write an empty transaction group and reduces the amount of code needed for prepending the GTID to the contents of the cache before flushing the cache to disk. (Bug #13877432)

References: See also Bug #13738296.

Performance: Within stored programs, the overhead of making statements log friendly was incurred even when the corresponding log was not enabled. (Bug #12884336)

Performance: The MD5() and SHA1() functions had excessive overhead for short strings. (Bug #49491, Bug #11757443, Bug #60227, Bug #14134662)

Incompatible Change: Metadata was handled incorrectly for objects such as tables or views that were used in a stored program. Metadata for each such object was gathered at the beginning of program execution, but not updated if DDL statements modified the object during program execution (or modified it between executions of the program if the program remained in the stored program cache). This resulted in mismatches between the actual object structure and the structure the stored program believed the object to have during execution, and caused problems such as data errors or server crashes.

Now metadata changes to objects used in a stored program are detected during execution and affected statements within the program are reparsed so that they use the updated metadata.

Example: Suppose that a stored program executes this statement in a loop and that the columns in the table t1 are altered during loop execution:

SELECT * FROM t1;

Previously, errors occurred because program execution did not detect that SELECT * evaluates to a different set of columns after the change. Now the table change is detected and the SELECT is reparsed to determine the new set of columns.

Reparsing occurs for other cases as well, such as t1 being changed from a base table to a view or a TEMPORARY table. For more information, see Section 8.9.4, “Caching of Prepared Statements and Stored Programs”.

There is a possible incompatibility regarding the new behavior: Application code that assumed the previous behavior and implemented a workaround may need to be changed.

Other instances of corrected problems:

(Bug #61434, Bug #12652835, Bug #55678, Bug #11763018, Bug #64574, Bug #13840615, Bug #33843, Bug #11747732, Bug #33289, Bug #11747626, Bug #33255, Bug #11747619, Bug #33000, Bug #11747566, Bug #27011, Bug #11746530, Bug #33083, Bug #11747581, Bug #32868, Bug #11747537, Bug #12257, Bug #11745236)

Important Change: MySQL Cluster: mysqld_safe now traps Signal 13 (SIGPIPE) so that this signal no longer kills the MySQL server process. (Bug #33984)

InnoDB: Replication: When binary log statements were replayed on the slave, the Com_insert, Com_update, and Com_delete counters were incremented by BEGIN statements initiating transactions affecting InnoDB tables but not by COMMIT statements ending such transactions. This affected these statements whether they were replicated or they were run using mysqlbinlog. (Bug #12662190)

InnoDB: If MySQL crashed during an ALTER TABLE t DISCARD TABLESPACE operation, it could leave InnoDB in a state where it crashes at the next startup. The error message was:

InnoDB: Error: a record lock wait happens in a dictionary operation! 

(Bug #14146981)

InnoDB: When importing an InnoDB tablespace representing a compressed table, unnecessary checksum calculations were being performed. (Bug #14161424)

InnoDB: Dropping an InnoDB temporary table could leave behind the .ibd file if the table was created with the innodb_file_per_table setting enabled. On Windows systems, this could cause an additional problem: repeated attempts to drop the file for 2000 seconds. In addition to resolving the incorrect path name used to drop the file, this fix also limits the retry loop to 10 seconds, for example if the file cannot be removed because it is locked by a backup process. (Bug #14169459)

InnoDB: A race condition could cause a crash during an online CREATE INDEX statement for an InnoDB table. This bug only affected very small tables. It required a DML operation to be in progress for the table, affecting the primary key columns, at the same time the CREATE INDEX statement was issued. (Bug #14117641)

InnoDB: If a row was deleted from an InnoDB table, then another row was re-inserted with the same primary key value, an attempt by a concurrent transaction to lock the row could succeed when it should have waited. This issue occurred if the locking select used a WHERE clause that performed an index scan using a secondary index. (Bug #14100254, Bug #65389)

InnoDB: An assertion error could occur if an XA transaction was created within a session designated as read-only. (Bug #14108709)

InnoDB: This fix improves the accuracy of the data in the information_schema table innodb_metrics for systems with innodb_buffer_pool_instances set to greater than 1. The improved information applies to the number of pages flushed from the buffer pool, specifically these entries in the table:

buffer_flush_batch_total_pages
buffer_flush_neighbor_total_pages
buffer_flush_adaptive_total_pages
buffer_flush_sync_total_pages
buffer_flush_background_total_pages
buffer_LRU_batch_total_pages

(Bug #14037167)

InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE statement for an InnoDB table could sometimes overlook rows recently committed by other transactions. As explained in Section 14.2.4.3, “Consistent Nonlocking Reads”, DML statements within a REPEATABLE READ transaction apply to rows committed by other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)

InnoDB: During an ANALYZE TABLE statement for an InnoDB table, the server could hang (in non-debug builds), or an assertion error could occur, indicating recursive acquisition of a lock (in debug builds). (Bug #14007109)

InnoDB: Using the KILL statement to terminate a query could cause an unnecessary message in the error log:

[ERROR] Got error -1 when reading table table_name

(Bug #13933132)

InnoDB: Querying the INFORMATION_SCHEMA.INNODB_TRX or related tables while the server was running a heavy InnoDB workload could cause a crash, with messages in the error log referring to the function fetch_data_into_cache_low. This issue arose during new feature work and only affected MySQL 5.6. (Bug #13966453)

InnoDB: Fixes a recently introduced issue with InnoDB persistent statistics, that could cause a crash (non-debug builds) or assertion error (debug builds). (Bug #13946118)

InnoDB: Including a % character in a query using an InnoDB FULLTEXT index could cause a crash. (FULLTEXT indexes for InnoDB tables are a new feature, still under development.) (Bug #13940669, Bug #64901)

InnoDB: An assertion could be raised if an InnoDB table was moved to a different database using ALTER TABLE ... RENAME while the database was being dropped by DROP DATABASE. (Bug #13982017)

InnoDB: If the server crashed while dropping an InnoDB temporary table or an index on a temporary table, further errors could occur during crash recovery, preventing the server from restarting. (Bug #13913670)

InnoDB: A FULLTEXT query for an InnoDB table could filter the search terms incorrectly if a term using the minus operator was followed by another term using the plus operator. (Bug #13907075)

InnoDB: When a table was renamed, the InnoDB persistent statistics were not associated with the new table name. (Bug #13920437)

InnoDB: With the innodb_force_recovery configuration option set to 2 or greater, a shutdown could hang after the message:

InnoDB: Waiting for purge thread to be suspended 

This issue was introduced during recent changes within the MySQL 5.6 development cycle. (Bug #13830371)

InnoDB: Deleting a huge amount of data from InnoDB tables within a short time could cause the purge operation that removes delete-marked records to stall. This issue could result in unnecessary disk space use, but does not cause any problems with data integrity. If this issue causes a disk space shortage, restart the server to work around it. This issue is only likely to occur on 32-bit platforms. (Bug #13847885)

InnoDB: The server could crash when using the SAVEPOINT statement in conjunction with InnoDB tables containing FULLTEXT indexes. (Bug #13831840)

InnoDB: Running concurrent bulk inserts on a server with auto_increment_offset=1, auto_increment_increment greater than 1, and innodb_autoinc_lock_mode=1 could result in intermittent errors like the following, even with the primary key set to auto_increment and omitted from the INSERT statement:

Duplicate entry 'value' for key 'PRIMARY' 

The workaround was to set auto_increment_offset=1 or innodb_autoinc_lock_mode=0 (“traditional”). (Bug #13817703, Bug #61209)

InnoDB: During an ALTER TABLE statement to create a primary key for an InnoDB table, some column characteristics could be set incorrectly, leading to errors during subsequent queries. The incorrect data could be the maximum length for a column prefix, or the state of the NOT NULL flag.

In MySQL 5.1, this fix applies to the InnoDB Plugin, but not the built-in InnoDB storage engine. (Bug #13641275)

InnoDB: The server could halt with an assertion error when DDL and DML operations were run on the same InnoDB table simultaneously:

InnoDB: Error: a record lock wait happens in a dictionary operation! 

This fix stems from the online DDL feature in MySQL 5.6. (Bug #13641926)

InnoDB: An ALTER TABLE statement for an InnoDB table that dropped one index and create another could fail with an error code 1280, and displaying the wrong index name in the message. (Bug #13029445, Bug #62544)

InnoDB: The error handling and message was improved for attempting to create a foreign key with a column referencing itself. The message suggested a potential problem with the data dictionary, when no such problem existed. (Bug #12902967)

InnoDB: For an InnoDB table with a trigger, under the setting innodb_autoinc_lock_mode=1, sometimes auto-increment values could be interleaved when inserting into the table from two sessions concurrently. The sequence of auto-increment values could vary depending on timing, leading to data inconsistency in systems using replication. (Bug #12752572, Bug #61579)

InnoDB: When data was removed from an InnoDB table, newly inserted data might not reuse the freed disk blocks, leading to an unexpected size increase for the system tablespace or .ibd file (depending on the setting of innodb_file_per_table. The OPTIMIZE TABLE could compact a .ibd file in some cases but not others. The freed disk blocks would eventually be reused as additional data was inserted. (Bug #11766634, Bug #59783)

InnoDB: The CHECK TABLE statement could fail for a large InnoDB table due to a timeout value of 2 hours. For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB, depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the timeout less likely. It also makes InnoDB recognize the syntax CHECK TABLE QUICK, which avoids the possibility of the timeout entirely. (Bug #11758510, Bug #50723)

InnoDB: Full-text search in InnoDB tried to follow foreign key references without keeping track of which ones it had already seen. With circular and other complex setups, this could loop forever or a very long time, leading to the appearance of the query thread hanging. (Bug #64274, Bug #13701973)

Partitioning: If a partitioned table t1 was created using the ROW_FORMAT option, attempting to perform ALTER TABLE t1 EXCHANGE PARTITION ... WITH TABLE t2 failed with the error Tables have different definitions even if the definition for table t2 was identical to that for t1. This occurred because a check was made for an explicit ROW_FORMAT setting in the table definition, and if this was set, the operation was rejected.

Now in such cases the row format actually used for each table is checked explicitly and the EXCHANGE PARTITION operation is permitted to execute if both row formats are the same. (Bug #11894100)

Partitioning: The PARTITION_COMMENT column of the INFORMATION_SCHEMA.PARTITIONS table truncated partition comments, displaying only the first 80 characters.

As part of the fix for this issue, the maximum length for a partition comment is now set at 1024 characters, and this width is honored by INFORMATION_SCHEMA.PARTITIONS.PARTITION_COMMENT. (Bug #11748924, Bug #37728)

Replication: It was possible in some cases when using semisynchronous replication for log rotation to take place before an ongoing transaction was committed or rolled back. (Bug #14123372)

Replication: When a complete global transaction spanned relay logs such that only its GTID appeared in a given relay log while the body of the transaction (including BEGIN and COMMIT statements) appeared in the next relay log, the GTID was interpreted incorrectly as belonging to an empty group. (Bug #14136654)

Replication: If the relay logs were removed after the server was stopped, without stopping replication first, the server could not be started correctly. (Bug #14029212, Bug #65152)

References: See also Bug #13971348.

Replication: The --bootstrap option for mysqld is used by mysql_install_db when it initializes the system tables. Now, whenever this option is used, GTIDs (see Section 16.1.3, “Replication with Global Transaction Identifiers”) and replication are automatically disabled. (Bug #13992602)

Replication: It was theoretically possible for concurrent execution of more than one instance of SHOW BINLOG EVENTS to crash the MySQL Server. (Bug #13979418)

Replication: The text for the error ER_AUTO_POSITION_REQUIRES_GTID_MODE_ON referred to AUTO_POSITION = 1 although this should be MASTER_AUTO_POSITION = 1. The text has been corrected. (Bug #13868465)

Replication: If errors were encountered while trying to initialize the mysql.slave_master_info or mysql.slave_relay_log_info tables, the server refused to start. Now in such cases, the warning message Error while checking replication metadata. This might also happen when doing a live upgrade from a version that did not make use of the replication metadata tables is issued to advise the user that this has happened, but the server is permitted to continue starting. (Bug #13893363)

Replication: Queries that were more than 255 characters in length were truncated when viewed in the output of SHOW BINLOG EVENTS or mysqlbinlog. This was due to the length of the query being stored in Rows_query_log_events using a single byte. (Bug #13799489)

Replication: A CHANGE MASTER TO statement could alter the effective value of relay_log_purge. In addition, the relay_log_recovery system variable is now read-only, and can be changed only by starting the server with --relay-log-recovery. (Bug #13840948)

Replication: When binlog_rows_query_log_events = 1 and a statement is written to the binary log using the row-based logging format, the server generates a an additional log event containing the text of the original statement. If mysqlbinlog is executed on this log using the --verbose --verbose, the original statement is printed. To prevent the statement from being executed in addition to the row event (which would in effect cause the statement to be excuted twice), it is commented out with a leading # character.

This was implemented with the assumption that such a statement would consist of a single line, which meant that a statement covering multiple lines was handled incorrectly, in that only the first line of the statement actually commented out. Now in such cases, every line of the statement is commented out with a leading #. (Bug #13799555)

Replication: Replication locks and some of the protocols controlling the use of these locks were not well implemented or enforced. In particular, this fix improves lock handling for statements such as CHANGE MASTER TO, SHOW SLAVE STATUS, and FLUSH LOGS. (Bug #13779291)

Replication: When logging transactions that affected both transactional and nontransactional tables, the following statements could sometimes be written into the binary log in the wrong order or on the wrong side of a transaction boundary:

(Bug #13627921)

Replication: To provide a crash-safe slave, it was previously necessary to change the storage engine for the slave_master_info, slave_relay_log_info, and slave_worker_info tables from MyISAM to InnoDB manually, by issuing ALTER TABLE. To simplify the setup of replication using these slave log tables, they are now created using the InnoDB storage engine. (Bug #13538891)

Replication: Setting binlog_checksum on the master to a value that was unknown on the slave caused replication to fail. Now in such cases, replication checksums are disabled on the slave and replication stops with an appropriate error message. (Bug #13553750, Bug #61096)

Replication: When the slave had been set using CHANGE MASTER TO with the MASTER_DELAY option equal to any permitted value greater than zero, then stopped using STOP SLAVE, pointed at the current relay log position (as shown by SHOW SLAVE STATUS), and started again, START SLAVE failed with the error Could not initialize master info structure. (Bug #12995174)

Replication: The --relay-log-space-limit option was sometimes ignored.

More specifically, when the SQL thread went to sleep, it allowed the I/O thread to queue additional events in such a way that the relay log space limit was bypassed, and the number of events in the queue could grow well past the point where the relay logs needed to be rotated. Now in such cases, the SQL thread checks to see whether the I/O thread should rotate and provide the SQL thread a chance to purge the logs (thus freeing space).

Note that, when the SQL thread is in the middle of a transaction, it cannot purge the logs; it can only ask for more events until the transaction is complete. Once the transaction is finished, the SQL thread can immediately instruct the I/O thread to rotate. (Bug #12400313, Bug #64503)

References: See also Bug #13806492.

Replication: An event whose length exceeded the size of the master dump thread's max_allowed_packet caused replication to fail. This could occur when updating many large rows and using row-based replication.

As part of this fix, a new server option --slave-max-allowed-packet is added, which permits max_allowed_packet to be exceeded by the slave SQL and I/O threads. Now the size of a packet transmitted from the master to the slave is checked only against this value (available as the value of the slave_max_allowed_packet server system variable), and not against the value of max_allowed_packet. (Bug #12400221, Bug #60926)

Replication: Statements using AUTO_INCREMENT, LAST_INSERT_ID(), RAND(), or user variables could be applied in the wrong context on the slave when using statement-based replication and replication filtering server options (see Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”). (Bug #11761686, Bug #54201)

References: See also Bug #11754117, Bug #45670, Bug #11746146, Bug #23894.

Replication: An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based binary logging or replication. Such statements are now marked as unsafe and fail with an error when using the STATEMENT binary logging format. For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”, as well as Section 16.4.1.1, “Replication and AUTO_INCREMENT”.

(Bug #11754117, Bug #45670)

References: See also Bug #11761686, Bug #54201, Bug #11746146, Bug #23894.

Replication: After upgrading a replication slave to MySQL 5.6.2 or later, enabling the query cache eventually caused the slave to fail. (Bug #64624, Bug #14005409)

After running ALTER TABLE tbl DISCARD TABLESPACE for an InnoDB table, certain other ALTER TABLE operations such as renaming the table or rebuilding the primary key could cause a crash. (Bug #14213568)

For conditions of the form WHERE p1 AND (p2 OR p3), the optimizer now uses the index merge access method on (p2,p3) if it is more efficient than a range scan on p1. Previously, index merge was not considered when a range scan was possible. (Bug #14208922)

With logging of the general query log to a table, logging was disabled within a read-only transaction because write lock acquisition on the log table was blocked. (Bug #14136866)

Error messages that should have said "YEAR(2)" said "YEAR(0)" instead. (Bug #14167585)

For debug builds, INSERT IGNORE INTO ... SELECT that selected more than max_join_size rows could raise an assertion. (Bug #14145442)

If a nonexistent page was requested to be loaded into the InnoDB buffer pool by the innodb_buffer_pool_load_at_startup configuration option, a subsequent shutdown operation could hang. (Bug #14106082)

The Performance Schema did not generate consistent digest values for CALL statements. (Bug #14069132)

The ARCHIVE storage engine could not be built unless the Performance Schema was also built. (Bug #14116252)

In debug builds, warnings occurring during creation of an InnoDB table with ROW_FORMAT=DYNAMIC and innodb_file_per_table disabled could raise an assertion. (Bug #14101563)

Derived tables and tables created with CREATE TABLE ... SELECT using the output from single-row queries with NULL in the first column could change the value to 0. (Bug #14069831)

Incorrect assessment of column nullability for a subquery result within a trigger could cause “column cannot be null” errors. (Bug #14069810, Bug #14005353)

In debug builds, the server failed to check for error status from the storage engine and raised an assertion. (Bug #14101852)

When the index enforcing a foreign key constraint was dropped while foreign_key_checks=0, further operations involving the foreign key column could cause a serious error after the foreign_key_checks option was re-enabled. (Bug #14025221)

For debug builds compiled with gcov, tests that used DBUG_SUICIDE lost gcov data. (Bug #14028421)

The LooseScan semi-join strategy could fail to remove duplicates from the result set. (Bug #14053325)

Certain arguments to RPAD() could lead to “uninitialized variable” warnings. (Bug #14039955)

Mishandling of failed internal commits in administrative statements such as ANALYZE TABLE could cause an assertion to be raised. (Bug #14001091)

Queries containing references to user variables were not written to the general query log with some rewriting, not as received. (Bug #13958454)

For debug builds, conversion of a double-precision value to the lldiv_t type could raise an assertion. (Bug #13976233)

Some arguments to MAKETIME() could cause a buffer overflow. (Bug #13982125)

Selecting MIN() or MAX() from a left or right join involving an INFORMATION_SCHEMA table could cause a server crash. (Bug #13966514)

For debug builds, the optimizer could change the query plan when checking sort order and return incorrect results. (Bug #13949068)

Improper calculation of decimals for TIME values given as arguments to IF() or IFNULL() could cause a server crash. (Bug #13988413, Bug #14042545)

Mishandling of failure during multiple-table UPDATE IGNORE statements could cause an assertion to be raised. (Bug #13974815)

Queries that grouped by an outer BLOB column in a subquery caused a server crash. (Bug #13966809)

Overhead for Performance Schema table aggregation operations was excessive. (Bug #13862186)

An infinite thread loop could develop within Performance Schema, causing the server to become unresponsive. (Bug #13898343)

When the InnoDB persistent statistics feature was turned on, an ALTER TABLE statement on an InnoDB table with delete-marked records could cause a crash (non-debug builds) or assertion error (debug builds). (Bug #13838962, Bug #13867915)

viosslfactories did not compile on Oracle Linux 6.0 with CMake options -DWITH_SSL=system and -DWITH_DEBUG=1. (Bug #13799126)

If KILL QUERY interrupted an INSERT or UPDATE that had the IGNORE modifier, OK was incorrectly returned to the client rather than an error code. Now an error (“Query execution was interrupted”) is returned instead. (Bug #13822652)

If KILL QUERY interrupted a statement during derived table materialization, the server crashed later trying to read the nonexistent materialized table. (Bug #13820776)

For comparison of a temporal value to and indexed character column, the optimizer could apply the range access method and thus perform an indexed search that found only literal matches. This is incorrect because MySQL permits a variety of delimiters in temporal values represented as strings. (Bug #13803810)

Some errors in MySQL 5.6 had different numbers than in MySQL 5.5. (Bug #13833438)

Incorrect stored program caching could cause statements within a stored program that included a GROUP BY clause to return different results across multiple program invocations. (Bug #13805127)

With subquery materialization enabled, some queries with a subquery in the HAVING clause caused a server crash. (Bug #13848789)

Several clarifications were made to optimizer trace output. (Bug #13799348)

The version_compile_machine system variable sometimes did not include the value 64 for server binaries compiled on a 64-bit system. (Bug #13859866)

The Performance Schema stored identifiers in digest tables as utf8 without converting them from the original character set first. (Bug #13809293)

In bootstrap mode, the server signal handler thread did not shut down if the server aborted early. (Bug #13837221)

Incorrect cost calculations for two-table joins could lead to incorrect join order. (Bug #13810048)

References: This bug is a regression of Bug #26106.

In debug builds, a race condition in a signal handler during shutdown caused a server crash. (Bug #13793813)

For open ranges that effectively resulted in a full index scan, the optimizer did not discard the range predicate as unneeded. (Bug #13731380)

A prepared statement that referenced views and were executed using semi-join transformation could return different results for different executions. (Bug #13773979)

References: See also Bug #14641759.

(a,b) IN (SELECT c,d FROM t1 WHERE ...) could produce incorrect results if t1 had an index on (c, d) and c or d contained NULL values. (Bug #13731417)

Outer join queries with ALL could return incorrect results because the optimizer incorrectly rewrote them to use inner join. (Bug #13735712)

The range optimizer sometimes did not treat equivalent expressions the same, depending on the order of the operands. For example, it could treat a <= b and b >= a differently. (Bug #13701206)

With semi-join optimization enabled, an assertion was raised for queries for which the number of tables was greater than the search depth. (Bug #13685026)

A query executed with literal values in the WHERE clause could return results different from the same query written to select the same literal values from a separate table using a SELECT statement in the WHERE clause. (Bug #13468414)

Truncating a table partition did not invalidate queries in the query cache that used the table. (Bug #13485448)

Setting max_sort_length to small values could cause a server crash. (Bug #13485416)

Condition handler code could assume that after handler execution, control would pass up a single level to the parent, sometimes leading to a server crash. (Bug #13431226)

If a GROUP_CONCAT() result was calculated using intermediate results (for example, if ORDER BY or DISTINCT was present), individual intermediate results were each truncated to a maximum of 64K, even if the group_concat_max_len system variable was set to a larger value. Now the length of any intermediate result and the final result are controlled by the group_concat_max_len value. (Bug #13387020)

Queries with ALL subquery predicates could return incorrect results due to a faulty query transformation. (Bug #13330886)

Switching between index scans and random scans using the HANDLER interface could result in failure of the interface to properly reinitialize scans. (Bug #13008220)

The presence of a file named .empty in the test database prevented that database from being dropped. (Bug #12845091)

For queries with ORDER BY COUNT(*) and LIMIT, the optimizer could choose an execution plan that produced incorrect results. (Bug #12713907)

IPv6 functions such as IS_IPV6() produced Valgrind warnings with arguments that used a multi-byte character set. (Bug #12635232, Bug #14040277)

For some subqueries that should be executed using a range scan on a non-primary index and required use of filesort, only the first execution of the subquery was done as a range scan. All following executions were done as full table scans, resulting in poor performance. In addition, if index condition pushdown was used, incorrect results could be returned. (Bug #12667154)

Queries that used STRAIGHT_JOIN and were executed using Multi-Range Read optimization could result in a memory leak. (Bug #12365385)

Overhead for the Performance Schema was reduced. (Bug #12346211)

IN subqueries that used a variance or standard deviation aggregate function could return a different result depending on whether the optimizer_switch materialization flag was enabled.

(Bug #11766758)

On Windows, initial database creation failed during bootstrapping. (Bug #11766342)

SAVEPOINT statements were incorrectly disallowed within XA transactions. (Bug #64374, Bug #13737343)

References: See also Bug #11766752.

If the --bind-address option was given a host name value and the host name resolved to more than one IP address, the server failed to start. For example, with --bind-address=localhost, if localhost resolved to both 127.0.0.1 and ::1, startup failed. Now the server prefers the IPv4 address in such cases. (Bug #61713, Bug #12762885)

Under some conditions, the effect of RENAME USER was not recognized until FLUSH PRIVILEGES was used (which should not be necessary). (Bug #61865, Bug #12766319)

The Performance Schema incorrectly displayed some backslashes in Windows file names (by doubling them). (Bug #63339, Bug #13417446)

SHOW statements treated stored procedure, stored function, and event names as case sensitive. (Bug #56224, Bug #11763507)

With lower_case_table_names=2 on systems with case-insensitive file systems such as Windows or Mac OS X, CREATE TABLE ... LIKE did not preserve lettercase of the destination table name as given in the statement. (Bug #64211, Bug #13702397)

MySQL was overly agressive in enforcing the NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes for default values in column definitions for CREATE TABLE and ALTER TABLE statements. Previously, default dates that were invalid with those SQL modes enabled produced an error, even when strict mode was not enabled. Now with NO_ZERO_DATE or NO_ZERO_IN_DATE enabled, invalid default dates produce a warning if strict SQL mode is not enabled, and an error if strict mode is enabled. (Bug #34280, Bug #11747847)

mysql_store_result() and mysql_use_result() are not for use with prepared statements and are not intended to be called following mysql_stmt_execute(), but failed to return an error when invoked that way in libmysqld. (Bug #62136, Bug #13738989)

References: See also Bug #47485.

For ALTER TABLE with the IGNORE keyword, IGNORE is now part of the information provided to the storage engine. It is up to the storage engine whether to use this when choosing between the in-place or copy algorithm for altering the table. For InnoDB index operations, IGNORE is not used if the index is unique, so the copy algorithm is used. (Bug #40344, Bug #11750045)

Some Czech error messages contained invalid characters. (Bug #64310, Bug #13726075)

A multiple-table UPDATE with the IGNORE keyword resulted in an inappropriate and not meaningful Got error 0 from storage engine message. (Bug #49539, Bug #11757486)

SHOW TABLES was very slow unless the required information was already in the disk cache. (Bug #60961, Bug #12427262)

File access by the ARCHIVE storage engine was not instrumented and thus not shown in Performance Schema tables. (Bug #63340, Bug #13417440)

An inappropriate mutex was used to protect random number generation, causing contention during connect operations. (Bug #62282, Bug #12951609)

mysqlbinlog exited with no error code if file write errors occurred. (Bug #55289, Bug #11762667)

On Windows, the mysql client crashed when invoked using its full path name. (Bug #60858, Bug #12402882)

Due to a race condition, it was possible for two threads to end up with the same query ID for different queries. (Bug #58785, Bug #11765785)

The server crashed at shutdown if the slow query log file was a named pipe. (Bug #64345, Bug #13733221)

Using CONCAT() to construct a pattern for a LIKE pattern match could result in memory corrupting and match failure. (Bug #59140, Bug #11766101)

For debug builds, errors occurring during processing of INSERT DELAYED statements could crash the server. (Bug #60114, Bug #11827404)

When an ALTER TABLE operation was performed with an invalid foreign key constraint, the error reported was ER_CANT_CREATE_TABLE rather than ER_CANNOT_ADD_FOREIGN. (Bug #64617, Bug #13840553)

yaSSL rejected valid SSL certificates that OpenSSL accepts. (Bug #54348, Bug #11761822)

A regression bug in the optimizer could cause excessive disk usage for UPDATE statements on InnoDB tables. For tables created with innodb_file_per_table enabled, OPTIMIZE TABLE can be used to recover excessive space used. For tables created in the InnoDB system tablespace,it is necessary to perform a dump and restore into a new instance of the system tablespace. (Bug #65745, Bug #14248833)

Sessions could end up deadlocked when executing a combination of SELECT, DROP TABLE, KILL, and SHOW ENGINE INNODB STATUS. (Bug #60682, Bug #12636001)

For queries with range predicates, the optimizer could miscalculate the number of key parts used, possibly leading to a server crash. (Bug #58731, Bug #11765737)

Parse errors that occurred while loading UCA or LDML collation descriptions were not written to the error log. (Bug #65593, Bug #14197426)

The optimizer could chose a worse execution plan for a condition that used a quoted number compared to the unquoted number. (Bug #43319, Bug #11752201)

When a query was killed, the error code was not always properly propagated up through the server code. (Bug #43353, Bug #11752226)

LEFT JOIN on derived tables was very slow. This is now addressed through the use of subquery materialization. (Bug #34364, Bug #11747876)

Queries that used WHERE (col1, col2) IN ((const, const)) were optimized for SELECT, but not for DELETE or UPDATE. (Bug #43187, Bug #11752097)

If an account had a nonzero MAX_USER_CONNECTIONS value, that value was not always respected. (Bug #65104, Bug #14003080)

If the server held a global mutex while doing network I/O, client disconnections could be slow. (Bug #53096, Bug #11760669)

On Windows, mysqlslap crashed for attempts to connect using shared memory. (Bug #31173, Bug #11747181, Bug #59107, Bug #11766072)

During the startup process, mysqld could incorrectly remove the PID file of an already running mysqld. (Bug #23790, Bug #11746142)

For Microsoft Windows, the deprecated MySQL Configuration Wizard is no longer distributed, and instead the newer MySQL Installer is available and preferred.

Using ALTER TABLE to add a TIMESTAMP column containing DEFAULT CURRENT_TIMESTAMP in the definition resulted in a column containing '0000-00-00 00:00:00', not the current timestamp. (Bug #17392, Bug #11745578)

For table or database names that are longer than 64 characters, the error “Incorrect table name” was returned rather than “Identifier too long”. (Bug #25168, Bug #11746295)

Redundant “Specified key was too long” messages could be produced by index-creation operations. (Bug #31149, Bug #11747177)

Code for the storage engine API did not check the return value from the ha_rnd_init(), ha_index_init(), and index_init() functions. (Bug #26040, Bug #11746399, Bug #54166, Bug #11761652)

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Important Change: Replication: This release introduces global transaction identifiers (GTIDs) for MySQL Replication. A GTID is a unique identifier that is assigned to each transaction as it is committed; this identifier is unique on the MySQL Server where the transaction originated, as well as across all MySQL Servers in a given replication setup. Because GTID-based replication depends on tracking transactions, it cannot be employed with tables that employ a nontransactional storage engine such as MyISAM; thus, it is currently supported only with InnoDB tables.

Because each transaction is uniquely identified, it is not necessary when using GTIDs to specify positions in the master's binary log when starting a new slave or failing over to a new master. This is reflected in the addition of a new MASTER_AUTO_POSITION option for the CHANGE MASTER TO statement which takes the place of the MASTER_LOG_FILE and MASTER_LOG_POS options when when executing this statement to prepare a MySQL Server to act as a replication slave.

To enable GTIDs on a MySQL Server, the server must be started with the options --gtid-mode=ON --disable-gtid-unsafe-statements --log-bin --log-slave-updates. These options are needed whether the server acts as a replication master or as a replication slave; the --gtid-mode and --disable-gtid-unsafe-statements options are new in this release. Once the master and slave have each been started with these options, it is necessary only to issue a CHANGE MASTER TO ... MASTER_AUTO_POSITION=1 followed by START SLAVE on the slave to start replication.

A number of new server system variables have also been added for monitoring GTID usage. For more information about these options and variables, see Section 16.1.4.5, “Global Transaction ID Options and Variables”.

As part of these changes, three new mysqlbinlog options—--include-gtids, --exclude-gtids, and --skip-gtids—have been added for reading binary logs produced when the server participates in replication with GTIDs.

For additional information about GTIDs and setting up GTID-based replication, see Section 16.1.3, “Replication with Global Transaction Identifiers”.

MySQL now provides more information about the causes of errors that occur when clients connect to the server, as well as improved access to the host cache, which contains client IP address and host name information and is used to avoid DNS lookups. These changes have been implemented:

For more information, see Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”, and Section 20.9.9.1, “The host_cache Table”. (Bug #22821, Bug #24906, Bug #45817, Bug #59404, Bug #11746048, Bug #11746269, Bug #11754244, Bug #11766316)

These query optimizer improvements were implemented:

The Performance Schema has these additions:

If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

For more information, see Chapter 20, MySQL Performance Schema.

Security Fix: Passwords stored in the older format used before MySQL 4.1 are now deprecated, and the secure_auth system variable is enabled by default to prevent connections using accounts that have passwords stored in the old format. To permit such connections, start the server with --skip-secure-auth. (Bug #13586336)

Security Fix: MySQL client programs now issue a warning if a password is given on the command line that this can be insecure.

Incompatible Change: The obsolete OPTION modifier for the SET statement has been removed.

InnoDB: --ignore-builtin-innodb is now ignored if used. (Bug #13586262)

The MySQL-shared-compat RPM package enables users of Red Hat-provided mysql-*-5.1 RPM packages to migrate to Oracle-provided MySQL-*-5.5 packages. MySQL-shared-compat now replaces the Red Hat mysql-libs package by replacing libmysqlclient.so files of the latter package, thus satisfying dependencies of other packages on mysql-libs. This change affects only users of Red Hat (or Red Hat-compatible) RPM packages. Nothing is different for users of Oracle RPM packages. (Bug #13867506)

Temporary tables for INFORMATION_SCHEMA queries now use dynamic MyISAM row format if they contain sufficiently large VARCHAR columns, resulting in space savings. (Bug #13627632)

A new CMake option, MYSQL_PROJECT_NAME, can be set on Windows or Mac OS X to be used in the project name. (Bug #13551687)

As of MySQL 5.5.3, the LOW_PRIORITY modifier for LOCK TABLES ... LOW_PRIORITY WRITE has no effect. This modifier is now deprecated. Its use should be avoided and now produces a warning. Use LOCK TABLES ... WRITE instead. (Bug #13586314)

If the log_queries_not_using_indexes system variable is enabled, slow queries that do not use indexes are written to the slow query log. In this case, it is now possible to put a logging rate limit on these queries by setting the new log_throttle_queries_not_using_indexes system variable, so that the slow query log does not grow too quickly. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes. (Bug #55323, Bug #11762697)

A new server option, --slow-start-timeout, controls the Windows service control manager's service start timeout. The value is the maximum number of milliseconds that the service control manager waits before trying to kill the MySQL service during startup. The default value is 15000 (15 seconds). If the MySQL service takes too long to start, you may need to increase this value. A value of 0 means there is no timeout. (Bug #45546, Bug #11754011)

The mysql client now supports an --init-command=str option. The option value is an SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs. (Bug #45634, Bug #11754087)

Several subquery performance issues were resolved through the implementation of semi-join subquery optimization strategies. See Section 8.13.15.1, “Optimizing Subqueries with Semi-Join Transformations”. (Bug #47914, Bug #11756048, Bug #58660, Bug #11765671, Bug #10815, Bug #11745162, Bug #9021, Bug #13519134, Bug #48763, Bug #11756798, Bug #25130, Bug #11746289)

New utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations have been added that preserve the behavior of utf8_general_ci and ucs2_general_ci from versions of MySQL previous to 5.1.24. Bug #27877 corrected an error in the original collations but introduced an incompatibility for columns that contain German 'ß' LATIN SMALL LETTER SHARP S. (As a result of the fix, that character compares equal to characters with which it previously compared different.) A symptom of the problem after upgrading to MySQL 5.1.24 or newer from a version older than 5.1.24 is that CHECK TABLE produces this error:

Table upgrade required.
Please do "REPAIR TABLE `t`" or dump/reload to fix it!

Unfortunately, REPAIR TABLE could not fix the problem. The new collations permit older tables created before MySQL 5.1.24 to be upgraded to current versions of MySQL.

To convert an affected table after a binary upgrade that leaves the table files in place, alter the table to use the new collation. Suppose that the table t1 contains one or more problematic utf8 columns. To convert the table at the table level, use a statement like this:

ALTER TABLE t1
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;

To apply the change on a column-specific basis, use a statement like this (be sure to repeat the column definition as originally specified except for the COLLATE clause):

ALTER TABLE t1
MODIFY c1 CHAR(N) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;

To upgrade the table using a dump and reload procedure, dump the table using mysqldump, modify the CREATE TABLE statement in the dump file to use the new collation, and reload the table.

After making the appropriate changes, CHECK TABLE should report no error.

For more information, see Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, and Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”. (Bug #43593, Bug #11752408)

MySQL distributions no longer include the GPL readline input-editing library. This results in simpler maintenance and support, and simplifies licensing considerations.

The SET TRANSACTION and START TRANSACTION statements now support READ WRITE and READ ONLY modifiers to set the transaction access mode for tables used in transactions. The default mode is read/write, which is the same mode as previously. Read/write mode now may be specified explicitly with the READ WRITE modifier. Using READ ONLY prohibits table changes and may enable storage engines to make performance improvements that are possible when changes are not permitted.

In addition, the new --transaction-read-only option and tx_read_only system variable permit the default transaction access mode to be set at server startup and runtime.

For more information, see Section 13.3.6, “SET TRANSACTION Syntax”, and Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

Performance: InnoDB: The optimizer now takes into account InnoDB page sizes other than 16KB, which can be configured with the innodb_page_size option when creating a MySQL instance. This change improves the estimates of I/O costs for queries on systems with non-default InnoDB page sizes. (Bug #13623078)

Performance: InnoDB: Memory allocation for InnoDB tables was reorganized to reduce the memory overhead for large numbers of tables or partitions, avoiding situations where the “resident set size” could grow regardless of FLUSH TABLES statements. The problem was most evident for tables with large row size. Some of the memory that was formerly allocated for every open table is now allocated only when the table is modified for the first time. (Bug #11764622, Bug #57480)

Incompatible Change: Replication: CHANGE MASTER TO statements were written into the error log using quoted numeric values, although the syntax for this statement does not allow such option values to be quoted. This meant that such statements could not be copied from the error log and re-run verbatim. Now CHANGE MASTER TO statements are written to the error log without the extraneous quotation marks, and so are syntactically correct as logged.

Incompatible Change: A change in MySQL 5.6.3 caused LAST_DAY() to be more strict and reject incomplete dates with a day part of zero. For this function, a nonzero day part is not necessary, so the change has been reverted. (Bug #13458237)

Important Change: InnoDB: When a row grew in size due to an UPDATE operation, other (non-updated) columns could be moved to off-page storage so that information about the row still fit within the constraints of the InnoDB page size. The pointer to the new allocated off-page data was not set up until the pages were allocated and written, potentially leading to lost data if the system crashed while the column was being moved out of the page. The problem was more common with tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED along with the Barracuda file format, particularly with the innodb_file_per_table setting enabled, because page allocation operations are more common as the .ibd tablespace files are extended. Still, the problem could occur with any combination of InnoDB version, file format, and row format.

A related issue was that during such an UPDATE operation, or an INSERT operation that reused a delete-marked record, other transactions could see invalid data for the affected column, regardless of isolation level.

The fix corrects the order of operations for moving the column data off the original page and replacing it with a pointer. Now if a crash occurs at the precise moment when the column data is being transferred, the transfer will not be re-run during crash recovery.

In MySQL 5.1, this fix applies to the InnoDB Plugin, but not the built-in InnoDB storage engine. (Bug #13721257, Bug #12612184, Bug #12704861)

Important Change: Partitioning: The query cache did not always function correctly with partitioned tables in a transactional context. For this reason, the query cache is now disabled for any queries using partitioned tables, and such queries can no longer be cached. For more information, see Section 17.6, “Restrictions and Limitations on Partitioning”. (Bug #11761296, Bug #53775)

Important Change: Replication: The CHANGE MASTER TO statement was not checked for invalid characters in values for options such as MASTER_HOST and MASTER_USER. In addition, when the server was restarted, a value containing certain characters was trimmed, causing the loss of its original value. Now such values are validated, and in cases where the value contains invalid characters, including linefeed (\n or 0x0A) characters, the statement fails with an error (ER_MASTER_INFO). (Bug #11758581, Bug #50801)

Important Change: Replication: Moving the binary log file, relay log file, or both files to a new location, then restarting the server with a new value for --log-bin, --relay-log, or both, caused the server to abort on start. This was because the entries in the index file overrode the new location. In addition, paths were calculated relative to datadir (rather than to the --log-bin or --relay-log values).

The fix for this problem means that, when the server reads an entry from the index file, it now checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the --log-bin or --relay-log option. An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. (Bug #11745230, Bug #12133)

InnoDB: If InnoDB was started with innodb_force_recovery set to a value of 3 or 4, and there are transactions to roll back, normal shutdown would hang waiting for those transactions to complete. Now the shutdown happens immediately, without rolling back any transactions, because non-zero values for innodb_force_recovery are only appropriate for troubleshooting and diagnostic purposes. (Bug #13628420)

InnoDB: The MySQL server could hang in some cases if the configuration option innodb_use_native_aio was turned off. (Bug #13619598)

InnoDB: An erroneous assertion could occur, in debug builds only, when creating an index on a column containing zero-length values (that is, ''). (Bug #13654923)

InnoDB: A DDL operation such as ALTER TABLE ... ADD COLUMN could stall, eventually timing out with an Error 1005: Can't create table message referring to fil_rename_tablespace. (Bug #13636122, Bug #62100, Bug #63553)

InnoDB: The configuration option innodb_sort_buf_size was renamed to innodb_sort_buffer_size for consistency. This work area is used while creating an InnoDB index. (Bug #13610358)

InnoDB: A Valgrind error was fixed in the function os_aio_init(). (Bug #13612811)

InnoDB: The server could crash when creating an InnoDB temporary table under Linux, if the $TMPDIR setting points to a tmpfs filesystem and innodb_use_native_aio is enabled, as it is by default in MySQL 5.5.4 and higher. The entry in the error log looked like:

101123  2:10:59  InnoDB: Operating system error number 22 in a file operation. 
InnoDB: Error number 22 means 'Invalid argument'.

The crash occurred because asynchronous I/O is not supported on tmpfs in some Linux kernel versions. The workaround was to turn off the innodb_use_native_aio setting or use a different temporary directory. The fix causes InnoDB to turn off the innodb_use_native_aio setting automatically if it detects that the temporary file directory does not support asynchronous I/O. (Bug #13593888, Bug #11765450, Bug #58421)

InnoDB: During startup, the status variable Innodb_buffer_pool_dump_status could be empty for a brief time before being initialized to the correct value not started. (Bug #13513676)

InnoDB: Valgrind errors when referencing the internal function buf_LRU_scan_and_free_block() were fixed. (Bug #13491704)

InnoDB: The MySQL server could halt with an assertion error:

InnoDB: Failing assertion: page_get_n_recs(page) > 1

Subsequent restarts could fail with the same error. The error occurred during a purge operation involving the InnoDB change buffer. The workaround was to set the configuration option innodb_change_buffering=inserts. (Bug #13413535, Bug #61104)

InnoDB: The MySQL error log could contain messages like:

InnoDB: Ignoring strange row from mysql.innodb_index_stats WHERE ...

The fix makes the contents of the innodb_index_stats and innodb_table_stats tables case-sensitive, to properly distinguish the statistics for tables whose names differ only in letter case. Other cases were fixed where the wrong name could be selected for an index while retrieving persistent statistics. (Bug #13432465)

InnoDB: References to C preprocessor symbols and macros HAVE_purify, UNIV_INIT_MEM_TO_ZERO, and UNIV_SET_MEM_TO_ZERO were removed from the InnoDB source code. They were only used in debug builds instrumented for Valgrind. They are replaced by calls to the UNIV_MEM_INVALID() macro. (Bug #13418934)

InnoDB: Certain CREATE TABLE statements could fail for InnoDB child tables containing foreign key definitions. This problem affected Windows systems only, with the setting lower_case_table_names=0. It was a regression from MySQL bug #55222. (Bug #13083023, Bug #60229)

InnoDB: When doing a live downgrade from MySQL 5.6.4 or later, with innodb_page_size set to a value other than 16384, now the earlier MySQL version reports that the page size is incompatible with the older version, rather than crashing or displaying a “corruption” error. (Bug #13116225)

InnoDB: If the server crashed during a TRUNCATE TABLE or CREATE INDEX statement for an InnoDB table, or a DROP DATABASE statement for a database containing InnoDB tables, an index could be corrupted, causing an error message when accessing the table after restart:

InnoDB: Error: trying to load index index_name for table table_name
InnoDB: but the index tree has been freed!

In MySQL 5.1, this fix applies to the InnoDB Plugin, but not the built-in InnoDB storage engine. (Bug #12861864, Bug #11766019)

InnoDB: A DDL operation for an InnoDB table could cause a busy MySQL server to halt with an assertion error:

InnoDB: Failing assertion: trx->error_state == DB_SUCCESS 

The error occurred if the DDL operation was run while all 1023 undo slots were in use by concurrent transactions. This error was less likely to occur in MySQL 5.5 and 5.6, because raising the number of InnoDB undo slots increased the number of simultaneous transactions (corresponding to the number of undo slots) from 1K to 128K. (Bug #12739098, Bug #62401)

InnoDB: InnoDB persistent statistics gave less accurate estimates for date columns than for columns of other data types. The fix changes the way cardinality is estimated for nonunique keys, and avoids situations where identical values could be counted twice if they occurred on different index pages. (Bug #12429443)

InnoDB: With 1024 concurrent InnoDB transactions running concurrently and the innodb_file_per_table setting enabled, a CREATE TABLE operation for an InnoDB table could fail. The .ibd file from the failed CREATE TABLE was left behind, preventing the table from being created later, after the load had dropped.

The fix adds error handling to delete the erroneous .ibd file. This error was less likely to occur in MySQL 5.5 and 5.6, because raising the number of InnoDB undo slots increased the number of simultaneous transactions needed to trigger the bug, from 1K to 128K. (Bug #12400341)

InnoDB: Improved the accuracy of persistent InnoDB statistics for large tables. The estimate of distinct records could be inaccurate if the index tree was more than 3 levels deep. (Bug #12316365)

InnoDB: Shutdown could hang with messages like this in the log:

Waiting for purge thread  to be suspended 

After 1 hour, the shutdown times out and mysqld quits. This problem is most likely to occur with a high value for innodb_purge_threads. (Bug #11765863, Bug #58868, Bug #60939)

InnoDB: When DROP TABLE failed due to all undo slots being in use, the error returned was Unknown table '...' rather than the expected Too many active concurrent transactions. (Bug #11764724, Bug #57586)

References: See also Bug #11764668, Bug #57529.

InnoDB: Server startup could produce an error for temporary tables using the InnoDB storage engine, if the path in the $TMPDIR variable ended with a / character. The error log would look like:

120202 19:21:26  InnoDB: Operating system error number 2 in a file operation. 
InnoDB: The error means the system cannot find the path specified. 
InnoDB: If you are installing InnoDB, remember that you must create 
InnoDB: directories yourself, InnoDB does not create them. 
120202 19:21:26  InnoDB: Error: trying to open a table, but could not 
InnoDB: open the tablespace file './t/#sql7750_1_0.ibd'! 
InnoDB: Have you moved InnoDB .ibd files around without using the 
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? 
InnoDB: It is also possible that this is a temporary table #sql..., 
InnoDB: and MySQL removed the .ibd file for this. 

The workaround for the problem was to create a similar temporary table again, copy its .frm file to tmpdir under the name mentioned in the error message (for example, #sql123.frm) and restart mysqld with tmpdir set to its normal value without a trailing slash, for example /var/tmp. On startup, MySQL would see the .frm file and issue DROP TABLE for the orphaned temporary table. (Bug #11754376, Bug #45976)

Partitioning: When creating a view from a SELECT statement that used explicit partition selection, the partition selection portion of the query was ignored. (Bug #13559657)

Partitioning: Adding a partition to an already existing LIST-partitioned table did not work correctly if the number of items in the new partition was greater than 16. This could happen when trying to add a partition using an ALTER TABLE ... ADD PARTITION statement, or an ALTER TABLE ... REORGANIZE PARTITION statement.

This 16-item limit was not apparent when using either CREATE TABLE ... PARTITION BY LIST or ALTER TABLE ... PARTITION BY LIST. (Bug #13029508, Bug #62505)

Partitioning: A function internal to the code for finding matching subpartitions represented an unsigned number as signed, with the result that matching subpartitions were sometimes missed in results of queries. (Bug #12725206, Bug #61765)

References: See also Bug #20257.

Partitioning: An ALTER TABLE ... ADD PARTITION statement subsequent to ALTER TABLE ... REORGANIZE PARTITION failed on a table partitioned by HASH or KEY. (Bug #11764110, Bug #56909)

Replication: Executing mysqlbinlog with the --start-position=N option, where N was equal either to 0 or to a value greater than the length of the dump file, caused it to crash.

This issue was introduced in MySQL 5.5.18 by the fix for Bug #32228 and Bug #11747416. (Bug #13593869, Bug #64035)

Replication: When starting the server, replication repositories were checked even when the --server-id was equal to 0 (the default), in spite of the fact that a valid nonzero value for --server-id must be supplied for a server that acts as either a master or a slave in MySQL replication.

This could cause problems when trying to perform a live upgrade from MySQL 5.5, although it was possible to work around the issue by starting the server with --skip-slave-start (in addition to any other required options).

To avoid this problem, replication repositories are now checked only when the server is started with --server-id using a nonzero value. (Bug #13427444, Bug #13504821)

Replication: Formerly, the default value shown for the Port column in the output of SHOW SLAVE HOSTS was 3306 whether the port had been set incorrectly or not set at all. Now, when the slave port is not set, the actual port used by the slave is shown. This change also affects the default shown for the --report-port server option. (Bug #13333431)

Replication: A race condition could occur when running multiple instances of mysqld on a single machine, when more than slave thread was started at the same time, and each such thread tried to use the same temporary file concurrently. (Bug #12844302, Bug #62055)

Replication: mysqlbinlog --database=dbname included all SET INSERT_ID=n assignments from the binary log in its output, even if database dbname was never referenced in the binary log. This was due to the fact that COMMIT statements were not associated with any database in the binary log. Now in such cases, the current database is tracked so that only those SET INSERT_ID assignments that are made in the context of changes to tables in database dbname are actually printed in the mysqlbinlog output. (Bug #11746146, Bug #23894)

References: See also Bug #23890, Bug #46998, Bug #11761686, Bug #54201, Bug #11754117, Bug #45670.

Replication: Statements that wrote to tables with AUTO_INCREMENT columns based on an unordered SELECT from another table could lead to the master and the slave going out of sync, as the order in which the rows are retrieved from the table may differ between them. Such statements include any INSERT ... SELECT, REPLACE ... SELECT, or CREATE TABLE ... SELECT statement. Such statements are now marked as unsafe for statement-based replication, which causes the execution of one to throw a warning, and forces the statement to be logged using the row-based format if the logging format is MIXED. (Bug #11758263, Bug #50440)

Replication: On Windows replication slave hosts, STOP SLAVE took an excessive length of time to complete when the master was down. (Bug #11752315, Bug #43460)

The optimizer did not perform constant propagation for views, so a query containing views resulted in a less efficient execution plan than the corresponding query using only base tables. (Bug #13783777)

After using an ALTER TABLE statement to change the KEY_BLOCK_SIZE property for an InnoDB table, for example when switching from an uncompressed to a compressed table, subsequent server restarts could fail with a message like:

InnoDB: Error: data file path/ibdata2 uses page size 1024, 
InnoDB: but the only supported page size in this release is=16384

This issue is a regression introduced in MySQL 5.5.20. (Bug #13698765, Bug #64160)

_mi_print_key() iterated one time too many when there was a NULL bit, resulting in Valgrind warnings. (Bug #13686970)

In debug builds, a Debug Sync timeout warning was treated as an error, causing an assertion to be raised. (Bug #13688248)

A memory leak could occur for queries containing a subquery that used GROUP BY on an outer column. (Bug #13724099)

If during server startup a signal such as SIGHUP was caught prior to full server initialization, the server could crash. This was due to a race condition between the signal handler thread and the main thread performing server initialization. To prevent this from happening, signal processing is now suspended until full initialization of all server components has been completed successfully. (Bug #13608371, Bug #62311)

A SELECT from a subquery that returned an empty result could itself fail to return an empty result as expected. (Bug #13651009, Bug #13650418)

An aggregated expression of type MIN() or MAX() should return NULL but could instead return the empty set if the query was implicitly grouped and there was no HAVING clause that evaluates to FALSE. (Bug #13599013)

The shared version of libmysqlclient did not export these functions for linking by client programs: get_tty_password(), handle_options(), my_print_help(). (Bug #13604121)

For debug builds, negative values with a zero integer part and nonzero fractional part (such as -0.1111) were not detected, so the negative fractional part was later cast to a large unsigned number and raised an assertion. (Bug #13616434)

Pushing down to InnoDB an index condition that called a stored function resulted in a server crash. This kind of condition is no longer pushed down. (Bug #13655397)

Date-handling code could raise an assertion attempting to calculate the number of seconds since the epoch. (Bug #13545236)

Left join queries could be incorrectly converted to inner joins and return erroneous result sets. (Bug #13595212)

Converting a string ending with a decimal point (such as '1.') to a floating-point number raised a data truncation warning. (Bug #13500371)

The Performance Schema instrumentation for stages did not fully honor the ENABLED column in the schema.setup_instruments table. (Bug #13509513)

SELECT statements failed for the EXAMPLE storage engine. (Bug #13511529)

References: This bug was introduced by Bug #11746275.

For queries that used a join type of ref_or_null, the optimizer could skip the filesort operation and sort the results incorrectly. (Bug #13531865)

For some queries, a filesort operation was done even when the result contained only a single row and needed no sorting. (Bug #13529048)

The optimizer could return an incorrect select limit in some cases when a query included no explicit LIMIT clause. (Bug #13528826)

In some cases, the optimizer failed to use a covering index when that was possible and read data rows instead. (Bug #13514959)

Use of an uninitialized TABLE_SHARE member could cause a server crash. (Bug #13489996)

A query that used an index on a CHAR column referenced in a BETWEEN clause could return invalid results. (Bug #13463488, Bug #63437)

Expressions that compared a BIGINT column with any non-integer constant were performed using integers rather than decimal or float values, with the result that the constant could be truncated. This could lead to any such comparison that used <, >, <=, >=, =, !=/<>, IN, or BETWEEN yielding false positive or negative results. (Bug #13463415, Bug #11758543, Bug #63502, Bug #50756)

Enabling index condition pushdown could cause performance degradation. (Bug #13430436)

For debug builds, enabling optimizer_trace could cause an assertion to be raised. (Bug #13430443)

When the optimizer performed conversion of DECIMAL values while evaluating range conditions, it could produce incorrect results. (Bug #13453382)

On Windows, rebuilds in a source distribution failed to create the initial database due to insufficient cleanup from the previous run or failure to find the proper server executable. (Bug #13431251)

An application linked against libmysqld could crash in debug mode with a stack smashing detected error if it tried to connect without specifying the user name. (Bug #13460909)

Implicitly grouped queries with a const table and no matching rows could return incorrect results. (Bug #13430588)

Some outer joins that used views as inner tables did not evaluate conditions correctly. (Bug #13464334)

Instantiating a derived table for a query with an empty result caused a server crash. (Bug #13457552)

Temporary MyISAM tables (unlike normal MyISAM tables) did not use the dynamic row format when they contained VARCHAR columns, resulting in larger temporary files (and more file I/O) than necessary. (Bug #13350136)

When a fixed-width row was inserted into a MyISAM temporary table, the entire content of the record buffer was written to the table, including any trailing space contained in VARCHAR columns, the issue being that this trailing space could be uninitialized. This problem has been resolved by insuring that only the bytes actually used to store the VARCHAR (and none extra) are copied and inserted in such cases. (Bug #13389854)

When merging ranges that effectively resulted in a full index scan, the optimizer did not discard the range predicate as unneeded. (Bug #13354910)

Fractional seconds parts were lost for certain UNION ALL queries. (Bug #13375823)

When executing EXPLAIN, it was assumed that only the default multi-range read implementation could produce an ordered result; this meant that when a query on a table that used a storage engine providing its own sorted MRR, it was ignored, so that EXPLAIN failed to report Using MRR even when a multi-range read was used. (Bug #13330645)

Performance Schema idle event timings were not normalized to the same units as wait timings. (Bug #13018537)

In MySQL 5.6.3, a number of status variables were changed to longlong types so that they would roll over much later. However, the format string used by mysqladmin status to print Queries per second values did not reflect this, causing such values to be misreported. (Bug #12990746)

References: See also Bug #42698. This bug was introduced by Bug #11751727.

For debug builds, two assertions could be raised erroneously for UPDATE statements. (Bug #12912171)

When the result of a stored function returning a non-integer type was evaluated for NULL, an incorrect type warning (Warning 1292 Truncated incorrect INTEGER value) is generated, although such a test for NULL should work with any type. This could cause stored routines not handling the warning correctly to fail.

The issue could be worked around by wrapping the result in an expression, using a function such as CONCAT(). (Bug #12872824, Bug #62125)

A query that used an aggregate function such as MAX() or MIN() of an index with NOT BETWEEN in the WHERE clause could fail to match rows, thus returning an invalid result. (Bug #12773464, Bug #61925)

When running mysqldump with both the --single-transaction and --flush-logs options, the flushing of the log performed an implicit COMMIT (see Section 13.3.3, “Statements That Cause an Implicit Commit”), causing more than one transaction to be used and thus breaking consistency. (Bug #12809202, Bug #61854)

With ONLY_FULL_GROUP_BY SQL mode enabled, columns that were not aggregated in the the select list or named in a GROUP BY were incorrectly permitted in ORDER BY. (Bug #12626418)

Mishandling of NO_BACKSLASH_ESCAPES SQL mode within stored procedures on slave servers could cause replication failures. (Bug #12601974)

With ONLY_FULL_GROUP_BY SQL mode enabled, a query that uses GROUP BY on a column derived from a subquery in the FROM clause failed with a column isn't in GROUP BY error, if the query was in a view. (Bug #11923239)

Attempting to execute ALTER TABLE on a temporary MERGE table having an underlying temporary table rendered the MERGE table unusable, unless the ALTER TABLE specified a new list of underlying tables. (Bug #11764786, Bug #57657)

It was possible on replication slaves where FEDERATED tables were in use to get timeouts on long-running operations, such as Error 1160 Got an error writing communication packets. The FEDERATED tables did not need to be replicated for the issue to occur. (Bug #11758931, Bug #51196)

References: See also Bug #12896628, Bug #61790.

When used with the --xml option, mysqldump --routines failed to dump any stored routines, triggers, or events. (Bug #11760384, Bug #52792)

A HAVING clause in a query using MIN() or MAX() was sometimes ignored. (Bug #11760517, Bug #52935)

References: See also Bug #11758970, Bug #51242, Bug #11759718, Bug #52051.

Previously, .OLD files were not included among the files deleted by DROP DATABASE. Files with this extension are now also deleted by the statement. (Bug #11751736, Bug #42708)

A prepared statement using a view whose definition changed between preparation and execution continued to use the old definition, which could cause the prepared statement to return incorrect results. (Bug #11748352, Bug #36002)

If an attempt to initiate a statement failed, the issue could not be reported to the client because it was not prepared to receive any error messages prior to the execution of any statement. Since the user could not execute any queries, they were simply disconnected without providing a clear error.

After the fix for this issue, the client is prepared for an error as soon as it attempts to initiate a statement, so that the error can be reported prior to disconnecting the user. (Bug #11755281, Bug #47032)

It was possible in the event of successive failures for mysqld_safe to restart quickly enough to consume excessive amounts of CPU. Now, on systems that support the sleep and date system utilities, mysqld_safe checks to see whether it has restarted more than 5 times in the current second, and if so, waits 1 second before attempting another restart. (Bug #11761530, Bug #54035)

The embedded server crashed when argc = 0. (Bug #57931, Bug #12561297)

Enabling myisam_use_mmap could cause the server to crash. (Bug #48726, Bug #11756764)

The handle_segfault() signal-handler code in mysqld could itself crash due to calling unsafe functions. (Bug #54082, Bug #11761576)

Stored functions could produce an error message that referred to ORDER BY even though the offending statement within the function had no such clause. (Bug #35410, Bug #11748187)

UPDATE IGNORE returned an incorrect count for number of rows updated when there were duplicate-key conflicts in a multiple-table update. (Bug #59715, Bug #11766576)

Locale information for FORMAT() function instances was lost in view definitions. (Bug #63020, Bug #13344643)

The stored routine cache was subject to a small memory leak that over time or with many routines being used could result in out-of-memory errors.

The fix for this issue also introduces a new global server system variable stored_program_cache which can be used for controlling the size of the stored routine cache. (Bug #44585, Bug #11753187)

On Windows, the server incorrectly constructed the full path name of the plugin binary for INSTALL PLUGIN and CREATE FUNCTION ... SONAME. (Bug #45549, Bug #11754014)

Using myisamchk with the sort recover method to repair a table having fixed-width row format could cause the row pointer size to be reduced, effectively resulting in a smaller maximum data file size. (Bug #48848, Bug #11756869)

myisam_sort_buffer_size could not be set larger than 4GB on 64-bit systems. (Bug #45702, Bug #11754145)

The optimizer mishandled STRAIGHT_JOIN used with nested joins; for example, by not evaluating tables in the specified order. (Bug #59487, Bug #11766384, Bug #43368, Bug #11752239, Bug #60080, Bug #11766858)

A subquery involved in a comparison requiring a character set conversion caused an error that resulted in a server crash. (Bug #59185, Bug #11766143)

mysqlhotcopy failed for databases containing views. (Bug #62472, Bug #13006947, Bug #12992993)

Assigning the result of a subquery to a user variable raised an assertion when the outer query included DISTINCT and GROUP BY. (Bug #57196, Bug #11764371)

On Windows, pasting multiple-line input including a CRLF terminator on the last line into the mysql client resulted in the first character of the last line being changed, resulting in erroneous statements. (Bug #60901, Bug #12589167)

Invalid memory reads could occur when cmp_item_sort_string::store_value() tried to refer to a temporary value that could be changed or deleted by other functions. (Bug #57510, Bug #11764651)

Due to improper locking, concurrent inserts into an ARCHIVE table at the same time as repair and check operations on the table resulted in table corruption. (Bug #37280, Bug #11748748)

For MEMORY tables, a scan of a HASH index on a VARCHAR column could fail to find some rows if the index was on a prefix of the column. (Bug #47704, Bug #11755870)

If tables were locked by LOCK TABLES ... READ in another session, SET GLOBAL read_only = 1 failed to complete. (Bug #57612, Bug #11764747)

The VIO description string was initialized even for connections where it was unneeded. (Bug #62285, Bug #12951586)

The contents of the shared and shared-compat RPM packages had been changed in versions 5.5.6 and 5.6.1 to avoid the overlap which they traditionally had (and still have in MySQL 5.0 and 5.1). However, the RPM meta information had not been changed in accordance, and so RPM still assumed a conflict between shared and shared-compat RPM packages. This has been fixed. (Bug #60855, Bug #12368215)

References: See also Bug #56150.

The result of SUBSTRING_INDEX() could be missing characters when used as an argument to conversion functions such as LOWER(). (Bug #60166, Bug #11829861)

Some debugging information was written to the buffer after a flush, resulting in the information not appearing until the next flush. (Bug #64048, Bug #13608112)

A confusing CREATE TABLE error message was improved. (Bug #54963, Bug #11762377)

Under some circumstances, the result of SUBSTRING_INDEX() incorrectly depended on the contents of the previous row. (Bug #42404, Bug #11751514)

Setting an event to DISABLED status and with the ON COMPLETION NOT PRESERVE attribute caused it to be dropped at the next server restart. (Bug #37666, Bug #11748899)

For comparisons containing out-of-range constants, the optimizer permitted warnings to leak through to the client, even though it accounted for the range issue internally. (Bug #56962, Bug #11764155)

The decision about how to sort a result set could be reported incorrectly by EXPLAIN for some statements, causing Using filesort or Using temporary to be reported when they should not have been or vice versa. This could occur for statements that included index hints, that had the form SELECT SQL_BIG_RESULT ... GROUP BY, that used SQL_CALC_FOUND_ROWS with LIMIT, or that used GROUP BY, ORDER BY, and LIMIT.

Incompatible Change: MySQL now supports the GET DIAGNOSTICS statement. GET DIAGNOSTICS provides applications a standardized way to obtain information from the diagnostics area, such as whether the previous SQL statement produced an exception and what it was. For more information, see Section 13.6.7.3, “GET DIAGNOSTICS Syntax”.

In addition several deficiencies in condition handler processing rules were corrected so that MySQL behavior is more like standard SQL:

The work just described involved several condition-handler bug fixes:

For more information, see Section 13.6.7.6, “Scope Rules for Handlers”. (Bug #12951117, Bug #38806, Bug #11749343, Bug #55852, Bug #11763171, Bug #61392, Bug #12652873, Bug #11660, Bug #11745196)

Incompatible Change: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

The following items summarize the implications of this change. See also Section 11.3.6, “Fractional Seconds in Time Values”.

In some cases, previously accepted syntax may produce different results. The following items indicate where existing code may need to be changed to avoid problems:

(Bug #8523, Bug #11745064)

MySQL now supports FULLTEXT indexes for InnoDB tables. The core syntax is very similar to the FULLTEXT capability from earlier releases, with the CREATE TABLE and CREATE INDEX statements, and MATCH() ... AGAINST() clause in the SELECT statement. The new @ operator allows proximity searches for terms that are near each other in the document. The detailed search processing is controlled by a new set of configuration options: innodb_ft_enable_stopword, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_cache_size, innodb_ft_min_token_size, and innodb_ft_max_token_size. You can monitor the workings of the InnoDB full-text search system by querying new INFORMATION_SCHEMA tables: innodb_ft_default_stopword, innodb_ft_index_table, innodb_ft_index_cache, innodb_ft_config, innodb_ft_deleted, and innodb_ft_being_deleted.

These query optimizer improvements were implemented:

The Performance Schema has these additions:

If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

For more information, see Chapter 20, MySQL Performance Schema.

Performance: InnoDB: You can now set the InnoDB page size for uncompressed tables to 8KB or 4KB, as an alternative to the default 16KB. This setting is controlled by the innodb_page_size configuration option. You specify the size when creating the MySQL instance. All InnoDB tablespaces within an instance share the same page size. Smaller page sizes can help to avoid redundant or inefficient I/O for certain combinations of workload and storage devices, particularly SSD devices with small block sizes.

Performance: InnoDB: New optimizations apply to read-only InnoDB transactions. See Section 14.2.5.2.2, “Optimizations for Read-Only Transactions” for details. The new optimizations make autocommit more applicable to InnoDB queries than before, as a way to signal that a transaction is read-only because it is a single-statement SELECT.

Replication: Previously, replication slaves could connect to the master server only through master accounts that use native authentication. Now replication slaves can also connect through master accounts that use nonnative authentication if the required client-side plugin is installed on the slave side in the directory named by the slave plugin_dir system variable. (Bug #12897501)

The optimizer trace capability now tracks temporary tables created by the server during statement execution. (Bug #13400713)

Performance of metadata locking operations on Windows XP systems was improved by instituting a cache for metadata lock objects. This permits the server to avoid expensive operations for creation and destruction of synchronization objects on XP. A new system variable, metadata_locks_cache_size, permits control over the size of the cache. The default size is 1024. (Bug #12695572)

Upgrading from an Advanced GPL RPM package to an Advanced RPM package did not work. Now on Linux it is possible to use rpm -U to replace any installed MySQL product by any other of the same release family. It is not necessary to remove the old produce with rpm -e first. (Bug #11886309)

MEMORY table creation time is now available in the CREATE_TIME column of the INFORMATION_SCHEMA.TABLES table and the Create_time column of SHOW TABLE STATUS output. (Bug #51655, Bug #11759349)

Previously, MySQL servers from 5.1 and up refused to open ARCHIVE tables created in 5.0 because opening them caused a server crash. The server now can open 5.0 ARCHIVE tables, and REPAIR TABLE updates them to the format used in 5.6. However, the recommended upgrade procedure is still to dump 5.0 ARCHIVE tables before upgrading and reload them after upgrading. (Bug #48633, Bug #11756687)

The make_win_bin_dist script is no longer used and has been removed from MySQL distributions and the manual. (Bug #58241)

Error messages that referred only to an error code now also include the corresponding error description. (Bug #48348, Bug #11756433)

The MySQL code base was changed to permit use of the C++ Standard Library and to enable exceptions and runtime type information (RTTI). This change has the following implications:

Security Enhancement: Replication: The START SLAVE statement now accepts USER and PASSWORD options. By default, MySQL native authentication is used, and the user name and password are stored in the master.info repository. This behavior can be overridden by additionally specifying the name (DEFAULT_AUTH) and location (PLUGIN_DIR) of an authentication plugin when issuing START SLAVE. .

As part of this change, warnings are now issued in the following cases:

See also Section 6.3.6, “Pluggable Authentication”. (Bug #13083642)

Performance: InnoDB: The process of deallocating the InnoDB Adaptive Hash Index was made faster, during shutdown or when turning off the AHI with the statement:

SET GLOBAL innodb_adaptive_hash_index=OFF;

(Bug #13006367, Bug #62487)

Performance: InnoDB: This fix improves the performance of instrumentation code for InnoDB buffer pool operations. (Bug #12950803, Bug #62294)

Performance: InnoDB: This fix improved the efficiency and concurrency of freeing pages in the InnoDB buffer pool when performing a DROP TABLE for an InnoDB table when the innodb_file_per_table option is enabled.

This change is most noticeable for systems with large buffer pools. During the drop operation, one traversal of the buffer pool memory structure is changed from the LRU list (the entire buffer pool) to the flush list (a much smaller structure). The LRU scanning is reduced, but not entirely eliminated. The buffer pool mutex is also released periodically, so that if the drop operation takes significant time, other threads can proceed concurrently. (Bug #11759044, Bug #51325)

Incompatible Change: Replication: The statements in the following list are now marked as unsafe for statement-based replication. This is due to the fact that each of these statements depends on the results of a SELECT statement whose order cannot always be determined. When using STATEMENT logging mode, a warning is issued in the binary log for any of these statements; when using MIXED logging mode, the statement is logged using the row-based format.

When upgrading, you should note the use of these statements in your applications, keeping in mind that a statement that inserts or replaces rows obtained from a SELECT can take up many times as much space in the binary log when logged using row-based format than when only the statement itself is logged. Depending on the number and size of the rows selected and inserted (or replaced) by any such statements, the difference in size of the binary log after the logging of these statements is switched from statement-based to row-based can potentially be several orders of magnitude. See Section 16.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”. (Bug #11758262, Bug #50439)

Incompatible Change: Previously, “Aborted connection” errors were written to the error log based on the session value of log_warnings, which permitted users with minimal privileges to cause many messages to be written to the log unless restricted by the MAX_CONNECTIONS_PER_HOUR resource limit. Now this logging is based on the global log_warnings variable. There are no remaining uses of the session log_warnings variable, so it has been removed and the variable now has only a global value. (Bug #53466, Bug #11761014)

Important Change: InnoDB: If an ALTER TABLE statement failed for an InnoDB table due to an error code from an underlying file-renaming system call, InnoDB could lose track of the .ibd file for the table. This issue only occurred when the innodb_file_per_table configuration option was enabled, and when the low-level error persisted through thousands of retry attempts. In MySQL 5.1, this issue applied to the InnoDB Plugin but not the built-in InnoDB storage engine.

For example, if you encounter an error like the following:

mysql> alter table sb2 add column d2 int;
ERROR 1025 (HY000): Error on rename of './sbtest/#sql-1eb9_1' to 
'./sbtest/sb2' (errno: -1) 

you might be able to access the #sql* table by copying a .frm file from a table with an identical schema. The table name to use for the .frm filewould be `sbtest.#mysql50##sql-1eb9_1` in the preceding example. (Bug #12884631, Bug #62146)

Important Change: Replication: Setting an empty user in a CHANGE MASTER TO statement caused an invalid internal result and is no longer permitted. Trying to use MASTER_USER='' or setting MASTER_PASSWORD while leaving MASTER_USER unset causes the statement to fail with an error. (Bug #13427949)

InnoDB: An internal deadlock could occur within InnoDB, on a server doing a substantial amount of change buffering for DML operations, particularly DELETE statements. (Bug #13340047)

InnoDB: Fixed a compilation problem that affected the InnoDB source code with gcc 4.6.1. The affected InnoDB source file was btr/btr0cur.c. (Bug #13116045)

InnoDB: Querying the INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS table could cause the server to halt with an assertion error, in debug builds only. (Bug #12960058)

InnoDB: Valgrind errors when building with the settings innodb_checksum_algorithm=innodb and innodb_checksum_algorithm=crc32 were fixed. (Bug #12939557)

InnoDB: Unused functions were removed from the internal InnoDB code related to mini-transactions, to clarify the logic. (Bug #12626794, Bug #61240)

InnoDB: Lookups using secondary indexes could give incorrect matches under a specific set of conditions. The conditions involve an index defined on a column prefix, for a BLOB or other long column stored outside the index page, with a table using the Barracuda file format. (Bug #12601439, Bug #12543666)

InnoDB: An UPDATE statement for an InnoDB table could hang. The issue affects tables using the Barracuda file format and having multiple indexes on column prefixes. The size of an undo log record could exceed the page size, even though the total size of the column prefixes was less than the page size (usually 16KB). In MySQL 5.5 and higher, this error is now reported using the new code ER_UNDO_RECORD_TOO_BIG. In MySQL 5.1 with the InnoDB Plugin, this error is reported using the existing code ER_TOO_BIG_ROWSIZE. (Bug #12547647)

InnoDB: This fix corrects cases where the MySQL server could hang or abort with a long semaphore wait message. (This is a different issue than when these symptoms occurred during a CHECK TABLE statement.) (Bug #11766591, Bug #59733)

InnoDB: Issuing INSERT...ON DUPLICATE KEY statements for InnoDB tables from concurrent threads could cause a deadlock, particularly with the INSERT...ON DUPLICATE KEY UPDATE form. The problem could also be triggered by issuing multiple INSERT IGNORE statements. The fix avoids deadlocks caused by the same row being accessed by more than one transaction. Deadlocks could still occur when multiple rows are inserted and updated simultaneously by different transactions in inconsistent order; those types of deadlocks require the standard error handling on the application side, of re-trying the transaction. (Bug #11759688, Bug #52020, Bug #12842206)

Partitioning: CHECKSUM TABLE returned 0 for a partitioned table unless the statement was used with the EXTENDED option. (Bug #11933226, Bug #60681)

Partitioning: Error 1214 (ER_TABLE_CANT_HANDLE_FT), given when trying to use a FULLTEXT index with a partitioned table, displayed the misleading text The used table type doesn't support FULLTEXT indexes was misleading and has been replaced with Error 1752 (ER_FULLTEXT_NOT_SUPPORTED_WITH_PARTITIONING) which shows the more accurate FULLTEXT index is not supported for partitioned tables. (Bug #11763825, Bug #56590)

Partitioning: Using ALTER TABLE to remove partitioning from a valid MyISAM table could corrupt it. (Bug #52599, Bug #11760213)

Replication: The value set for the slave_parallel_workers system variable (or the corresponding --slave-parallel-workers server option) was not always honored correctly; in such cases a random value was used. (Bug #13334470)

Replication: Execution of LOAD DATA on a MyISAM table having an after-insert trigger which wrote into an InnoDB table caused multi-threaded statement-based replication to abort with error 1742 (Cannot execute the current event group in the parallel mode). (Bug #12982188)

Replication: Several warnings and informational messages were revised for typographic errors and clarity. (Bug #12947248, Bug #12978113)

Replication: When a statement containing a large number of rows to be applied on a slave table that does not contain a primary key, a considerable amount of time can be needed to find and change all the rows that are to be changed. The current fix helps diagnose this issue by printing a message to the error log if the execution time for a given statement replicated using row-based replication takes more than 60 seconds. log_warnings must be greater than 1 for this message to be printed to the error log. (Bug #11760927, Bug #53375)

Replication: mysqlbinlog --hexdump printed the last row of the hex dump incorrectly, in two ways:

(Bug #11747887, Bug #34386)

Replication: A replication master could send damaged events to slaves after the binary log disk on the master became full. To correct this issue, only complete events are now pushed by the master dump thread to the slave I/O thread. In addition, the error text that the master sends to the slave when an incomplete event is found now states that the incomplete event may have been caused by running out of disk space on the master, and provides coordinates of the first and the last event bytes read. (Bug #11747416, Bug #32228)

References: See also Bug #64035, Bug #13593869.

Replication: --replicate-rewrite-db=from_name->to_name did not work correctly when the name of the source database (from_name) consisted of only a single character. (Bug #34332, Bug #11747866)

With InnoDB change buffering enabled and innodb_page_size set to an 8K or 4K page size, an UPDATE statement could fail if a column being updated contained a value longer than 1/8th of the page size. (Bug #13336585)

An incorrect InnoDB assertion could cause the server to halt. This issue only affected debug builds. The assertion referenced the source file btr0pcur.ic and the variable cursor->pos_state. (Bug #13358468)

A derived table with more than 64 columns caused a server crash. (Bug #13354889)

Writes to the slow log involved a call to thd->current_utime() even if no log entries ended up being written, unnecessarily reducing performance. (Bug #13326965)

Access privileges were checked for each stored program instruction, even if the instruction used no tables, resulting in reduced performance. (Bug #13251277)

The error message for ER_EVENT_CANNOT_ALTER_IN_THE_PAST was incorrect. (Bug #13247871)

Rounding DBL_MAX returned DBL_MAX, not 'inf'. (Bug #13261955)

For materialized temporary tables, a missing key length check could cause incorrect query results. (Bug #13261277)

During the table-opening process, memory was allocated and later freed that was needed view loading, even for statements that did not use views. These unnecessary allocation and free operations are no longer done. (Bug #13116518)

Subqueries with OUTER JOIN could return incorrect results if the subquery referred to a column from another SELECT. (Bug #13068506)

mysql_plugin returned the wrong error code from failed server bootstrap execution. (Bug #12968567)

mysql_plugin mishandled the --plugin-ini, --mysqld, and --my-print-defaults options under some circumstances. (Bug #12968815)

The Performance Schema nested some network I/O events within the wrong statement. (Bug #12981100)

Internal conversion of zero to binary and back could yield a result with incorrect precision. (Bug #12911710)

With index condition pushdown enabled, STRAIGHT_JOIN queries could produce incorrect results. (Bug #12822678, Bug #12724899)

Valgrind warnings generated by filesort operations were fixed. (Bug #12856915)

An IN-to-EXISTS subquery transformation could yield incorrect results if the outer value list contained NULL. (Bug #12838171)

The result of ROUND() was incorrect for certain numbers. (Bug #12744991)

A warning resulting from use of SPACE() referred to REPEAT() in the warning message. (Bug #12735829)

IN and EXISTS subqueries with DISTINCT and ORDER BY could return incorrect results. (Bug #12709738)

Several improvements were made to the libedit library bundled with MySQL distributions, and that is available for all platforms that MySQL supports except Windows.

(Bug #12605400, Bug #12613725, Bug #12618092, Bug #12624155, Bug #12617651, Bug #12605388)

SELECT SQL_BUFFER_RESULT query results included too many rows if a GROUP BY clause was optimized away. (Bug #12578908)

mysqldump --all-databases did not dump the replication log tables. (They could be dumped only by naming them explicitly when invoking mysqldump, and using the --master-data option.)

As a result of the fix for this problem, it is now possible to execute statements requiring read locks on the replication log tables at any time, while any statements requiring a write lock on either or both of these tables are disallowed whenever replication is in progress. For more information, see Section 16.2.2, “Replication Relay and Status Logs”. (Bug #12402875, Bug #60902)

mysqld_safe did not properly check for an already running instance of mysqld. (Bug #11878394)

For a lower_case_table_names value of 1 or 2 and a database having a mixed-case name, calling a stored function using a fully qualified name including the database name failed. (Bug #60347, Bug #11840395)

The help message for mysql_install_db did not indicate that it supports the --defaults-file, --defaults-extra-file and --no-defaults options. (Bug #58898, Bug #11765888)

myisampack could create corrupt FULLTEXT indexes when compressing tables. (Bug #53646, Bug #11761180)

For debug builds, an assertion could be raised for ALTER statements that performed a RENAME operation. This occurred for storage engine handlertons that exposed the HTON_FLUSH_AFTER_RENAME flag. (Bug #38028, Bug #11749050)

An assertion designed to detect zero-length sort keys also was raised when the entire key set fit in memory. (Bug #58200, Bug #11765254)

An ALTER TABLE that included an ADD ... AFTER operation to add a new column after a column that had been modified earlier in the statement failed to find the existing column. (Bug #34972, Bug #11748057)

A linking problem prevented the FEDERATED storage engine plugin from loading. (Bug #40942, Bug #11750417)

When a join operation contained a view, the optimizer sometimes failed to associate the view's WHERE clause with the first table or view in a join when it was possible to do so, resulting in a less efficient query. (Bug #59696, Bug #11766559)

A multiple-table UPDATE statement required the UPDATE privilege on a view which was only read if the view was processed using the merge algorithm. (Bug #59957, Bug #11766767)

If index condition pushdown access was chosen and then abandoned, some variables were not cleared, leading to incorrect query results. (Bug #62533)

For FEDERATED tables, loss of connection to the remote table during some insert operations could cause a server crash. (Bug #34660, Bug #11747970)

mysql_install_db printed the --skip-grant-tables server option as --skip-grant in one of its error messages. (Bug #58534, Bug #11765553)

A query that selected a GROUP_CONCAT() function result could return different values depending on whether an ORDER BY of the function result was present. (Bug #41090, Bug #11750518)

Subqueries could return incorrect results when materialization was enabled. (Bug #40037, Bug #11749901, Bug #12705660, Bug #12908058)

InnoDB used incorrect identifier quoting style in an error message that resulted in an error if a user followed the suggestion in the message. (Bug #49556, Bug #11757503)

OPTIMIZE TABLE could corrupt MyISAM tables if myisam_use_mmap was enabled. (Bug #49030, Bug #11757032)

ARCHIVE tables with NULL columns could cause server crashes or become corrupt under concurrent load. (Bug #51252, Bug #11758979)

During optimization, ZEROFILL values may be converted to string constants. However, CASE expressions did not handle switching data types after the planning stage, leading to CASE finding a null pointer instead of its argument. (Bug #57135, Bug #11764313)

Deadlock could occur when these four things happened at the same time: 1) An old dump thread was waiting for the binary log to grow. 2) The slave server that replicates from the old dump thread tried to reconnect. During reconnection, the new dump thread tried to kill the old dump thread. 3) A KILL statement tried to kill the old dump thread. 4) An INSERT statement caused a binary log rotation. (Bug #56299, Bug #11763573)

The estimate of space required for filesort operations could be too high, resulting in inefficient initialization. (Bug #37359, Bug #11748783)

If a plugin was uninstalled, thread local variables for plugin variables of string type with wth PLUGIN_VAR_MEMALLOC flag were not freed. (Bug #56652, Bug #11763882)

An assertion was raised when selecting from a view that selects from a view that used a user-defined function that had been deleted. (Bug #59546, Bug #11766440)

mysql_upgrade did not upgrade the system tables or create the mysql_upgrade_info file when run with the --write-binlog or --skip-write-binlog option. (Bug #60223, Bug #11827359)

Concurrent access to ARCHIVE tables could cause corruption. (Bug #42784, Bug #11751793)

The SQL_BIG_RESULT modifier could change the results for queries that included a GROUP BY clause. (Bug #53534, Bug #11761078)

The CMake configuration checks did not properly test whether the C compiler supports the inline keyword. (Bug #61708, Bug #12711108)

For some queries, the index_merge access method was used even when more expensive then ref access. (Bug #32254, Bug #11747423)

Collation for the SPACE() function was determined by the parse time value of the collation_connection system variable (instead of the runtime value), which could give unexpected results from prepared statements, triggers, and stored procedures. (Bug #23637, Bug #11746123)

Replication: MySQL replication now supports a multi-threaded slave executing replication events from the master across different databases in parallel, which can result in significant improvements in application throughput when certain conditions are met. The optimum case is that the data be partitioned per database, and that updates within a given database occur in the same order relative to one another as they do on the master. However, transactions do not need to be coordinated between different databases.

The slave_parallel_workers server system variable (added in this release) sets the number of slave worker threads for executing replication events in parallel. When parallel execution is enabled, the slave SQL thread acts as the coordinator for the slave worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the slave slave can process successive transactions on a given database without waiting for updates on other databases to complete.

Due to the fact that transactions on different databases can occur in a different order on the slave than on the master, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave. This has implications for logging and recovery when using a multi-threaded slave. For information about how to interpret binary logging information when using multi-threading on the slave, see Section 13.7.5.35, “SHOW SLAVE STATUS Syntax”.

These query optimizer improvements were implemented:

(Bug #44802, Bug #11753371, Bug #14295, Bug #11745379)

The Performance Schema has these additions:

If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

For more information, see Chapter 20, MySQL Performance Schema.

Performance: InnoDB: At shutdown, MySQL can record the pages that are cached in the InnoDB buffer pool, then reload those same pages upon restart. This technique can help to quickly reach consistent throughput after a restart, without a lengthy warmup period. This preload capability uses a compact save format and background I/O to minimize overhead on the MySQL server. The basic dump/restore capability is enabled through the configuration options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup. Related configuration options such as innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now offer extra flexibility for advanced users to configure the MySQL server for different workloads. See Section 14.2.5.2.7, “Faster Restart by Preloading the InnoDB Buffer Pool” for details. (Bug #11765816, Bug #58819)

Performance: InnoDB: Improved concurrency for extending InnoDB tablespace files, which could prevent stalls on busy systems with many tables that use that innodb_file_per_table setting. (Bug #11763692, Bug #56433)

Performance: InnoDB: You can improve the efficiency of the InnoDB checksum feature by specifying the configuration option innodb_checksum_algorithm=crc32, which turns on a faster checksum algorithm. This option replaces the innodb_checksums option. Data written using the old checksum algorithm (option value innodb) is fully upward-compatible; tablespaces modified using the new checksum algorithm (option value crc32) cannot be downgraded to an earlier version of MySQL that does not support the innodb_checksum_algorithm option. See Section 14.2.5.2.6, “Fast CRC32 Checksum Algorithm” for details. (Bug #11757757, Bug #49852)

Performance: InnoDB: This feature optionally moves the InnoDB undo log out of the system tablespace into one or more separate tablespaces. The I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. This feature is controlled by the configuration options innodb_undo_directory, innodb_undo_tablespaces, and innodb_undo_logs (formerly known as innodb_rollback_segments). Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces.

MySQL instances configured this way are not downward-compatible; older versions of MySQL cannot access the undo logs that reside in their own tablespace.

For details, see Section 14.2.5.2.3, “Separate Tablespaces for InnoDB Undo Logs”.

Performance: InnoDB: Work continues to offload flush operations from the InnoDB main thread, doing them in the page_cleaner thread instead. The latest changes to the the buffer pool flushing algorithms can improve performance for some I/O-bound workloads, particularly in configurations with multiple buffer pool instances. You control this feature by adjusting the settings for the innodb_lru_scan_depth and innodb_flush_neighbors configuration options. To find the optimal settings, test each combination of the above settings with both the Adaptive Hash Index and the Doublewrite Buffer turned on and off. See Section 14.2.5.2.8, “Improvements to Buffer Pool Flushing” for more details.

Performance: InnoDB: The InnoDB thread-scheduling code has been enhanced to work better with greater than 16 threads. Where possible, atomic instructions are used. You control this feature by setting the configuration option innodb_thread_concurrency to a non-zero value, and adjusting the value of innodb_adaptive_max_sleep_delay. See Section 14.2.5.2.14, “Changes Regarding Thread Concurrency” for details.

Performance: InnoDB: The code that detects deadlocks in InnoDB transactions has been modified to use a fixed-size work area rather than a recursive algorithm. The resulting detection operation is faster as a result. You do not need to do anything to take advantage of this enhancement. For details, see Section 14.2.5.2.5, “Non-Recursive Deadlock Detection”.

Incompatible Change: In the audit plugin interface, the event_class member was removed from the mysql_event_general structure and the calling sequence for the notification function was changed. Originally, the second argument was a pointer to the event structure. The function now receives this information as two arguments: an event class number and a pointer to the event. Corresponding to these changes, MYSQL_AUDIT_INTERFACE_VERSION was increased to 0x0300.

The plugin_audit.h header file, and the NULL_AUDIT example plugin in the plugin/audit_null directory were modified per these changes. See Section 22.2.4.8, “Writing Audit Plugins”.

Important Change: Replication: The RESET SLAVE statement has been extended with an ALL keyword. In addition to deleting the master.info, relay-log.info, and all relay log files, RESET SLAVE ALL also clears all connection information otherwise held in memory following execution of RESET SLAVE. (Bug #11809016)

InnoDB: InnoDB now permits concurrent reads while creating a secondary index. (Bug #11853126)

References: See also Bug #11751388, Bug #11784056, Bug #11815600.

InnoDB: The InnoDB redo log files now have a maximum combined size of 512GB, increased from 4GB. You can specify the larger values through the innodb_log_file_size option. (Bug #11765780, Bug #58779)

InnoDB: InnoDB tables can now be created with character sets whose collation ID is greater than 255. This capability opens up InnoDB tables for use with a range of user-defined character sets. MySQL's predefined character sets have previously been limited to a maximum of 255, and now that restriction is lifted. See Section 14.2.6.3, “2-Byte Collation IDs for InnoDB Tables” for details.

Replication: MySQL 5.6.1 added timestamps to the error messages shown in the Last_IO_Error and Last_SQL_Error columns of the output of SHOW SLAVE STATUS. Now these timestamps are shown in separate columns of their own, named Last_IO_Error_Timestamp and Last_SQL_Error_Timestamp, respectively. (Bug #11765599, Bug #58584)

References: See also Bug #43535, Bug #11752361.

Replication: BEGIN, COMMIT, and ROLLBACK statements are now cached along with the statements instead of being written when the cache is flushed to the binary log. This change does not affect DDL statements—which are written into the statement cache, then immediately flushed—or Incident events (which, along with Rotate events, are still written directly to the binary log).

References: See also Bug #57275, Bug #11764443.

Following EXPLAIN EXTENDED, a change has been made to the transformed query displayed by SHOW WARNINGS. Each SELECT part now is preceded by the id value from the associated EXPLAIN output row. This makes it easier to see the correspondence between those rows and parts of the transformed query. For example, this query:

EXPLAIN EXTENDED SELECT 36 FROM DUAL

results in:

/* select#1 */ select 36 from dual

And this query:

EXPLAIN EXTENDED SELECT a FROM t
WHERE a IN (SELECT b FROM u UNION SELECT c from v)

results in:

/* select#1 */ select a from t where a in (/* select#2 */
select b from u union /* select#3 */ select c from v);

(Bug #13035597)

Several memory allocation calls were eliminated, resulting in improved performance. (Bug #12552221)

CMake configuration support on Linux now provides a boolean ENABLE_GCOV option to control whether to include support for gcov. (Bug #12549572)

Previously, Performance Schema instrumentation for both the binary log and the relay log used these instruments:

wait/io/file/sql/binlog 
wait/io/file/sql/binlog_index 
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index 
wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond 

Now instrumentation for the relay log uses these instruments, which makes it possible to distinguish binary log and relay log events:

wait/io/file/sql/relaylog 
wait/io/file/sql/relaylog_index 
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index 
wait/synch/cond/sql/MYSQL_RELAY_LOG::update_cond 

(Bug #59658, Bug #11766528)

The server now exposes SSL certificate expiration dates through the Ssl_server_not_before and Ssl_server_not_after status variables. Both variables have values in ANSI time format (for example, Sep 12 16:22:06 2013 GMT), or are blank for non-SSL connections. (Bug #57648, Bug #11764778)

When invoked with the --auto-generate-sql option, mysqlslap dropped the schema specified with the --create-schema option at the end of the test run, which may have been unexpected by the user. mysqlslap no longer drops the schema, but has a new --create-and-drop-schema option that both creates and drops a schema. (Bug #58090, Bug #11765157)

Previously, TEMPORARY tables created with CREATE TEMPORARY TABLES had the default storage engine unless the definition included an explicit ENGINE option. (The default engine is the value of the default_storage_engine system variable.) Since MySQL 5.5.5, when the default storage engine was changed from the nontransactional MyISAM engine to the transactional InnoDB engine, TEMPORARY tables have incurred the overhead of transactional processing.

To permit the default storage engine for TEMPORARY tables to be set independently of the default engine for permanent tables, the server now supports a default_tmp_storage_engine system variable. For example, to create TEMPORARY tables as nontransactional tables by default, start the server with --default_tmp_storage_engine=MyISAM. The storage engine for TEMPORARY tables can still be specified on an individual basis by including an ENGINE option in table definitions. (Bug #49232, Bug #11757216)

A new server option, --plugin-load-add, complements the --plugin-load option. --plugin-load-add adds a plugin or plugins to the set of plugins to be loaded at startup. The argument format is the same as for --plugin-load. --plugin-load-add can be used to avoid specifying a large set of plugins as a single long unwieldy --plugin-load argument.

--plugin-load-add can be given in the absence of --plugin-load, but any instance of --plugin-load-add that appears before --plugin-load. has no effect because --plugin-load resets the set of plugins to load.

This change affects the output of mysqld --verbose --help in that a value for plugin-load is no longer printed. (Bug #59026, Bug #11766001)

The mysql client program now has a --binary-mode option that helps when processing mysqlbinlog output that may contain BLOB values. By default, mysql translates \r\n in statement strings to \n and interprets \0 as the statement terminator. --binary-mode disables both features. It also disables all mysql commands except charset and delimiter in non-interactive mode (for input piped to mysql or loaded using the source command). (Bug #33048, Bug #11747577)

Previously, for MySQL binaries linked against OpenSSL, if an SSL key file supplied to the MySQL server or a MySQL client program (using the --ssl-key option) was protected by a passphrase, the program would prompt the user for the passphrase. This is now also the case for MySQL binaries linked against yaSSL. (Bug #44559, Bug #11753167)

The NULL_AUDIT example plugin in the plugin/audit_null directory has been updated to count instances of events in the MYSQL_AUDIT_CONNECTION_CLASS event class. See Section 22.2.4.8, “Writing Audit Plugins”.

The max_allowed_packet system variable now controls the maximum size of parameter values that can be sent with the mysql_stmt_send_long_data() C API function.

For temporary tables created with the CREATE TEMPORARY TABLE statement, the privilege model has changed.

Previously, the CREATE TEMPORARY TABLES privilege enabled users to create temporary tables with the CREATE TEMPORARY TABLE statement. However, other operations on a temporary table, such as INSERT, UPDATE, or SELECT, required additional privileges for those operations for the database containing the temporary table, or for the nontemporary table of the same name.

To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation was to create a database dedicated to the use of temporary tables. Then for that database, a user could be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.

Now, the CREATE TEMPORARY TABLES privilege enables users to create temporary tables with CREATE TEMPORARY TABLE, as before. However, after a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

One implication of this change is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it. (Bug #27480, Bug #11746602)

The Windows installer now creates an item in the MySQL menu named MySQL command line client - Unicode. This item invokes the mysql client with properties set to communicate through the console to the MySQL server using Unicode. It passes the --default-character-set=utf8 option to mysql and sets the font to the Lucida Console Unicode-compatible font. See Section 4.5.1.6.1, “Unicode Support on Windows”.

MySQL now includes support for manipulating IPv6 network addresses and for validating IPv4 and IPv6 addresses:

IS_IPV4() is more strict than INET_ATON() about what constitutes a valid IPv4 address, so it may be useful for applications that need to perform strong checks against invalid values. Alternatively, use INET6_ATON() to convert IPv4 addresses to internal form and check for a NULL result (which indicates an invalid address). INET6_ATON() is equally strong as IS_IPV4() about checking IPv4 addresses.

Client programs now display more information for SSL errors to aid in diagnosis and debugging of connection problems. (Bug #21287, Bug #11745920)

The undocumented --all option for perror has been removed. Also, perror no longer displays messages for BDB error codes.

The following items are deprecated and will be removed in a future MySQL release. Where alternatives are shown, applications should be updated to use them.

A new utility, mysql_plugin, enables MySQL administrators to manage which plugins a MySQL server loads. It provides an alternative to manually specifying the --plugin-load option at server startup or using the INSTALL PLUGIN and UNINSTALL PLUGIN statements at runtime. See Section 4.4.4, “mysql_plugin — Configure MySQL Server Plugins”.

mysqld now has an --ignore-db-dir option that tells the server to ignore a given name for purposes of the SHOW DATABASES statement or INFORMATION_SCHEMA tables. For example, if a MySQL configuration locates the data directory at the root of a file system on Unix, the system might create a lost+found directory there that the server should ignore. Starting the server with --ignore-db-dir=lost+found causes that name not to be listed as a database.

To specify more than one name, use this option multiple times, once for each name. Specifying the option with an empty value (that is, as --ignore-db-dir=) resets the directory list to the empty list.

Instances of this option given at server startup are used to set the ignore_db_dirs system variable.

In addition to directories named by --ignore-db-dir, directories having a name that begins with a period are ignored. (Bug #22615, Bug #11746029)

MySQL binaries linked against OpenSSL (but not yaSSL) now support certificate revocation lists for SSL connections:

(Bug #31224, Bug #11747191)

Statement logging has been modified so that passwords do not appear in plain text. Passwords in statements such as CREATE USER or GRANT are rewritten not to appear literally in statement text, for the general query log, slow query log, and binary log.

Password rewriting can be suppressed for the general query log by starting the server with the --log-raw option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use.

Some plugins operate in such a matter that they should be loaded at server startup, and not loaded or unloaded at runtime. The plugin API now supports marking plugins this way. The st_mysql_plugin structure now has a flags member, which can be set to the OR of the applicable flags. The PLUGIN_OPT_NO_INSTALL flag indicates that the plugin cannot be loaded at runtime with the INSTALL PLUGIN statement. This is appropriate for plugins that must be loaded at server startup with the --plugin-load option. The PLUGIN_OPT_NO_UNINSTALL flag indicates that the plugin cannot be unloaded at runtime with the UNINSTALL PLUGIN statement.

The new member changes the interface, so the plugin interface version, MYSQL_PLUGIN_INTERFACE_VERSION, has been incremented from 0x0102 to 0x0103. Plugins that require access to the new member must be recompiled to use version 0x0103 or higher.

Security Fix: Bug #59533 was fixed.

Performance: InnoDB: This fix improves the performance of operations on VARCHAR(N) columns in InnoDB tables, where N is declared as a large value but the actual string values in the table are short. (Bug #12835650)

Performance: InnoDB: The mechanism that InnoDB uses to detect if the MySQL server is idle was made more accurate, to avoid slowdown due to flush operations that normally occur when no other activity is taking place. The mechanism now considers that the server is not idle if there are pending read requests for the InnoDB buffer pool. (Bug #11766123, Bug #59163)

Incompatible Change: For socket I/O, an optimization for the case when the server used alarms for timeouts could cause a slowdown when socket timeouts were used instead.

The fix for this issue results in several changes:

(Bug #54790, Bug #11762221, Bug #36225, Bug #11762221, Bug #51244, Bug #11758972)

Incompatible Change: Handling of a date-related assertion was modified. A consequence of this change is that several functions become more strict when passed a DATE() function value as their argument and reject incomplete dates with a day part of zero. These functions are affected: CONVERT_TZ(), DATE_ADD(), DATE_SUB(), DAYOFYEAR(), LAST_DAY(), TIMESTAMPDIFF(), TO_DAYS(), TO_SECONDS(), WEEK(), WEEKDAY(), WEEKOFYEAR(), YEARWEEK().

It was later determined that stricter handling is unnecessary for LAST_DAY(), which was reverted to permit a zero day part in MySQL 5.6.5.

References: See also Bug #13458237.

InnoDB: Replication: Trying to update a column, previously set to NULL, of an InnoDB table with no primary key caused replication to fail with Can't find record in 'table' on the slave. (Bug #11766865, Bug #60091)

InnoDB: The DATA_LENGTH column in the INFORMATION_SCHEMA.TABLES table now correctly reports the on-disk sizes of tablespaces for InnoDB compressed tables. (Bug #12770537)

InnoDB: A failed CREATE INDEX operation for an InnoDB table could result in some memory being allocated but not freed. This memory leak could affect tables created with the ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED setting. (Bug #12699505)

InnoDB: With the configuration settings innodb_file_per_table=1 and innodb_file_format=Barracuda, inserting a column value greater than half the page size, and including that column in a secondary index, could cause a crash when that column value was updated. (Bug #12637786)

InnoDB: The underlying tables to support the InnoDB persistent statistics feature were renamed and moved into the mysql database. innodb.table_stats became mysql.innodb_table_stats, and innodb.index_stats became mysql.innodb_index_stats. (Bug #12604399)

InnoDB: The server could halt if InnoDB interpreted a very heavy I/O load for 15 minutes or more as an indication that the server was hung. This change fixes the logic that measures how long InnoDB threads were waiting, which formerly could produce false positives. (Bug #11877216, Bug #11755413, Bug #47183)

InnoDB: With the setting lower_case_table_names=2, inserts into InnoDB tables covered by foreign key constraints could fail after a server restart. (Bug #11831040, Bug #60196, Bug #60909)

InnoDB: If the MySQL Server crashed immediately after creating an InnoDB table, attempting to access the table after restart could cause another crash. The issue could occur if the server halted after InnoDB created the primary index for the table, but before the index definition was recorded in the MySQL metadata. (Bug #11766824, Bug #60042)

InnoDB: If the server crashed while an XA transaction was prepared but not yet committed, the transaction could remain in the system after restart, and cause a subsequent shutdown to hang. (Bug #11766513, Bug #59641)

InnoDB: The MySQL server could hang during CREATE TABLE, OPTIMIZE TABLE, or ALTER TABLE or other DDL operation that performs a table copy for an InnoDB table, if such operations were performed by multiple sessions simultaneously. The error was reported as:

InnoDB: Error: semaphore wait has lasted > 600 seconds

(Bug #11760042, Bug #52409)

InnoDB: With the setting lower_case_table_names=2, inserts into InnoDB tables covered by foreign key constraints could fail after a server restart. This is a similar problem to the foreign key error in Bug #11831040 / Bug #60196 / Bug #60909, but with a different root cause and occurring on Mac OS X.

Partitioning: The internal get_partition_set() function did not take into account the possibility that a key specification could be NULL in some cases. (Bug #12380149)

Partitioning: Auto-increment columns of partitioned tables were checked even when they were not being written to. In debug builds, this could lead to a server crash. (Bug #11765667, Bug #58655)

Partitioning: When executing a row-ordered retrieval index merge, the partitioning handler used memory from that allocated for the table, rather than that allocated to the query, causing table object memory not to be freed until the table was closed. (Bug #11766249, Bug #59316)

Partitioning: Attempting to use ALTER TABLE ... EXCHANGE PARTITION to exchange a view with a (nonexistent) partition of a table that was not partitioned caused the server to crash. (Bug #11766232, Bug #60039)

Partitioning: The UNIX_TIMESTAMP() function was not treated as a monotonic function for purposes of partition pruning. (Bug #11746819, Bug #28928)

Partitioning: A problem with a previous fix for poor performance of INSERT ON DUPLICATE KEY UPDATE statements on tables having many partitions caused the handler function for reading a row from a specific index to fail to store the ID of the partition last used. This caused some statements to fail with Can't find record errors. (Bug #59297, Bug #11766232)

References: This bug is a regression of Bug #52455.

Replication: When using row-based replication and attribute promotion or demotion (see Section 16.4.1.8.2, “Replication of Columns Having Different Data Types”), memory allocated internally for conversion of BLOB columns was not freed afterwards. (Bug #12558519)

Replication: A memory leak could occur when re-creating a missing master info repository, because a new I/O cache used for a reference to the repository was re-created when the repository was re-created, but the previous cache was never removed. (Bug #12557307)

Replication: A mistake in thread cleanup could cause a replication master to crash. (Bug #12578441)

Replication: A race condition could occur between a user thread and the SQL thread when both tried to read the same memory before its value was safely set. This issue has now been corrected.

In addition, internal functions relating to creation of and appending to log events, when storing data, used memory local to the functions which was freed when the functions returned. As part of the fix for this problem, the output of SHOW SLAVE STATUS has been modified such that it no longer refers to files or file names in the accompanying status message, but rather contains one of the messages Making temporary file (append) before replaying LOAD DATA INFILE or Making temporary file (create) before replaying LOAD DATA INFILE. (Bug #12416611)

Replication: The name of the Ssl_verify_server_cert column in the mysql.slave_master_info table was misspelled as Ssl_verify_servert_cert. (Bug #12407446, Bug #60988)

Replication: When mysqlbinlog was invoked using --base64-output=decode-row and --start-position=pos, (where pos is a point in the binary log past the format description log event), a spurious error of the type shown here was generated:

malformed binlog: it does not contain any Format_description_log_event... 

However, since there is nothing unsafe about not printing the format description log event, the error has been removed for this case. (Bug #12354268)

Replication: It is no longer possible to change the storage engine used by the mysql.slave_master_info and mysql.slave_relay_log_info tables while replication is running. This means that, to make replication crash-safe, you must make sure that both of these tables use a transactional storage engine before starting replication.

For more information, see Section 16.2.2, “Replication Relay and Status Logs”, and Options for logging slave status to tables. (Bug #11765887, Bug #58897)

Replication: A failed CREATE USER statement was mistakenly written to the binary log. (Bug #11827392, Bug #60082)

Replication: When a slave requested a binary log file which did not exist on the master, the slave continued to request the file regardless. This caused the slave's error log to be flooded with low-level EE_FILENOTFOUND errors (error code 29) from the master. (Bug #11745939, Bug #21437)

Replication: Retrying a transaction on the slave could insert extra data into nontransactional tables. (Bug #11763126, Bug #55789)

References: See also Bug #11763471, Bug #56184.

Replication: A transaction was written to the binary log even when it did not update any nontransactional tables. (Bug #11763471, Bug #56184)

References: See also Bug #11763126, Bug #55789.

Replication: Typographical errors appeared in the text of several replication error messages. (The word “position” was misspelled as “postion”.) (Bug #11762616, Bug #55229)

Replication: mysqlbinlog using the --raw option did not function correctly with binary logs from MySQL Server versions 5.0.3 and earlier. (Bug #11763265, Bug #55956)

Replication: Temporary deadlocks in the slave SQL thread could cause unnecessary Deadlock found when trying to get lock; try restarting transaction error messages to be logged on the slave.

Now in such cases, only a warning is logged unless slave_transaction_retries has been exceeded by the number of such warnings for a given transaction. (Bug #11748510, Bug #36524)

Replication: Processing of corrupted table map events could cause the server to crash. This was especially likely if the events mapped different tables to the same identifier, such as could happen due to Bug #56226.