MySQL 5.6 Release Notes
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.)
InnoDB:
The new INFORMATION_SCHEMA
tables
INNODB_BUFFER_PAGE
,
INNODB_BUFFER_PAGE_LRU
, and
INNODB_BUFFER_POOL_STATS
display
InnoDB
buffer pool information
for tuning on large-memory or highly loaded systems.
InnoDB:
A new INFORMATION_SCHEMA
table,
INNODB_METRICS
, lets you query
low-level InnoDB
performance information,
getting cumulative counts, averages, and minimum/maximum values
for internal aspects of the storage engine operation. You can
start, stop, and reset the metrics counters using the
innodb_monitor_enable
,
innodb_monitor_disable
,
innodb_monitor_reset
, and
innodb_monitor_reset_all
system
variables.
InnoDB:
The InnoDB
data dictionary, containing
metadata about InnoDB
tables, columns,
indexes, and foreign keys, is available for SQL queries through
a set of INFORMATION_SCHEMA
tables.
Performance; InnoDB:
The optimizer statistics for InnoDB
tables
can now persist across server restarts, producing more stable
query performance. You can also control the amount of sampling
done to estimate cardinality for each index, resulting in more
accurate optimizer statistics. This feature involves the
configuration options
innodb_analyze_is_persistent
(later replaced
by innodb_stats_persistent
),
innodb_stats_persistent_sample_pages
,
and
innodb_stats_transient_sample_pages
,
and the ANALYZE TABLE
statement.
See Configuring Persistent Optimizer Statistics Parameters for details.
InnoDB:
To ease the memory load on systems with huge numbers of tables,
InnoDB
now frees up the memory associated
with an opened table using an LRU algorithm to select tables
that have gone the longest without being accessed. To reserve
more memory to hold metadata for open InnoDB
tables, increase the value of the
table_definition_cache
configuration option. InnoDB
treats this
value as a “soft limit” for the number of open
table instances in the InnoDB
data dictionary
cache. The actual number of tables with cached metadata could be
higher than the value specified for
table_definition_cache
, because
metadata for InnoDB
system tables, and parent
and child tables in foreign key relationships, is never evicted
from memory. For additional information, refer to the
table_definition_cache
documentation.
(Bug #20877, Bug #11745884)
The optimizer now more efficiently handles queries (and subqueries) of the following form:
SELECT ... FROMsingle_table
... ORDER BYnon_index_column
[DESC] LIMIT [M
,]N
;
That type of query is common in web applications that display only a few rows from a larger result set. For example:
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10; SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
The sort buffer has a size of
sort_buffer_size
. If the sort
elements for N
rows are small enough
to fit in the sort buffer
(M
+N
rows
if M
was specified), the server can
avoid using a merge file and perform the sort entirely in
memory. For details, see LIMIT Query Optimization.
The optimizer implements Disk-Sweep Multi-Range Read. Reading rows using a range scan on a secondary index can result in many random disk accesses to the base table when the table is large and not stored in the storage engine's cache. With the Disk-Sweep Multi-Range Read (MRR) optimization, MySQL tries to reduce the number of random disk access for range scans by first scanning the index only and collecting the keys for the relevant rows. Then the keys are sorted and finally the rows are retrieved from the base table using the order of the primary key. The motivation for Disk-sweep MRR is to reduce the number of random disk accesses and instead achieve a more sequential scan of the base table data. For more information, see Multi-Range Read Optimization.
The optimizer implements Index Condition Pushdown (ICP), an
optimization for the case where MySQL retrieves rows from a
table using an index. Without ICP, the storage engine traverses
the index to locate rows in the base table and returns them to
the MySQL server which evaluates the WHERE
condition for the rows. With ICP enabled, and if parts of the
WHERE
condition can be evaluated by using
only fields from the index, the MySQL server pushes this part of
the WHERE
condition down to the storage
engine. The storage engine then evaluates the pushed index
condition by using the index entry and only if this is satisfied
is base row be read. ICP can reduce the number of accesses the
storage engine has to do against the base table and the number
of accesses the MySQL server has to do against the storage
engine. For more information, see
Index Condition Pushdown Optimization.
Partitioning:
It is now possible to select one or more partitions or
subpartitions when querying a partitioned table. In addition,
many data modification statements
(DELETE
,
INSERT
,
REPLACE
,
UPDATE
, LOAD
DATA
, and LOAD XML
)
that act on partitioned tables also now support explicit
partition selection. For example, assume we have a table named
t
with some integer column named
c
, and t
has 4 partitions
named p0
, p1
,
p2
, and p3
. Then the query
SELECT * FROM t
PARTITION (p0, p1) WHERE c < 5
returns rows only in
partitions p0
and p1
that
match the WHERE
condition, whereas partitions
p2
and p3
are not checked.
For additional information and examples, see Partition Selection, as well as the descriptions of the statements just listed.
The Performance Schema has these additions:
The Performance Schema now has tables that contain summaries
for table and index I/O wait events, as generated by the
wait/io/table/sql/handler
instrument:
table_io_waits_summary_by_table
:
Aggregates table I/O wait events. The grouping is by
table.
table_io_waits_summary_by_index_usage
:
Aggregates table index I/O wait events. The grouping is
by table index.
The information in these tables can be used to assess the impact of table I/O performed by applications. For example, it is possible to see which tables are used and which indexes are used (or not used), or to identify bottlenecks on a table when multiple applications access it. The results may be useful to change how applications issue queries against a database, to minimize application footprint on the server and to improve application performance and scalability.
A change that accompanies the new tables is that the
events_waits_current
table now has an
INDEX_NAME
column to identify which index
was used for the operation that generated the event. The
same is true of the event-history tables,
events_waits_history
, and
events_waits_history_long
.
The Performance Schema now has an instrument named
wait/lock/table/sql/handler
in the
setup_instruments
table for
instrumenting table lock wait events. It differs from
wait/io/table/sql/handler
, which
instruments table I/O. This enables independent
instrumentation of table I/O and table locks.
Accompanying the new instrument, the Performance Schema has
a table named
table_lock_waits_summary_by_table
that aggregates table lock wait events, as generated by the
new instrument. The grouping is by table.
The information in this table may be used to assess the impact of table locking performed by applications. The results may be useful to change how applications issue queries against the database and use table locks, to minimize the application footprint on the server and to improve application performance and scalability. For example, an application locking tables for a long time may negatively affect other applications; the instrumentation makes this visible.
To selectively control which tables to instrument for I/O
and locking, use the
setup_objects
table. See
Pre-Filtering by Object.
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 MySQL Performance Schema.
MySQL distributions now include auth_socket
,
a server-side authentication plugin that authenticates clients
that connect from the local host through the Unix socket file.
The plugin uses the SO_PEERCRED
socket option
to obtain information about the user running the client program
(and thus can be built only on systems that support this
option). For a connection to succeed, the plugin requires a
match between the login name of the connecting client user and
the MySQL user name presented by the client program. For more
information, see
Socket Peer-Credential Pluggable Authentication.
(Bug #59017, Bug #11765993, Bug #9411, Bug #11745104)
MySQL distributions now include
mysql_clear_password
, a client-side
authentication plugin that sends the password to the server
without hashing or encryption. Although this is insecure, and
thus appropriate precautions should be taken (such as using an
SSL connection), the plugin is useful in conjunction with
server-side plugins that must have access to the original
password in clear text. For more information, see
Client-Side Cleartext Pluggable Authentication.
A new plugin service, my_plugin_log_service
,
enables plugins to report errors and specify error messages. The
server writes the messages to the error log. See
MySQL Plugin Services.
Replication:
Support for checksums when writing and reading the binary log is
added to the MySQL Server. Writing checksums into the binary log
is disabled by default; it can be enabled by starting the server
with the --binlog-checksum
option. To cause the server to read checksums from the binary
log, start the server with the
master_verify_checksum
system
variable enabled. The
--slave-sql-verify-checksum
option causes the slave to read checksums from the relay log.
Replication: The MySQL Server now records and reads back only complete events or transactions to and from the binary log. By default, the server now logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly to the log. A master also uses by default this value to verify events when reading from the binary log.
If you enable writing of checksums (using the
binlog_checksum
system
variable), the master can use these instead by enabling the
master_verify_checksum
system
variable. The slave I/O thread also verifies events received
from the master. You can cause the slave SQL thread to use
checksums (if available) as well, when reading from the relay
log, by enabling the
slave_sql_verify_checksum
system variable on the slave.
Replication:
It is now possible to write information about the slave
connection to the master and about the slave's execution point
within the relay log to tables rather than files. Logging of
master connection information and of slave relay log information
to tables can be done independently of one another; this is
controlled by the
master_info_repository
and
relay_log_info_repository
system variables. When
master_info_repository=TABLE
,
connection information is logged in the
slave_master_info
table in the
mysql
system database. When
relay_log_info_repository=TABLE
,
relay log information is logged to the
slave_relay_log_info
table, also in the
mysql
system database.
Replication:
Added the binlog_row_image
server system variable, which can be used to enable row image
control for row-based replication. This means that you can
potentially save disk space, network resources, and memory usage
by the MySQL Server by logging only those columns that are
required for uniquely identifying rows, or which are actually
changed on each row, as opposed to logging all columns for each
and every row change event. In addition, you can use a
“noblob” mode where all columns, except for
unneeded BLOB
or
TEXT
columns, are logged.
For more information, see System Variables Used with Binary Logging. (Bug #47200, Bug #47303, Bug #56917, Bug #11755426, Bug #11755513, Bug #11764116)
Incompatible Change: The following obsolete constructs have been removed. Where alternatives are shown, applications should be updated to use them.
The FLUSH MASTER
and FLUSH
SLAVE
statements. Use the
RESET MASTER
and
RESET SLAVE
statements
instead.
Important Change; Replication:
Added the
binlog_rows_query_log_events
system variable for mysqld. Enabling this
variable causes a server logging in row-based mode to write
informational rows query
log events (SQL statements, for debugging and other
purposes) to the binary log. MySQL server and MySQL programs
from MySQL 5.6.2 and later normally ignore such events, so that
they do not pose an issue when reading the binary log.
mysqld and mysqlbinlog
from previous MySQL releases cannot read such events in the
binary log, and fail if they attempt to do so. For this reason,
you should never prepare logs for a MySQL 5.6.1 or earlier
replication slave server (or other reader such as
mysqlbinlog) with this option enabled on the
master.
(Bug #50935, Bug #11758695)
Performance; InnoDB:
A separate InnoDB
thread
(page_cleaner
) now handles the flushing of
dirty pages that was formerly done by the
InnoDB
master thread.
(Bug #11762412, Bug #55004)
Performance; InnoDB:
The innodb_purge_threads
system
variable can now be set to a value higher than 1.
Performance; InnoDB:
The InnoDB
kernel mutex, which controls
concurrent access to the InnoDB
kernel, has
been split into several
mutexes and
rw-locks, for improved
concurrency.
InnoDB:
InnoDB
can optionally log details about all
deadlocks that occur, to assist with troubleshooting and
diagnosis. This feature is controlled by the
innodb_print_all_deadlocks
system variable.
(Bug #1784, Bug #17572)
Replication:
On MySQL replication slaves having multiple network interfaces,
it is now possible to set which interface to use for connecting
to the master using the
MASTER_BIND='
option in a interface
'CHANGE MASTER TO
statement.
The value set by this option can be seen in the
Master_Bind
column of the output from
SHOW SLAVE STATUS
or the
Bind
column of the
mysql.slave_master_info
table.
(Bug #25939, Bug #11746389)
Replication:
Added the log_bin_basename
system variable, which contains the complete file name and path
to the binary log file. (The
log_bin
system variable shows
only whether or not binary logging is enabled;
log_bin_basename
, however,
reflects the name set with the
--log-bin
server option.) Also
added relay_log_basename
system
variable, which shows the file name and complete path to the
relay log file.
References: See also: Bug #19614, Bug #11745759.
The mysql_upgrade,
mysqlbinlog, mysqlcheck,
mysqlimport, mysqlshow,
and mysqlslap clients now have
--default-auth
and
--plugin-dir
options for specifying which
authentication plugin and plugin directory to use.
(Bug #58139)
The server now includes the thread ID in rows written to the
slow query log. In the slow query log file, the thread ID is the
last value in the line. In the mysql.slow_log
log table, there is a new thread_id
column.
To update the slow_log
table if you are
upgrading from an earlier release, run
mysql_upgrade and restart the server. See
mysql_upgrade — Check and Upgrade MySQL Tables.
(Bug #53630, Bug #11761166)
The server now writes thread shutdown messages to the error log during the shutdown procedure. (Bug #48388, Bug #11756464)
If the init_file
system
variable is given, the server now writes messages indicating the
beginning and end of file execution to the error log.
(Bug #48387, Bug #11756463)
Boolean system variables can be enabled at run time by setting
them to the value ON
or
OFF
, but previously this did not work at
server startup. Now at startup such variables can be enabled by
setting them to ON
or
TRUE
, or disabled by setting them to
OFF
or FALSE
. Any other
nonnumeric value is invalid.
(Bug #46393)
References: See also: Bug #11754743, Bug #51631.
MySQL distributions now include an INFO_SRC
file that contains information about the source distribution,
such as the MySQL version from which it was created. MySQL
binary distributions additionally include an
INFO_BIN
file that contains information
about how the distribution was built, such as compiler options
and feature flags. In RPM packages, these files are located in
the /usr/share/doc/packages/MySQL-server
directory. In tar.gz
and derived packages,
they are located in the Docs
directory
under the location where the distribution is unpacked.
(Bug #42969, Bug #11751935)
Multi-read range access is now based on cost estimates and no longer used for simple queries for which it is not beneficial. (Bug #37576, Bug #11748865)
Previously, for queries that were aborted due to a sort problem,
the server wrote the message Sort aborted
to
the error log. Now the server writes more information to provide
a more specific message, such as:
Sort aborted: Out of memory (Needed 24 bytes) Out of sort memory, consider increasing server sort buffer size Sort aborted: Out of sort memory, consider increasing server sort buffer size Sort aborted: Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1
In addition, if the server was started with
--log-warnings=2
, the server
writes information about the host, user, and query.
(Bug #36022, Bug #11748358)
Previously, for queries that were aborted due to a sort problem
or terminated with KILL
in the middle of a
sort, the server wrote the message Sort
aborted
to the error log. Now the server writes more
information about the cause of the error. These causes include:
Insufficient disk space in the temporary file directory prevented a temp file from being created
Insufficient memory for
sort_buffer_size
to be
allocated
Somebody ran KILL
in the middle of a
filesort operation
id
The server was shut down while some queries were sorting
A transaction was rolled back or aborted due to a lock wait timeout or deadlock
Unexpected errors, such as a source table or even temp table was corrupt
Processing of a subquery failed which was also sorting
(Bug #30771, Bug #11747102)
mysqldump --xml now displays comments from column definitions. (Bug #13618, Bug #11745324)
Windows provides APIs based on UTF-16LE for reading from and
writing to the console. MySQL now supports a
utf16le
character set for UTF-16LE, and the
mysql client for Windows has been modified to
provide improved Unicode support by using these APIs.
To take advantage of this change, you must run mysql within a console that uses a compatible Unicode font and set the default character set to a Unicode character set that is supported for communication with the server. For instructions, see Unicode Support on Windows.
The undocumented SHOW NEW MASTER
statement
has been removed, and the Com_show_new_master
status variable along with it.
Security Fix:
Pre-evaluation of LIKE
predicates during view
preparation could cause a server crash.
(Bug #54568, Bug #11762026)
Incompatible Change; Replication:
It is no longer possible to issue a
CREATE
TABLE ... SELECT
statement which changes any tables
other than the table being created. Any such statement is not
executed and instead fails with an error.
One consequence of this change is that FOR
UPDATE
may no longer be used at all with the
SELECT
portion of a
CREATE
TABLE ... SELECT
.
This means that, prior to upgrading from a previous release, you
should rewrite any
CREATE
TABLE ... SELECT
statements that cause changes in
other tables so that the statements no longer do so.
This change also has implications for replication between a
MySQL 5.6 (or later slave) and a master running a previous
version of MySQL. In such a case, if a
CREATE
TABLE ... SELECT
statement on the master that causes
changes in other tables succeeds on the master, the statement
nonetheless fails on the slave, causing replication to stop. To
keep this from happening, you should rewrite the offending
statement before running it on the master.
(Bug #11749792, Bug #11745361, Bug #39804, Bug #55876)
References: See also: Bug #47899.
Incompatible Change:
When auto_increment_increment
is greater than
one, values generated by a bulk insert that reaches the maximum
column value could wrap around rather producing an overflow
error.
As a consequence of the fix, it is no longer possible for an
auto-generated value to be equal to the maximum BIGINT
UNSIGNED
value. It is still possible to store that
value manually, if the column can accept it.
(Bug #39828, Bug #11749800)
Important Change; Partitioning:
Date and time functions used as partitioning functions now have
the types of their operands checked; use of a value of the wrong
type is now disallowed in such cases. In addition,
EXTRACT(WEEK FROM
, where
col_name
)col_name
is a
DATE
or
DATETIME
column, is now
disallowed altogether because its return value depends on the
value of the
default_week_format
system
variable.
(Bug #54483, Bug #11761948)
References: See also: Bug #57071, Bug #11764255.
Important Change; Replication:
The CHANGE MASTER TO
statement
required the value for RELAY_LOG_FILE
to be
an absolute path, whereas the MASTER_LOG_FILE
path could be relative.
The inconsistent behavior is resolved by permitting relative
paths for RELAY_LOG_FILE
, in which case the
path is assumed to be relative to the slave's data directory.
(Bug #12190, Bug #11745232)
Performance; InnoDB:
An UPDATE
statement for an
InnoDB
table could be slower than
necessary if it changed a column covered by a prefix index, but
did not change the prefix portion of the value. The fix improves
performance for InnoDB 1.1 in MySQL 5.5 and higher, and the
InnoDB Plugin for MySQL 5.1.
(Bug #58912, Bug #11765900)
InnoDB; Partitioning:
The partitioning handler did not pass locking information to a
table's storage engine handler. This caused high contention and
thus slower performance when working with partitioned
InnoDB
tables.
(Bug #59013)
InnoDB:
This fix introduces a new configuration option,
innodb_change_buffer_max_size
,
which defines the size of the
change buffer as a
percentage of the size of the
buffer pool. Because the
change buffer shares memory space with the buffer pool, a
workload with a high rate of DML operations could cause pages
accessed by queries to age out of the buffer pool sooner than
desirable. This fix also devotes more I/O capacity to flushing
entries from the change buffer when it exceeds 1/2 of its
maximum size.
(Bug #11766168, Bug #59214)
InnoDB:
The presence of a double quotation mark inside the
COMMENT
field for a column could prevent a
foreign key constraint from being created properly.
(Bug #59197, Bug #11766154)
InnoDB:
It was not possible to query the
information_schema.INNODB_TRX
table
while other connections were running queries involving
BLOB
types.
(Bug #55397, Bug #11762763)
InnoDB:
InnoDB
returned values for
“rows examined” in the query plan that were higher
than expected. NULL
values were treated in an
inconsistent way. The inaccurate statistics could trigger
“false positives” in combination with the
max_join_size
setting, because
the queries did not really examine as many rows as reported.
A new configuration option
innodb_stats_method
lets you specify how
NULL
values are treated when calculating
index statistics. Allowed values are
nulls_equal
(the default),
nulls_unequal
and
null_ignored
. The meanings of these values
are similar to those of the
myisam_stats_method
option.
(Bug #30423)
Partitioning:
Failed ALTER TABLE
... PARTITION
statements could cause memory leaks.
(Bug #56380, Bug #11763641)
References: See also: Bug #46949, Bug #11755209, Bug #56996, Bug #11764187.
Replication:
When using the statement-based logging format,
INSERT ON
DUPLICATE KEY UPDATE
and
INSERT IGNORE
statements affecting transactional tables that did not fail were
not written to the binary log if they did not insert any rows.
(With statement-based logging, all successful statements should
be logged, whether they do or do not cause any rows to be
changed.)
(Bug #59338, Bug #11766266)
Replication:
Formerly, STOP SLAVE
stopped the
slave I/O thread first and then stopped the slave SQL thread;
thus, it was possible for the I/O thread to stop after
replicating only part of a transaction which the SQL thread was
executing, in which case—if the transaction could not be
rolled back safely—the SQL thread could hang.
Now, STOP SLAVE
stops the slave
SQL thread first and then stops the I/O thread; this guarantees
that the I/O thread can fetch any remaining events in the
transaction that the SQL thread is executing, so that the SQL
thread can finish the transaction if it cannot be rolled back
safely.
(Bug #58546, Bug #11765563)
Replication:
mysqlbinlog printed
USE
statements to its output only
when the default database changed between events. To illustrate
how this could cause problems, suppose that a user issued the
following sequence of statements:
CREATE DATABASE mydb; USE mydb; CREATE TABLE mytable (column_definitions
); DROP DATABASE mydb; CREATE DATABASE mydb; USE mydb; CREATE TABLE mytable (column_definitions
);
When played back using mysqlbinlog, the
second CREATE TABLE
statement
failed with Error: No Database Selected
because the second USE
statement
was not played back, due to the fact that a database other than
mydb
was never selected.
This fix ensures that mysqlbinlog outputs a
USE
statement whenever it reads
one from the binary log.
(Bug #50914, Bug #11758677)
Replication:
The --help
text for
mysqlbinlog now indicates that the
--verbose
(-v
) option outputs pseudo-SQL that is not
necessarily valid SQL and cannot be guaranteed to work verbatim
in MySQL clients.
(Bug #47557, Bug #11755743)
Microsoft Windows: On Windows, an object in thread local storage could be used before the object was created. (Bug #55730, Bug #11763065)
Two unused test files in
storage/ndb/test/sql
contained incorrect
versions of the GNU Lesser General Public License. The files and
the directory containing them have been removed.
(Bug #11810224)
References: See also: Bug #11810156.
Queries that used COALESCE()
with
cp1251
strings could result in an
“illegal mix of collations” error.
(Bug #60101, Bug #11766874)
An assertion was raised if an
XA COMMIT
was
issued when an XA transaction had already encountered an error
(such as a deadlock) that required the transaction to be rolled
back.
(Bug #59986, Bug #11766788)
On some systems, debug builds of comp_err
could fail due to an uninitialized variable.
(Bug #59906, Bug #11766729)
Setting the optimizer_switch
system variable to an invalid value caused a server crash.
(Bug #59894, Bug #11766719)
Attempting to create a spatial index on a
CHAR
column longer than 31 bytes
led to an assertion failure if the server was compiled with
safemutex support.
(Bug #59888, Bug #11766714)
Aggregation followed by a subquery could produce an incorrect result. (Bug #59839, Bug #11766675)
The Performance Schema did not update status handler status
variables, so SHOW
STATUS LIKE '%handler%'
produced undercounted values.
(Bug #59799, Bug #11766645)
Internally, XOR items partially behaved like functions and partially as conditions. This resulted in inconsistent handling and crashes. The issue is fixed by consistently treating XOR items as functions. (Bug #59793, Bug #11766642)
An incorrect character set pointer passed to
my_strtoll10_mb2()
caused an assertion to be
raised.
(Bug #59648, Bug #11766519)
DES_DECRYPT()
could crash if the
argument was not produced by
DES_ENCRYPT()
.
(Bug #59632, Bug #11766505)
The server and client did not always properly negotiate authentication plugin names. (Bug #59453, Bug #11766356)
--autocommit=ON
did not work (it
set the global autocommit
value
to 0, not 1).
(Bug #59432, Bug #11766339)
FIND_IN_SET()
could work
differently in MySQL 5.5 than in 5.1.
(Bug #59405, Bug #11766317)
mysqldump did not quote database names in
ALTER DATABASE
statements in its
output, which could cause an error at reload time for database
names containing a dash.
(Bug #59398, Bug #11766310)
If filesort
fell back to an ordinary
sort/merge, it could fail to handle memory correctly.
(Bug #59331, Bug #11766260)
Comparisons of aggregate values with
TIMESTAMP
values were incorrect.
(Bug #59330, Bug #11766259)
The “greedy” query plan optimizer failed to consider the size of intermediate query results when calculating the cost of a query. This could result in slowly executing queries when there are much faster execution plans available. (Bug #59326, Bug #11766256)
A query of the following form returned an incorrect result,
where the values for col_name
in the
result set were entirely replaced with NULL
values:
SELECT DISTINCTcol_name
... ORDER BYcol_name
DESC;
(Bug #59308, Bug #11766241)
The MYSQL_HOME
environment variable was being
ignored.
(Bug #59280, Bug #11766219)
SHOW PRIVILEGES
did not display a
row for the PROXY
privilege.
(Bug #59275, Bug #11766216)
SHOW PROFILE
could truncate
source file names or fail to show function names.
(Bug #59273, Bug #11766214)
For DIV
expressions, assignment of
the result to multiple variables could cause a server crash.
(Bug #59241, Bug #11766191)
References: See also: Bug #8457.
MIN(
could return an incorrect result in some cases.
(Bug #59211, Bug #11766165)year_col
)
With index condition pushdown enabled, a join could produce an extra row due to parts of the select condition for the second table in the join not being evaluated. (Bug #59186, Bug #11766144)
DELETE
or
UPDATE
statements could fail if
they used DATE
or
DATETIME
values with a year,
month, or day part of zero.
(Bug #59173)
The ESCAPE
clause for the
LIKE
operator permits only
expressions that evaluate to a constant at execution time, but
aggregate functions were not being rejected.
(Bug #59149, Bug #11766110)
Valgrind warnings about uninitialized variables were corrected. (Bug #59145, Bug #11766106)
Memory leaks detected by Valgrind, some of which could cause incorrect query results, were corrected. (Bug #59110, Bug #11766075)
mysqlslap failed to check for a
NULL
return from
mysql_store_result()
and crashed
trying to process the result set.
(Bug #59109, Bug #11766074)
There was an erroneous restriction on file attributes for
LOAD DATA
. The requirement that a
file be located in the database directory or world readable is
now that the be located in the database directory or readable by
the user account used to run the server.
(Bug #59085, Bug #11766052)
SHOW CREATE TRIGGER
failed if
there was a temporary table with the same name as the trigger
subject table.
(Bug #58996, Bug #11765972)
The DEFAULT_CHARSET
and
DEFAULT_COLLATION
CMake options did not work.
(Bug #58991, Bug #11765967)
In a subquery, a UNION
with no
referenced tables (or only a reference to the
DUAL
virtual table) did not permit an
ORDER BY
clause.
(Bug #58970, Bug #11765950)
OPTIMIZE TABLE
for an
InnoDB
table could raise an
assertion if the operation failed because it had been killed.
(Bug #58933, Bug #11765920)
If max_allowed_packet
was set larger than
16MB, the server failed to reject too-large packets with
“Packet too large” errors.
(Bug #58887, Bug #11765878)
With index condition pushdown enabled, incorrect results were
returned for queries on MyISAM
tables involving HAVING
and
LIMIT
, when the column in the
WHERE
condition contained
NULL
.
(Bug #58838, Bug #11765835)
An uninitialized variable for the index condition pushdown access method could result in a server crash or Valgrind warnings. (Bug #58837, Bug #11765834)
A NOT IN
predicate with a subquery containing
a HAVING
clause could retrieve too many rows,
when the subquery itself returned NULL
.
(Bug #58818, Bug #11765815)
Running a query against an InnoDB
table twice, first with index condition pushdown enabled and
then with it disabled, could produce different results.
(Bug #58816, Bug #11765813)
An assertion was raised if a stored routine had a
DELETE IGNORE
statement that failed but due to the IGNORE
had not reported any error.
(Bug #58709, Bug #11765717)
WHERE
conditions of the following forms were
evaluated incorrectly and could return incorrect results:
WHEREnull-valued-const-expression
NOT IN (subquery
) WHEREnull-valued-const-expression
IN (subquery
) IS UNKNOWN
(Bug #58628, Bug #11765642)
Issuing EXPLAIN EXTENDED
for a
query that would use condition pushdown could cause
mysqld to crash.
(Bug #58553, Bug #11765570)
An OUTER JOIN
query using WHERE
could
return an incorrect result.
(Bug #58490, Bug #11765513)col_name
IS NULL
Starting the server with the
--defaults-file=
option, where the file name had no extension, caused a server
crash.
(Bug #58455, Bug #11765482)file_name
Outer joins with an empty table could produce incorrect results. (Bug #58422, Bug #11765451)
In debug builds, SUBSTRING_INDEX(FORMAT(...),
FORMAT(...))
could cause a server crash.
(Bug #58371, Bug #11765406)
When mysqladmin was run with the
--sleep
and
--count
options, it went into
an infinite loop executing the specified command.
(Bug #58221, Bug #11765270)
Some string-manipulating SQL functions use a shared string
object intended to contain an immutable empty string. This
object was used by the SQL function
SUBSTRING_INDEX()
to return an
empty string when one argument was of the wrong data type. If
the string object was then modified by the SQL function
INSERT()
, undefined behavior
ensued.
(Bug #58165, Bug #11765225)
Condition pushdown optimization could push down conditions with incorrect column references. (Bug #58134, Bug #11765196)
injector::transaction
did not have support
for rollback.
(Bug #58082, Bug #11765150)
Parsing nested regular expressions could lead to recursion resulting in a stack overflow crash. (Bug #58026, Bug #11765099)
The fix for Bug #25192 caused load_defaults()
to add an argument separator to distinguish options loaded from
option files from those provided on the command line, whether or
not the application needed it.
(Bug #57953, Bug #11765041)
References: See also: Bug #25192, Bug #11746296.
The mysql client went into an infinite loop if the standard input was a directory. (Bug #57450, Bug #11764598)
If a multiple-table update updated a row through two aliases and the first update physically moved the row, the second update failed to locate the row. This resulted in different errors depending on the storage engine, although these errors did not accurately describe the problem:
For MyISAM
, which is
nontransactional, the update executed first was performed but
the second was not. In addition, for two equal multiple-table
update statements, one could succeed and the other fail
depending on whether the record actually moved, which is
inconsistent.
Now such an update returns an error if it will update a table through multiple aliases, and perform an update that may physically move the row in at least one of these aliases. (Bug #57373, Bug #11764529, Bug #55385, Bug #11762751)
SHOW WARNINGS
output following
EXPLAIN EXTENDED
could include
unprintable characters.
(Bug #57341, Bug #11764503)
Outer joins on a unique key could return incorrect results. (Bug #57034, Bug #11764219)
For a query that used a subquery that included GROUP
BY
inside a < ANY()
construct,
no rows were returned when there should have been.
(Bug #56690, Bug #11763918)
Some RPM installation scripts used a hardcoded value for the data directory, which could result in a failed installation for users who have a nonstandard data directory location. The same was true for other configuration values such as the PID file name. (Bug #56581, Bug #11763817)
On FreeBSD and OpenBSD, the server incorrectly checked the range of the system date, causing legal values to be rejected. (Bug #55755, Bug #11763089)
If one connection locked the mysql.func
table
using either FLUSH TABLES WITH READ
LOCK
or
LOCK TABLE
mysql.func WRITE
and a second connection tried to
either create or drop a UDF function, a deadlock occurred when
the first connection tried to use a UDF function.
(Bug #53322, Bug #11760878)
DISTINCT
aggregates on DECIMAL
UNSIGNED
fields could trigger an assertion.
(Bug #52171, Bug #11759827)
On FreeBSD, if mysqld was killed with a
SIGHUP
signal, it could corrupt
InnoDB
.ibd
files.
(Bug #51023, Bug #11758773)
An assertion could be raised if −1 was inserted into an
AUTO_INCREMENT
column by a statement writing
more than one row.
(Bug #50619, Bug #11758417)
A query that contains an aggregate function but no
GROUP BY
clause is implicitly grouped. If
such a query also contained an ORDER BY
clause, the optimizer could choose to use a temporary table to
perform the ordering. This is unnecessary because implicitly
grouped queries return at most one row and need no ordering.
(Bug #47853)
The parser failed to initialize some internal objects properly, which could cause a server crash in the cleanup phase after statement execution. (Bug #47511, Bug #11755703)
When CASE ... WHEN
arguments had different
character sets, 8-bit values could be referenced as
utf16
or utf32
values,
raising an assertion.
(Bug #44793, Bug #11753363)
When using ExtractValue()
or
UpdateXML()
, if the XML to be
read contained an incomplete XML comment, MySQL read beyond the
end of the XML string when processing, leading to a crash of the
server.
(Bug #44332, Bug #11752979)
Bitmap functions used in one thread could change bitmaps used by other threads, raising an assertion. (Bug #43152, Bug #11752069)
DATE_ADD()
and
DATE_SUB()
return a string if the
first argument is a string, but incorrectly returned a binary
string. Now they return a character string with a collation of
connection_collation
.
(Bug #31384, Bug #11747221)