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
(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)
INFORMATION_SCHEMA objects are now reported
SYSTEM VIEW table type.
DESCRIBE on a view after
renaming a column in one of the view's base tables caused the
server to crash.
A query of the form
SHOW TABLE STATUS FROM
db_name WHERE name IN
SHOW ENGINE INNODB
STATUS now can display longer query strings.
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 TABLE STATUS FROM INFORMATION_SCHEMA now
sorts output by table name the same as it does for other
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)
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)
SLEEP() function, which
pauses for the number of seconds given by its argument.
The stability of cursors when used with
InnoDB tables was greatly improved.
(Bug #11309, Bug #11832, Bug #12243)
SHOW OPEN TABLES now supports
MySQL Cluster: Improved error messages related to file system issues. (Bug #11218)
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 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: The wrong error message was displayed when the cluster management server port was closed while a mysqld process was trying to connect. (Bug #10950)
Invalid values in
ndb_mgmd to crash.
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.
MySQL Cluster: When a Disk is full condition occurred, ndbd exited without reporting this condition in the error log. (Bug #12716)
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
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.
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
Columns defined as
TINYINT(1) were redefined
TINYINT(4) when incorporated into a
FLUSH INSTANCES followed by
STOP INSTANCE caused instance manager to
DISTINCT query with a constant value for one of the
columns would return only a single row.
Duplicate instructions in stored procedures resulted in incorrect execution when the optimizer optimized the duplicate code away. (Bug #12168)
rest() macro in
to avoid name clashes with user code.
ROW() contructor returned an incorrect
result when comparison involved
Some subqueries of the form
SELECT ... WHERE ROW(...)
IN ( were being
Improper use of loose index scan in
sometimes caused incorrect query results.
The only valid values for the
option are 0 and 1, but other values were being accepted.
UNION query with
FULLTEXT could cause server crash.
ISO-8601 formatted dates were not being
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.
SHOW TABLE STATUS sometimes
Row_format value of
though such tables always have a format of
NUMERIC_SCALE column of the
should be returned as
0 for integer columns.
It was being returned as
Mishandling of comparison for rows containing
NULL values against rows produced by an
IN subquery could cause a server crash.
Built-in commands for the mysql client, such
now always parsed within files that are read using 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)
STRCMP() was not handled
correctly in views.
A trigger that included a
statement could cause a server crash.
MEMORY tables using
index on 64-bit platforms could produce false table is full
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.
ALTER TABLE did not move the table to
default database unless the new name was qualified with the
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
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
Stored procedures with particularly long loops could crash the server due to a memory leak. (Bug #11247, Bug #12297)
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
could fail with an erroneous “Column
col_name' specified twice”
A data type of
CHAR BINARY was not recognized
as valid for stored routine parameters.
An incorrect conversion from
ulonglong caused indexes not to be used for
BDB tables on HP-UX.
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
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)
TRUNCATE TABLE did not work with
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.
A failure to obtain a lock for a
LOCK IN SHARE
MODE query could result in a server crash.
CREATE_OPTIONS column of
incorrect options for tables in
It was not possible to create a stored function with a spatial return value data type. (Bug #10499)
FLUSH TABLES WITH READ
LOCK combined with
LOCK TABLE ..
WRITE caused deadlock.
SHOW TABLES FROM returned the wrong error
message if the schema specified did not exist.
Using a stored procedure that referenced tables in the
INFORMATION_SCHEMA database would return an
empty result set.
(Bug #10055, Bug #12278)
XA permitted two active transactions to be
started with the same XID.
Users created using an IP address or other alias rather than a
host name listed in
/etc/hosts could not
set their own passwords.
TRANSACTION ISOLATION LEVEL was not working.
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.
InnoDB multi-part primary keys
VARCHAR columns can
result in incorrect results.
Column names in subqueries must be unique, but were not being checked for uniqueness. (Bug #11864)
Added portability check for Intel compiler to address a problem
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)
Creating a view that included the
TIMESTAMPDIFF() function resulted
in a invalid view.
mysql_fix_privilege_tables.sql was missing
a comma, causing a syntax error when executed.
Prepared statement parameters could cause errors in the binary
log if the character set was
VARCHAR columns of
different lengths could produce incorrect results.
It was permitted for a view to depend on a function that referred to a temporary table. (Bug #10970)
SELECT ... INTO within a trigger could cause a server crash.
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
Using cursors and nested queries for the same table, corrupted results were returned for the outer query. (Bug #11909)
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
Selecting from a view after
INSERT statements for the view's
underlying table yielded different results than subsequent
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)
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.