Changes in MySQL Shell 8.0.14 (2019-01-21, General Availability)

• AdminAPI Added or Changed Functionality

• AdminAPI Bugs Fixed

• Functionality Added or Changed

• Bugs Fixed

AdminAPI Added or Changed Functionality

• The Cluster.status() operation has been extended to enable you to display information about the underlying Group Replication group used by the cluster. Now you can retrieve information from all members of a cluster without having to connect to each member individually.

  To see information about the groupName and memberId; and general statistics about the number of transactions checked, proposed, and rejected by members issue:

  Cluster.status({extended:true})

  To see information about recovery and regular transaction I/O, applier worker thread statistics and any lags; applier coordinator statistics, if parallel apply is enabled; error, and other information from I/O and applier threads issue:

  Cluster.status({queryMembers:true})

  In addition, in previous versions the URI-type string shown for groupInformationSourceMember in the output of Cluster.status() could be the cluster's MySQL Router address, rather than the address of the instance which provided the displayed group information. This has been improved to ensure groupInformationSourceMember always shows the correct hostname, or report_host, value and port, or report_port, value of the instance which provided the group information. (Bug #28636963, Bug #26519466, Bug #27824265, Bug #28366027, WL #11997)

• AdminAPI no longer relies on the mysqlprovision check command. This work has resulted in the following:

  • The errors field in the JSON returned by dba.checkInstanceConfiguration() has been removed, because it was only used to hold errors issued by mysqlprovision. Any errors are now reported directly, for example as RuntimeError.

  • The dba.verbose value no longer influences the amount of debug information displayed for dba.checkInstanceConfiguration(), dba.configureInstance(), and dba.configureLocalInstance() because it was only used to control the verbosity of the information displayed from mysqlprovision. Instead, the generic verbose value from MySQL Shell is used to control the verbosity level for those functions.

  • In addition, the messages returned have been generally improved to make them more accurate.

  (WL #12006)

  References: See also: Bug #28737777, Bug #27305806, Bug #28768627, Bug #27702439, Bug #28733883.

• When you create a cluster, you can set the timeout before instances are expelled from the cluster, for example when they become unreachable. Pass the new expelTimeout option to the dba.createCluster() operation, which configures the group_replication_member_expel_timeout variable on the seed instance. All instances running MySQL server 8.0.13 and later which are added to the cluster are automatically configured to have the same group_replication_member_expel_timeout value as configured on the seed instance. (WL #12050)

• You can now configure an InnoDB Cluster's mode while the cluster remains online. This enables you to configure the underlying Group Replication group to choose a specific instance as the new primary in single-primary mode, or to change between multi-primary and single-primary modes without taking the cluster offline. This uses the group coordinator and the functions added in WL#10378, see Configuring an Online Group. Use the following operations:

  • Cluster.setPrimaryInstance(instance), which forces the election of instance as the new primary by overriding any election process.

  • Cluster.switchToMultiPrimaryMode(), which switches the cluster to multi-primary mode. All instances become primaries.

  • Cluster.switchToSinglePrimaryMode([instance]), which switches the cluster to single-primary mode. If instance is specified, it becomes the primary and all the other instances become secondaries. If instance is not specified, the new primary is the instance with the highest member weight (and the lowest UUID in case of a tie on member weight).

  (WL #12052)

• You can now check and modify the settings in place for an InnoDB Cluster while the instances are online. To check the current settings of a cluster, use the following operation:

  • Cluster.options(), which lists the cluster configuration options for its ReplicaSets and instances. A boolean option all can also be specified to include information about all Group Replication system variables in the output.

  This work also enables you to configure the InnoDB Cluster options at a cluster level or instance level, while instances remain online. This avoids the need to remove, reconfigure and then again add the instance to change InnoDB Cluster options. Use the following operations:

  • Cluster.setOption(option, value) to change the settings of all cluster instances globally

  • Cluster.setInstanceOption(instance, option, value) to change the settings of individual cluster instances

  The way which you use InnoDB Cluster options with the operations listed depends on whether the option can be changed to be the same on all instances or not. These options are changeable at both the cluster (all instances) and per instance level:

  • exitStateAction

  • memberWeight

  This option is changeable at the per instance level only:

  • label

  These options are changeable at the cluster level only:

  • failoverConsistency

  • expelTimeout

  • clusterName

  (WL #11465)

• The Cluster.rescan() operation has been extended to enable you to detect changes to the cluster's topology, and modify the cluster metadata, for example to remove old instance data. Now you can:

  • use the updateTopologyMode option to detect if the Group Replication mode (single-primary or multi-primary mode) registered in the metadata matches the current mode of the cluster, updating that information in the metadata if requested through a new option or by a prompt confirmation. You can use this option to update the metadata after using the Cluster.switchToSinglePrimaryMode([instance]) and Cluster.switchToMultiPrimaryMode() options added in WL#12052.

  • use the addInstances option to specify a list of new instances to add to the metadata, or the removeInstances option to specify a list of obsolete instances to remove from the metadata. Pass the auto value to these options to automatically add or remove instances from the metadata, without having to specify an explicit list of instances. This enables the function to update the metadata even in noninteractive mode, making it consistent with the other AdminAPI operations.

  • In addition, a new interactive option has been added to the cluster.rescan() operation, to enable or disable interactive mode prompts specifically for the cluster.rescan() command.

  (WL #10644)

  References: See also: Bug #28997465, Bug #28529362, Bug #28889563, Bug #25675665, Bug #28542904.

• In 8.0.14, Group Replication introduces the ability to specify the failover guarantees (eventual or “read your writes”) if a primary failover happens in single-primary mode (see WL#11123). Configure the failover guarantees of an InnoDB Cluster at creation by passing the new failoverConsistency option to the dba.createCluster() operation, which configures the group_replication_consistency system variable on the seed instance. This option defines the behavior of a new fencing mechanism used when a new primary is elected in a single-primary group. The fencing restricts connections from writing and reading from the new primary until it has applied any pending backlog of changes that came from the old primary (sometimes referred to as “read your writes”). While the fencing mechanism is in place, applications effectively do not see time going backward for a short period of time while any backlog is applied. This ensures that applications do not read stale information from the newly elected primary.

  The failoverConsistency option is only supported if the target MySQL server version is 8.0.14 or later, and instances added to a cluster which has been configured with the failoverConsistency option are automatically configured to have group_replication_consistency the same on all cluster members that have support for the option. The variable default value is controlled by Group Replication and is EVENTUAL, change the failoverConsistency option to BEFORE_ON_PRIMARY_FAILOVER to enable the fencing mechanism. Alternatively use failoverConsistency=0 for EVENTUAL and failoverConsistency=1 for BEFORE_ON_PRIMARY_FAILOVER.

  Note

  Using the failoverConsistency option on a multi-primary InnoDB Cluster has no effect but is allowed because the cluster can later be changed into single-primary mode with the Cluster.switchToSinglePrimaryMode() operation.

  (WL #12067)

AdminAPI Bugs Fixed

• The default value for group_replication_exit_state_action is ABORT_SERVER, but AdminAPI now overrides this and sets the default on instances to READ_ONLY. This ensures that instances which leave the group unexpectedly continue running and can be rejoined to the cluster. (Bug #28701263)

• When a cluster was created on a server that did not have the X Plugin enabled, a silent assumption was being made about the X Protocol port value. Now the value of an X Protocol port is only stored for instances on which X Plugin is enabled. (Bug #27677227)

• The dba.checkInstanceConfiguration() operation was not checking if the Performance Schema was enabled on the target instance. This could result in a situation where you could create a cluster but could not run several management operations on it, for example the Cluster.status() operation. Now, dba.checkInstanceConfiguration() checks that the Performance Schema is enabled on instances. (Bug #25867733)

• When Cluster.checkInstanceState() was executed on an instance which was already a member of the current cluster, the output indicated that the instance was fully recoverable. This was misleading and was caused by a missing validation to ensure the instance does not belong to a cluster. (Bug #24942875)

• The dba.checkInstanceConfiguration() operation did not recognize privileges when they were associated to a user through a role (available in MySQL server 8.0 and higher). In such a case, a missing privileges error was being incorrectly issued despite the user possessing all the required privileges. Now users with their privileges assigned by roles are recognized by AdminAPI operations correctly. (Bug #91394, Bug #28236922)

Functionality Added or Changed

• X DevAPI: The Table and Collection objects now support the .count() method, part of the X DevAPI. (WL #12447)

• When started from the command line, MySQL Shell prints information about the product, information about the session (such as the default schema and connection ID), warning messages, and any errors that are returned during startup and connection. You can now suppress printing of information that you do not need by using the --quiet-start[=1|2] mysqlsh command-line option. With a value of 1 (the default when the option is specified), information about the MySQL Shell product is not printed, but session information, warnings, and errors are printed. With a value of 2, only errors are printed.

  As part of this work, the printed information was tidied up so that the information about the MySQL Shell product is printed before the information about the session. Also, the handling of error printing was normalized to send diagnostic data to stderr, and errors to stdout. (Bug #28833718, Bug #28855291, WL #12631)

• MySQL Shell connections using classic MySQL protocol now support compression for information sent between the client and the server. You can specify compression when you start MySQL Shell and connect using command line options, or in a URI string or a key-value pair when you create a session using other interfaces. You can also use the MySQL Shell configuration option defaultCompress to enable compression for every global session.

  For MySQL Shell connections that use Unix socket files, the --socket command line option can now be specified with no argument to connect using the default Unix socket file for the protocol. (Bug #28730149, WL #12537)

• The MySQL Shell JSON import utility can now process BSON (binary JSON) data types that are represented in JSON documents. The data types used in BSON documents are not all natively supported by JSON, but can be represented using extensions to the JSON format. The import utility can process documents that use JSON extensions to represent BSON data types, convert them to an identical or compatible MySQL representation, and import the data value using that representation. The resulting converted data values can be used in expressions and indexes, and manipulated by SQL statements and X DevAPI functions.      

  To convert JSON extensions for BSON types into MySQL types in this way, you must specify the convertBsonTypes option when you run the import utility. Additional options are available to control the mapping and conversion for specific BSON data types. If you import documents with JSON extensions for BSON types and do not use this option, the documents are imported in the same way as they are represented in the input file. (WL #12134)

• A MySQL Shell configuration option showColumnTypeInfo and command line option --column-type-info have been added to display metadata for each column in a returned result set, such as the column type and collation. The metadata is printed before the result set, and is only shown in SQL mode.

  In the metadata, the column type is returned as both the type used by MySQL Shell (Type), and the type used by the original database (DBType). For MySQL Shell connections using classic MySQL protocol, DBType is as returned by the protocol, and for X Protocol connections, DBType is inferred from the available information. The column length (Length) is returned in bytes. (WL #12426)

• The upgrade checker utility provided by MySQL Shell, which is the checkForServerUpgrade() function of the util global object, has several enhancements:

  • The utility can now select and provide advice and instructions for relevant checks that cannot be automated, and must be performed manually. The manual checks are rated as either warning or notice (informational) level, and are listed after the automated checks. In MySQL Shell 8.0.14, the utility provides advice where relevant about the change of default authentication plugin in MySQL 8.0.

  • A check has been added for the removed log_syslog_* system variables that previously configured error logging to the system log (the Event Log on Windows, and syslog on Unix and Unix-like systems).

  • A check has been added for specific schema inconsistencies that can be caused by the deletion or corruption of a file, including the removal of the directory for a schema and the removal of a .frm file for a table.

  You can access the upgrade checker utility from within MySQL Shell or start it from the command line. For instructions and further information, see MySQL Shell Utilities. (WL #12506)

• MySQL Shell can print results in table, tabbed, or vertical format, or as pretty or raw JSON output. From MySQL Shell 8.0.14, the new MySQL Shell configuration option resultFormat can be used to specify any of these output formats as a persistent default for all sessions, or just for the current session. Changing this option takes effect immediately. Alternatively, the new command line option --result-format can be used at startup to specify the output format for a session. The existing command line options --table, --tabbed, and --vertical are now aliases for the --result-format option given with the corresponding value.

  The existing command line option --json controls JSON wrapping for all MySQL Shell output from a session. Specifying --json or --json=pretty turns on JSON wrapping and generates pretty-printed JSON. Specifying --json=raw turns on JSON wrapping and generates raw JSON. With any of these options, the value of the resultFormat MySQL Shell configuration option is ignored. Specifying --json=off or not specifying the --json option turns off JSON wrapping, and result sets are output as normal in the format specified by the resultFormat configuration option.

  The outputFormat MySQL Shell configuration option is now deprecated. This option combined the JSON wrapping and result printing functions, which have now been separated. If this option is still specified in your MySQL Shell configuration file or scripts, the behavior is as follows:

  • With the json or json/raw value, outputFormat activates JSON wrapping with pretty or raw JSON respectively.

  • With the table, tabbed, or vertical value, outputFormat turns off JSON wrapping and sets the resultFormat MySQL Shell configuration option for the session to the appropriate value.

  (WL #12141)

• The V8 library used by MySQL Shell has been updated to version 6.7.288.46. (WL #12264)

Bugs Fixed

• The TAR build of MySQL Shell comes with Python 2.7. When attempting to include the site package, an error was emitted because of missing build files needed by the include. (Bug #28973138)

• Handling procedures for user-supplied data in MySQL Shell were refactored to ensure correct cleanup after use. (Bug #28915716)

• The exception type and error messages returned by MySQL Shell functions for parameter errors have been standardized across the different functions. (Bug #28838958)

• MySQL Shell stopped unexpectedly if the shell.setCurrentSchema() method was called to set the default schema before an active session had been established. MySQL Shell now validates that there is an active session when the operation takes place. (Bug #28814112)

• The MySQL Shell JSON import utility no longer requires an empty dictionary to be supplied if there are no import options. (Bug #28768585)

• In SQL mode, MySQL Shell does not add statements to the history if they include the strings IDENTIFIED or PASSWORD, or other strings that you configure using the --histignore command option or shell.options["history.sql.ignorePattern"]. However, this previously meant that filtered-out statements were not available to be corrected immediately after entry, and had to be re-typed in case of any errors. MySQL Shell now always makes the last executed statement available to be recalled by pressing the Up arrow, regardless of the filters set in the history ignore list. If filtering applies to the last executed statement, it is removed from the history as soon as another statement is entered, or if you exit MySQL Shell immediately after executing the statement. (Bug #28749037)

• The result printing logic in MySQL Shell has been refactored to use back-end rather than high-level result data, delivering performance improvements for all types of result data and more accurate representation for JSON data. (Bug #28710831)

• A memory leak was fixed that occurred when the new MySQL Shell command-line syntax was used. (Bug #28705373)

• The check for partitioned tables in shared tablespaces in the upgrade checker utility provided by MySQL Shell (the util.checkForServerUpgrade() operation) did not return correct results for the 8.0.11 and 8.0.12 target versions. The check now uses alternative Information Schema tables that are populated with the required information in these versions. (Bug #28701423)

• The MySQL Shell command \option ignored additional arguments separated by spaces that were specified for an option after the initial value. (Bug #28658632)

• MySQL Shell permitted newline characters (line feed and carriage return) in passwords to be passed to a Secret Store Helper using the shell.storeCredential method, resulting in an error in the Secret Store Helper. MySQL Shell now returns an exception if newline characters are used in supplied passwords for the shell.storeCredential method, and does not pass them to the Secret Store Helper. (Bug #28597766)

• On the Windows platform, UTF-8 encoded strings were printed to the console using the cout object, which transfers a byte at a time. This resulted in multi-byte Unicode characters, such as a single quotation mark, being displayed and handled incorrectly. MySQL Shell now uses alternative functions for printing, and verifies that multi-byte UTF-8 characters are emitted as a complete unit. (Bug #28596692)

• When executing an SQL script in MySQL Shell, an inaccurate line number was reported for the location of syntax errors in the script. The number referenced the current SQL block rather than the line number in the script. The error message now uses the global line number. (Bug #28545982)

• The SQL statement splitting logic in MySQL Shell has been refactored to fix a number of issues and to match behaviors of the MySQL command-line tool mysql:

  • The backslash character (\) is no longer accepted in the delimiter string.

  • The use of the word “delimiter” in contexts other than as a command is now handled correctly.

  • In scripts, comments are not discarded, and groups of comments and statements are now split in the same way as mysql would split them.

  • Large scripts can now be successfully split into incremental chunks even when some tokens span across more than one chunk.

  • Scripts can now be parsed in the ANSI_QUOTES SQL mode.

  • Multi-line strings and comments that contain quotes are now parsed correctly.

  • Inline commands are handled in the same way as by mysql, as follows:

    • A \ character appearing at the beginning of a statement is interpreted as the start of a multi-letter MySQL Shell command.

    • A \ character appearing within a statement is interpreted as the start of a single-letter command. The command is executed immediately, then stripped out of the input statement.

    • A \ character appearing after the end of a statement is interpreted as the start of a single-letter command.

  (Bug #27959016, Bug #25689071)

• The handling of Windows named pipe connections by MySQL Shell has been improved and systematized. Now, if you specify the host name as a period (.) on Windows, MySQL Shell connects using a named pipe.

  • If you are connecting using a URI type string, specify user@.

  • If you are connecting using a data dictionary, specify {"host": "."}

  • If you are connecting using individual parameters, specify --host=. or -h .

  By default, the pipe name MySQL is used. You can specify an alternative named pipe using the --socket option or as part of the URI type string. If a URI type string is used, the named pipe must be prepended with the characters \\.\ as well as being either encoded using percent encoding or surrounded with parentheses, as shown in the following examples:

  (\\.\named:pipe)
  \\.\named%3Apipe

  (Bug #27381738)

• When JSON format output was enabled for MySQL Shell, the properties of the Shell API Options class (shell.options) and AdminAPI Cluster class (dba.getCluster) were not printed, only the class name. (Bug #25027181)