MySQL 5.7 Release Notes
Sun RPC is being removed from glibc
.
CMake now detects and uses
libtirpc
if glibc
does not
contain Sun RPC. (You might find it necessary to install
libtirpc
and rpcgen to
take advantage of this CMake feature.)
(Bug #27368272, Bug #89168)
These compatibility SQL modes are now deprecated and will be
removed in MySQL 8.0: DB2
,
MAXDB
,
MSSQL
,
MYSQL323
,
MYSQL40
,
ORACLE
,
POSTGRESQL
,
NO_FIELD_OPTIONS
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
. These
deprecations have two implications:
Assigning a deprecated mode to the
sql_mode
system variable
produces a warning.
With the MAXDB
SQL mode
enabled, using CREATE TABLE
or ALTER TABLE
to add a
TIMESTAMP
column to a table
produces a warning.
Statements that use these deprecated SQL modes may fail when replicated from a MySQL 5.7 master to a MySQL 8.0 slave, or may have different effects on master and slave. To avoid such problems, applications that use the modes deprecated in MySQL 5.7 should be revised not to use them.
Reduction of compiler and platform differences in GIS handling of floating-point results enables simplification of related test cases that no longer need rounding to avoid spurious test failures. Thanks to Daniel Black for the patch. (Bug #26540102, Bug #87223, Bug #27462294)
X Plugin connection attempts using the X Protocol did not return an error when the default database specified in the connection options was invalid, and the connection was allowed with a null default database. Connection attempts using the classic MySQL protocol did return an error and disallowed the connection. X Protocol connection attempts now also disallow the connection if an invalid schema is specified. (Bug #26965020)
Replication: Changes introduced in version 8 which enable XCom to identify members using the concept of an incarnation have been merged in to version 5.7. These underlying changes add a UUID to members each time they join a group and this information can be used to distinguish among different member incarnations.
Replication: It is now possible to specify whether information written into the binary log enables replication slaves to parallelize based on commit timestamps, or on transaction write sets.
Using write sets has a the potential for greater parallelism than using commit timestamps since it does not depend on the commit history. When applying binary logs in this fashion on a replication slave, it may be able to leverage capabilities of the underlying computing hardware (such as CPU cores) and thus speed up this process.
The interface for choosing the source of parallelization is
implemented as a new server system variable
binlog_transaction_dependency_tracking
which can take any one of the values
COMMIT_ORDER
, WRITESET
, or
WRITESET_SESSION
.
COMMIT_ORDER
(the default) causes
parallelization information to be logged using commit
timestamps; WRITESET
causes this information
to be logged using write sets in such a way that any
transactions not updating the same row can be parallelized; and
WRITESET_SESSION
acts in the same fashion as
WRITESET
, except that updates originating
with the same session cannot be reordered. The size of the row
hash history that is kept in memory for tracking transaction
dependencies can be set using
binlog_transaction_dependency_history_size
,
also introduced in this release.
JSON:
The JSON_MERGE()
function is
renamed to JSON_MERGE_PRESERVE()
.
This release also adds the
JSON_MERGE_PATCH()
function, an
RFC 7396 compliant version of
JSON_MERGE_PRESERVE()
; its behavior is the
same as that of JSON_MERGE_PRESERVE()
, with
the following two exceptions:
JSON_MERGE_PATCH()
removes any member in
the first object with a matching key in the second object,
provided that the value associated with the key in the
second object is not JSON null
.
If the second object has a member with a key matching a
member in the first object,
JSON_MERGE_PATCH()
replaces the value in the first object
with the value in the second object, whereas
JSON_MERGE_PRESERVE()
appends the second value to the first
value.
This example compares the results of merging the same 3 JSON
objects, each having a matching key "a"
, with
each of these functions:
mysql>SET @x = '{ "a": 1, "b": 2 }',
>@y = '{ "a": 3, "c": 4 }',
>@z = '{ "a": 5, "d": 6 }';
mysql>SELECT JSON_MERGE_PATCH(@x, @y, @z) AS Patch,
->JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
*************************** 1. row *************************** Patch: {"a": 5, "b": 2, "c": 4, "d": 6} Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
JSON_MERGE()
is still supported as an alias
of JSON_MERGE_PRESERVE()
, but is now
deprecated and subject to removal in a future MySQL version.
See Functions That Modify JSON Values, for more information. (Bug #81283, Bug #23255346)
JSON:
Added the JSON utility function
JSON_PRETTY()
, which prints an
existing JSON
value, or any
string that can successfully be parsed as a JSON document, in a
format that can be easily read by humans. Each JSON object
member or array value is displayed on a separate line of the
output; each child object or array is intended 2 spaces with
respect to its parent.
Examples:
mysql>SELECT JSON_PRETTY('123');
+--------------------+ | JSON_PRETTY('123') | +--------------------+ | 123 | +--------------------+ mysql>SELECT JSON_PRETTY("[1,3,5]");
+------------------------+ | JSON_PRETTY("[1,3,5]") | +------------------------+ | [ 1, 3, 5 ] | +------------------------+ mysql>SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}');
+---------------------------------------------+ | JSON_PRETTY('{"a":"10","b":"15","x":"25"}') | +---------------------------------------------+ | { "a": "10", "b": "15", "x": "25" } | +---------------------------------------------+
JSON:
Added the JSON utility function
JSON_STORAGE_SIZE()
in the MySQL
Server. This function returns the number of bytes used to store
the binary representation of a JSON document, whether the
document is presented as a column value in a table, as the value
of a user variable, or as a JSON literal.
This function, like many other MySQL functions that act on JSON values, also accepts a string that can be successfully parsed as a JSON document. For more information and examples, see JSON Utility Functions.
SHOW CREATE TABLE
normally does
not show the ROW_FORMAT
table option if the
row format is the default format. This can cause problems during
table import and export operations for transportable
tablespaces. MySQL now supports a
show_create_table_verbosity
system variable that, when enabled, causes
SHOW CREATE TABLE
to display
ROW_FORMAT
regardless of whether it is the
default format.
(Bug #27516741)
If the server PID file is configured to be created in a world-writable location, the server now issues a warning suggesting use of a more secure location. (Bug #26585560)
Added two JSON aggregation functions
JSON_ARRAYAGG()
and
JSON_OBJECTAGG()
. The
JSON_ARRAYAGG()
function takes a
column or column expression as an argument, and aggregates the
result set as a single JSON
array, as shown here:
mysql>SELECT col FROM t1;
+--------------------------------------+ | col | +--------------------------------------+ | {"key1": "value1", "key2": "value2"} | | {"keyA": "valueA", "keyB": "valueB"} | +--------------------------------------+ 2 rows in set (0.00 sec) mysql>SELECT JSON_ARRAYAGG(col) FROM t1;
+------------------------------------------------------------------------------+ | JSON_ARRAYAGG(col) | +------------------------------------------------------------------------------+ | [{"key1": "value1", "key2": "value2"}, {"keyA": "valueA", "keyB": "valueB"}] | +------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
The order of the array elements is unspecified.
JSON_OBJECTAGG()
takes two columns or
expressions which it interprets as a key and a value,
respectively; it returns the result as a single
JSON
object, as shown here:
mysql>SELECT id, col FROM t1;
+------+--------------------------------------+ | id | col | +------+--------------------------------------+ | 1 | {"key1": "value1", "key2": "value2"} | | 2 | {"keyA": "valueA", "keyB": "valueB"} | +------+--------------------------------------+ 2 rows in set (0.00 sec) mysql>SELECT JSON_OBJECTAGG(id, col) FROM t1;
+----------------------------------------------------------------------------------------+ | JSON_OBJECTAGG(id, col) | +----------------------------------------------------------------------------------------+ | {"1": {"key1": "value1", "key2": "value2"}, "2": {"keyA": "valueA", "keyB": "valueB"}} | +----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
A NULL
key causes an error; duplicate keys
are ignored.
For more information, see Aggregate Functions. (Bug #78117, Bug #21647417)
InnoDB: An incorrect compression length value in a page compression function caused hole punching to be skipped the first time pages are compressed. (Bug #27399897)
InnoDB:
Attempting to create a temporary table in a file-per-table
tablespace using
CREATE TEMPORARY
TABLE ... TABLESPACE
syntax failed to report an error.
Temporary tablespaces are only permitted in the temporary
tablespace.
(Bug #27361662)
InnoDB: A deadlock between background threads, one attempting to evict a full-text search table from the cache, and the other attempting to synchronize a table, caused InnoDB Cluster nodes to fail. (Bug #27304661)
InnoDB: Failure to skip predicate locks when releasing gaps locks raised debug assertions, as did failure to remove the supremum record bit prior releasing gaps locks on the supremum. (Bug #27272806, Bug #27294066)
InnoDB:
A REPLACE
operation on a
temporary table raised an assertion.
(Bug #27225649, Bug #27229072)
InnoDB:
Concurrent XA transactions that ran successfully to the XA
prepare stage on the master conflicted when replayed on the
slave, resulting in a lock wait timeout in the applier thread.
The conflict was due to the GAP lock range which differed when
the transactions were replayed serially on the slave. To prevent
this type of conflict, GAP locks taken by XA transactions in
READ COMMITTED
isolation
level are now released (and no longer inherited) when XA
transactions reach the prepare stage.
(Bug #27189701, Bug #25866046)
InnoDB:
An ALTER TABLE
operation that
added a foreign key constraint referencing a table with
generated virtual columns raised an assertion.
(Bug #27189701)
InnoDB:
An online ALTER TABLE
operation
on a table accompanied by concurrent DML on the same table
raised an assertion. An end-of-log check was not performed prior
to accessing the DML log to determine the length of a virtual
column.
(Bug #27158030)
InnoDB: When the addition of a virtual index failed, the virtual index that was freed was not removed from the lists of virtual column indexes. (Bug #27141613)
InnoDB: Adding a virtual column and index in the same statement caused an error. (Bug #27122803)
InnoDB:
A tablespace import operation on a server with a default row
format of REDUNDANT
raised an assertion
failure.
(Bug #26960215)
InnoDB: A stored field based on a generated column permitted the base column to have a NULL value. (Bug #26958695)
InnoDB: Evaluation of a subquery in a resolving function raised an assertion. (Bug #26909960)
InnoDB:
An incorrectly specified
innodb_data_file_path
or
innodb_temp_data_file_path
value returned a syntax error that did not specify the name of
the system variable that caused the initialization failure.
(Bug #26805833)
InnoDB: An online DDL operation that rebuilds the table raised an assertion when the last insert log record to be applied was split across two pages. (Bug #26696448, Bug #87532)
InnoDB:
A RENAME TABLE
operation that
renamed the schema failed to rename full-text search common
auxiliary tables that were left behind when the full-text search
index was removed previously, resulting in a assertion failure
when attempting to drop the old schema.
(Bug #26334149)
InnoDB: An assertion was raised when a thread attempted to read a record containing BLOB data while another thread was writing the same data to external pages. (Bug #26300119)
References: This issue is a regression of: Bug #23481444.
InnoDB:
InnoDB
failed to account for a virtual column
when using the column offset to search an index for an
auto-increment column.
(Bug #25076416)
InnoDB: An invalid debug condition caused a buffer pool chunk allocation failure, which resulted in an assertion failure when a purge thread attempted to access an unallocated chunk. (Bug #23593654)
References: This issue is a regression of: Bug #21348684.
Replication:
Group Replication conflict detection uses schema and table names
as part of the Primary Key Equivalent (PKE) in order to detect
and disallow conflicting transactions. The value of the
lower_case_table_names
system
variable changes how schema and table names are stored and
externalized, which depending on the configured value could
persist a table named T1 as t1. Such a difference in a group
could cause inconsistencies. Now, members must all have the same
value for
lower_case_table_names
.
(Bug #27401817)
Replication: Changing the Group Replication required settings incorrectly on online secondary members could result in an unexpected halt. (Bug #27317478, Bug #27157202)
Replication: When a member is joining a group there is a chance of the request to join being rejected. If the rejection resulted in a retry, for example because the seed member being contacted was not in the group, then there was a possibility of the retry cycle continuing infinitely. (Bug #27294009)
Replication:
When write sets are used for parallelization by a replication
slave, the case and accent sensitivity of the database are now
taken into account when generating the write set information.
Write set information is generated when the
transaction_write_set_extraction
system variable is enabled. Previously, duplicate keys could be
incorrectly identified as different, causing transactions to
have incorrect dependencies and so potentially be executed in
the wrong order.
(Bug #26985561, Bug #88120)
Replication:
The statements
CREATE USER IF
EXISTS
(or IF NOT EXISTS
) and
ALTER USER IF
EXISTS
(or IF NOT EXISTS
) were
written to the binary log even when the query resulted in an
error. MySQL Server now checks for errors that cause these
queries to fail (for example, an invalid plugin was specified),
and does not log the statement in that situation. Note that if
these statements succeed but have no effect on the master
because the condition is not met, the statements are written to
the binary log, as the condition might be met on a replication
slave (see Bug #25813089, Bug #85733).
(Bug #26680035)
References: See also: Bug #25813089, Bug #85733.
Replication: For updates to virtual generated columns containing the BLOB data type, both the old and the new BLOB values are required by some storage engines for replication. This fix extends the same behavior to JSON and GEOMETRY data types, which are based on the BLOB data type and so produce the same issue when the old value is not stored. (Bug #25873029)
Replication:
On a multithreaded replication slave (with
slave_parallel_workers
greater
than 0), the slave's lag behind the master was not being
reported by the Seconds_Behind_Master
field
for SHOW SLAVE STATUS
. The value
is now reported correctly. Thanks to Robert Golebiowski for the
patch.
(Bug #25407335, Bug #84415)
Replication:
When invoked with the options
--read-from-remote-server
and --hexdump
,
mysqlbinlog was not able to produce a hex
dump of the binary log contents following an SQL statement that
generated an autoincrement value, referenced a user-defined
variable, or invoked RAND()
. The event types
for these events are followed by an informational row query log
event, and mysqlbinlog caches the original
event for printing when the subsequent row query log event is
received. The pointer to the memory containing the original
event was invalidated when the subsequent event was received, so
the original data could not be accessed to produce the hex dump.
The issue has now been fixed.
(Bug #24674276)
Replication: A number of changes were made to the binary log decoding procedure to improve handling of invalid or corrupted binary log entries. (Bug #24365972)
Replication:
Following the introduction of binary logging for XA transactions
WL#6860,
an assertion could be raised in debug builds during replication
from a master with the feature to a slave without the feature,
if MASTER_AUTO_POSITION=1
was set on the
slave. The assertion has been removed, so that debug builds now
have the same behavior as non-debug builds, and can attempt
replication of unsupported event types whether or not
MASTER_AUTO_POSITION=1
is set.
(Bug #20677683)
Replication:
When using
group_replication_ip_whitelist
,
it was possible to configure a group so that it functioned even
though all members could not establish the internal group
communication connection to each other, resulting in
inconsistent behavior. Now, incoming connections are accepted if
the IP is in the white list or if the IP belongs to a current
member of the XCom configuration. This ensures members are
always able to create the internal network required for group
communication.
(Bug #87834, Bug #26846549, Bug #27406775)
JSON:
Queries that executed a JSON
function that raised an error could cause a server exit.
(Bug #22253965)
Upgrades from MariaDB to MySQL Community Edition failed on Fedora 27. (Bug #27484835)
Selecting from the Performance Schema
status_by_thread
or
variables_by_thread
table was not
thread safe and could yield incorrect results.
(Bug #27471510)
INSERT ... ON DUPLICATE KEY UPDATE
could be
handled improperly if a source table produced no rows.
(Bug #27460607)
The LDAP group search filter specified by the
authentication_ldap_sasl_group_search_filter
or
authentication_ldap_simple_group_search_filter
system variable is now more flexible about whether to insert a
user name or full user DN. The filter value now uses
{UA}
and {UD}
notation to
represent the user name and the full user DN. For example,
{UA}
is replaced with a user name such as
"admin"
, whereas {UD}
is
replaced with a use full DN such as
"uid=admin,ou=People,dc=example,dc=com"
. The
following value is the default, which supports both OpenLDAP and
Active Directory:
(|(&(objectClass=posixGroup)(memberUid={UA})) (&(objectClass=group)(member={UD})))
Previously, if the group search attribute was
isMemberOf
or memberOf
, it
was treated as a user attribute that has group information.
However, in some cases for the user scenario,
memberOf
was a simple user attribute that
held no group information. For additional flexibility, an
optional {GA}
prefix now can be used with the
group search attribute. (Previously, it was assumed that if the
group search attribute is isMemberOf
, it will
be treated differently. Now any group attribute with a {GA}
prefix is treated as a user attribute having group names.) For
example, with a value of {GA}MemberOf
, if the
group value is the DN, the first attribute value from the group
DN is returned as the group name.
(Bug #27438458, Bug #27480946)
Metadata from result sets for UNION ALL
queries could say NEWDATE
rather than
DATE
.
(Bug #27422376)
Linux RPM and Debian packages now include dependency information for the Perl JSON module required to run the MySQL test suite. Linux RPM packages now include dependency information for the Perl Digest module required to run the MySQL test suite. (Bug #27392800, Bug #89250, Bug #27392808, Bug #89244)
When run in key migration mode, the server ignored invalid options. (Bug #27387331)
During configuration, CMake assumed that rpcgen was available rather than checking for it. (Bug #27368078)
The client authentication process could use memory after it had been freed. (Bug #27366143)
-DWITH_ZLIB=system
could cause
other CMake feature tests to fail.
(Bug #27356658, Bug #89135)
Builds using RPM source packages now use a secure connection if Boost must be downloaded. (Bug #27343289, Bug #89104)
The audit_log
plugin could write statements
to the binary log even with binary logging disabled.
(Bug #27315321)
For accounts that authenticated using the
auth_sock
authentication plugin, the server
was unable to accept connections from clients from older MySQL
versions.
(Bug #27306178)
Accounts that authenticated with the
auth_sock
authentication plugin could not
connect using older clients.
(Bug #27306178)
An audit_log
plugin memory leak was
corrected.
(Bug #27302151)
audit_log
plugin user-defined functions did
not report an error on failures.
(Bug #27300689)
LDAP authentication plugins were not built on FreeBSD. (Bug #27238252)
RPM and Debian packages listed openldap-devel
as a dependency for the LDAP authentication plugins, but only
for Enterprise distributions. They now list the dependency for
Community distributions as well.
(Bug #27232163, Bug #88789)
Adding a unique index to an InnoDB
table on
which multiple locks were held could raise an assertion.
(Bug #27216817)
For some statements, the FILE
privilege was not properly checked.
(Bug #27160888)
A multiple-insert statement on a table containing a
FULLTEXT
key and a
FTS_DOC_ID
column caused a server error.
(Bug #27041445, Bug #88267)
References: This issue is a regression of: Bug #22679185.
The audit_log
plugin could mishandle aborts
of event executions, causing a server exit.
(Bug #27008133)
Installing and uninstalling a plugin many times from multiple sessions could cause the server to become unresponsive. (Bug #26946491)
An ALTER TABLE
operation
attempted to set the AUTO_INCREMENT
value for
table in a discarded tablespace.
(Bug #26935001)
MyISAM
index corruption could occur for
bulk-insert and table-repair operations that involve the
repair-by-sorting algorithm and many (more than 450 million)
rows.
(Bug #26929724, Bug #88003, Bug #28483283)
Dropping an index from a system table could cause a server exit. (Bug #26881798)
A prepared statement using
CREATE
TABLE ... SELECT
led to unexpected behavior when it
referred in a GROUP BY
to a view having the
same name.
(Bug #26881703)
The server could dereference a null pointer while loading privileges. (Bug #26881508)
Some diagnostic messages produced by LDAP authentication plugins misleadingly suggested an error when no error had occurred. (Bug #26844713)
A server exit could result from simultaneous attempts by multiple threads to register and deregister metadata Performance Schema objects, or to acquire and release metadata locks. (Bug #26502135)
LDAP authentication plugins could fail if their associated system variables were set to invalid values. (Bug #26474964)
The thread pool plugin logged too much information for failed connections. (Bug #26368725, Bug #86863)
For debug builds, using KILL
to
terminate a stored routine could raise an assertion. Thanks to
Laurynas Biveinis for the patch.
(Bug #26040870, Bug #86260)
If the init_connect
system
variable was set, its contents could not be executed by clients
with expired passwords, who therefore were prevented from
connecting. Now, if a client has an expired password,
init_connect
execution is
skipped, which enables the client to connect and change
password.
(Bug #25968185)
Some memory leaks related to the LDAP authentication plugins were fixed. (Bug #25964438)
Dates using the YYYYMMDD
format were
not recognized correctly in a query meeting all three of the
following conditions:
The query performed a left join.
A DATE
column in the inner table
of the join was part of a multi-column primary key.
Every column in the inner table's primary key was compared with another value; this could be either a literal or a column value. (Bug #25949639)
Using the C API, when trying to execute an
INSERT
prepared statement with
CURSOR_TYPE_READ_ONLY
set, the client hung.
(Bug #25701141, Bug #85105)
Large --ssl-cipher
values could
cause client programs to exit.
(Bug #25483593)
MySQL client programs could exit unexpectedly if malformed client/server protocol packets were received. (Bug #25471090)
Incorrect handling by the CONNECTION_CONTROL
plugin of an internal hash led to spurious messages in the error
log and eventual server exit.
(Bug #25052009)
Conversion of JSON
documents to
string could be slow if the document was large and contained
many signed integers.
(Bug #24586888)
For debug builds, a missing error check on the result of a
subquery that accessed a JSON
value could raise an assertion.
(Bug #22522073)
DO
turned error signals into
warnings.
(Bug #17043030, Bug #69647)
The audit_log
plugin did not log placeholder
values for prepared statements.
(Bug #16617026)
When an on-disk temporary table was created from an in-memory temporary table, the indexes remained uninitialized for the new on-disk table. (Bug #88601, Bug #27214153)
When a stored procedure contained a statement referring to a view which in turn referred to another view, the procedure could not be invoked successfully more than once. (Bug #87858, Bug #26864199)
References: See also: Bug #26627136.
A CREATE
TABLE ... SELECT
statement with a
UNION
in the
SELECT
failed in strict mode for
a DATE
column declared as
NOT NULL
.
(Bug #87711, Bug #27068222)
Prepared statements using nested sub-selects were not always handled correctly. (Bug #87484, Bug #26657904)
Manipulation of a value returned by the
JSON_MERGE()
function using
JSON_SET()
sometimes produced an
invalid result.
(Bug #80787, Bug #22961128)