This release continues the process begun in MySQL 5.6.6 of making changes to the default values of 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.
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
its previous default of 128, adjusted up by an amount
proportional to the value of
max_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.
|Parameter||Old Default||New Default|
On Unix platforms, mysql_install_db supports
a new option,
that provides for more secure MySQL installation. Invoking
causes it to perform the following actions in addition to its
The installation process creates a random password, assigns
it to the initial MySQL
and sets the “password expired” flag for those
The initial random
root password is
written to the
.mysql_secret file in
the directory named by the
environment variable. Depending on operating system, using a
command such as sudo may cause the value
HOME to refer to the home directory of
root system user.
.mysql_secret is created with mode 600
to be accessible only to the system user for whom it is
.mysql_secret already exists, the
new password information is appended to it. Each password
entry includes a timestamp so that in the event of multiple
install operations it is possible to determine the password
associated with each one.
No anonymous-user MySQL accounts are created.
As a result of these actions, it is necessary after installation
to start the server, connect as
the password written to the
file, and to select a new
Until this is done,
root cannot do anything
else. This must be done for each
you intend to use. To change the password, you can use the
SET PASSWORD statement (for
example, with the mysql client). You can also
use mysqladmin or
New RPM install operations (not upgrades) invoke
mysql_install_db with the
--random-passwords option. As a consequence,
RPM installs from this version onward will have their
root accounts secured, and will have no
anonymous-user accounts. (Install operations using RPMs for
Unbreakable Linux Network are unaffected because they do not use
For install operations using a binary
.tar.gz distribution or a source
distribution, you can invoke mysql_install_db
--random-passwords option manually to
make your MySQL installation more secure. This is recommended,
particularly for sites with sensitive data.
mysql_install_db is now a Perl script and can be used on any system with Perl installed. Previously, it was a shell script and available only on Unix platforms.
In addition, mysql_install_db is more strict
option value. Only the last component of the path name is
created if it does not exist; the parent directory must already
exist or an error occurs. Previously, it created any nonexistent
directories in the path name.
On Unix platforms, mysql_install_db now
creates a default option file named
in the base installation directory. This file is created from a
template included in the distribution package named
my-default.cnf. You can find the template
in or under the base installation directory. When started using
mysqld_safe, the server uses
my.cnf file by default. If
my.cnf already exists,
mysql_install_db assumes it to be in use and
writes a new file named
With one exception, the settings in the default option file are
commented and have no effect. The exception is that the file
variable from its default of
NO_ENGINE_SUBSTITUTION to also
This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Server SQL Modes.
my-default.cnf template replaces the
older sample option files (
my-medium.cnf, and so forth), which are no
Functionality Added or Changed
InnoDB transportable tablespace feature
was enhanced to allow
ALTER TABLE ... IMPORT
TABLESPACE to succeed in some cases where the
.cfg file was not available.
This enhancement allows recovery of data even in some cases
where the system
tablespace is corrupted or deleted.
(Bug #14589582, Bug #66715)
The number of atomic operations performed by the Performance Schema was reduced. (Bug #14658739)
ALTER USER now can be used as a
(Bug #66874, Bug #14646014)
On Unix systems, the mysql client writes
statements executed interactively to a history file (see
mysql Logging). mysql now
ignores for logging purposes statements that match any pattern
in the “ignore” list. By default, the pattern list
"*IDENTIFIED*:*PASSWORD*", to ignore
statements that refer to passwords. Pattern matching is not case
sensitive. Within patterns, two characters are special:
? matches any single character.
* matches any sequence of zero or more
To specify additional patterns, use the
--histignore command option or set
MYSQL_HISTIGNORE environment variable.
(If both are specified, the option value takes precedence.) The
value should be a colon-separated list of one or more patterns,
which are appended to the default pattern list.
Patterns specified on the command line might need to be quoted
or escaped to prevent your command interpreter from treating
them specially. For example, to suppress logging for
statements in addition to statements that refer to passwords,
invoke mysql like this:
(Bug #48287, Bug #11756377)
On Windows, many MySQL executables depend on the
ssleay32.dll SSL libraries at runtime. To
ensure that the proper versions of these libraries are found,
the install process copies them into the same directory as the
SHOW AUTHORS and
CONTRIBUTORS statements have been removed.
Important Change; Replication:
When running the slave with the
successive skipped events (errors logged as warnings) were found
to contain information from previous warnings, which caused an
excessive amount of redundant information to be written to the
error log. This problem could occur when using row-based or
mixed-format binary logging.
The fix for this issue is to clear these warnings prior to
processing the next skipped event. In addition, the skipped
events are now handled in the same way regardless of the value
binlog_format, and a skipped
error always causes a warning to be written to the error log, as
long as the value of the
log_warnings system variable is
greater than 1.
The server system variables
profiling_history_size are now
deprecated, and are subject to removal in a future release of
the MySQL Server.
InnoDB: Assertion failure in thread
thread_numin file fsp0fsp.cc line 1882
CREATE INDEX operation
failed for an
FULLTEXT index due to a duplicate key error,
some allocated memory was not freed.
During a brief time window while creating an
InnoDB unique index, MySQL could print a
spurious warning message:
WARNING: CANNOT FIND INDEX ?
index_nameIN INNODB INDEX TRANSLATION TABLE
The cause was that MySQL started enforcing the uniqueness constraint before the existence of the index was fully registered. The fix suppresses the incorrect message during this final stage of index creation. (Bug #14735988)
InnoDB: An online DDL operation to create a unique index could fail to detect duplicate index values, when the duplicate values were caused by DML operations while the index was being created. (Bug #14733674)
InnoDB: During an online DDL operation, a duplicate key error could be incorrectly issued if a record was inserted and subsequently updated while the table was being rebuilt. (Bug #14723456)
When using the
tablespace feature, the
ALTER TABLE ... IMPORT
TABLESPACE statement could crash if the
InnoDB table being flushed contained a
FULLTEXT index. With this fix, the table data
can be imported, although you must drop and re-create the
FULLTEXT index after the import operation.
(Bug #14712962, Bug #67081)
An assertion failure occurred when a bogus duplicate key error
was flagged during online
TABLE. This issue only occurred for a table that
lacked a primary key and
indexes. This patch fixes the assertion failure, but not
the bogus duplicate key error, which is reported as
plugin can now work with tables where the underlying character
set is multibyte.
(Bug #14711015, Bug #67076)
CREATE TABLE statement
failed due to a disk full error, some memory allocated during
the operation was not freed properly.
ALTER TABLE operation on an
InnoDB table containing a
FULLTEXT index could cause make the server
halt with an assertion error. The fix causes all
TABLE operations for such tables to use the
table-copying behavior of the
If the server crashed while executing
TRUNCATE TABLE for an
InnoDB table containing a
FULLTEXT index, further errors could occur
recovery, preventing the server from restarting.
InnoDB table containing a
FULLTEXT index was being modified by a
TRUNCATE TABLE statement and on
online DDL operation
simultaneously, the server could end up with inconsistent
internal locks or could crash.
If creation of a
FULLTEXT index failed
because of a “row too large” condition, a
ALTER TABLE operation
could cause the server to halt with an error.
If the MySQL server crashed while
XA transactions were in
PREPARED state, inconsistent data could be
produced during crash
recovery if the query cache was enabled. The fix allows
MySQL to disable the query cache during crash recovery if
MySQL could crash while creating an
table if the disk became full at a specific moment: after the
.frm file was created but
before the corresponding .ibd
file was created.
InnoDB: If the server crashed at the specific point when a change buffer entry was being merged into a buffer pool page, the transaction log and the change buffer were left in an inconsistent state. After a restart, MySQL could crash after reading the corresponding secondary index page. The problem was more likely to occur in MySQL 5.5 or later, where the original insert buffering mechanism was generalized to cover other operations. (Bug #14636528, Bug #66819, Bug #58571, Bug #61104, Bug #65443)
If a crash occurred during a
TABLE operation, the
could be left in an inconsistent state, causing a crash if the
partially created table was accessed later.
On startup, MySQL would not start if there was a mismatch
between the value of the
configuration option and the actual size of the
ib_logfile* files that make up the
redo log. This behavior
required manually removing the redo log files after changing the
innodb_log_file_size. The fix causes
MySQL to write all dirty
pages to disk and re-create the redo log files during
startup if it detects a size mismatch.
setting enabled, a
operation could cause a crash, due to a race condition that
depended on the timing of pending I/O requests.
(Bug #14594600, Bug #66718)
A query against an
InnoDB table with a
FULLTEXT index could crash, if the
AGAINST clause contained a character sequence
that was encoded incorrectly for the character set of the table.
FULLTEXT index was dropped from an
InnoDB table, and the server crashed later
for an unrelated reason, an additional error could occur while
attempting to access nonexistent
InnoDB: The server could crash with a confusing message if it ran out of space for temporary files during index creation.
InnoDB: Assertion failure in thread
thread_numin file mtr0mtr.cc line 306 InnoDB: Failing assertion: mtr->state == 12231
ALTER TABLE on an
InnoDB table that dropped the primary key and
then re-created it with columns in a different order could cause
an error. The issue affected tables where the swapped columns
referenced each other in a single-table
relationship. The data dictionary could be left in an
inconsistent state, where the table was listed in
TABLES output but could not be queried or dropped. For
example, if the table was declared with primary key columns
(c1,c2) and a foreign key with
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY (c2, c1); ERROR 1030 (HY000): Got error 38 from storage engine
During an online DDL operation, a
affecting the same table could cause an assertion error if the
table formerly contained a
Some bookkeeping information related to
FULLTEXT indexes for
InnoDB tables is preserved even after such an
index is dropped.
InnoDB: If a table was defined with an index key length very close to the upper length limit of 3072, a query against that table could cause a serious error. (Bug #14500557, Bug #14537695)
Table names containing non-ASCII characters were displayed
incorrectly when the
A race condition could cause a crash during an online
CREATE INDEX statement for an
InnoDB 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 the
CREATE INDEX statement was
If a transaction was started with a consistent snapshot, then
new indexes were added to the table while the transaction was in
progress, a subsequent
UPDATE statement could
incorrectly encounter the error:
ER_TABLE_DEF_CHANGED: insufficient history for index
This issue could cause an assertion error in debug builds. (Bug #14036214)
The server could crash with an assertion error during operations
on tables with
In rare circumstances, during operations on an
InnoDB table containing
foreign keys, pages in
the buffer pool could be
evicted but not written to disk, leading to data inconsistency.
In rare circumstances, MySQL could apply
records out of order during a
ROLLBACK of an operation
that modified a BLOB column. This issue could cause an assertion
error in debug builds:
In debug builds, a mismatch in the
PAGE_FREE list would cause an assertion.
Partitioning: The server now skips pruning of tables (see Partition Pruning) that use a storage engine which handles its own partitioning internally. The server now also explicitly rejects attempts to use explicit partitioning for such tables. (Bug #14672885)
When used with a table having multiple columns in its primary
key, but partitioned by
KEY using a column
that was not part of the primary key as the partitioning column,
a query using an aggregate function and
DISTINCT such as
was not handled
Replication: When using a multi-threaded slave, if all worker threads were kept busy, it was possible for cleanup of an internal MTS circular buffer to fail, resulting in a full buffer and failure of the slave. (Bug #14710881)
When invoked while
was set to
SQL_THREAD_WAIT_AFTER_GTIDS() function waited
indefinitely, unless a timeout was specified. In the latter
case, the function could return incorrect values. Now, when
SQL_THREAD_WAIT_AFTER_GTIDS() always returns
NULL, as expected.
REVOKE statements were not always
handled the same way on the master and the slave. We now log an
incident event whenever an error occurs, even if it is only a
partial error, with a message stating that manual reconciliation
There existed a gap in time between the appending of the current
GTID to the server's list of logged GTIDs and the commit of
the transaction by the storage engine. On slow platforms, or
when using profiling, this could cause
to return before the data actually reached the database.
Now the current GTID is appended to the logged GTIDs following the commit, which removes this gap and so eliminates a possible source of inconsistency. (Bug #14116526)
The error shown when a relay log file was missing from the relay
log index file informed the user only that the log file was not
found, but did not specify the exact reason. Now in such cases,
the error message returned is Could not find target
log file mentioned in relay log info in the index file
index_file_name' during relay log
Replication: Following an insert into a nontransactional table that failed due to insufficient disk space, the server did not properly clean up all pending events, leading to an assert or possibly to other errors. (Bug #11750014)
`) characters were not always
handled correctly in internally generated SQL statements, which
could sometimes lead to errors on replication slaves or cause
failure of restore operations from binary log files.
(Bug #66550, Bug #14548159, Bug #29422, Bug #11746883)
DELETE statement for an
InnoDB table could write incorrect
transaction metadata into a record, causing the server to halt
with an error. To work around this issue, reduce the specified
length of the primary key to less than 1K bytes.
mysql_secure_installation could not change
the password for an account that had
password_expired='Y' in the
mysql.user table row for that account.
Patches for materialized semi-joins caused failures of the query
plan interface used by
mysqladmin password did not work for accounts with an expired password. (The fix for this problem is limited to accounts with passwords that use native or “old” native hashing. It still does not handle accounts that use SHA-256 password hashing.)
As a consequence of this patch, the restricted mode of operation
enforced by the server on operations permitted to clients with
expired passwords now includes
statements in addition to
PASSWORD. This is useful if the account uses a
password hashing format that requires
old_passwords to be set to a
value different from its default.
Repeated execution of a query containing a subquery that used
MAX() could result in increasing
Queries that used a nested join with a subquery in the
FROM clause and an
ORDER BY ...
DESC clause could return too few rows.
With the optimizer tracing enabled, the
table can be queried to find tracing information about the last
statements. However, for queries for which the results were
retrieved from the query cache, this information was not
There was a performance regression for queries using
SELECT ... INTO user variables and a
WHERE condition on one or more of the
variables in the
References: This bug was introduced by Bug #12408412.
In debug builds, the server could crash because
db_suicide() failed to handle
could fail with
Unknown database when
dbname contained multiple backtick
Outer joins could execute inefficiently and return incorrect results if joins were pushed down to the storage engine. (Bug #14644936)
A prepared statement that referenced views in an
IN subquery could return different results
for different executions.
References: See also Bug #13773979.
Within a stored program, memory allocated to hold condition information was not released until program exit, leading to excessive memory use. (Bug #14640599)
An incomplete result could be stored in the query cache when a query failed with an error (providing that the query cache was enabled, and was set to a nonzero size). This fix ensures that it is no longer possible for queries that finish with an error to be cached. (Bug #14621700)
References: This bug was introduced by Bug #40264.
The thread cache implementation worked in LIFO rather than FIFO fashion and could result in a thread being denied service (although this was a remote possibility). (Bug #14621627)
The server could crash when registering tables in the query cache for queries that selected from views. (Bug #14619935)
With semi-join and materialization optimizations enabled, a
query that materialized a
returned incorrect results when
Index condition pushdown in conjunction with descending index range scan could return incorrect results if there were multiple ranges in the range scan. (Bug #14604223)
EXPLAIN DELETE ...
could function incorrectly when it was used in a stored routine.
References: This bug was introduced by Bug #11752097.
The configure.pl script that converts GNU
configure options to CMake
equivalents generated erroneous output for the
--with-mysqld-ldflags options. It now ignores
A query with a subquery and
ORDER BY and
LIMIT clauses returned fewer rows than
expected when executed using semi-join materialization.
The server printed excessive
Got error 159 when reading
table messages to the error log when one transaction
attempted to access a table that had been modified by another.
The optimizer could choose an incorrect execution plan for
InnoDB tables based on
indexes that use column prefixes.
Materialization of a subquery in the
clause could return the wrong number of rows if the subquery
In-source builds modified the source file
Improper memory cleanup could cause the server to exit. (Bug #14536113)
A query with a subquery in the
JOIN ... ON
clause with an outer reference to a field that was out of scope
could cause the server to crash.
On Windows, mysql_plugin could not find my_print_defaults. (Bug #14471052)
Now, as a result of this fix, quoted name and host identifiers
used in a
GRANT statement are automatically
trimmed of any leading and trailing spaces, before privileges
On Mac OS X, the
variable did not include the value
server binaries compiled on a 64-bit system.
INFORMATION_SCHEMA tables through a
view could leak memory.
On Microsoft Windows with CMake 2.6, the build process would not
stop if the
create_initial_db step failed.
The test in mysqld_safe for the presence of
--plugin_dir option and
assignment of a default value to it were performed before the
actual argument parsing took place.
A cached query result was not empty at the end of statement execution as expected. This could occur when executing queries (with the query cache enabled and set to a nonzero size) where the result was not sent to the client such as those executed by the Event Scheduler, or when executing stored routines containing queries while the server was running in bootstrap mode. (Bug #11755580, Bug #14609893)
The number of connection errors from a given host as counted by
the server was periodically reset, with the result that
max_connect_errors was never
reached and invalid hosts were never blocked from trying to
References: See also Bug #38247, Bug #43006, Bug #45584, Bug #45606.
Range checked for each record
optimization is now used for conditions with outer query
Random number generation during client authentication consumed excessive CPU. (Bug #66567, Bug #14555434)
Metadata locking resulted in excessive contention in read-only
and a low number of connections.
Now the set of metadata locks can be partitioned into separate
hashes to permit connections accessing different objects to use
different locking hashes and reduce contention. The new
system variable can be used to specify the number of hashes.
(Bug #66473, Bug #14569140)
On Windows, the Perl version of mysql_install_db created system tables in the mysql database that were not populated properly. (Bug #65584, Bug #14181049)
ST_Within() incorrectly reported that a
polygon did not contain itself.
incorrectly returned 0 for polygons that differed only in
(Bug #64653, Bug #13864679)
ST_Difference() could incorrectly produce
empty polygons in the result.
(Bug #64649, Bug #13865773)
libmysqlclient did not use symbol versioning.
Thanks to Nicholas Bamber for the patch.
(Bug #64386, Bug #13788218)
The parser rejected legal queries that involved a
UNION where the right hand side
query term has a table in parenthese.
(Bug #54382, Bug #11761854)
For some queries involving
ORDER BY, the
optimizer chose the wrong index for accessing the table.
(Bug #45969, Bug #11754370, Bug #14338686)
In debug builds,
errno rather than
socket_error to the debug trace.
(Bug #28775, Bug #11746795)