13 Changes in MySQL Shell 8.0.31 (2022-10-11, General Availability)

AdminAPI Added or Changed Functionality

The AdminAPI command dba.rebootClusterFromCompleteOutage() has been reimplemented for usability and to reduce complexity of operation and the potential for split-brain scenarios.
Topology changes are no longer permitted. The following options were removed:
- removeInstances
- rejoinInstances
The following options were added:
- force: executes the operation even if some members of the Cluster cannot be reached, or the primary instance selected has a diverging or lower GTID_SET.
- primary: instance definition representing the instance that must be selected as the primary.
- dryRun: all validations and steps of the command are executed, but no changes are actually made.
If enabled on the primary instance of the cluster, while in single-primary mode, super_read_only is automatically disabled.
See Rebooting a Cluster from a Major Outage. (Bug #33871079, Bug #30877552, Bug #26586818, Bug #25487521, Bug #29763863, Bug #32002281, Bug #28939352, Bug #33389693, Bug #33791275, Bug #33875723, WL #11561)
The -- cluster option is extended to assign a ClusterSet variable if the selected Cluster is a member of a ClusterSet. (Bug #108112, Bug #34485950)

AdminAPI Bugs Fixed

Clusters upgraded from 8.0.26 to 8.0.27, or higher, only had access to the Group Replication member actions available in 8.0.26. The upgraded members did not have access to any action introduced after 8.0.26, such as mysql_start_failover_channels_if_primary, which was introduced in 8.0.27.
As a result, attempting to create a Replica Cluster resulted in an error.
As of this release, createReplicaCluster() resets the Group Replica member actions of the target instance using group_replication_reset_member_actions(). (Bug #34465006)
clusterSet.setRoutingOption(option, null) reset all routing options to their default values instead of resetting the specified option, only. (Bug #34458017)
It was not possible to change a cluster's options, using cluster.setOption(), if the cluster was a member of a ClusterSet. A runtime error occurred. (Bug #34457903)
It was not possible to use cluster.fenceWrites() on Replica Clusters.
As of this release, it is possible to use cluster.fenceWrites() on INVALIDATED Replica Clusters. Also, if run against a Replica Cluster with super_read_only disabled, it will enable it.
If cluster.fenceWrites() is run on a healthy Replica Cluster, no changes are made.
It is not possible to use cluster.unfenceWrites() on a Replica Cluster. (Bug #34417802)
Rejoining an instance to a Replica Cluster, using the MYSQL communication stack, resulted in an error if the primary cluster of the ClusterSet was under heavy load. The rejoin process recreates the replication account on the primary cluster of the ClusterSet and this transaction took too long to replicate to the Replica Cluster where it was required for Group Replication. (Bug #34264094)
MySQL Router periodically updates the last_check_in of the Cluster metadata schema. Under certain circumstances, such as a ClusterSet split-brain scenario, these updates can make rejoin operations impossible due to the creation of errant transactions.
As of this release, a new settable option is added in the Metadata schema, stats_updates_frequency. This option defines, in seconds, the interval between MySQL Router check-in updates.
For more information on MySQL Router's behavior, see the MySQL Router 8.0.31 documentation.
This value can be set using Cluster.setRoutingOptions and can be viewed using Cluster.routingOptions([router]).
If set to 0 (default), no periodic updates are done. MySQL Router rounds up the value to a multiple of its TTL. For example:
- If lower than TTL its gets rounded up to TTL. For example: if TTL=30 and stats_updates_frequency=1, the effective frequency is 30 seconds.
- If not a multiple of TTL, it is rounded up and adjusted according to the TTL. For example, if TTL=5 and stats_updates_frequency=11, the effective frequency is 15 seconds, or if TTL=5 and stats_updates_frequency=13, the effective frequency is 15 seconds.
If the value is null, the option value is cleared and the default value takes effect. (Bug #34190956)
Under certain circumstances, it is possible for the transaction size on a Replica to be larger than that on the source. In ClusterSets, this can result in issues with the ClusterSet replication channel. To avoid this issue, the following changes were made:
- group_replication_transaction_size_limit is set to the maximum value in Replica Clusters and stores the original value in the metadata schema so it can be restored in the event of a switchover or failover.
- You can manually define group_replication_transaction_size_limit when creating a Cluster or Replica Cluster with the option transactionSizeLimit.
- addInstance() and rejoinInstance(), automatically set the value of group_replication_transaction_size_limit to ensure all members use the same value.
- You can change the value of group_replication_transaction_size_limit on a running Cluster with Cluster.setOption().
- You can view the option's value with Cluster.options().
- The original value of the option is restored if the Cluster is removed from a ClusterSet using removeCluster().
- cluster.Status() detects inconsistencies in member values and enables you to correct them with Cluster.rescan().
(Bug #34011262)
dba.upgradeMetadata() displayed messages relating to InnoDB Cluster regardless of the metadata being upgraded. (Bug #33931291)
Commands which called external processes, such as dba.deploySandboxInstance(), did not include the name of the external process in the error message if the process failed. As of this release, if the external process fails, its name is included in the error message. (Bug #28543317)
clusterSet.status() failed with a logic error if run against a ClusterSet where one or more of the Replica Clusters were offline and the Group Replication plugin was uninstalled from each member. (Bug #108066, Bug #34465132)
ClusterSet commands which perform transaction set consistency checking, such as rejoinCluster and setPrimaryCluster, became unresponsive during view change log event reconciliations if the write load was high. This occurred because reconciliation included the entire transaction backlog instead of just the view change log events. (Bug #108065, Bug #34462141)
Cluster failover could fail under high load because the cluster being promoted was compared with itself in checks to confirm the promoted cluster was the most up-to-date. This comparison failed because the applier was catching up and the GTID_EXECUTED comparison resulted in two different values.
As of this release, the check for most up-to-date cluster does not include the promoted cluster. (Bug #108064, Bug #34465882)

Functionality Added or Changed

The upgrade checker utility now checks for schema and table names using invalid characters. (Bug #34460142)
A new command line option -c/--pyc=cmd is added in this release. This command enables you to execute a Python command and quit. Any options specified after the command are treated as arguments of the processed command. (Bug #34361996)
The keyword FULL was added to the upgrade checker utility's keyword list. (Bug #34285219)
Load performance is improved for parallel load of dumps with large numbers of large tables. (Bug #34173880)
The following new diagnostics were added in this release:
- util.debug.collectHighLoadDiagnostics(path[, options]): runs multiple iterations of diagnostics on the target MySQL server.
- util.debug.collectSlowQueryDiagnostics(path, query[, options]): runs diagnostics for the duration of the supplied query's duration.
For more information, see collectDiagnostics Utility.
The options customShell and customSql, included in both new diagnostics, were also added to the existing util.debug.collectDiagnostics:
- customShell: array of additional operating system shell commands to run.
- customSql: array of additional SQL statements to run.
Note
The options customShell and customSql can be run with before, during, and after when run in util.debug.collectHighLoadDiagnostics and util.debug.collectSlowQueryDiagnostics. They are run before the metrics collection begins in util.debug.collectDiagnostics and this is not configurable.
As part of this development, the following auxiliary functions were added to the mysql module:
- mysql.parseStatementAst(sql): returns AST encoded as a JSON structure.
- mysql.splitScript(script): returns a string containing multiple statements.
(Bug #34056331, Bug #34056336, WL #14886)
MySQL Shell now supports the parallel index creation introduced in MySQL Server 8.0.27. Previously, the dump loading utilities added indexes sequentially, one at a time. As of this release, all indexes in a table are added simultaneously.
See Configuring Parallel Threads for Online DDL Operations for restrictions and configuration. (Bug #33976718)
The schema option of utils.loadDump() is extended to enable creation of the schema if it does not already exist.
This load option is supported for single schema dumps, or if the filtering options result in a single schema.
Note
If the new schema name differs from the schema name in the dump, the dump is loaded to the new schema, but no changes are made to the loaded data. That is, any reference to the old schema name remains in the data. All stored procedures, views, and so on, refer to the original schema, not the new one.
(Bug #33799352)
MySQL Shell now retrieves the most up-to-date view of the managed topology before performing any validation, update, or operation. getCluster(), getClusterSet(), or getReplicaSet() are now called first. This ensures all operations on AdminAPI objects are run against the latest version of that object.
Note
The dba.getCluster() option, connectToPrimary, is deprecated in this release. The operation now always connects to the primary member, if possible. If not, it connects to a secondary.
This patch also resolves the problem of the excessive number of sessions and metadata refreshes happening with each command executed. Several refactors of the usage of connection pools were done. (Bug #30884646, Bug #33381060, Bug #33415656, WL #14862)
Support was added for Python modules which invoke the Python interpreter, such as multiprocessing. (Bug #99796, Bug #31460929)
MySQL Shell's SQL Auto Completion is now context aware. It now presents object names, keywords, and so on, which are relevant to the command being entered and the context that command is in.
The autocompletion API is exposed to developers through the functions shell.autoCompleteSql(statement, options) (JavaScript) and shell.auto_complete_sql(str statement, dict options) (Python).
See Autocompleting SQL. (WL #13397)

Bugs Fixed

It was not possible to install MySQL Shell from the Compressed TAR installation package on Oracle Linux 9 due to a dependency on the libcrypt library, which is not delivered on that platform. (Bug #34495554)
It was not possible to import a dump or table on Windows if the file size was larger than 4GB. The error, stoul argument out of range was returned. (Bug #34479702)
Under certain circumstances, when loading a table, the index creation and analyze table tasks could occur before the table was loaded. (Bug #34465642)
Under certain circumstances, when processing command-line arguments, such as --cluster rescan, MySQL Shell attempted to write to the log file before it was initialized. This resulted in a segmentation fault. As of this release, the command line parser does not attempt to write to the log. (Bug #34460776)

Accessing a non-existing key in a Shell dictionary using the get() method resulted in an exception similar to the following:

        Traceback (most recent call last):
        File "<string>", line 1, in <module>
        SystemError: 
        <built-in method get of shell.Dict object at 0x7fa458731c90>
        returned a result with an error set

(Bug #34403275)

The Python getattr() function threw an exception when used with MySQL Shell dictionaries. (Bug #34387005)
It was not possible to load files with CRLF line endings in JavaScript mode. This affected the following JS loading methods:
- os.loadTextFile() function
- \source command
- JavaScript plugin loading
(Bug #34321108)
util.debug.collectDiagnostics() failed if a log file included non-utf8 characters. (Bug #34208308)
Data was not loaded for util.loadDump or util.importTable, if the server global variable AUTOCOMMIT was set to OFF.
As of this release, the AUTOCOMMIT session variable is set to ON by the load and import utilities. (Bug #34173126, Bug #33360787)
MySQL Shell output BIT columns as base10 integers instead of HEX strings. As of this release, BIT columns are output as HEX strings. (Bug #34110337)
It was not possible to load a dump created from a MySQL 5.7 instance if it contained a table with a BLOB, TEXT, GEOMETRY or JSON column with a default value.
As of this release, the upgrade checker utility scans for such tables and reports an error if they are found. (Bug #34063868)
In certain circumstances, importing dumps from MySQL-compatible databases resulted in syntax errors due to differences in CREATE USER syntax.
As of this release, MySQL Shell detects the invalid syntax during the dump process, displays an error, and provides information on how to exclude such users. (Bug #34049624)
The upgrade checker utility did not detect reserved words in stored routines. (Bug #33866437)
MySQL Shell included log_syslog in the configuration files of sandbox instances. This option was removed in MySQL 8.0.13. (Bug #29882585)
The MySQL Shell man pages were not included in the sources or installation packages. (Bug #29770787)
Passwords were not accepted when provided by the --passwords-from-stdin command-line option. (Bug #108158, Bug #34501568)
The following issues were found in the MySQL Shell source files:
- INSTALL had the following issues:
  - The mandatory dependencies listed Python 3.7. Python 3.8 is the minimum required version.
  - The mandatory dependencies did not list the required libssh version, 0.9.2.
  - The mandatory dependencies listed CMake 2.8.12. CMake 3.5.1 is the minimum required version.
  - The Linux build instructions included an incorrect path change, cd bld, instead of cd mysql-shell/bld.
- README contained an incorrect link for the InnoDB Cluster documentation.
- postrm.in contained an inaccurate message.
Thanks to Evgeniy Patlan for these contributions. (Bug #107412, Bug #34219276, Bug #107415, Bug #34219284, Bug #107411, Bug #34219274, Bug #107413, Bug #34219279, Bug #107416, Bug #34219285, Bug #107414, Bug #34219283)
Session variables, such as error_count, which are affected by the last executed statement, changed unexpectedly if MySQL Shell custom prompts used queried variables. (Bug #104955, Bug #33362817)