Functionality Added or Changed
Beginning with MySQL 5.0.12, natural joins and joins with
USING, including outer join variants, are
processed according to the SQL:2003 standard. The changes
include elimination of redundant output columns for
NATURAL joins and joins specified with a
USING clause and proper ordering of output
columns. The precedence of the comma operator also now is lower
In addition, a Duplicate column name
error no longer occurs when selecting from a view
SELECT * from a join that uses a
USING clause on tables that have a common
These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see JOIN Syntax. (Bug #6495, Bug #6136, Bug #10972, Bug #9978, Bug #10428, Bug #10646, Bug #6276, Bug #6489, Bug #6558, Bug #9067, Bug #4789, Bug #12065, Bug #13551)
The parsing of the
CLUSTERLOG command by
ndb_mgm was corrected to permit multiple
Replication: Interleaved execution of stored procedures and functions could be written to the binary log incorrectly, causing replication slaves to get out of sync. (Bug #12335)
Replication: Calls to stored procedures were written to the binary log even within transactions that were rolled back, causing them to be executed on replication slaves. (Bug #12334)
A query of the form
SHOW TABLE STATUS FROM
db_name WHERE name IN
DESCRIBE on a view after
renaming a column in one of the view's base tables caused the
server to crash.
If a thread (connection) has tables locked, the query cache is switched off for that thread. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385)
SHOW TABLE STATUS FROM INFORMATION_SCHEMA now
sorts output by table name the same as it does for other
SHOW OPEN TABLES now supports
Recursive triggers are detected and not permitted. Also, within a stored function or trigger, it is not permissible to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Bug #11896, Bug #12644)
INFORMATION_SCHEMA objects are now reported
SYSTEM VIEW table type.
The stability of cursors when used with
InnoDB tables was greatly improved.
(Bug #11309, Bug #11832, Bug #12243)
Trying to drop the default keycache by setting
@@global.key_buffer_size to zero now returns
a warning that the default keycache cannot be dropped.
SHOW ENGINE INNODB
STATUS now can display longer query strings.
SLEEP() function, which
pauses for the number of seconds given by its argument.
MySQL Cluster: When it could not copy a fragment, ndbd exited without printing a message about the condition to the error log. Now the message is written. (Bug #12900)
MySQL Cluster: When a Disk is full condition occurred, ndbd exited without reporting this condition in the error log. (Bug #12716)
MySQL Cluster: Cluster failed to take character set data into account when recomputing hashes (and thus could not locate records for updating or deletion) following a configuration change and node restart. (Bug #12220)
ALTER TABLE statement caused
loss of data stored prior to the issuing of the command.
Invalid values in
ndb_mgmd to crash.
MySQL Cluster: When a schema was detected to be corrupt, ndb neglected to close it, resulting in a file already open error if the schema was opened again later. written. (Bug #12027)
MySQL Cluster: Improved error messages related to file system issues. (Bug #11218)
MySQL Cluster: The wrong error message was displayed when the cluster management server port was closed while a mysqld process was trying to connect. (Bug #10950)
Replication: Some statements executed on a master server caused the SQL thread on a slave to run out of memory. (Bug #12532)
Replication: Trigger and stored procedure execution could break replication. (Bug #12482)
CURRENT_TIME() and values
generated by timestamp columns are now constant for the duration
of a stored function or trigger. This prevents the breaking of
Slave I/O threads were considered to be in the running state
when launched (rather than after successfully connecting to the
master server), resulting in incorrect
SLAVE STATUS output.
DROP DATABASE fails on a
master server due to the presence of a nondatabase file in the
database directory, the master have the database tables deleted,
but not the slaves. To deal with failed database drops, we now
DROP TABLE statements to
the binary log for the tables so that they are dropped on
An optimizer estimate of zero rows for a nonempty
InnoDB table used in a left or right join
could cause incomplete rollback for the table.
mysql_fix_privilege_tables.sql was missing
a comma, causing a syntax error when executed.
Improper use of loose index scan in
sometimes caused incorrect query results.
DISTINCT query with a constant value for one of the
columns would return only a single row.
SHOW TABLES FROM returned the wrong error
message if the schema specified did not exist.
A server crash could result from an update of a view defined as a join, even though the update updated only a single table. (Bug #12569)
COLUMN_DEFAULT column of the
should be returned as
NULL if a column has no
default value. An empty string was being returned if the column
was defined as
ROW() contructor returned an incorrect
result when comparison involved
STRCMP() was not handled
correctly in views.
Selecting from a view defined as a join over many tables could
result in a server crash due to miscalculation of the number of
conditions in the
MEMORY tables using
index on 64-bit platforms could produce false table is full
CREATE_OPTIONS column of
incorrect options for tables in
Mishandling of comparison for rows containing
NULL values against rows produced by an
IN subquery could cause a server crash.
Selecting from a view after
INSERT statements for the view's
underlying table yielded different results than subsequent
InnoDB multi-part primary keys
VARCHAR columns can
result in incorrect results.
rest() macro in
to avoid name clashes with user code.
INFORMATION_SCHEMA as the
default database after failing to execute a stored procedure in
an inaccessible database, the server returned a spurious
ERROR 42000: Unknown database
Users created using an IP address or other alias rather than a
host name listed in
/etc/hosts could not
set their own passwords.
NUMERIC_SCALE column of the
should be returned as
0 for integer columns.
It was being returned as
Creating a view that included the
TIMESTAMPDIFF() function resulted
in a invalid view.
CHECKSUM TABLE statement
returned incorrect results for tables with deleted rows. After
upgrading, users who used stored checksum information to detect
table changes should rebuild their checksum data.
NULL into a
GEOMETRY column for a table that has a
trigger could result in a server crash if the table was
myisampack failed to delete
.TMD temporary files when run with the
A race condition between server threads could cause a crash if one thread deleted a stored routine while another thread was executing a stored routine. (Bug #12228)
Duplicate instructions in stored procedures resulted in incorrect execution when the optimizer optimized the duplicate code away. (Bug #12168)
XA permitted two active transactions to be
started with the same XID.
NULL column definitions were read incorrectly
for inner tables of nested outer joins.
GROUP_CONCAT ignored the
DISTINCT modifier when used in a query
joining multiple tables where one of the tables had a single
A failure to obtain a lock for a
LOCK IN SHARE
MODE query could result in a server crash.
SELECT ... INTO within a trigger could cause a server crash.
Using cursors and nested queries for the same table, corrupted results were returned for the outer query. (Bug #11909)
A query using a
LEFT JOIN, an
IN subquery on the outer table, and an
ORDER BY clause, caused the server to crash
when cursors were enabled.
UNION query with
FULLTEXT could cause server crash.
Some subqueries of the form
SELECT ... WHERE ROW(...)
IN ( were being
Column names in subqueries must be unique, but were not being checked for uniqueness. (Bug #11864)
TRUNCATE TABLE did not work with
mysql_info() C API function
could return incorrect data when executed as part of a
multi-statement that included a mix of statements that do and do
not return information.
A trigger that included a
statement could cause a server crash.
Built-in commands for the mysql client, such
now always parsed within files that are read using the
Added portability check for Intel compiler to address a problem
ALTER TABLE did not move the table to
default database unless the new name was qualified with the
VARCHAR columns of
different lengths could produce incorrect results.
For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380)
Prepared statement parameters could cause errors in the binary
log if the character set was
Columns defined as
TINYINT(1) were redefined
TINYINT(4) when incorporated into a
Stored procedures with particularly long loops could crash the server due to a memory leak. (Bug #11247, Bug #12297)
TRANSACTION ISOLATION LEVEL was not working.
It was permitted for a view to depend on a function that referred to a temporary table. (Bug #10970)
FLUSH INSTANCES followed by
STOP INSTANCE caused instance manager to
User variables were not automatically cast for comparisons, causing queries to fail if the column and connection character sets differed. Now when mixing strings with different character sets but the same coercibility, permit conversion if one character set is a superset of the other. (Bug #10892)
An incorrect conversion from
ulonglong caused indexes not to be used for
BDB tables on HP-UX.
It was not possible to create a stored function with a spatial return value data type. (Bug #10499)
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
could fail with an erroneous “Column
col_name' specified twice”
The only valid values for the
option are 0 and 1, but other values were being accepted.
Using a stored procedure that referenced tables in the
INFORMATION_SCHEMA database would return an
empty result set.
(Bug #10055, Bug #12278)
FLUSH TABLES WITH READ
LOCK combined with
LOCK TABLE ..
WRITE caused deadlock.
A data type of
CHAR BINARY was not recognized
as valid for stored routine parameters.
ISO-8601 formatted dates were not being
On Windows when the
option has been given, the server detects whether AWE support is
available and has been compiled into the server, and displays an
appropriate error message if not.
SHOW TABLE STATUS sometimes
Row_format value of
though such tables always have a format of