This is a milestone release, for use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. (Making a backup before the upgrade is a prudent precaution in any case.)
-
Performance: The server now implements group commit for the binary log: Multiple commits are grouped in memory, then written and flushed to disk as a group rather than individually. This reduces the number of writes and flushes, improving performance of binary logging. Group commit works for all storage engines.
InnoDB
implements some optimizations to take advantage of group commit capability.These system variables were added in conjunction with group commit:
binlog_order_commits
: Whether to commit transactions in the same order they are written to the binary log or permit them to be committed in parallel.binlog_max_flush_queue_time
: How long in microseconds to keep reading transactions from the flush queue before proceeding with the group commit.innodb_flush_log_at_timeout
: Write and flush logs everyN
seconds.
-
This MySQL release implements changes to the default values of several server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are subject to revision in future releases as we gain feedback. (See Changes to Server Defaults.)
In some cases, a parameter has a different fixed default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a fixed value. For example, the setting for
back_log
is its previous default of 50, adjusted up by an amount proportional to the value ofmax_connections
. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.The following table summarizes changes to defaults. For variables that are autosized, the main variable description provides additional detail about the sizing algorithm. See Server System Variables, and InnoDB Startup Options and System Variables. Any of these default settings can be overridden by specifying an explicit value at server startup.
With regard to compatibility with previous releases, the most important changes are:
innodb_file_per_table
is enabled (previously disabled)innodb_checksum_algorithm
isCRC32
(previouslyINNODB
)binlog_checksum
isCRC32
(previouslyNONE
)
Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:
[mysqld] innodb_file_per_table=0 innodb_checksum_algorithm=INNODB binlog_checksum=NONE
Those settings preserve compatibility as follows:
With the new default of
innodb_file_per_table
enabled,ALTER TABLE
operations following an upgrade will moveInnoDB
tables that are in the system tablespace to individual.ibd
files. Usinginnodb_file_per_table=0
will prevent this from happening.Setting
innodb_checksum_algorithm=INNODB
permits binary downgrades after upgrading to this release. With a setting ofCRC32
, InnoDB would use checksumming that older MySQL versions cannot use.With
binlog_checksum=NONE
, the server can be used as a replication master without causing failure of older slaves that do not understand binary log checksums.
Important Change:
INSERT DELAYED
is now deprecated, and will be removed in a future release. UseINSERT
(withoutDELAYED
) instead. (Bug #13985071)The
YEAR(2)
data type is now deprecated because it is problematic.YEAR(2)
columns in existing tables are treated as before, butYEAR(2)
in new or altered tables are converted toYEAR(4)
. Support forYEAR(2)
will be removed entirely in a future MySQL release. For more information, see 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR.
-
The Performance Schema is now enabled by default (the
performance_schema
system variable is enabled by default). To disable it, setperformance_schema=off
at server startup.In addition, the Performance Schema now automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. To see which parameters are sized under this policy, use mysqld --verbose --help and look for those with a default value of −1, or see Performance Schema System Variables.
For each autosized parameter that is not set at server startup (or is set to −1), the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:
max_connections open_files_limit table_definition_cache table_open_cache
To override autosizing for a given parameter, set it a value other than −1 at startup. In this case, the Performance Schema assigns it the specified value.
At runtime,
SHOW VARIABLES
displays the actual values that autosized parameters were set to.If the Performance Schema is disabled, its autosized parameters remain set to −1 and
SHOW VARIABLES
displays −1.
-
These security improvements were implemented:
-
MySQL now provides a method for storing authentication credentials encrypted in an option file named
.mylogin.cnf
. To create the file, use the mysql_config_editor utility. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to a MySQL server. mysql_config_editor writes the.mylogin.cnf
file using encryption so the credentials are not stored as clear text, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable. For more information, see mysql_config_editor — MySQL Configuration Utility.The
.mylogin.cnf
file can contain multiple sets of options, known as “login paths.” This makes it easy to set up multiple “personalities” for connecting to different MySQL servers. Any of these can be selected by name later using the--login-path
option when you invoke a client program. See Command-Line Options that Affect Option-File Handling. -
MySQL now supports stronger encryption for user account passwords, available through an authentication plugin named
sha256_password
that implements SHA-256 password hashing. This plugin is built in, so it is always available and need not be loaded explicitly. For more information, including instructions for creating accounts that use SHA-256 passwords, see SHA-256 Pluggable Authentication.Other changes associated with the introduction of the
sha256_password
plugin:-
The
old_passwords
system variable previously permitted values of 1 or 0 to control whether “old” or “new” MySQL native password hashing was used by theCREATE USER
andGRANT
statements and thePASSWORD()
function. Nowold_passwords
permits a value of 2 to select use of SHA-256 password hashing.NotePreviously,
old_passwords
permitted values ofOFF
orON
as synonyms for 0 or 1. That is no longer true. SHA-256 password hashing (
old_passwords=2
) uses a random salt value, which makes the result fromPASSWORD()
nondeterministic. Consequently, statements that use this function are no longer safe for statement-based replication and cannot be stored in the query cache.If MySQL is built with OpenSSL, RSA encryption can be used to transmit passwords during the client connection process. The
sha256_password_private_key_path
andsha256_password_public_key_path
system variables permit the private and public key files to be named on the server side. TheRsa_public_key
status variable displays the public key value. The mysql and mysqltest clients support a--server-public-key
option permitting the public key file to be specified explicitly when connecting to the server. (This option is implemented through a newMYSQL_SERVER_PUBLIC_KEY
option to themysql_options()
C API function.)
MySQL Connector support: Connectors that use the C client library should work with
sha256_password
with no changes. Connectors that implement the authentication process for themselves must be updated to account for changes in the client/server protocol. -
-
The server now has a
--default-authentication-plugin
option to specify the default plugin to associate with new accounts for which no plugin is named explicitly. Permitted values aremysql_native_password
(use MySQL native passwords; this is the default value) andsha256_password
(use SHA-256 passwords). This option also changes the initialold_passwords
value to be consistent with the password hashing method required by the default plugin, if necessary.NoteIf you use this option to change the default authentication method to a value other than
mysql_native_password
, clients older than MySQL 5.5.7 will no longer be able to connect because they will not understand the change to the authentication protocol. -
The
mysql.user
table now has apassword_expired
column to enable DBAs to expire account passwords and require users to reset their password. The defaultpassword_expired
value is'N'
, but can be set to'Y'
with the newALTER USER
statement. After an account's password has been expired, all operations performed by the account in subsequent connections to the server result in an error until the user issues aSET PASSWORD
statement to establish a new account password. For more information, see ALTER USER Statement, and Server Handling of Expired Passwords.If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change into the
mysql
database.CautionUpdate:
ALTER USER
also set thePassword
column to the empty string, so do not use this statement in 5.6.6. This problem has been fixed in MySQL 5.6.7. -
MySQL now has provision for checking password security:
In statements that assign a password supplied as a cleartext value, the value is checked against the current password policy and rejected if it is weak (the statement returns an
ER_NOT_VALID_PASSWORD
error). This affects theCREATE USER
,GRANT
, andSET PASSWORD
statements. Passwords given as arguments to thePASSWORD()
andOLD_PASSWORD()
functions are checked as well.The strength of potential passwords can be assessed using the new
VALIDATE_PASSWORD_STRENGTH()
SQL function, which takes a password argument and returns an integer from 0 (weak) to 100 (strong).
Both capabilities are implemented by the
validate_password
plugin. If the plugin is not installed, the affected statements andPASSWORD()
andOLD_PASSWORD()
work as before (no password checking), andVALIDATE_PASSWORD_STRENGTH()
always returns 0.The
validate_password
plugin also implements a set of system variables corresponding to the parameters that control password checking. If the plugin is installed, you can modify these variables to configure the password policy.The
validate_password
plugin is written using the MySQL plugin API, which has been extended to support writing password-validation plugins.For more information, see The Password Validation Plugin. For information about writing password-checking plugins, see Writing Password-Validation Plugins.
mysql_upgrade now produces a warning if it finds user accounts with passwords hashed with the older pre-4.1 hashing method. Such accounts should be updated to use more secure password hashing. See Password Hashing in MySQL
(Bug #65461, Bug #14136939)
-
For the
WITH_SSL
CMake option,no
is no longer a permitted value or the default value. The default is nowbundled
. Consequently, MySQL now is always built with SSL support.
-
Incompatible Change: It is now explicitly disallowed to assign the value
DEFAULT
to stored procedure or function parameters or stored program local variables (for example with aSET
statement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertently used this construct. It remains permissible to assignvar_name
= DEFAULTDEFAULT
to system variables, as before, but assigningDEFAULT
to parameters or local variables now results in a syntax error.After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.
Incompatible Change: The
--safe-mode
server option has been removed.Important Change; Partitioning: MySQL nows supports partition lock pruning, which allows for many DDL and DML statements against partitioned tables using
MyISAM
(or another storage engine that employs table-level locking) to lock only those partitions directly affected by the statement. These statements include (but are not limited to) manySELECT
,SELECT ... PARTITION
,UPDATE
,REPLACE
,INSERT
, and other statements. This enhancement improves especially the performance of many such statements when used with tables having many (32 or more) partitions. For a complete list of affected statements with particulars, and other information, see Partitioning and Locking. (Bug #37252, Bug #11748732)-
Important Change; Replication: It is now possible, in the event that a multithreaded slave fails while running with the
--relay-log-recovery
option, to switch it safely to single-threaded mode despite the presence of any gaps with unprocessed transactions in the relay log. To accomplish this, you can now useSTART SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS
to cause the slave SQL threads to run until no more such gaps are found in the relay log. Once this statement has completed, you can change theslave_parallel_workers
system variable, and (if necessary) issue aCHANGE MASTER TO
statement before restarting the slave. (Bug #13893363)References: See also: Bug #13893310.
Important Change; Replication:
INSERT ON DUPLICATE KEY UPDATE
is now marked as unsafe for statement-based replication if the target table has more than one primary or unique key. For more information, see Determination of Safe and Unsafe Statements in Binary Logging. (Bug #58637, Bug #11765650, Bug #13038678)Important Change; Replication: The
SHOW BINARY LOGS
statement (and its equivalentSHOW MASTER LOGS
) may now be executed by a user with theREPLICATION CLIENT
privilege. (Formerly, theSUPER
privilege was necessary to use either form of this statement.)-
Important Change: In MySQL, the
TIMESTAMP
data type differs in nonstandard ways from other data types:TIMESTAMP
columns not explicitly declared with theNULL
attribute are assigned theNOT NULL
attribute. (Columns of other data types permitNULL
values if not explicitly declared with theNOT NULL
attribute.) Setting such a column toNULL
sets it to the current timestamp.The first
TIMESTAMP
column in a table, if not declared with theNULL
attribute or an explicitDEFAULT
orON UPDATE
attribute, is automatically assigned theDEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
attributes.TIMESTAMP
columns following the first one, if not declared with theNULL
attribute or an explicitDEFAULT
attribute, are automatically assignedDEFAULT '0000-00-00 00:00:00'
(the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned'0000-00-00 00:00:00'
and no warning occurs.
Those nonstandard behaviors remain the default for
TIMESTAMP
but now are deprecated and this warning appears at startup:[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
As indicated by the warning, to turn off the nonstandard behaviors, enable the new
explicit_defaults_for_timestamp
system variable at server startup. With this variable enabled, the server handlesTIMESTAMP
as follows instead:TIMESTAMP
columns permitNULL
values if not explicitly declared with theNOT NULL
attribute. Setting such a column toNULL
sets it toNULL
, not the current timestamp.No
TIMESTAMP
column is assigned theDEFAULT CURRENT_TIMESTAMP
orON UPDATE CURRENT_TIMESTAMP
attributes automatically. Those attributes must be explicitly specified.TIMESTAMP
columns declared asNOT NULL
and without an explicitDEFAULT
attribute are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of'0000-00-00 00:00:00'
and a warning occurs. This is similar to how MySQL treats other temporal types such asDATETIME
.
To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of
explicit_defaults_for_timestamp
:-
Bring down the slaves, upgrade them, configure them with the desired value of
explicit_defaults_for_timestamp
, and bring them back up.The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of
explicit_defaults_for_timestamp
) and that operations onTIMESTAMP
columns coming from the master use the oldTIMESTAMP
behavior. Bring down the master, upgrade it, and configure it with the same
explicit_defaults_for_timestamp
value used on the slaves, and bring it back up.
(Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)
-
Performance; InnoDB: Many DDL operations on
InnoDB
tables can now be performed online, without making the tables unavailable for queries. Some operations, such as creating or dropping indexes, even allow DML statements (INSERT
,UPDATE
,DELETE
) on the table while the operation is in progress. A single online DDL operation can also take the place of a sequence of statements, such as severalDROP INDEX
statements,ALTER TABLE ... ADD COLUMN
, and then severalCREATE INDEX
statements. See InnoDB and Online DDL for full details.An additional effect of this change occurs for consistent-read transactions that try to re-read data from a table which was changed by
ALTER TABLE
in another session. Instead of receiving an empty set, the transaction will receive an error (ER_TABLE_DEF_CHANGED
, “Table definition has changed, please retry transaction”). (Bug #58368, Bug #11765404, Bug #11872643, Bug #12325508, Bug #11765266, Bug #60689) Performance; InnoDB: The persistent statistics feature for
InnoDB
tables is now enabled by default, and can be controlled at the level of individual tables. This feature involves the configuration optionsinnodb_stats_persistent
,innodb_stats_auto_recalc
, andinnodb_stats_persistent_sample_pages
, and the clausesSTATS_PERSISTENT
,STATS_AUTO_RECALC
, andSTATS_SAMPLE_PAGES
of theCREATE TABLE
andALTER TABLE
statements. See Configuring Persistent Optimizer Statistics Parameters for usage details.-
Performance; InnoDB: The MySQL server now includes the widely used memcached in-memory caching system, and a plugin that allows fast NoSQL-style access to
InnoDB
tables through the memcached protocol. This access method avoids the overhead of SQL parsing and constructing a query optimization plan. You can store the underlying data in a singleInnoDB
table, or spread it across multiple tables. You can read and write data through bothmemcached
and SQL. For example, you can do fast single-key lookups through memcachedget
calls, and do statistical reports across all the data through SQL.Several configuration options let you fine-tune this system, in particular to balance raw performance against durability and consistency of data. The main new configuration options are
daemon_memcached_option
,daemon_memcached_r_batch_size
,daemon_memcached_w_batch_size
,innodb_api_trx_level
,innodb_api_enable_mdl
, andinnodb_api_enable_binlog
.See InnoDB memcached Plugin for full details.
InnoDB: For systems with constant heavy workloads, or workloads that fluctuate widely, several new configuration options let you fine-tune the flushing behavior for
InnoDB
tables:innodb_adaptive_flushing_lwm
,innodb_max_dirty_pages_pct_lwm
,innodb_max_io_capacity
(changed in subsequent point releases toinnodb_io_capacity_max
), andinnodb_flushing_avg_loops
. These options feed into an improved formula used by theinnodb_adaptive_flushing
option. See Configuring Buffer Pool Flushing.-
InnoDB:
InnoDB
tables now support the notion of “transportable tablespaces”, allowing.ibd
files to be exported from a running MySQL instance and imported into another running instance. TheFOR EXPORT
clause of theFLUSH TABLES
statement writes any unsaved changes fromInnoDB
memory buffers to the.ibd
file. After copying the.ibd
file and a separate metadata file to the other server, you can use theDISCARD TABLESPACE
andIMPORT TABLESPACE
clauses of theALTER TABLE
statement to bring the table data into a different MySQL instance.For more information, see Importing InnoDB Tables.
InnoDB:
InnoDB
now supports theDATA DIRECTORY='
clause of thedirectory
'CREATE TABLE
statement, which permits creating tables outside the data directory. For more information, see Creating Tables Externally.Replication: The
STOP SLAVE
optionSQL_BEFORE_GTIDS
did not function correctly, and theSQL_AFTER_GTIDS
option for the same statement did not function at all. (Bug #13810456)Replication: Added the
slave_rows_search_algorithms
system variable for mysqld, which determines the search algorithms used for finding matches for slave updates whenslave_allow_batching
is enabled, including whether or not table or index hashing is used with searches employing a primary or unique key, some other key, or no key.The Performance Schema has a new system variable,
performance_schema_session_connect_attrs_size
, and new status variable,Performance_schema_session_connect_attrs_lost
. The system variable is the amount of preallocated memory per thread reserved to hold connection attribute key/value pairs. If the aggregate size of connection attribute data sent by a client is larger than this amount, the Performance Schema truncates the attribute data and increments the status variable. See Performance Schema Connection Attribute Tables. (Bug #14076427)-
yaSSL was upgraded from version 1.7.2 to 2.1.4. (Bug #13713205)
References: See also: Bug #13706828.
The optimizer's cost model for disk-sweep Multi-Read Range (DS-MRR) has been improved. The improved cost model makes it more likely that DSMRR will be used for queries that read much data from disk.
Previously, the default value for the
bind_address
system variable was0.0.0.0
, which causes the server to accept TCP/IP connections on all server host IPv4 interfaces. To make it easier to use IPv6 connections without special configuration, the defaultbind_address
value now is*
. This is similar to0.0.0.0
, but causes the server to also accept TCP/IP connections on all IPv6 interfaces if the server host supports IPv6. (Another way to accept IPv4 and IPv6 connections is by usingbind_address=::
, but in this case an error occurs if the server host does not support IPv6.)-
It is now possible for client programs to pass connection attributes to the server in the form of key/value pairs. Attributes are manipulated using the
MYSQL_OPT_CONNECT_ATTR_RESET
andMYSQL_OPT_CONNECT_ATTR_DELETE
options for themysql_options()
C API function, and theMYSQL_OPT_CONNECT_ATTR_ADD
option for the newmysql_options4()
function. Connection attributes are exposed through thesession_connect_attrs
andsession_account_connect_attrs
Performance Schema tables.If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the
performance_schema
database.For more information, see C API Function Reference, and MySQL Performance Schema.
Previously, for semijoin processing the outer query specification was limited to simple table scans or inner joins using comma syntax, and view references were not possible. Now outer join and inner join syntax is permitted in the outer query specification, and the restriction that table references must be base tables has been lifted.
-
To improve scalability by reducing contention among sessions for the global lock on the open tables cache, the cache now can be partitioned into several smaller cache instances. A session now need lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that need to use the cache when many there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)
A new system variable,
table_open_cache_instances
, permits control over the number of cache instances. Each instance has a size oftable_open_cache
/table_open_cache_instances
. By default, the number of instances is 1.Three new status variables provide information about the operation of the open tables cache.
Table_open_cache_hits
andTable_open_cache_misses
indicate the number of hits and misses or lookups in the cache.Table_open_cache_overflows
indicates how many times, after a table is opened or closed, an instance has an unused entry and the size of the instance is larger thantable_open_cache
/table_open_cache_instances
. -
The generic “procedure API” has been removed from the server. This was formerly present as a means of writing server procedures, but went unused except for
PROCEDURE ANALYSE()
. Removing the interface simplifies aspects of the internal procedure representation that were related to code no longer in the server but had a negative effect on its operation, in the sense that these aspects hindered the ability of the optimizer to perform better on more common query types. In addition, this code hindered future optimizer development and its removal will have benefit that development.PROCEDURE ANALYSE()
remains available, but is no longer implemented using a public interface. (For information, see Using PROCEDURE ANALYSE.) One consequence of removing the procedure interface is thatEXPLAIN SELECT ... PROCEDURE ANALYSE()
now works where previously it produced an error.
-
Incompatible Change: Metadata was handled incorrectly for objects such as tables or views that were used in a stored program. Metadata for each such object was gathered at the beginning of program execution, but not updated if DDL statements modified the object during program execution (or modified it between executions of the program if the program remained in the stored program cache). This resulted in mismatches between the actual object structure and the structure the stored program believed the object to have during execution, and caused problems such as data errors or server crashes.
Now metadata changes to objects used in a stored program are detected during execution and affected statements within the program are reparsed so that they use the updated metadata.
Example: Suppose that a stored program executes this statement in a loop and that the columns in the table
t1
are altered during loop execution:SELECT * FROM t1;
Previously, errors occurred because program execution did not detect that
SELECT *
evaluates to a different set of columns after the change. Now the table change is detected and theSELECT
is reparsed to determine the new set of columns.Reparsing occurs for other cases as well, such as t1 being changed from a base table to a view or a
TEMPORARY
table. For more information, see Caching of Prepared Statements and Stored Programs.There is a possible incompatibility regarding the new behavior: Application code that assumed the previous behavior and implemented a workaround may need to be changed.
Other instances of corrected problems:
SELECT *
within a stored program could fail forTEMPORARY
tables created within the program using prepared statements.“Unknown column” errors or bad data could result from changing the set of columns in a table used within a stored program between executions of the program or while the table was used within a program loop. Errors could also occur under similar circumstances for a view if the view definition was changed, for a
TEMPORARY
table if the table was dropped.Failure of triggers to notice metadata changes in objects accessed within the program could cause trigger malfunction.
Failure of a stored program to notice metadata changes in objects accessed within the program could cause replication to fail.
(Bug #61434, Bug #12652835, Bug #55678, Bug #11763018, Bug #64574, Bug #13840615, Bug #33843, Bug #11747732, Bug #33289, Bug #11747626, Bug #33255, Bug #11747619, Bug #33000, Bug #11747566, Bug #27011, Bug #11746530, Bug #33083, Bug #11747581, Bug #32868, Bug #11747537, Bug #12257, Bug #11745236)
Important Change; NDB Cluster: mysqld_safe now traps Signal 13 (
SIGPIPE
) so that this signal no longer kills the MySQL server process. (Bug #33984)-
Performance; InnoDB; Partitioning: The statistics used by the optimizer for queries against partitioned
InnoDB
tables were based only on the first partition of each such table, leading to use of the wrong execution plan. (Bug #13694811)References: This issue is a regression of: Bug #11756867.
Performance; InnoDB: Improved the efficiency of
InnoDB
code with regard to CPU cache coherency. (Bug #14034087)Performance; InnoDB: Improved the efficiency of the system calls to get the system time to record the start time for a transaction. This fix reduces potential cache coherency issues that affected performance. (Bug #13993661)
Performance; InnoDB: Improved the algorithm related to adaptive flushing. This fix increases the rate of flushing in cases where compression is used and the data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061)
Performance; InnoDB: Improved the efficiency of the
COMMIT
operation forInnoDB
tables, by reducing the potential for context switching and acquiring/re-acquiring mutexes while the operation is in progress. (Bug #13989037)Performance; InnoDB: The order in which flushes are performed when the
innodb_flush_neighbors
configuration option is enabled was improved. The algorithm makes the neighbor-flushing technique faster on HDD storage, while reducing the performance overhead on SSD storage. (innodb_flush_neighbors
typically is not needed for SSD hardware.) (Bug #13798956)Performance; InnoDB: This fix improves the speed of
DROP TABLE
forInnoDB
tables by removing a scan of the buffer pool to remove entries for the adaptive hash index. This improvement is most noticeable on systems with very large buffer pools and theinnodb_adaptive_hash_index
option enabled. (Bug #13704145, Bug #64284)-
Performance; Replication: All changes made as part of a given transaction are cached; when the transaction is committed, the contents of this cache are written to the binary log. When using global transaction identifiers, the GTID identifying this transaction must be the first event among all events in the cache belonging to the transaction.
Previously, a portion of the cache was preallocated as a buffer when the transaction began; upon commit it was completed with a valid GTID. However, because it was not possible to perform a seek in the cache, it was necessary to flush it to a temporary file, and then seek within this file. When the cache buffer is not big enough to accommodate all changes comprising a given transaction, it swapped the data to disk, then reinitialized the cache to have the buffer properly filled with the correct data again. The buffer was actually flushed and the cache reinitialized every time a GTID event was written, even in those cases in which all events making up a given transaction fit within the cache buffer, which could negatively impact the performance of binary logging (and thus replication) when using GTIDs.
Now the cache is reinitialized only when it is actually necessary—in other words, only when the cache is in fact swapped to disk.
In addition, the fix for this issue addresses a missing unlock operation when the server failed to write an empty transaction group and reduces the amount of code needed for prepending the GTID to the contents of the cache before flushing the cache to disk. (Bug #13877432)
References: See also: Bug #13738296.
Performance: Within stored programs, the overhead of making statements log friendly was incurred even when the corresponding log was not enabled. (Bug #12884336)
Performance: The
MD5()
andSHA1()
functions had excessive overhead for short strings. (Bug #49491, Bug #11757443, Bug #60227, Bug #14134662)InnoDB; Replication: When binary log statements were replayed on the slave, the
Com_insert
,Com_update
, andCom_delete
counters were incremented byBEGIN
statements initiating transactions affectingInnoDB
tables but not byCOMMIT
statements ending such transactions. This affected these statements whether they were replicated or they were run using mysqlbinlog. (Bug #12662190)InnoDB: Dropping an
InnoDB
temporary table could leave behind the.ibd
file if the table was created with theinnodb_file_per_table
setting enabled. On Windows systems, this could cause an additional problem: repeated attempts to drop the file for 2000 seconds. In addition to resolving the incorrect path name used to drop the file, this fix also limits the retry loop to 10 seconds, for example if the file cannot be removed because it is locked by a backup process. (Bug #14169459)InnoDB: When importing an
InnoDB
tablespace representing a compressed table, unnecessary checksum calculations were being performed. (Bug #14161424)-
InnoDB: If MySQL crashed during an
ALTER TABLE t DISCARD TABLESPACE
operation, it could leaveInnoDB
in a state where it crashes at the next startup. The error message was:InnoDB: Error: a record lock wait happens in a dictionary operation!
(Bug #14146981)
InnoDB: A race condition could cause a crash during an online
CREATE INDEX
statement for anInnoDB
table. This bug only affected very small tables. It required a DML operation to be in progress for the table, affecting the primary key columns, at the same time theCREATE INDEX
statement was issued. (Bug #14117641)InnoDB: An assertion error could occur if an XA transaction was created within a session designated as read-only. (Bug #14108709)
InnoDB: If a row was deleted from an
InnoDB
table, then another row was re-inserted with the same primary key value, an attempt by a concurrent transaction to lock the row could succeed when it should have waited. This issue occurred if the locking select used aWHERE
clause that performed an index scan using a secondary index. (Bug #14100254, Bug #65389)-
InnoDB: This fix improves the accuracy of the data in the
INFORMATION_SCHEMA
tableinnodb_metrics
for systems withinnodb_buffer_pool_instances
set to greater than 1. The improved information applies to the number of pages flushed from the buffer pool, specifically these entries in the table:buffer_flush_batch_total_pages buffer_flush_neighbor_total_pages buffer_flush_adaptive_total_pages buffer_flush_sync_total_pages buffer_flush_background_total_pages buffer_LRU_batch_total_pages
(Bug #14037167)
InnoDB: In a transaction using the
REPEATABLE READ
isolation level, anUPDATE
orDELETE
statement for anInnoDB
table could sometimes overlook rows recently committed by other transactions. As explained in Consistent Nonlocking Reads, DML statements within aREPEATABLE READ
transaction apply to rows committed by other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)InnoDB: During an
ANALYZE TABLE
statement for anInnoDB
table, the server could hang (in non-debug builds), or an assertion error could occur, indicating recursive acquisition of a lock (in debug builds). (Bug #14007109)InnoDB: An assertion could be raised if an
InnoDB
table was moved to a different database usingALTER TABLE ... RENAME
while the database was being dropped byDROP DATABASE
. (Bug #13982017)InnoDB: Querying the
INFORMATION_SCHEMA.INNODB_TRX
or related tables while the server was running a heavyInnoDB
workload could cause a crash, with messages in the error log referring to the functionfetch_data_into_cache_low
. This issue arose during new feature work and only affected MySQL 5.6. (Bug #13966453)InnoDB: Fixes a recently introduced issue with
InnoDB
persistent statistics, that could cause a crash (non-debug builds) or assertion error (debug builds). (Bug #13946118)InnoDB: Including a
%
character in a query using anInnoDB
FULLTEXT
index could cause a crash. (FULLTEXT
indexes forInnoDB
tables are a new feature, still under development.) (Bug #13940669, Bug #64901)-
InnoDB: Using the
KILL
statement to terminate a query could cause an unnecessary message in the error log:[ERROR] Got error -1 when reading table table_name
(Bug #13933132)
InnoDB: When a table was renamed, the
InnoDB
persistent statistics were not associated with the new table name. (Bug #13920437)InnoDB: If the server crashed while dropping an
InnoDB
temporary table or an index on a temporary table, further errors could occur during crash recovery, preventing the server from restarting. (Bug #13913670)InnoDB: A
FULLTEXT
query for anInnoDB
table could filter the search terms incorrectly if a term using the minus operator was followed by another term using the plus operator. (Bug #13907075)InnoDB: The
performance_schema
counters forInnoDB
RW-locks did not record some cases where mini-transactions acquired locks. (Bug #13860722)InnoDB: Deleting a huge amount of data from
InnoDB
tables within a short time could cause the purge operation that removes delete-marked records to stall. This issue could result in unnecessary disk space use, but does not cause any problems with data integrity. If this issue causes a disk space shortage, restart the server to work around it. This issue is only likely to occur on 32-bit platforms. (Bug #13847885)InnoDB: A slave server in a replication configuration could exit while creating an
InnoDB
temporary table. (Bug #13838761)InnoDB: The server could crash when using the
SAVEPOINT
statement in conjunction withInnoDB
tables containingFULLTEXT
indexes. (Bug #13831840)-
InnoDB: With the
innodb_force_recovery
configuration option set to 2 or greater, a shutdown could hang after the message:InnoDB: Waiting for purge thread to be suspended
This issue was introduced during recent changes within the MySQL 5.6 development cycle. (Bug #13830371)
-
InnoDB: Running concurrent bulk inserts on a server with
auto_increment_offset=1
,auto_increment_increment
greater than 1, andinnodb_autoinc_lock_mode=1
could result in intermittent errors like the following, even with the primary key set to auto_increment and omitted from theINSERT
statement:Duplicate entry 'value' for key 'PRIMARY'
The workaround was to set
auto_increment_offset=1
orinnodb_autoinc_lock_mode=0
(“traditional”). (Bug #13817703, Bug #61209) -
InnoDB: The server could halt with an assertion error when DDL and DML operations were run on the same
InnoDB
table simultaneously:InnoDB: Error: a record lock wait happens in a dictionary operation!
This fix stems from the online DDL feature in MySQL 5.6. (Bug #13641926)
-
InnoDB: During an
ALTER TABLE
statement to create a primary key for anInnoDB
table, some column characteristics could be set incorrectly, leading to errors during subsequent queries. The incorrect data could be the maximum length for a column prefix, or the state of theNOT NULL
flag.In MySQL 5.1, this fix applies to the InnoDB Plugin, but not the built-in InnoDB storage engine. (Bug #13641275)
InnoDB: An
ALTER TABLE
statement for anInnoDB
table that dropped one index and create another could fail with an error code 1280, and displaying the wrong index name in the message. (Bug #13029445, Bug #62544)InnoDB: If the
innodb_undo_tablespaces
andinnodb_undo_logs
configuration options were specified to refer to separate undo tablespaces, and the associated tablespaces did not exist, that error condition was not being correctly detected during startup. (Bug #13016100)InnoDB: The error handling and message was improved for attempting to create a foreign key with a column referencing itself. The message suggested a potential problem with the data dictionary, when no such problem existed. (Bug #12902967)
InnoDB: For an
InnoDB
table with a trigger, under the settinginnodb_autoinc_lock_mode=1
, sometimes auto-increment values could be interleaved when inserting into the table from two sessions concurrently. The sequence of auto-increment values could vary depending on timing, leading to data inconsistency in systems using replication. (Bug #12752572, Bug #61579)InnoDB: An
ALTER TABLE
with bothIGNORE
andADD UNIQUE KEY
clauses produced an error if duplicates were found, rather than removing all duplicate rows after the first one. With this fix, theALTER TABLE IGNORE
syntax automatically enables theALGORITHM=COPY
clause if theALTER TABLE
statement creates an index. (Bug #12622150)InnoDB: When data was removed from an
InnoDB
table, newly inserted data might not reuse the freed disk blocks, leading to an unexpected size increase for the system tablespace or.ibd
file (depending on the setting ofinnodb_file_per_table
. TheOPTIMIZE TABLE
could compact a.ibd
file in some cases but not others. The freed disk blocks would eventually be reused as additional data was inserted. (Bug #11766634, Bug #59783)InnoDB: The
CHECK TABLE
statement could fail for a largeInnoDB
table due to a timeout value of 2 hours. For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB, depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the timeout less likely. It also makesInnoDB
recognize the syntaxCHECK TABLE QUICK
, which avoids the possibility of the timeout entirely. (Bug #11758510, Bug #50723)InnoDB: Full-text search in
InnoDB
tried to follow foreign key references without keeping track of which ones it had already seen. With circular and other complex setups, this could loop forever or a very long time, leading to the appearance of the query thread hanging. (Bug #64274, Bug #13701973)-
Partitioning: If a partitioned table
t1
was created using theROW_FORMAT
option, attempting to performALTER TABLE t1 EXCHANGE PARTITION ... WITH TABLE t2
failed with the error Tables have different definitions even if the definition for tablet2
was identical to that fort1
. This occurred because a check was made for an explicitROW_FORMAT
setting in the table definition, and if this was set, the operation was rejected.Now in such cases the row format actually used for each table is checked explicitly and the
EXCHANGE PARTITION
operation is permitted to execute if both row formats are the same. (Bug #11894100) -
Partitioning: The
PARTITION_COMMENT
column of theINFORMATION_SCHEMA.PARTITIONS
table truncated partition comments, displaying only the first 80 characters.As part of the fix for this issue, the maximum length for a partition comment is now set at 1024 characters, and this width is honored by
INFORMATION_SCHEMA.PARTITIONS.PARTITION_COMMENT
. (Bug #11748924, Bug #37728) Replication: When a complete global transaction spanned relay logs such that only its GTID appeared in a given relay log while the body of the transaction (including
BEGIN
andCOMMIT
statements) appeared in the next relay log, the GTID was interpreted incorrectly as belonging to an empty group. (Bug #14136654)Replication: It was possible in some cases when using semisynchronous replication for log rotation to take place before an ongoing transaction was committed or rolled back. (Bug #14123372)
-
Replication: If the relay logs were removed after the server was stopped, without stopping replication first, the server could not be started correctly. (Bug #14029212, Bug #65152)
References: See also: Bug #13971348.
Replication: The
--bootstrap
option for mysqld is used by mysql_install_db when it initializes the system tables. Now, whenever this option is used, GTIDs (see Replication with Global Transaction Identifiers) and replication are automatically disabled. (Bug #13992602)Replication: It was theoretically possible for concurrent execution of more than one instance of
SHOW BINLOG EVENTS
to crash the MySQL Server. (Bug #13979418)Replication: If errors were encountered while trying to initialize the
mysql.slave_master_info
ormysql.slave_relay_log_info
tables, the server refused to start. Now in such cases, the warning message Error while checking replication metadata. This might also happen when doing a live upgrade from a version that did not make use of the replication metadata tables is issued to advise the user that this has happened, but the server is permitted to continue starting. (Bug #13893363)Replication: The text for the error
ER_AUTO_POSITION_REQUIRES_GTID_MODE_ON
referred toAUTO_POSITION = 1
although this should beMASTER_AUTO_POSITION = 1
. The text has been corrected. (Bug #13868465)Replication: A
CHANGE MASTER TO
statement could alter the effective value ofrelay_log_purge
. In addition, therelay_log_recovery
system variable is now read-only, and can be changed only by starting the server with--relay-log-recovery
. (Bug #13840948)-
Replication: When
binlog_rows_query_log_events
= 1 and a statement is written to the binary log using the row-based logging format, the server generates a an additional log event containing the text of the original statement. If mysqlbinlog is executed on this log using the--verbose
--verbose
, the original statement is printed. To prevent the statement from being executed in addition to the row event (which would in effect cause the statement to be excuted twice), it is commented out with a leading#
character.This was implemented with the assumption that such a statement would consist of a single line, which meant that a statement covering multiple lines was handled incorrectly, in that only the first line of the statement actually commented out. Now in such cases, every line of the statement is commented out with a leading
#
. (Bug #13799555) Replication: Queries that were more than 255 characters in length were truncated when viewed in the output of
SHOW BINLOG EVENTS
or mysqlbinlog. This was due to the length of the query being stored inRows_query_log_events
using a single byte. (Bug #13799489)Replication: Replication locks and some of the protocols controlling the use of these locks were not well implemented or enforced. In particular, this fix improves lock handling for statements such as
CHANGE MASTER TO
,SHOW SLAVE STATUS
, andFLUSH LOGS
. (Bug #13779291)-
Replication: When logging transactions that affected both transactional and nontransactional tables, the following statements could sometimes be written into the binary log in the wrong order or on the wrong side of a transaction boundary:
(Bug #13627921)
Replication: Setting
binlog_checksum
on the master to a value that was unknown on the slave caused replication to fail. Now in such cases, replication checksums are disabled on the slave and replication stops with an appropriate error message. (Bug #13553750, Bug #61096)Replication: To provide a crash-safe slave, it was previously necessary to change the storage engine for the
slave_master_info
,slave_relay_log_info
, andslave_worker_info
tables fromMyISAM
toInnoDB
manually, by issuingALTER TABLE
. To simplify the setup of replication using these slave log tables, they are now created using theInnoDB
storage engine. (Bug #13538891)Replication: When the slave had been set using
CHANGE MASTER TO
with theMASTER_DELAY
option equal to any permitted value greater than zero, then stopped usingSTOP SLAVE
, pointed at the current relay log position (as shown by SHOW SLAVE STATUS), and started again,START SLAVE
failed with the error Could not initialize master info structure. (Bug #12995174)-
Replication: The
--relay-log-space-limit
option was sometimes ignored.More specifically, when the SQL thread went to sleep, it allowed the I/O thread to queue additional events in such a way that the relay log space limit was bypassed, and the number of events in the queue could grow well past the point where the relay logs needed to be rotated. Now in such cases, the SQL thread checks to see whether the I/O thread should rotate and provide the SQL thread a chance to purge the logs (thus freeing space).
Note that, when the SQL thread is in the middle of a transaction, it cannot purge the logs; it can only ask for more events until the transaction is complete. Once the transaction is finished, the SQL thread can immediately instruct the I/O thread to rotate. (Bug #12400313, Bug #64503)
References: See also: Bug #13806492.
-
Replication: An event whose length exceeded the size of the master dump thread's
max_allowed_packet
caused replication to fail. This could occur when updating many large rows and using row-based replication.As part of this fix, a new
slave_max_allowed_packet
system variable is added, which permitsmax_allowed_packet
to be exceeded by the slave SQL and I/O threads. Now the size of a packet transmitted from the master to the slave is checked only against this value, and not against the value ofmax_allowed_packet
. (Bug #12400221, Bug #60926) -
Replication: Statements using
AUTO_INCREMENT
,LAST_INSERT_ID()
,RAND()
, or user variables could be applied in the wrong context on the slave when using statement-based replication and replication filtering server options (see How Servers Evaluate Replication Filtering Rules). (Bug #11761686, Bug #54201)References: See also: Bug #11754117, Bug #45670, Bug #11746146, Bug #23894.
-
Replication: An
INSERT
into a table that has a composite primary key that includes anAUTO_INCREMENT
column that is not the first column of this composite key is not safe for statement-based binary logging or replication. Such statements are now marked as unsafe and fail with an error when using theSTATEMENT
binary logging format. For more information, see Determination of Safe and Unsafe Statements in Binary Logging, as well as Replication and AUTO_INCREMENT.NoteThis issue does not affect tables using the
InnoDB
storage engine, since anInnoDB
table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.(Bug #11754117, Bug #45670)
References: See also: Bug #11761686, Bug #54201, Bug #11746146, Bug #23894.
Replication: After upgrading a replication slave to MySQL 5.6.2 or later, enabling the query cache eventually caused the slave to fail. (Bug #64624, Bug #14005409)
Microsoft Windows: On Windows, mysqlslap crashed for attempts to connect using shared memory. (Bug #31173, Bug #11747181, Bug #59107, Bug #11766072)
Microsoft Windows: For Microsoft Windows, the deprecated MySQL Configuration Wizard is no longer distributed, and instead the newer MySQL Installer is available and preferred.
After running
ALTER TABLE
for antbl
DISCARD TABLESPACEInnoDB
table, certain otherALTER TABLE
operations such as renaming the table or rebuilding the primary key could cause a crash. (Bug #14213568)For conditions of the form
WHERE p1 AND (p2 OR p3)
, the optimizer now uses the index merge access method on(p2,p3)
if it is more efficient than a range scan onp1
. Previously, index merge was not considered when a range scan was possible. (Bug #14208922)Error messages that should have said "YEAR(2)" said "YEAR(0)" instead. (Bug #14167585)
For debug builds,
INSERT IGNORE INTO ... SELECT
that selected more thanmax_join_size
rows could raise an assertion. (Bug #14145442)With logging of the general query log to a table, logging was disabled within a read-only transaction because write lock acquisition on the log table was blocked. (Bug #14136866)
The
ARCHIVE
storage engine could not be built unless the Performance Schema was also built. (Bug #14116252)If a nonexistent page was requested to be loaded into the
InnoDB
buffer pool by theinnodb_buffer_pool_load_at_startup
configuration option, a subsequent shutdown operation could hang. (Bug #14106082)In debug builds, the server failed to check for error status from the storage engine and raised an assertion. (Bug #14101852)
In debug builds, warnings occurring during creation of an
InnoDB
table withROW_FORMAT=DYNAMIC
andinnodb_file_per_table
disabled could raise an assertion. (Bug #14101563)Derived tables and tables created with
CREATE TABLE ... SELECT
using the output from single-row queries withNULL
in the first column could change the value to 0. (Bug #14069831)Incorrect assessment of column nullability for a subquery result within a trigger could cause “column cannot be null” errors. (Bug #14069810, Bug #14005353)
The Performance Schema did not generate consistent digest values for
CALL
statements. (Bug #14069132)The LooseScan semijoin strategy could fail to remove duplicates from the result set. (Bug #14053325)
Certain arguments to
RPAD()
could lead to “uninitialized variable” warnings. (Bug #14039955)For debug builds compiled with
gcov
, tests that usedDBUG_SUICIDE
lostgcov
data. (Bug #14028421)When the index enforcing a foreign key constraint was dropped while
foreign_key_checks=0
, further operations involving the foreign key column could cause a serious error after theforeign_key_checks
option was re-enabled. (Bug #14025221)Mishandling of failed internal commits in administrative statements such as
ANALYZE TABLE
could cause an assertion to be raised. (Bug #14001091)Improper calculation of decimals for
TIME
values given as arguments toIF()
orIFNULL()
could cause a server crash. (Bug #13988413, Bug #14042545)Some arguments to
MAKETIME()
could cause a buffer overflow. (Bug #13982125)For debug builds, conversion of a double-precision value to the
lldiv_t
type could raise an assertion. (Bug #13976233)Mishandling of failure during multiple-table
UPDATE IGNORE
statements could cause an assertion to be raised. (Bug #13974815)Queries that grouped by an outer
BLOB
column in a subquery caused a server crash. (Bug #13966809)Selecting
MIN()
orMAX()
from a left or right join involving anINFORMATION_SCHEMA
table could cause a server crash. (Bug #13966514)Queries containing references to user variables were not written to the general query log with some rewriting, not as received. (Bug #13958454)
For debug builds, the optimizer could change the query plan when checking sort order and return incorrect results. (Bug #13949068)
An infinite thread loop could develop within Performance Schema, causing the server to become unresponsive. (Bug #13898343)
Overhead for Performance Schema table aggregation operations was excessive. (Bug #13862186)
The
version_compile_machine
system variable sometimes did not include the value64
for server binaries compiled on a 64-bit system. (Bug #13859866)With subquery materialization enabled, some queries with a subquery in the
HAVING
clause caused a server crash. (Bug #13848789)When the
InnoDB
persistent statistics feature was turned on, anALTER TABLE
statement on anInnoDB
table with delete-marked records could cause a crash (non-debug builds) or assertion error (debug builds). (Bug #13838962, Bug #13867915)In bootstrap mode, the server signal handler thread did not shut down if the server aborted early. (Bug #13837221)
Some errors in MySQL 5.6 had different numbers than in MySQL 5.5. (Bug #13833438)
If
KILL QUERY
interrupted anINSERT
orUPDATE
that had theIGNORE
modifier, OK was incorrectly returned to the client rather than an error code. Now an error (“Query execution was interrupted”) is returned instead. (Bug #13822652)If
KILL QUERY
interrupted a statement during derived table materialization, the server crashed later trying to read the nonexistent materialized table. (Bug #13820776)-
Incorrect cost calculations for two-table joins could lead to incorrect join order. (Bug #13810048)
References: This issue is a regression of: Bug #26106.
The Performance Schema stored identifiers in digest tables as
utf8
without converting them from the original character set first. (Bug #13809293)Incorrect stored program caching could cause statements within a stored program that included a
GROUP BY
clause to return different results across multiple program invocations. (Bug #13805127)For comparison of a temporal value to and indexed character column, the optimizer could apply the
range
access method and thus perform an indexed search that found only literal matches. This is incorrect because MySQL permits a variety of delimiters in temporal values represented as strings. (Bug #13803810)Several clarifications were made to optimizer trace output. (Bug #13799348)
viosslfactories
did not compile on Oracle Linux 6.0 with CMake options-DWITH_SSL=system
and-DWITH_DEBUG=1
. (Bug #13799126)In debug builds, a race condition in a signal handler during shutdown caused a server crash. (Bug #13793813)
-
A prepared statement that referenced views and were executed using semijoin transformation could return different results for different executions. (Bug #13773979)
References: See also: Bug #14641759.
Outer join queries with
ALL
could return incorrect results because the optimizer incorrectly rewrote them to use inner join. (Bug #13735712)(a,b) IN (SELECT c,d FROM t1 WHERE ...)
could produce incorrect results ift1
had an index on(c, d)
andc
ord
containedNULL
values. (Bug #13731417)For open ranges that effectively resulted in a full index scan, the optimizer did not discard the range predicate as unneeded. (Bug #13731380)
The range optimizer sometimes did not treat equivalent expressions the same, depending on the order of the operands. For example, it could treat
a <= b
andb >= a
differently. (Bug #13701206)With semijoin optimization enabled, an assertion was raised for queries for which the number of tables was greater than the search depth. (Bug #13685026)
Truncating a table partition did not invalidate queries in the query cache that used the table. (Bug #13485448)
Setting
max_sort_length
to small values could cause a server crash. (Bug #13485416)A query executed with literal values in the
WHERE
clause could return results different from the same query written to select the same literal values from a separate table using aSELECT
statement in theWHERE
clause. (Bug #13468414)Condition handler code could assume that after handler execution, control would pass up a single level to the parent, sometimes leading to a server crash. (Bug #13431226)
If a
GROUP_CONCAT()
result was calculated using intermediate results (for example, ifORDER BY
orDISTINCT
was present), individual intermediate results were each truncated to a maximum of 64K, even if thegroup_concat_max_len
system variable was set to a larger value. Now the length of any intermediate result and the final result are controlled by thegroup_concat_max_len
value. (Bug #13387020)Queries with
ALL
subquery predicates could return incorrect results due to a faulty query transformation. (Bug #13330886)Switching between index scans and random scans using the
HANDLER
interface could result in failure of the interface to properly reinitialize scans. (Bug #13008220)The presence of a file named
.empty
in thetest
database prevented that database from being dropped. (Bug #12845091)For queries with
ORDER BY COUNT(*)
andLIMIT
, the optimizer could choose an execution plan that produced incorrect results. (Bug #12713907)For some subqueries that should be executed using a range scan on a nonprimary index and required use of filesort, only the first execution of the subquery was done as a range scan. All following executions were done as full table scans, resulting in poor performance. In addition, if index condition pushdown was used, incorrect results could be returned. (Bug #12667154)
IPv6 functions such as
IS_IPV6()
produced Valgrind warnings with arguments that used a multibyte character set. (Bug #12635232, Bug #14040277)Queries that used
STRAIGHT_JOIN
and were executed using Multi-Range Read optimization could result in a memory leak. (Bug #12365385)Overhead for the Performance Schema was reduced. (Bug #12346211)
-
IN
subqueries that used a variance or standard deviation aggregate function could return a different result depending on whether theoptimizer_switch
materialization
flag was enabled.NoteThose aggregate functions may now return a result with a different number of decimals from previously.
(Bug #11766758)
On Windows, initial database creation failed during bootstrapping. (Bug #11766342)
A regression bug in the optimizer could cause excessive disk usage for
UPDATE
statements onInnoDB
tables. For tables created withinnodb_file_per_table
enabled,OPTIMIZE TABLE
can be used to recover excessive space used. For tables created in theInnoDB
system tablespace,it is necessary to perform a dump and restore into a new instance of the system tablespace. (Bug #65745, Bug #14248833)Parse errors that occurred while loading UCA or LDML collation descriptions were not written to the error log. (Bug #65593, Bug #14197426)
Incorrect metadata could be produced for columns returned from some views. (Bug #65379, Bug #14096619)
If an account had a nonzero
MAX_USER_CONNECTIONS
value, that value was not always respected. (Bug #65104, Bug #14003080)When an
ALTER TABLE
operation was performed with an invalid foreign key constraint, the error reported wasER_CANT_CREATE_TABLE
rather thanER_CANNOT_ADD_FOREIGN
. (Bug #64617, Bug #13840553)-
SAVEPOINT
statements were incorrectly disallowed within XA transactions. (Bug #64374, Bug #13737343)References: See also: Bug #11766752.
The server crashed at shutdown if the slow query log file was a named pipe. (Bug #64345, Bug #13733221)
Some Czech error messages contained invalid characters. (Bug #64310, Bug #13726075)
With
lower_case_table_names=2
on systems with case-insensitive file systems such as Windows or OS X,CREATE TABLE ... LIKE
did not preserve lettercase of the destination table name as given in the statement. (Bug #64211, Bug #13702397)File access by the
ARCHIVE
storage engine was not instrumented and thus not shown in Performance Schema tables. (Bug #63340, Bug #13417440)The Performance Schema incorrectly displayed some backslashes in Windows file names (by doubling them). (Bug #63339, Bug #13417446)
An inappropriate mutex was used to protect random number generation, causing contention during connect operations. (Bug #62282, Bug #12951609)
-
mysql_store_result()
andmysql_use_result()
are not for use with prepared statements and are not intended to be called followingmysql_stmt_execute()
, but failed to return an error when invoked that way inlibmysqld
. (Bug #62136, Bug #13738989)References: See also: Bug #47485.
Under some conditions, the effect of
RENAME USER
was not recognized untilFLUSH PRIVILEGES
was used (which should not be necessary). (Bug #61865, Bug #12766319)If the
bind_address
system variable was given a host name value and the host name resolved to more than one IP address, the server failed to start. For example, withbind_address=localhost
, iflocalhost
resolved to both127.0.0.1
and::1
, startup failed. Now the server prefers the IPv4 address in such cases. (Bug #61713, Bug #12762885)SHOW TABLES
was very slow unless the required information was already in the disk cache. (Bug #60961, Bug #12427262)On Windows, the mysql client crashed when invoked using its full path name. (Bug #60858, Bug #12402882)
Sessions could end up deadlocked when executing a combination of
SELECT
,DROP TABLE
,KILL
, andSHOW ENGINE INNODB STATUS
. (Bug #60682, Bug #12636001)For debug builds, errors occurring during processing of
INSERT DELAYED
statements could crash the server. (Bug #60114, Bug #11827404)Using
CONCAT()
to construct a pattern for aLIKE
pattern match could result in memory corrupting and match failure. (Bug #59140, Bug #11766101)Due to a race condition, it was possible for two threads to end up with the same query ID for different queries. (Bug #58785, Bug #11765785)
For queries with range predicates, the optimizer could miscalculate the number of key parts used, possibly leading to a server crash. (Bug #58731, Bug #11765737)
SHOW
statements treated stored procedure, stored function, and event names as case sensitive. (Bug #56224, Bug #11763507)mysqlbinlog exited with no error code if file write errors occurred. (Bug #55289, Bug #11762667)
yaSSL rejected valid SSL certificates that OpenSSL accepts. (Bug #54348, Bug #11761822)
If the server held a global mutex while doing network I/O, client disconnections could be slow. (Bug #53096, Bug #11760669)
A multiple-table
UPDATE
with theIGNORE
keyword resulted in an inappropriate and not meaningfulGot error 0 from storage engine
message. (Bug #49539, Bug #11757486)When dumping the
mysql
database, mysqldump did not include thegeneral_log
andslow_query_log
tables because they cannot be locked. This caused a problem after reloading the dump file if that file contained aDROP DATABASE
statement for themysql
database: The database no longer contained the log tables and attempts to log to them failed. Now mysqldump includes statements to re-create thegeneral_log
andslow_query_log
tables so that they exist after loading the dump file. Log table contents still are not dumped. (Bug #45740, Bug #11754178)When a query was killed, the error code was not always properly propagated up through the server code. (Bug #43353, Bug #11752226)
The optimizer could chose a worse execution plan for a condition that used a quoted number compared to the unquoted number. (Bug #43319, Bug #11752201)
Queries that used
WHERE (
were optimized forcol1
,col2
) IN ((const
,const
))SELECT
, but not forDELETE
orUPDATE
. (Bug #43187, Bug #11752097)For
ALTER TABLE
with theIGNORE
keyword,IGNORE
is now part of the information provided to the storage engine. It is up to the storage engine whether to use this when choosing between the in-place or copy algorithm for altering the table. ForInnoDB
index operations,IGNORE
is not used if the index is unique, so the copy algorithm is used. (Bug #40344, Bug #11750045)LEFT JOIN
on derived tables was very slow. This is now addressed through the use of subquery materialization. (Bug #34364, Bug #11747876)MySQL was overly agressive in enforcing the
NO_ZERO_DATE
andNO_ZERO_IN_DATE
SQL modes for default values in column definitions forCREATE TABLE
andALTER TABLE
statements. Previously, default dates that were invalid with those SQL modes enabled produced an error, even when strict mode was not enabled. Now withNO_ZERO_DATE
orNO_ZERO_IN_DATE
enabled, invalid default dates produce a warning if strict SQL mode is not enabled, and an error if strict mode is enabled. (Bug #34280, Bug #11747847)Redundant “Specified key was too long” messages could be produced by index-creation operations. (Bug #31149, Bug #11747177)
Code for the storage engine API did not check the return value from the
ha_rnd_init()
,ha_index_init()
, andindex_init()
functions. (Bug #26040, Bug #11746399, Bug #54166, Bug #11761652)For table or database names that are longer than 64 characters, the error “Incorrect table name” was returned rather than “Identifier too long”. (Bug #25168, Bug #11746295)
-
During the startup process, mysqld could incorrectly remove the PID file of an already running mysqld. (Bug #23790, Bug #11746142)
References: See also: Bug #14726272.
Using
ALTER TABLE
to add aTIMESTAMP
column containingDEFAULT CURRENT_TIMESTAMP
in the definition resulted in a column containing'0000-00-00 00:00:00'
, not the current timestamp. (Bug #17392, Bug #11745578)