Changes in MySQL Shell 8.0.17 (2019-07-22, General Availability)

• AdminAPI Added or Changed Functionality

• AdminAPI Bugs Fixed

• Functionality Added or Changed

• Bugs Fixed

AdminAPI Added or Changed Functionality

• Important Change: The handling of internal recovery accounts created by InnoDB Cluster has been changed so that by default accounts are always created as “mysql_innodb_cluster_server_id@%”, where server_id is instance specific. This generated recovery account name is stored in the InnoDB Cluster metadata, to ensure the correct account is always removed if the instance is removed from the cluster.

  The previous behavior where multiple accounts would be created if ipWhitelist was given has been removed. In addition Cluster.removeInstance() no longer removes all recovery accounts on the instance being removed. It now removes the recovery account of the instance being removed on the primary and waits for the changes to be replicated before actually removing the instance from the group. Similarly, Cluster.rejoinInstance() no longer drops any recovery accounts. It only creates the recovery account of the instance being rejoined if it no longer exists on the primary (which it should in normal circumstances). If the recovery account already exists, it is reused by Cluster.rejoinInstance().

  When a cluster is adopted from an existing Group Replication deployment, new recovery accounts are created and set for each member. Pre-existing accounts configured by the user are left unchanged and not dropped, unless they have the “mysql_innodb_cluster_” prefix.

  As part of this work, the behavior of dba.createCluster() and Cluster.rebootClusterFromCompleteOutage() operations has been changed. Now, if these operations encounter an instance which has super_read_only=ON, it is disabled automatically. Therefore the clearReadOnly option has been deprecated for these operations. (WL #12773)

  References: See also: Bug #29629121, Bug #29559303.

• The dba.createCluster() operation has been improved, and as part of this work the order in which some steps of the operation are executed was changed. Now, the creation of the recovery (replication) user and updates to the Metadata are performed after bootstrapping the Group Replication group. As part of this work, the dba.createCluster() operation has been updated to support the interactive option, which is a boolean value that controls the wizards provided. When interactive is true, prompts and confirmations are displayed by the operation. The default value of interactive is equal to useWizards option. (WL #12011)

• The compatibility policies that Group Replication implements for member versions in groups now consider the patch version of a member's MySQL Server release. Previously, when combining instances running different MySQL versions, only the major version was considered. InnoDB Cluster has been updated to support cluster operations where these compatibility policies have an impact. Using the patch version ensures better replication safety for mixed version groups during group reconfiguration and upgrade procedures. As part of this work the information provided about instances has been extended.

  The following InnoDB Cluster changes have been made to support the compatibility policies:

  • The Cluster.addInstance() operation now detects incompatibilities due to MySQL versions and in the event of an incompatibility aborts with an informative error.

  • The Cluster.status() attribute mode now considers the value of super_read_only and whether the cluster has quorum.

  • The Cluster.status() output now includes the boolean attribute autoRejoinRunning, which is displayed per instance belonging to the cluster and is true when automatic rejoin is running.

  • The extended option has been changed to accept integer or Boolean values. This makes the behavior similar to the queryMembers option, so that option has now been deprecated.

  (WL #13084)

  References: See also: Bug #29557250.

• InnoDB Cluster supports the new MySQL Clone plugin on instances running 8.0.17 and later. When an InnoDB Cluster is configured to use MySQL Clone, instances which join the cluster choose whether to use Group Replication's distributed recovery or MySQL Clone to recover the transactions processed by the cluster. You can optionally configure this behavior, for example to force cloning, which replaces any transactions already processed. You can also configure how Cluster.addInstance() behaves, letting cloning operations proceed in the background or showing different levels of progress in MySQL Shell. This enables you to automatically provision instances in the most efficient way. In addition, the output of Cluster.status() for members in RECOVERING state has been extended to include recovery progress information to enable you to easily monitor recovery operations, whether they be using MySQL Clone or distributed recovery. (WL #13208)

AdminAPI Bugs Fixed

• Important Change: The sandboxes deployed using the AdminAPI did not support the RESTART statement. Now, the wrapper scripts call mysqld in a loop so that there is a monitoring process which ensures that RESTART is supported. (Bug #29725222)

• The Cluster.addInstance() operation did not validate if the server_id of the joining instance was not unique among all cluster members. Although the use of a unique server_id is not mandatory for Group Replication to work properly (because all internal replication channels use --replicate-same-server-id=ON), it was recommended that all instances in a replication stream have a unique server_id. Now, this recommendation is a requirement for InnoDB Cluster, and when you use the Cluster.addInstance() operation if the server_id is already used by an instance in the cluster then the operation fails with an error. (Bug #29809560)

• InnoDB Clusters do not support instances that have binary log filters configured, but replication filters were being allowed. Now, instances with replication filters are also blocked from InnoDB Cluster usage. (Bug #29756457)

  References: See also: Bug #28064729, Bug #29361352.

• On instances running version 8.0.16, the Cluster.rejoinInstance() operation failed when one or more cluster members were in RECOVERING state, because the Group Replication communication protocol could not be obtained. More specifically, the group_replication_get_communication_protocol() function failed because it could only be executed if all members were ONLINE. Now, in the event of the function failing when rejoining an instance a warning is displayed and AdminAPI proceeds with the execution of the operation.

  Starting from MySQL 8.0.17, the group_replication_get_communication_protocol() function no longer issues an error if a member is RECOVERING. (Bug #29754915)

• On Debian-based hosts, hostname resolves to the IP address 127.0.1.1 by default, which does not match a real network interface. This is not supported by Group Replication, which made sandboxes deployed on such hosts unusable unless a manual change to the configuration file was made. Now, the sandbox configuration files created by MySQL Shell contain the following additional line:

  report_host = 127.0.0.1

  In other words the report_host variable is set to the loopback IP address. This ensures that sandbox instances can be used on Debian-based hosts without any additional manual changes. (Bug #29634828)

• If the binary logs had been purged from all cluster instances, Cluster.checkInstanceState() lacked the ability to check the instance's state, resulting in erroneous output values. Now, Cluster.checkInstanceState() validates the value of GTID_PURGED on all cluster instances and provides the correct output and also an informative message mentioning the possible actions to be taken. In addition, Cluster.addInstance() and Cluster.rejoinInstance() were not using the checks performed by Cluster.checkInstanceState() in order to verify the GTID status of the target instance in relation to the cluster. In the event of all cluster instances having their binary logs purged, the Cluster.addInstance() command would succeed but the instance would never be able to join the cluster as distributed recovery failed to execute. Now, both operations make use of the checks performed by Cluster.checkInstanceState() and provide informative error messages. (Bug #29630591, Bug #29790569)

• When using the dba.configureLocalInstance() operation in interactive mode, if you provided the path to an option file it was ignored. (Bug #29554251)

• Calling cluster.removeInstance() on an instance that did not exist, for example due to a typo or because it was already removed, resulted in a prompt asking whether the instance should be removed anyway, and the operation then failing. (Bug #29540529)

• To add or rejoin an instance to an existing InnoDB Cluster, the instance must not already be operating as a replica in asynchronous replication. Previously, dba.checkInstanceConfiguration() incorrectly reported target instances operating as a replica as valid for InnoDB Cluster usage. As a consequence, attempting to use the instance which had been incorrectly validated with operations such as Cluster.addInstance() failed without informative errors.

  Now, dba.checkInstanceConfiguration() verifies if the target instance is already configured as a replica and generates a warning if that is the case. Similarly, the Cluster.addInstance() and Cluster.rejoinInstance() operations detect such instances and block them from InnoDB Cluster usage, failing with an error. Note that this does not prevent instances which belong to a cluster also operating as the source in asynchronous replication. (Bug #29305551)

• The dba.createCluster() operation was allowed on a target instance that already had a populated Metadata schema, when the instance was already in that Metadata. The Metadata present on the target instance was being overridden, which was unexpected. Now, in such a situation the dba.createCluster() throws an exception and you can choose to either drop the Metadata schema or reboot the cluster. (Bug #29271400)

• When a sandbox instance of MySQL had been successfully started from MySQL Shell using dba.startSandboxInstance(), pressing Ctrl+C in the same console window terminated the sandbox instance. Sandbox instances are now launched in a new process group so that they are not affected by the interrupt. (Bug #29270460)

• During the creation of a cluster using the AdminAPI, some internal replication users are created with user names which start with “mysql_innodb_cluster”. However, if the MySQL server had a global password expiration policy defined, for example if default_password_lifetime was set to a value other than zero, then the passwords for the internal users expired after reaching the specified period. Now, the internal user accounts are created by the AdminAPI with password expiration disabled. (Bug #28855764)

• The dba.checkInstanceConfiguration() and dba.configureInstance() operations were not checking the validity of persisted configurations, which can be different from the corresponding system variable value, in particular when changed with SET PERSIST_ONLY. This could lead these operations to report wrong or inaccurate results, for example reporting that the instance configuration is correct when in reality the persisted configuration was invalid and wrong settings could be applied after a restart of the server, or inaccurately reporting that a server update was needed when only a restart was required. (Bug #28727505)

  References: See also: Bug #29765093.

• When you removed an instance's metadata from a cluster without removing the metadata from the instance itself (for example because of wrong authentication or when the instance was unreachable) the instance could not be added again to the cluster. Now, another validation has been added to Cluster.addInstance() to verify if the instance already belongs to the cluster's underlying group but is not in the InnoDB Cluster metadata, issuing an error if it already belongs to the ReplicaSet. Similarly, an error is issued when the default port automatically set for the local address is invalid (out of range) instead of using a random port. (Bug #28056944)

• When issuing dba.configureInstance() in interactive mode and after selecting option number 2 “Create a new admin account for InnoDB cluster with minimal required grants” it was not possible to enter a password for the new user.

Functionality Added or Changed

• MySQL Shell has a new function for SQL query execution for X Protocol sessions that works in the same way as the function for SQL query execution in classic MySQL protocol sessions. The new function, Session.runSql(), can be used in MySQL Shell only as an alternative to X Protocol's Session.sql() to create a script that is independent of the protocol used for connecting to the MySQL server. Note that Session.runSql() is exclusive to MySQL Shell and is not part of the standard X DevAPI. As part of this change, the ClassicSession.query function for SQL query execution, which is a synonym of ClassicSession.runSQL(), is now deprecated.

  A new function fetchOneObject() is also provided for classic MySQL protocol and X Protocol sessions to return the next result as a scripting object. Column names are used as keys in the dictionary (and as object attributes if they are valid identifiers), and row values are used as attribute values in the dictionary. This function enables the query results to be browsed and used in protocol-independent scripts. Updates made to the returned object are not persisted on the database. (WL #12766)

• MySQL Shell's new parallel table import utility provides rapid data import to a MySQL relational table for large data files. The utility analyzes an input data file, divides it into chunks, and uploads the chunks to the target MySQL server using parallel connections. The utility is capable of completing a large data import many times faster than a standard single-threaded upload using a LOAD DATA statement.

  When you invoke the parallel table import utility, you specify the mapping between the fields in the data file and the columns in the MySQL table. You can set field- and line-handling options as for the LOAD DATA command to handle data files in arbitrary formats. The default dialect for the utility maps to a file created using a SELECT ... INTO OUTFILE statement with the default settings for that statement. The utility also has preset dialects that map to the standard data formats for CSV files (created on DOS or UNIX systems), TSV files, and JSON, and you can customize these using the field- and line-handling options as necessary. (WL #12193)

• MySQL Shell has a number of new display options for query results:

  • The shell.dumpRows() function can format a result set returned by a query in any of the output formats supported by MySQL Shell, and dump it to the console. Note that the result set is consumed by the function. This function can be used in MySQL Shell to display the results of queries run by scripts to the user in the same ways as the interactive SQL mode can.

  • The new MySQL Shell output format json/array produces raw JSON output wrapped in a JSON array. The output format ndjson is added as a synonym for json/raw, and both those output formats produce raw JSON output delimited by newlines. You can select MySQL Shell output formats by starting MySQL Shell with the --result-format=[value] command line option, or setting the MySQL Shell configuration option resultFormat.

  A new function shell.unparseUri() is also added, which converts a dictionary of URI components and connection options into a valid URI string for connecting to MySQL. (WL #13030)

• You can now extend MySQL Shell with plugins that are loaded at startup. MySQL Shell plugins can be written in either JavaScript or Python, and the functions they contain are available in MySQL Shell in both JavaScript and Python modes. The plugins can be used to contain functions that are registered as MySQL Shell reports, and functions that are members of extension objects that are made available as part of user-defined MySQL Shell global objects.

  You can create a MySQL Shell plugin by storing code in a subfolder of the plugins folder in the MySQL Shell user configuration path, with an initialization file that MySQL Shell locates and executes at startup. You can structure a plugin group, with a collection of related plugins that can share common code, by placing the subfolders for multiple plugins in a containing folder under the plugins folder. (WL #13051)

• You can now extend the base functionality of MySQL Shell by defining extension objects and making them available as part of additional MySQL Shell global objects. Extension objects can be written in JavaScript or Python. When you create and register an extension object, it is available in MySQL Shell in both JavaScript and Python modes. You construct and register extension objects using functions provided by the built-in global object shell. (WL #12625)

• You can now configure MySQL Shell to send logging information to the console, in addition to sending it to the application log. The --verbose command-line option and the verbose MySQL Shell configuration option activate this function. By default, when the option is set, internal error, error, warning, and informational messages are sent to the console, which is the equivalent to a logging level of 5 for the application log. You can add three further levels of debug messages, up to the highest level of detail. (WL #13047)

• MySQL Shell's upgrade checker utility (the util.checkForServerUpgrade() operation) carries out two new checks. When checking for upgrade from any MySQL 5.7 release to any MySQL 8.0 release, the utility identifies partitioned tables that use storage engines other than InnoDB or NDB and therefore rely on generic partitioning support from the MySQL server, which is no longer provided. When checking for upgrade from any release to MySQL 8.0.17, the utility identifies circular directory references in tablespace data file paths, which are no longer permitted. (WL #13140)

• X DevAPI now supports indexing array fields. A single index field description can contain a new member name array that takes a Boolean value. If set to true, the field is assumed to contain arrays of elements of the given type. In addition, the set of possible index field data types (used as values of member type in index field descriptions) is extended with type CHAR(N), where the length N is mandatory. (WL #12254)

• MySQL Shell now supports the ability to send connection attributes (key-value pairs that application programs can pass to the server at connect time). MySQL Shell defines a default set of attributes, which can be disabled or enabled. In addition, applications can specify attributes to be passed in addition to the default attributes. The default behavior is to send the default attribute set.

  You specify connection attributes as a connection-attributes parameter in a connection string. The connection-attributes parameter value must be empty (the same as specifying true), a Boolean value (true or false to enable or disable the default attribute set), or a list or zero or more key=value specifiers separated by commas (to be sent in addition to the default attribute set). Within a list, a missing key value evaluates as an empty string. Examples:

  "mysqlx://user@host?connection-attributes"
  "mysqlx://user@host?connection-attributes=true"
  "mysqlx://user@host?connection-attributes=false"
  "mysqlx://user@host?connection-attributes=[attr1=val1,attr2,attr3=]"
  "mysqlx://user@host?connection-attributes=[]"

  You can specify connection attributes for both X Protocol connections and classic MySQL protocol connections. The default attributes set by MySQL Shell are:

  > \sql SELECT ATTR_NAME, ATTR_VALUE FROM performance_schema.session_account_connect_attrs;
  +-----------------+------------+
  | ATTR_NAME       | ATTR_VALUE |
  +-----------------+------------+
  | _pid            | 28451      |
  | _platform       | x86_64     |
  | _os             | Linux      |
  | _client_name    | libmysql   |
  | _client_version | 8.0.17     |
  | program_name    | mysqlsh    |
  +-----------------+------------+

  Application-defined attribute names cannot begin with _ because such names are reserved for internal attributes.

  If connection attributes are not specified in a valid way, an error occurs and the connection attempt fails.

  For general information about connection attributes, see Performance Schema Connection Attribute Tables. (WL #12446)

• MySQL Shell now supports the OVERLAPS and NOT OVERLAPS operators for expressions on JSON arrays or objects:

  expr OVERLAPS expr
  expr NOT OVERLAPS expr

  These operators behave in a similar way to the JSON_OVERLAPS() function. Suppose that a collection has these contents:

  mysql-js> myCollection.add([{ "_id": "1", "list": [1, 4] }, { "_id": "2", "list": [4, 7] }])

  This operation:

  mysql-js> var res = myCollection.find("[1, 2, 3] OVERLAPS $.list").fields("_id").execute();
  mysql-js> res

  Should return:

  {
  "_id": "1"
  }
  1 document in set (0.0046 sec)

  This operation:

  mysql-js> var res = myCollection.find("$.list OVERLAPS [4]").fields("_id").execute();
  mysql-js> res

  Should return:

  {
  "_id": "1"
  }
  {
  "_id": "2"
  }
  2 documents in set (0.0031 sec)

  An error occurs if an application uses either operator and the server does not support it. (WL #12767)

Bugs Fixed

• With MySQL Shell in Python mode, using auto-completion on a native MySQL Shell object caused informational messages about unknown attributes to be written to the application log file. (Bug #29907200)

• The execution time for statements issued in MySQL Shell in multiple-line mode has been reduced by reparsing the code only after the delimiter is found. (Bug #29864587)

• Python's sys.argv array was only initialized when MySQL Shell was started in batch mode, and was not initialized when MySQL Shell was started in interactive mode. (Bug #29811021)

• MySQL Shell incorrectly encoded the CAST operation as a function call rather than a binary operator, resulting in SQL syntax errors. (Bug #29807711)

• MySQL Shell now supports the unquoting extraction operator ->> for JSON. (Bug #29794340)

• Handling of empty lines in scripts processed by MySQL Shell in batch mode has been improved. (Bug #29771369)

• On Windows, when a MySQL Shell report was displayed using the \watch command, pressing Ctrl+C to interrupt execution of the command did not take effect until the end of the refresh interval specified with the command. The interrupt now takes effect immediately. Also, any queries executed by reports run using the \show or \watch commands are now automatically canceled when Ctrl+C is pressed. (Bug #29707077)

• In Python mode, native dictionary objects created by MySQL Shell did not validate whether they contained a requested key, which could result in random values being returned or in a SystemError exception being thrown. Key validation has now been added, and a KeyError exception is thrown if an invalid key is requested. (Bug #29702627)

• When using MySQL Shell in interactive mode, if raw JSON output was being displayed from a source other than a terminal (for example a file or a pipe), in some circumstances the prompt was shown on the same line as the last line of the output. The issue has now been corrected, and a new line is printed before the prompt message if the last line of the output did not end with one. (Bug #29699640)

• The MySQL Shell \sql command, which executes a single SQL statement while another language is active, now supports the \G statement delimiter to print result sets vertically. (Bug #29693853)

• Some inconsistencies in MySQL Shell's choice of stdout or stderr for output have been corrected, so that only expected output that is intended to be processed by other programs goes to stdout, and all informational messages, warnings, and errors go to stderr. (Bug #29688637)

• When MySQL Shell was started with the option --quiet-start=2 to print only error messages, warning messages about the operation of the upgrade checker utility checkForServerUpgrade() were still printed. (Bug #29620947)

• In Python mode, native dictionary objects created by MySQL Shell did not provide an iterator, so it was not possible to iterate over them or use them with the in keyword. Functionality to provide Python's iterator has now been added. (Bug #29599261)

• When a MySQL Shell report was displayed using the \watch command, the screen was cleared before the report was rerun. With a report that executed a slow query, this resulted in a blank screen being displayed for noticeable periods of time. The screen is now cleared just before the report generates its first text output. (Bug #29593246)

• MySQL Shell's upgrade checker utility checkForServerUpgrade() returned incorrect error text for each removed system variable that was detected in the configuration file. (Bug #29508599)

• MySQL Shell would hang when attempting to handle output from a stored procedure that produced results repeatedly from a single statement. The issues have now been corrected. (Bug #29451154, Bug #94577, Bug #28880081, Bug #93070)

• You can now specify the command line option --json to activate JSON wrapping when you start MySQL Shell to use the upgrade checker utility. In this case, JSON output is returned as the default, and you can choose raw JSON format by specifying --json=raw. Also, warning and error messages relating to running the utility have been removed from the JSON output. (Bug #29416162)

• In SQL mode, when MySQL Shell was configured to use an external pager tool to display output, the pager was invoked whether or not the query result was valid. For an invalid query, this resulted in the pager displaying an empty page, and the error message was only visible after quitting the pager. The pager tool is now only invoked when a query returns a valid result, otherwise the error message is displayed. (Bug #29408598, Bug #94393)

• MySQL Shell did not take the ANSI_QUOTES SQL mode into account when parsing quote characters. (Bug #27959072)

• Prompt theme files for MySQL Shell that were created on Windows could not be used on other platforms. The issue, which was caused by the parser handling the carriage return character incorrectly, has now been fixed. (Bug #26597468)

• The use of the mysqlsh command-line option --execute (-e) followed by --file (-f) when starting MySQL Shell is now disallowed, as these options are mutually exclusive. If the options are specified in that order, an error is returned. Note that if --file is specified first, --execute is treated as an argument of the processed file, so no error is returned. (Bug #25686324)

• Syntax errors returned by MySQL Shell's JavaScript expression parser have been improved to provide context and clarify the position of the error. (Bug #24916806)