Incompatible change: Scheduled events now use the MySQL server time zone to determine their schedules, rather than UTC as in previous releases. Because of this change, scheduled event metadata now includes time zone information, which can be seen in the TIME_ZONE column of the INFORMATION_SCHEMA.EVENTS table and the Time zone column in the output of the SHOW EVENTS statement. These columns have been added in this release, along with a time_zone column in the mysql.event table. Due to these changes, events created in previous versions of MySQL cannot be created, viewed, or used until mysql.event has been upgraded. (Bug#16420)

Important change: The CREATE EVENT and ALTER EVENT statements now support a DEFINER clause, similar to that used in the CREATE TRIGGER statement. (Bug#16425)

See Section 20.2.1, “CREATE EVENT Syntax”, for detailed information.

Important change: The following options for controlling replication master configuration on a slave are now deprecated.

To change the master configuration on a slave you should use the CHANGE MASTER statement. (See also #21490)

Statements that affect mysql database tables now are written to the binary log using the following rules:

For more details, see Section 6.1.2.4, “Logging Format for Changes to mysql Database Tables”. (Bug#25091)

Prepared statements now use the query cache under the conditions described in Section 5.13.1, “How the Query Cache Operates”. (Bug#735)

Added the --secure-file-priv option for mysql-test-run.pl, which limits the effect of the load_file command for mysqltest and for the LOAD DATA and SELECT ... INTO OUTFILE statements to work with files in a given directory. (Bug#18628)

Added the innodb_stats_on_metadata system variable to enable control over whether InnoDB performs statistics gathering when metadata statements are executed. See Section 14.5.4, “InnoDB Startup Options and System Variables”. (Bug#26598)

Added the hostname system variable, which the server sets at startup to the server hostname.

The syntax for index hints has been extended to enable more fine-grained control over the optimizer's selection of an execution plan for various phases of query processing. See Section 13.2.7.2, “Index Hint Syntax”.

Added the old_mode system variable to cause the server to revert to certain behaviors present in older versions. Currently, this variable affects handling of index hints. See Section 13.2.7.2, “Index Hint Syntax”.

NDB Cluster: Added the --skip-table-check option (short form -s) for ndb_restore, which causes the restoration process to ignore any changes that may have occurred in table schemas after the backup was made. Previously, this was the default behavior. (Bug#24363)

See Section 15.8.3, “ndb_restore — Restore a Cluster Backup”, for more information.

The server now includes a timestamp in error messages that are logged as a result of unhandled signals (such as mysqld got signal 11 messages). (Bug#24878)

Prefix lengths for columns in SPATIAL indexes no longer can be specified. For tables created in older versions of MySQL that have SPATIAL indexes containing prefixed columns, dumping and reloading the table causes the indexes to be created with no prefixes. (The full column width of each column is indexed.) (Bug#26794)

Added a --no-beep option to mysqladmin. It suppresses the warning beep that is emitted by default for errors such as a failure to connect to the server. (Bug#26964)

Incompatible change: INSERT DELAYED statements are not supported for MERGE tables, but the MERGE storage engine was not rejecting such statements, resulting in table corruption. Applications previously using INSERT DELAYED into MERGE table will break when upgrading to versions with this fix. To avoid the problem, remove DELAYED from such statements. (Bug#26464)

Creating a table on one SQL node while in single user mode caused other SQL nodes to crash. (Bug#26997)

NDB Cluster: The output from ndb_restore --print_data was incorrect for a backup made of a database containing tables with TINYINT or SMALLINT columns. (Bug#26740)

NDB Cluster: After putting the cluster in single user mode from one MySQL server, trying to drop an NDB table from a second MySQL server also connected to the cluster would cause the second MySQL server to hang. (Bug#27254)

NDB Cluster (Cluster APIs): You can now use the ndb_mgm_check_connection() function to determine whether a management server is running. See ndb_mgm_check_connection().

NDB Cluster: mysqld could crash shortly after a data node failure following certain DML operations. (Bug#27169)

NDB Cluster: The management client command node_id STATUS displayed the message Node node_id: not connected when node_id was not the node ID of a data node. (Bug#21715)

NDB Cluster (Cluster APIs): NAND and NOR operations with NdbScanFilter did not perform correctly. (Bug#24568)

NDB Cluster (Cluster Replication): The simultaneous failure of a data node and an SQL node could cause replication to fail. (Bug#27005)

NDB Cluster (Disk Data): Under some circumstances, a data node could fail during restart while flushing Disk Data UNDO logs. (Bug#27102)

NDB Cluster (Disk Data): Creating multiple Disk Data tables using different tablespaces could sometimes cause the cluster to fail. (Bug#25992)

NDB Cluster (Disk Data): ALTER TABLE ... ADD COLUMN ... on a Disk Data table moved data for existing non-indexed columns from the tablespace into memory. (Bug#25880)

NDB Cluster (Disk Data): DROP INDEX on a Disk Data table did not always move data from memory into the tablespace. (Bug#25877)

NDB Cluster (Disk Data): When creating a log file group, setting INITIAL_SIZE to less than UNDO_BUFFER_SIZE caused data nodes to crash. (Bug#25743)

NDB Cluster: It was not possible to set LockPagesInMainMemory equal to 0. (Bug#27291)

NDB Cluster: A race condition could sometimes occur if the node acting as master failed while node IDs were still being allocated during startup. (Bug#27286)

NDB Cluster: When a data node was taking over as the master node, a race condition could sometimes occur as the node was assuming responsibility for handling of global checkpoints. (Bug#27283)

NDB Cluster: A delete operation using a scan following by an insert using a scan could cause a data node to fail. (Bug#27203)

NDB Cluster: The same failed request from an API node could be handled by the cluster multiple times, resulting in reduced performance. (Bug#27087)

NDB Cluster: The failure of a data node while restarting could cause other data nodes to hang or crash. (Bug#27003)

NDB Cluster: mysqld processes would sometimes crash under high load. (Bug#26825)

NDB Cluster: When performing an upgrade or downgrade, no specific error information was made available when trying to upgrade data nodes or SQL nodes before upgrading management nodes. (Bug#21296)

NDB Cluster: Some values of MaxNoOfTables caused the error Job buffer congestion to occur. (Bug#19378)

NDB Cluster: An invalid pointer was returned following a FSCLOSECONF signal when accessing the REDO logs during a node restart or system restart. (Bug#26515)

NDB Cluster (Disk Data): A memory overflow could occur with tables having a large amount of data stored on disk, or with queries using a very high degree of parallelism on Disk Data tables. (Bug#26514)

NDB Cluster (Disk Data): Use of a tablespace whose INITIAL_SIZE was greater than 1 GB could cause the cluster to crash. (Bug#26487)

NDB Cluster: Using only the --print_data option (and no other options) with ndb_restore caused ndb_restore to fail. (Bug#26741)

This bug was introduced by the fix for Bug#14612.

NDB Cluster: An infinite loop in an internal logging function could cause trace logs to fill up with Unknown Signal type error messages and thus grow to unreasonable sizes. (Bug#26720)

For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. However, to perform such operations while a LOCK TABLES statement is in effect, the server required you to also lock the requisite system tables explicitly or a lock error occurred:

mysql> LOCK TABLE t1 READ;
Query OK, 0 rows affected (0.02 sec)
mysql> HELP HELP;
ERROR 1100 (HY000) at line 4: Table 'help_topic' was not
locked with LOCK TABLES

Now, the server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described:

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.proc
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement. (Bug#9953)

For a stored procedure containing a SELECT statement that used a complicated join with an ON expression, the expression could be ignored during re-execution of the procedure, yielding an incorrect result. (Bug#20492)

The parser accepted illegal code in SQL exception handlers, leading to a crash at runtime when executing the code. (Bug#26503)

On Windows, debug builds of mysqld could fail with heap assertions. (Bug#25765)

On Windows, debug builds of mysqlbinlog could fail with a memory error. (Bug#23736)

SELECT ... INTO OUTFILE with a long FIELDS ENCLOSED BY value could crash the server. (Bug#27231)

The server could hang during binary log rotation. (Bug#26079)

Increasing the width of a DECIMAL column could cause column values to be changed. (Bug#24558)

DOUBLE values such as 20070202191048.000000 were being treated as illegal arguments by WEEK(). (Bug#23616)

Replication between master and slave would infinitely retry binary log transmission where the max_allowed_packet on the master was larger than that on the slave if the size of the transfer was between these two values. (Bug#23775)

Setting event_scheduler=1 or event_scheduler=ON caused the server to crash if the server had been started with --skip-grant-tables. Starting the server with --skip-grant-tables now causes event_scheduler to be set to DISABLED automatically, overriding any other value that may have been set. (Bug#26807)

SHOW CREATE EVENT failed to display the STARTS and ENDS clauses for an event defined with STARTS NOW(), ENDS NOW(), or both. (Bug#26429)

Invalid optimization of pushdown conditions for queries where an outer join was guaranteed to read only one row from the outer table led to results with too few rows. (Bug#26963)

For INSERT ... ON DUPLICATE KEY UPDATE statements on tables containing AUTO_INCREMENT columns, LAST_INSERT_ID() was reset to 0 if no rows were successfully inserted or changed. “Not changed” includes the case where a row was updated to its current values, but in that case, LAST_INSERT_ID() should not be reset to 0. Now LAST_INSERT_ID() is reset to 0 only if no rows were successfully inserted or touched, whether or not touched rows were changed. (Bug#27033)

This bug was introduced by the fix for Bug#19978.

AFTER UPDATE triggers were not activated by the update part of INSERT ... ON DUPLICATE KEY UPDATE statements. (Bug#27006)

This bug was introduced by the fix for Bug#19978.

For INSERT ... ON DUPLICATE KEY UPDATE statements where some AUTO_INCREMENT values were generated automatically for inserts and some rows were updated, one auto-generated value was lost per updated row, leading to faster exhaustion of the range of the AUTO_INCREMENT column. (Bug#24432)

Because the original problem can affect replication (different values on master and slave), it is recommended that the master and its slaves be upgraded to the current version.

For an INSERT statement that should fail due to a column with no default value not being assigned a value, the statement succeeded with no error if the column was assigned a value in an ON DUPLICATE KEY UPDATE clause, even if that clause was not used. (Bug#26261)

A result set column formed by concatention of string literals was incomplete when the column was produced by a subquery in the FROM clause. (Bug#26738)

When using the result of SEC_TO_TIME() for time value greater than 24 hours in an ORDER BY clause, either directly or through a column alias, the rows were sorted incorrectly as strings. (Bug#26672)

If the server was started with --skip-grant-tables, Selecting from INFORMATION_SCHEMA tables causes a server crash. (Bug#26285)

Adding ORDER BY clause to a query on an InnoDB table could cause the statement to return no result if the optimizer chose a covering index that enabled it to skip the ORDER BY. (Bug#24778)

IN ((subquery)), IN (((subquery))), and so forth, are equivalent to IN (subquery), which is always interpreted as a table subquery (so that it is allowed to return more than one row). MySQL was treating the “over-parenthesized” subquery as a single-row subquery and rejecting it if it returned more than one row. This bug primarily affected automatically generated code (such as queries generated by Hibernate), because humans rarely write the over-parenthesized forms. (Bug#21904)

For MERGE tables defined on underlying tables that contained a short VARCHAR column (shorter than four characters), using ALTER TABLE on at least one but not all of the underlying tables caused the table definitions to be considered different from that of the MERGE table, even if the ALTER TABLE did not change the definition. (Bug#26881)

If a thread previously serviced a connection that was killed, excessive memory and CPU use by the thread occurred if it later serviced a connection that had to wait for a table lock. (Bug#25966)

CURDATE() is less than NOW(), either when comparing CURDATE() directly (CURDATE() < NOW() is true) or when casting CURDATE() to DATE (CAST(CURDATE() AS DATE) < NOW() is true). However, storing CURDATE() in a DATE column and comparing col_name < NOW() incorrectly yielded false. This is fixed by comparing a DATE column as DATETIME for comparisons to a DATETIME constant. (Bug#21103)

A view on a join is insertable for INSERT statements that store values into only one table of the join. However, inserts were being rejected if the inserted-into table was used in a self-join because MySQL incorrectly was considering the insert to modify multiple tables of the view. (Bug#25122)

Expressions involving SUM(), when used in an ORDER BY clause, could lead to out-of-order results. (Bug#25376)

LOAD DATA INFILE sent an okay to the client before writing the binary log and committing the changes to the table had finished, thus violating ACID requirements. (Bug#26050)

Views that used a scalar correlated subquery returned incorrect results. (Bug#26560)

IF(expr, unsigned_expr, unsigned_expr) was evaluated to a signed result, not unsigned. This has been corrected. The fix also affects constructs of the form IS [NOT] {TRUE|FALSE}, which were transformed internally into IF() expressions that evaluated to a signed result. (Bug#24532)

For existing views that were defined using IS [NOT] {TRUE|FALSE} constructs, there is a related implication. The definitions of such views were stored using the IF() expression, not the original construct. This is manifest in that SHOW CREATE VIEW shows the transformed IF() expression, not the original one. Existing views will evaluate correctly after the fix, but if you want SHOW CREATE VIEW to display the original construct, you must drop the view and re-create it using its original definition. New views will retain the construct in their definition.

BENCHMARK() did not work correctly for expressions that produced a DECIMAL result. (Bug#26093)

For MEMORY tables, extending the length of a VARCHAR column with ALTER TABLE might result in an unusable table. (Bug#26080)

For some values of the position argument, the INSERT() function could insert a NUL byte into the result. (Bug#26281)

Creating a table with latin characters in the name caused the output of SHOW FULL TABLES to have ERROR for the table type. (Bug#25081)

Inserting utf8 data into a TEXT column that used a single-byte character set could result in spurious warnings about truncated data. (Bug#25815)

EXPLAIN EXTENDED did not show WHERE conditions that were optimized away. (Bug#22331)

INSERT DELAYED statements inserted incorrect values into BIT columns. (Bug#26238)

For expr IN(value_list), the result could be incorrect if BIGINT UNSIGNED values were used for expr or in the value list. (Bug#19342)

When a TIME_FORMAT() expression was used as a column in a GROUP BY clause, the expression result was truncated. (Bug#20293)

For SUBSTRING() evaluation using a temporary table, when SUBSTRING() was used on a LONGTEXT column, the max_length metadata value of the result was incorrectly calculated and set to 0. Consequently, an empty string was returned instead of the correct result. (Bug#15757)

Use of a GROUP BY clause that referred to a stored function result together with WITH ROLLUP caused incorrect results. (Bug#25373)

Use of a subquery containing GROUP BY and WITH ROLLUP caused a server crash. (Bug#26830)

Use of a subquery containing a UNION with an invalid ORDER BY clause caused a server crash. (Bug#26661)

In certain cases it could happen that deleting a row corrupted an RTREE index. This affected indexes on spatial columns. (Bug#25673)

SSL connections failed on Windows. (Bug#26678)

Added support for --debugger=dbx for mysql-test-run.pl and fixed support for --debugger=devenv, --debugger=DevEnv, and --debugger=/path/to/devenv. (Bug#26792)

X() IS NULL and Y() IS NULL comparisons failed when X() and Y() returned NULL. (Bug#26038)

UNHEX() IS NULL comparisons failed when UNHEX() returned NULL. (Bug#26537)

The REPEAT() function did not allow a column name as the count parameter. (Bug#25197)

On 64-bit Windows, large timestamp values could be handled incorrectly. (Bug#26536)

In some error messages, inconsistent format specifiers were used for the translations in different languages. comp_err (the error message compiler) now checks for mismatches. (Bug#26571)

On Windows, the server exhibited a file-handle leak after reaching the limit on the number of open file descriptors. (Bug#25222)

A reference to a non-existent column in the ORDER BY clause of an UPDATE ... ORDER BY statement could cause a server crash. (Bug#25126)

mysqlimport used a variable of the wrong type for the --use-threads option, which could cause a crash on some architectures. (Bug#23814)

A multiple-row delayed insert with an auto increment column could cause duplicate entries to be created on the slave in a replication environment. (Bug#25507, Bug#26116)

Using mysqlbinlog on a binary log would crash if there were a large number of row-based events related to a single statement. (Bug#25628)

Duplicating the usage of a user variable in a stored procedure or trigger would not be replicated correctly to the slave. (Bug#25167)

User defined variables used within stored procedures and triggers are not replicated correctly when operating in statement-based replication mode. (Bug#20141, Bug#14914)

Loading data using LOAD DATA INFILE may not replicate correctly (due to character set incompatibilities) if the character_set_database variable is set before the data is loaded. (Bug#15126)

DROP TRIGGER statements would not be filtered on the slave when using the replication-wild-do-table option. (Bug#24478)

SHOW ENGINE MUTEX STATUS failed to produce an Unknown table engine error. (Bug#24392)

See Section 13.5.4.12, “SHOW ENGINE Syntax”.

MySQL would not compile when configured using --without-query-cache. (Bug#25075)

When using certain server SQL modes, the mysql.proc table was not created by mysql_install_db. In addition, the creation of this and other MySQL system tables was not checked for by mysql-test-run.pl. (Bug#23669, Bug#20166)

It was not possible to use XPath keywords as tag names for expressions used in the ExtractValue() function. (Bug#24747)

VIEW restrictions were applied to SELECT statements after a CREATE VIEW statement failed, as though the CREATE had succeeded. (Bug#25897)

An INSERT trigger invoking a stored routine that inserted into a table other than the one on which the trigger was defined would fail with a Table '...' doesn't exist referring to the second table when attempting to delete records from the first table. (Bug#21825)

A stored procedure that made use of cursors failed when the procedure was invoked from a stored function. (Bug#25345)

When nesting stored procedures within a trigger on a table, a false dependency error was thrown when one of the nested procedures contained a DROP TABLE statement. (Bug#22580)

When attempting to call a stored procedure creating a table from a trigger on a table tbl in a database db, the trigger failed with ERROR 1146 (42S02): Table 'db.tbl' doesn't exist. However, the actual reason that such a trigger fails is due to the fact that CREATE TABLE causes an implicit COMMIT, and so a trigger cannot invoke a stored routine containing this statement. A trigger which does so now fails with ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger, which makes clear the reason for the trigger's failure. (Bug#18914)

Local variables in stored routines or triggers, when declared as the BIT type, were interpreted as strings. (Bug#12976)

When a stored routine attempted to execute a statement accessing a nonexistent table, the error was not caught by the routine's exception handler. (Bug#8407, Bug#20713)

NOW() returned the wrong value in statements executed at server startup with the --init-file option. (Bug#23240)

Instance Manager did not remove the angel PID file on a clean shutdown. (Bug#22511)

Setting the slow_query_log_file system variable caused log output to go tothe general log, not the slow query log. (Bug#23225)

The server could crash if two or more threads initiated query cache resize operation at moments very close in time. (Bug#23527)

The conditions checked by the optimizer to allow use of indexes in IN predicate calculations were unnecessarily tight and were relaxed. (Bug#20420)

Several deficiencies in resolution of column names for INSERT ... SELECT statements were corrected. (Bug#25831)

Indexes on TEXT columns were ignored when ref accesses were evaluated. (Bug#25971)

The update columns for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE could be assigned incorrect values if a temporary table was used to evaluate the SELECT. (Bug#16630)

A user-defined variable could be assigned an incorrect value if a temporary table was employed in obtaining the result of the query used to determine its value. (Bug#24010)

Queries that used a temporary table for the outer query when evaluating a correlated subquery could return incorrect results. (Bug#23800)

For index reads, the BLACKHOLE engine did not return end-of-file (which it must because BLACKHOLE tables contain no rows), causing some queries to crash. (Bug#19717)

A query of type index_merge, and with a WHERE clause having the form WHERE indexed_column_1=value_1 OR indexed_column_2=value_2 on a partitioned table caused the server to crash. (Bug#26117)