Incompatible Change: Previously, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format. (Bug#12268)

Where a GROUP BY query uses a grouping column from the query's SELECT clause, MySQL now issues a warning. This is done because the SQL standard states that any grouping column must unambiguously reference a column of the table resulting from the query's FROM clause, and allowing columns from the SELECT clause to be used as grouping columns is a MySQL extension to the standard.

By way of example, consider the following table:

CREATE TABLE users (
  userid INT NOT NULL PRIMARY KEY,
  username VARCHAR(25),
  usergroupid INT NOT NULL
);

MySQL allows you to use the alias in this query:

SELECT usergroupid AS id, COUNT(userid) AS number_of_users
FROM users
GROUP BY id;

However, the SQL standard requires that the column name be used, as shown here:

SELECT usergroupid AS id, COUNT(userid) AS number_of_users
FROM users
GROUP BY usergroupid;

Queries such as the first of the two shown above will continue to be supported in MySQL; however, beginning with MySQL 5.0.8, using a column alias in this fashion will generate a warning. Note that in the event of a collision between column names and/or aliases used in joins, MySQL attempts to resolve the conflict by giving preference to columns arising from tables named in the query's FROM clause. (Bug#11211)

Using prepared statements within a stored routine (PREPARE, EXECUTE, DEALLOCATE) could cause the client connection to be dropped after the routine returned. In addition, executing a statement which called a function deallocating the same statement caused the server to crash. This is prevented by disabling dynamic SQL within stored routines.

(Bug#10975, Bug#10605)

See also Bug#7115

Added support for B'10' syntax for bit literal. (Bug#10650)

MEMORY tables now support indexes of up to 500 bytes. See Section 13.4, “The MEMORY (HEAP) Storage Engine”. (Bug#10566)

Expanded on information provided in general log and slow query log for prepared statements. (Bug#8367, Bug#9334)

Some data definition statements (CREATE TABLE where the table was not a temporary table, TRUNCATE TABLE, DROP DATABASE, and CREATE DATABASE) were not being written to the binary log after a ROLLBACK. This also caused problems with replication.

(Bug#6883)

New SQL_MODE - NO_ENGINE_SUBSTITUTION Prevents automatic substitution of storage engine when the requested storage engine is disabled or not compiled in. (Bug#6877)

Security Fix: On Windows systems, a user with any of the following privileges

on *.* could crash mysqld by issuing a USE LPT1; or USE PRN; command. In addition, any of the commands USE NUL;, USE CON;, USE COM1;, or USE AUX; would report success even though the database was not in fact changed.

(Bug#9148)

MySQL Cluster: Setting TransactionInactiveTimeout = 0 did not result in an infinite timeout. (Bug#11290)

MySQL Cluster: mysqld processes did not reconnect to the cluster following a restart of ndb_mgmd. (Bug#11221)

MySQL Cluster: Insert records were incorrectly applied by ndb_restore, thus making restoring from backup inconsistent if the binlog contained inserts. (Bug#11166)

MySQL Cluster: A DELETE performed as part of a transaction caused an erroneous result. (Bug#11133)

MySQL Cluster: Connections between data nodes and management nodes were not closed following shutdown of ndb_mgmd. (Bug#11132)

MySQL Cluster: The ndb_mgm client's SHOW command displayed incorrect output after master data node failure. (Bug#11050)

MySQL Cluster: When using dynamically allocated ports on Linux, the cluster would hang on initial startup. (Bug#10893)

MySQL Cluster: Not allowing sufficient parallelism in the cluster's configuration (for example, by setting NoOfTransactions too small) caused ndb_restore to fail without providing any error messages. (Bug#10294)

MySQL Cluster: Running ndb_select_count crashed the cluster when running on Red Hat Enterprise 4/64-bit/Opteron. (Bug#10058)

MySQL Cluster: Data nodes failed to restart on 64-bit Solaris. (Bug#9025)

Multiple range accesses in a subquery cause server crash. (Bug#11487)

A CAST() value could not be included in a VIEW. (Bug#11387)

Server crashed when using GROUP BY on the result of a DIV operation on a DATETIME value. (Bug#11385)

INSERT INTO SELECT FROM view produced incorrect result when using ORDER BY. (Bug#11298)

Possible NULL values in BLOB columns could crash the server when a BLOB was used in a GROUP BY query. (Bug#11295)

An outer join with an ON condition that evaluated to false could return an incorrect result. (Bug#11285)

An outer join with an empty derived table (a result from a subquery) returned no result. (Bug#11284)

CAST( ... AS DECIMAL) didn't work for strings. (Bug#11283)

Corrected a problem with IFNULL() returning an incorrect result on 64-bit systems. (Bug#11235)

The SHOW INSTANCE OPTIONS command in MySQL Instance Manager displayed option values incorrectly for options for which no value had been given. (Bug#11200)

The default hostname for MySQL server was always mysql. (Bug#11174)

Some internal functions did not take into account that, for multi-byte character sets, CHAR columns could exceed 255 bytes and VARCHAR columns could exceed 65,535 bytes, which could cause the server to crash. (Bug#11167)

Fixed locking problems for multiple-statement DELETE statements performed within a stored routine, such as incorrectly locking a to-be-modified table with a read lock rather than a write lock. (Bug#11158)

Fixed a portability problem testing for crypt() support that caused compilation problems when using OpenSSL/yaSSL on HP-UX and Mac OS X. (Bug#11150, Bug#10675)

The NULLIF() function could produce incorrect results if the first argument was NULL. (Bug#11142)

mysqld_safe would sometimes fail to remove the pid file for the old mysql process after a crash. As a result, the server would fail to start due to a false A mysqld process already exists... error. (Bug#11122)

Fixed 64 bit compiler warning for packet length in replication. (Bug#11064)

Calling a stored procedure that made use of an INSERT ... SELECT ... UNION SELECT ... query caused a server crash. (Bug#11060)

sql_data_access column of routines table of INFORMATION_SCHEMA was empty. (Bug#11055)

SELECT DISTINCT queries or GROUP BY queries without MIN() or MAX() could return inconsistent results for indexed columns. (Bug#11044)

A CREATE TABLE db_name.tbl_name LIKE ... statement would crash the server when no database was selected. (Bug#11028)

On Windows, mysqlshow did not interpret wildcard characters properly if they were given in the table name argument. (Bug#10947)

The hostname cache was not working. (Bug#10931)

A three byte buffer overflow in the client functions caused improper exiting of the client when reading a command from the user. (Bug#10841)

The mysql client would output a prompt twice following input of very long strings, because it incorrectly assumed that a call to the _cgets() function would clear the input buffer. (Bug#10840)

Setting @@SQL_MODE = NULL caused an erroneous error message. (Bug#10732)

When using a cursor with a prepared statement, the first execution returned the correct result but was not cleaned up properly, causing subsequent executions to return incorrect results. (Bug#10729)

Converting a VARCHAR column having an index to a different type (such as TINYTEXT) gave rise to an incorrect error message.

Note that this bugfix induces a slight change in the behavior of indexes: If an index is defined to be the same length as a field (or is left to default to that field's length), and the length of the field is later changed, then the index will adopt the new length of the field. Previously, the size of the index did not change for some field types (such as VARCHAR) when the field type was changed. (Bug#10543)

InnoDB: Pad UTF-8 VARCHAR columns with 0x20. Pad UCS2 CHAR columns with 0x0020. (Bug#10511)

InnoDB: Enforce maximum CHAR_LENGTH() of UTF-8 data in ON UPDATE CASCADE. (Bug#10409)

SELECT * FROM table returned incorrect results when called from a stored procedure, where table had a primary key. (Bug#10136)

The granting and revocation of privileges on a stored routine was performed when running the server with --skip-grant-tables even after the statement SET @@global.automatic_sp_privileges=1; was executed. (Bug#9993)

Fixed a problem where a stored procedure caused a server crash if the query cache was enabled. (Bug#9715)

Fixed a problem resolving table names with lower_case_table_names=2 when the table name lettercase differed in the FROM and WHERE clauses. (Bug#9500)

SHOW CREATE DATABASE INFORMATION_SCHEMA returned an “unknown database” error. (Bug#9434)

On 64-bit Solaris 9, the cluster timed out and crashed after the first query was made. (Bug#8918)

SELECT DISTINCT ... GROUP BY constant returned multiple rows (it should return a single row). (Bug#8614)

An issue with index merging could cause suboptimal index merge plans to be chosen when searching by indexes created on DATE columns. The same issue caused the InnoDB storage engine to issue the warning using a partial-field key prefix in search. (Bug#8441)

The mysqlhotcopy script was not parsing the output of SHOW SLAVE STATUS correctly when called with the --record_log_pos option. (Bug#7967)

Fixed hang/crash with Boolean full-text search where a query contained more query terms that one-third of the query length (it could be achieved with truncation operator: 'a*b*c*d*'). (Bug#7858)

When used in defining a view, the TIME_FORMAT() function failed with calculated values, for example, when passed the value returned by SEC_TO_TIME(). (Bug#7521)

Fixed column name generation in VIEW creation to ensure there are no duplicate column names. (Bug#7448)

An ORDER BY clause sometimes had no effect on the ordering of a result when selecting specific columns (as opposed to using SELECT *) from a view. (Bug#7422)

Using PREPARE to prepare a statement that invoked a stored routine that executed the prepared statement caused a Packets out of order error the second time the routine was invoked. This is prevented by disabling dynamic SQL within stored routines.

(Bug#7115)

See also Bug#10975, Bug#10605

Selecting from a view defined using SELECT SUM(DISTINCT ...) caused an error; attempting to execute a SELECT * FROM INFORMATION_SCHEMA.TABLES query after defining such a view crashed the server. (Bug#7015)

REFERENCES

SELECT

CREATE TEMPORARY TABLES

GRANT OPTION

CREATE