Changes in MySQL 5.7.22 (2018-04-19, General Availability)

• Compilation Notes

• Deprecation and Removal Notes

• Test Suite Notes

• X Plugin Notes

• Functionality Added or Changed

• Bugs Fixed

Compilation 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)

Deprecation and Removal Notes

• 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. (WL #11460)

Test Suite Notes

• 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 Notes

• 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)

Functionality Added or Changed

• 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. (WL #11338)

• 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. (WL #9556, WL #10750)

• 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, WL #9692)

• 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"
  }   |
  +---------------------------------------------+

  (WL #9191)

• 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. (WL #9192)

• 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, WL #7987)

Bugs Fixed

• 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: 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)

• 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 lower_case_table_names changes how schema and table names are stored and externalized; depending on the value, this 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)

• Group Replication: Changing the Group Replication required settings incorrectly on online secondary members could result in an unexpected halt. (Bug #27317478, Bug #27157202)

• 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 loadable 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)