Incompatible Change: A change has been made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the
PREPAREstatement) and those processed using the binary client/server protocol (using the
mysql_stmt_prepare()C API function).
Previously, changes to metadata of tables or views referred to in a prepared statement could cause a server crash when the statement was next executed, or perhaps an error at execute time with a crash occurring later. For example, this could happen after dropping a table and recreating it with a different definition.
Now metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed.
The server attempts repreparation up to three times. An error occurs if all attempts fail.
Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to
Repreparation is automatic, but to the extent that it occurs, performance of prepared statements is diminished.
Repreparation uses the default database and SQL mode that were in effect for the original preparation.
A status variable,
Com_stmt_reprepare, has been introduced to track the number of repreparations.
Along with this change to statement repreparation, the default value of the
table_definition_cachesystem variable has been increased from 128 to 256. The purpose of this increase is to lessen the chance that prepared statements will need repreparation due to referred-to tables/views having been flushed from the cache to make room for new entries.
An incompatibility with previous versions of MySQL is that a prepared statement may now return a different set of columns or different column types from one execution to the next. For example, if the prepared statement is
SELECT * FROM t1, altering
t1to contain a different number of columns causes the next execution to return a number of columns different from the previous execution.
Older versions of the client library cannot handle this change in behavior. For applications that use prepared statements with the new server, an upgrade to the new client library is strongly recommended. (Bug #27420, Bug #27430, Bug #27690)
Important Change: Some changes were made to
CHECK TABLE ... FOR UPGRADEand
REPAIR TABLEwith respect to detection and handling of tables with incompatible
.frmfiles (files created with a different version of the MySQL server). These changes also affect mysqlcheck because that program uses
REPAIR TABLE, and thus also mysql_upgrade because that program invokes mysqlcheck.
If your table was created by a different version of the MySQL server than the one you are currently running,
CHECK TABLE ... FOR UPGRADEindicates that the table has an
.frmfile with an incompatible version. In this case, the result set returned by
CHECK TABLEcontains a line with a
Table upgrade required. Please do "REPAIR TABLE `
tbl_name`" to fix it!
.frmfile to the current version.
If you use
REPAIR TABLE ...USE_FRMand your table was created by a different version of the MySQL server than the one you are currently running,
REPAIR TABLEwill not attempt to repair the table. In this case, the result set returned by
REPAIR TABLEcontains a line with a
Failed repairing incompatible .FRM file.
Previously, use of
REPAIR TABLE ...USE_FRMwith a table created by a different version of the MySQL server risked the loss of all rows in the table.
mysql_upgrade now has a
--tmpdiroption to enable the location of temporary files to be specified. (Bug #36469)
mysqldump now adds the
LOCALqualifier to the
FLUSH TABLESstatement that is sent to the server when the
--master-dataoption is enabled. This prevents the
FLUSH TABLESstatement from replicating to slaves, which is disadvantageous because it would cause slaves to block while the statement executes. (Bug #35157)
References: See also Bug #38303.
Important Change: The server no longer issues warnings for truncation of excess spaces for values inserted into
CHARcolumns. This reverts a change in the previous release that caused warnings to be issued. (Bug #30059)
References: See also Bug #32575.
Replication: When flushing tables, there was a slight chance that the flush occurred between the processing of one table map event and the next. Since the tables were opened one by one, subsequent locking of tables would cause the slave to crash. This problem was observed when replicating
InnoDBtables, when executing multi-table updates, and when a trigger or a stored routine performed an (additional) insert on a table so that two tables were effectively being inserted into in the same statement. (Bug #36197)
CREATE VIEWstatements containing extended comments were not written to the binary log correctly, causing parse errors on the slave. Now, all comments are stripped from such statements before being written to the binary log. (Bug #32575)
References: See also Bug #36570.
On Windows 64-bit systems, temporary variables of
longtypes were used to store
ulongvalues, causing key cache initialization to receive distorted parameters. The effect was that setting
key_buffer_sizeto values of 2GB or more caused memory exhaustion to due allocation of too much memory. (Bug #36705)
UPDATEstatements that used a temporary table could fail to update all qualifying rows or fail with a spurious duplicate-key error. (Bug #36676)
mysqltest ignored the value of
--tmpdirin one place. (Bug #36465)
When updating an existing instance (for example, from MySQL 5.0 to 5.1, or 5.1 to 6.0), the Instance Configuration Wizard unnecessarily prompted for a
rootpassword when there was an existing
rootpassword. (Bug #36305)
Conversion of a
FLOAT ZEROFILLvalue to string could cause a server crash if the value was
NULL. (Bug #36139)
On Windows, the installer attempted to use JScript to determine whether the target data directory already existed. On Windows Vista x64, this resulted in an error because the installer was attempting to run the JScript in a 32-bit engine, which wasn't registered on Vista. The installer no longer uses JScript but instead relies on a native WiX command. (Bug #36103)
mysqltest was performing escape processing for the
--replace_resultcommand, which it should not have been. (Bug #36041)
An error in calculation of the precision of zero-length items (such as
NULL) caused a server crash for queries that employed temporary tables. (Bug #36023)
EXPLAIN EXTENDED, execution of an uncorrelated
INsubquery caused a crash if the subquery required a temporary table for its execution. (Bug #36011)
MERGEstorage engine did a table scan for
SELECT COUNT(*)statements when it could calculate the number of records from the underlying tables. (Bug #36006)
The server crashed inside
NOT INsubqueries with an impossible
HAVINGclause, such as
NOT IN (SELECT ... FROM t1, t2, ... WHERE 0). (Bug #36005)
The Event Scheduler was not designed to work under the embedded server. It is now disabled for the embedded server, and the
event_schedulersystem variable is not displayed. (Bug #35997)
SET GLOBAL debug=''resulted in a Valgrind warning in
DbugParse(), which was reading beyond the end of the control string. (Bug #35986)
The “prefer full scan on clustered primary key over full scan of any secondary key” optimizer rule introduced by Bug #26447 caused a performance regression for some queries, so it has been disabled. (Bug #35850)
The server ignored any covering index used for
refaccess of a table in a query with
ORDER BYif this index was incompatible with the
ORDER BYlist and there was another covering index compatible with this list. As a result, suboptimal execution plans were chosen for some queries that used an
ORDER BYclause. (Bug #35844)
mysql_upgrade did not properly update the
mysql.eventtable. (Bug #35824)
An incorrect error and message was produced for attempts to create a
MyISAMtable with an index (
.MYI) file name that was already in use by some other
MyISAMtable that was open at the same time. For example, this might happen if you use the same value of the
INDEX DIRECTORYtable option for tables belonging to different databases. (Bug #35733)
The combination of
LEFT JOINcould crash the server when the right table is empty. (Bug #35298)
Some binaries produced stack corruption messages due to being built with versions of bison older than 2.1. Builds are now created using bison 2.3. (Bug #34926)
log_outputsystem variable could be set to an illegal value. (Bug #34820)
On Windows 64-bit builds, an apparent compiler bug caused memory overruns for code in
innobase/mem/*. Removed optimizations so as not to trigger this problem. (Bug #34297)
Several additional configuration scripts in the
BUILDdirectory now are included in source distributions. These may be useful for users who wish to build MySQL from source. (See Installing MySQL Using a Development Source Tree, for information about what they do.) (Bug #34291)
FLUSH PRIVILEGESstatement after creating a temporary table in the
mysqldatabase with the same name as one of the MySQL system tables caused the server to crash.Note
While it is possible to shadow a system table in this way, the temporary table exists only for the current user and connection, and does not effect any user privileges.
UNIONconstructs cannot contain
SELECT ... INTOexcept in the final
SELECT. However, if a
UNIONwas used in a subquery and an
INTOclause appeared in the top-level query, the parser interpreted it as having appeared in the
UNIONand raised an error. (Bug #32858)
mysql.serverstable was not created during installation on Windows. (Bug #28680, Bug #32797)
jptest suite was not working. (Bug #28563)
init_time()library function was renamed to
my_init_time()to avoid conflicts with external libraries. (Bug #26294)
The parser used signed rather than unsigned values in some cases that caused legal lengths in column declarations to be rejected. (Bug #15776)