Incompatible Change: Scheduled events now use the session time zone that is current when a CREATE EVENT or ALTER EVENT statement executes is used to interpret times specified in the event definition (rather than UTC as in previous releases). The session time zone becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. 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.

For retrievals from INFORMATION_SCHEMA.EVENTS or SHOW EVENTS, times previously displayed using UTC now use the event time zone. (Bug#16420)

Important Change: Replication: 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 TO statement.

See also Bug#21490.

Important Change: The CREATE EVENT and ALTER EVENT statements now support a DEFINER clause, similar to that used in the CREATE TRIGGER statement.

See Section 12.1.11, “CREATE EVENT Syntax”, for detailed information. (Bug#16425)

MySQL 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.

See Section 17.4.17, “ndb_restore — Restore a MySQL Cluster Backup”, for more information. (Bug#24363)

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)

Added the --service-startup-timeout option for mysql.server to specify how long to wait for the server to start. If the server does not start within the timeout period, mysql.server exits with an error. (Bug#26952)

Prefix lengths for columns in SPATIAL indexes can no longer 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 the innodb_stats_on_metadata system variable to enable control over whether InnoDB performs statistics gathering when metadata statements are executed. See Section 13.6.3, “InnoDB Startup Options and System Variables”. (Bug#26598)

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

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

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)

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 12.2.8.2, “Index Hint Syntax”. (Bug#21174)

Added the --secure-file-priv option for mysqld, which limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT ... INTO OUTFILE statements to work only with files in a given directory. (Bug#18628)

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

Added the thread_handling system variable to control whether the server use a single thread or one thread per connection. The --one-thread option now is deprecated; use --thread_handling=one-thread instead.

Statements such as GRANT that change the mysql database indirectly are logged as statements regardless of the value of binlog_format.

Added the read-only hostname system variable, which the server sets at startup to the server host name.

Online (noncopying) ADD INDEX and DROP INDEX operations are now performed on dynamic (variable-width) columns. Renaming of NDB and MyISAM tables and of columns in such tables is now performed without copying or locking the tables. As a result, these operations are now performed much more quickly than previously.

See Section 12.1.7, “ALTER TABLE Syntax”, Section 12.1.13, “CREATE INDEX Syntax”, and Section 12.1.24, “DROP INDEX Syntax”, for more information.

Data manipulation statements such as INSERT that change data in mysql database tables directly are logged according to the settings of the binlog_format system variable.

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 12.2.8.2, “Index Hint Syntax”.

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)

Important Note: The parser accepted invalid code in SQL condition handlers, leading to server crashes or unexpected execution behavior in stored programs. Specifically, the parser allowed a condition handler to refer to labels for blocks that enclose the handler declaration. This was incorrect because block label scope does not include the code for handlers declared within the labeled block.

The parser now rejects this invalid construct, but if you perform a binary upgrade (without dumping and reloading your databases), existing handlers that contain the construct are still invalid and should be rewritten even if they appear to function as you expect.

To find affected handlers, use mysqldump to dump all stored procedures and functions, triggers, and events. Then attempt to reload them into an upgraded server. Handlers that contain illegal label references will be rejected.

For more information about condition handlers and writing them to avoid invalid jumps, see Section 12.7.4.2, “DECLARE for Handlers”. (Bug#26503)

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

MySQL 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)

MySQL 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)

MySQL 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)

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

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

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

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

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

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

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

This regression was introduced by Bug#14612.

MySQL 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)

MySQL 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)

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

MySQL 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)

MySQL 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)

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

Replication: 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#26116, Bug#25507)

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

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

Replication: 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.

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. (Bug#24432)

Replication: 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)

Replication: 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)

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

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)

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

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

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

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

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

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

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

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

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

MyISAM tables converted to ARCHIVE were excessively large. (Bug#27533)

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

An INSERT ... ON DUPLICATE KEY UPDATE statement might modify values in a table but not flush affected data from the query cache, causing subsequent selects to return stale results. This made the combination of query cache plus ON DUPLICATE KEY UPDATE very unreliable. (Bug#27210)

See also Bug#27006, Bug#27033.

This regression was introduced by Bug#19978.

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)

See also Bug#27210, Bug#27006.

This regression was introduced by Bug#19978.

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 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)

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

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)

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

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)

SSL connections failed on Windows. (Bug#26678)

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)

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

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)

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

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

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

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

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

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

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)

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

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)

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)

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

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)

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

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

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)

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

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

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

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

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

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)

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

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

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

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

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

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

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)

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)

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

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

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

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.

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. (Bug#24532)

SHOW ENGINE MUTEX STATUS failed to produce an Unknown table engine error.

See Section 12.4.5.16, “SHOW ENGINE Syntax”. (Bug#24392)

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)

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

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

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

When using certain server SQL modes, the mysql.proc table was not created by mysql_install_db. (Bug#23669)

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

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

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

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

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)

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

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

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)

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)

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)

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#20713, Bug#8407)

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 conditions checked by the optimizer to allow use of indexes in IN predicate calculations were unnecessarily tight and were relaxed. (Bug#20420)

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

The creation of MySQL system tables was not checked for by mysql-test-run.pl. (Bug#20166)

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)

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 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)

While preparing prepared statements, the server acquired unnecessary table write locks. (Bug#18326)

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)

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)

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

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)