Changes in MySQL Shell 8.0.23 (2021-01-18, General Availability)

• AdminAPI Added or Changed Functionality

• AdminAPI Bugs Fixed

• Functionality Added or Changed

• Bugs Fixed

AdminAPI Added or Changed Functionality

• The output of the status() operation has been extended to provide more information relevant to diagnosing errors. The following information is available for InnoDB Clusters and InnoDB ReplicaSets:

  • the memberState field shows the actual status of the instance as queried locally, which can be one of offline, error, recovering, or online.

  • a recovery.recoveryChannel field shows instances performing incremental recovery or in which the recovery channel status is not off

  • a new instanceErrors field exists for each instance, displaying any diagnostic information that can be detected for it

  • when the extended option is set to greater than 0, the output includes an applierChannel field, with replication information if the instance is either online and the applier channel status is not on, or the status is not recovering or online and the applier channel status is not off

  For more information, see Checking a cluster's Status with Cluster.status(). (WL #11560)

• InnoDB Cluster and InnoDB ReplicaSet now support and enable parallel replication appliers, sometimes referred to as a multi-threaded replica. With the advances in MySQL such as binary log transaction dependency tracking and XXHASH64 based GTID set extraction, using multiple replica applier threads improves the throughput of both the replication applier and incremental recovery.

  This has resulted in the following changes:

  • the requirements for instances running 8.0.23 and later now also include:

    • binlog_transaction_dependency_tracking=WRITESET

    • slave_preserve_commit_order=ON

    • slave_parallel_type=LOGICAL_CLOCK

    • transaction_write_set_extraction=XXHASH64

    this means that new instances running 8.0.23 have these options configured by dba.configureInstance() and dba.configureReplicaSetInstance(). Attempting to add an instance running version 8.0.23 or later which does not have these variables configured results in an error. When you upgrade a cluster or replica set that has been running a version of MySQL server and MySQL Shell earlier than 8.0.23, the parallel replication applier is not enabled on the instances. This means you are not taking advantage of this feature, and you should reconfigure your instances to use the parallel replication applier. For more information, see Configuring the Parallel Replication Applier.

  • dba.checkInstanceConfiguration() validates if parallel replication appliers are enabled or not.

  • the new applierWorkerThreads option configures the number of replication applier threads the instance uses for replication, and defaults to 4 threads. Use this option with the dba.configureInstance() and dba.configureReplicaSetInstance(). You can change this option while the instance is online, but the change is only made after the instance is restarted.

  • the output of the .status(extended=1) and options() operations now includes information about the configuration of parallel appliers.

  (WL #13837)

AdminAPI Bugs Fixed

• The fix for Bug#29305551 extended the dba.checkInstanceConfiguration() operation to include a check to verify if asynchronous replication is configured and running on the target instance, and print a warning when that is the case. This check is also used by the Cluster.addInstance() and Cluster.rejoinInstance() operations to terminate them with an error when such a scenario is detected, and is also used by the dba.rebootClusterFromCompleteOutage() operation whenever there are instances to be rejoined to the cluster. However, the dba.createCluster() operation was erroneously skipping the check, and the dba.rebootClusterFromCompleteOutage() operation was skipping the check on the instance being used to bootstrap the cluster. The fix ensures that the check is also performed whenever creating or rebooting a cluster from complete outage. Additionally, it adds support to override the check for the dba.createCluster() operation by making use of the force option, and it improves the error messages. (Bug #32197222)

• The fix for Bug#29305551 extended dba.checkInstanceConfiguration() to verify if asynchronous replication is configured and running on the target instance and print a warning if that was the case. However, the check missed verifying if the replication channel was configured but not running. This fix ensures the verification also considers replication channels which are configured but are not actively running. Additionally, an erroneous message which suggested the possibility of using STOP REPLICA to override this check has been removed and replaced with an informative message which explains that unmanaged replication channels are not supported and the possible dangers of their usage. (Bug #32197197)

• Based on the terminology changes in WL#14189, AdminAPI has been aligned with the new terms. Error and log messages now use the terms source (previously master) and replica (previously slave). (Bug #32152133)

• During a Cluster.rebootClusterFromCompleteOutage() operation, the GTID superset is used to detect which instance should be used to reboot the cluster. If an instance had a diverging GTID set and you wanted to explicitly remove it from the cluster, the operation blocked because it could not determine which instance had the GTID superset. Previously, in such a situation there was no way to exclude the instance from the instances used to detect the GTID superset. Now, if you answer no during the interactive wizard, or configure the removeInstances option, the instance is not checked as part of finding the GTID superset. (Bug #32112864)

• When an instance had left a ReplicaSet, and then its configuration was changed in a way that made it invalid for InnoDB ReplicaSet usage, the ReplicaSet.rejoinInstance() operation did not detect that the configuration was invalid. Now, instances are checked to ensure they are valid before rejoining them to a ReplicaSet. (Bug #31975416)

• When upgrading the metadata using dba.upgradeMetadata(), if there are MySQL Router instances that need to be upgraded, the operation waits until all instances are upgraded before continuing. The operation offers you an option to re-check for outdated MySQL Router instances and continue with the metadata upgrade. A MySQL Router upgrade is only complete after a restart of the application, however the message printed did not mention that. This message now includes the information that MySQL Router instances must be restarted after the binaries are upgraded. (Bug #31882876)

• When you were connected to a secondary instance, attempting to issue operations such as Cluster.rejoinInstance(), Cluster.addInstance(), Cluster.dissolve() and so on would fail. Now, AdminAPI always connects to the current primary.

  As part of this work the following changes were made:

  • Now, in the event that dba.createCluster() or Cluster.addInstance() fail with a Group Replication error, AdminAPI returns the performance_schema.error_log entries.

  • The Cluster.rejoinInstance() operation has been changed to succeed if the instance is already in the cluster, instead of throwing an exception.

  • The dba.rebootCluster() operation has been changed to not clear super_read_only on the instance.

  (Bug #31757737)

• As part of the default settings for InnoDB Cluster, to ensure that instances automatically rejoin the cluster, the group_replication_start_on_boot option is automatically set to true. However, this meant that in environments with an external tool managing the cluster life cycle, for example an orchestrator such as Kubernetes, the automatic enabling of rejoin could cause conflicts with the tool. In addition, if the automatic rejoining of an instance was enabled at an unsuitable time (for example when rebooting, or while repairing a split-brain, and so on), a deadlock or long freezes could occur until a timeout happened. In some situations, instances could even potentially join the wrong cluster during a reconfiguration.

  To avoid such situations, the manualStartOnBoot boolean option has been added, which defaults to false. To disable the automatic rejoining of an instance, for example while repairing a split-brain, set the manualStartOnBoot option to true. This prevents the instance rejoining the cluster automatically while you make changes. You then need to rejoin the instance to the cluster manually, before setting the manualStartOnBoot option back to false to ensure instance it rejoins the cluster automatically again. Similarly, if you are using an external orchestrator to manage the life cycle of instances, set the manualStartOnBoot option to true across the whole cluster, to disable the automatic rejoining of instances to the cluster. Your orchestrator should then be configured to rejoin the instances manually. (Bug #31643595)

• Calling dba.checkInstanceConfiguration() with verifyMyCnf set to a file which did not exist, the operation completed successfully saying the configuration file had been checked. The fix checks if the file specified by verifyMyCnf exists, prints an error if not, and ensures the console does not show unnecessary error messages. (Bug #31468546)

• On an instance with the sql_mode variable set to ANSI_QUOTES, attempting to upgrade the metadata schema with dba.upgradeMetadata() failed with the error: Unknown column 'MySQL Router' in 'field list'. This was related to a query which uses single quotes to quote strings. As part of this fix, the upgrade metadata operation now prepares the session to be used by AdminAPI, and amongst other sanity checks it ensures that the sql_mode for that session uses the default value to avoid incompatible user configured settings. Additionally, the same was done for the dba.getCluster() and dba.dropMetadataSchema() operations. (Bug #31428813)

• If the MySQL Shell global session was connected to a sandbox instance, and that instance was stopped, MySQL Shell tried to incorrectly reconnect to the instance. Now, if the active session is connected to a sandbox instance which is being stopped, MySQL Shell closes the session. (Bug #31113914)

• The output of Cluster.status() now includes additional information about instances that are registered in the metadata but not currently online. MySQL Shell now connects to offline instances found in the metadata and attempts to diagnose them, providing additional information such as their connectivity and status. (Bug #30501615)

• Instances that are part of the underlying group but are not identified in the metadata, for example because they were configured manually and bypassing MySQL Shell, or because they were previously removed from the InnoDB Cluster but were not properly decommissioned, are now shown in the output of Cluster.status(), along with diagnostic warnings about the metadata discrepancy. This ensures you can detect situations where an instance is participating in the group but is not being managed by MySQL Shell. (Bug #27882663)

• An instance that belongs to an InnoDB Cluster is identified by its server UUID. If the UUID changed after the instance had left the cluster, for example because you used MySQL Enterprise Backup to restore from a backup, then the instance could not be rejoined to the cluster. Now, if the cluster encounters this situation, it checks the metadata to see if the instance can be identified using its host and port. If found, the metadata is updated based on the options used for the rejoin operation. This check is executed during the Cluster.rejoinInstance() and Cluster.rescan() operations.

  Additionally, a check is executed to verify the serverId of all the instances is registered in the metadata as an instance attribute. If it is not, the metadata is updated accordingly. This check is executed on add, rejoin and rescan operations. (Bug #26649039)

Functionality Added or Changed

• MySQL Shell’s parallel table import utility can now import a specified list of input data files, and it supports wildcard pattern matching to include all relevant files from a location. Multiple files uploaded by a single run of the utility are placed into a single relational table, so for example, data that has been exported from multiple hosts and stored in multiple files could be merged into a single table to be used for analytics. The files can be compressed in the gzip or zstd format, and in that case the utility reads them from storage in the compressed format, saving bandwidth for that part of the transfer. The utility then uses its parallel connections to decompress and upload several files simultaneously to the target server. (WL #13362)

Bugs Fixed

• When MySQL Shell's instance dump utility util.dumpInstance() was run with the ocimds option set to true to check compatibility with MySQL Database Service, and the users option set to true to include users and their roles and grants in the dump, the utility reported some compatibility errors for privileges that actually were permitted. MySQL Shell's allowed list of privileges for MySQL Database Service has now been updated. (Bug #32213605)

• The behavior of MySQL Shell's table dump utility util.dumpTables() and dump loading utility util.loadDump() regarding the schemas for single table dumps and loads has been changed. Previously, the dump files produced for a single table did not contain the SQL statements to recreate the schema, so the schema had to exist in the target MySQL instance before the dump loading utility could load the table. Now, the dumps produced by the table dump utility contain the schema statements, and when they are loaded with the dump loading utility, by default, the schema is created in the target MySQL instance if it does not already exist. The schema option can be used to load the table dump into another schema that exists in the target MySQL instance. Table dumps created using the earlier version of the utility still require the schema option and an existing schema. (Bug #32165101)

• MySQL Shell's table dump utility util.dumpTables() now supports the ocimds, compatibility, ociParManifest, and ociParExpireTime options, so you can check compatibility with MySQL Database Service, and generate pre-authenticated request URLs for the dump files. Also, the ignoreVersion option has been extended to allow the import of a dump that was created without the ocimds option into a MySQL DB System. (Bug #32140970)

• If a dump included users that were created with external authentication plugins, MySQL Shell's dump loading utility util.loadDump() was unable to load the dump if those plugins were not available on the target server instance. The ocimds option for MySQL Shell's instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas which checks compatibility with MySQL Database Service, now checks for accounts using authentication plugins that are not supported in MySQL Database Service. The compatibility option has an additional modification option skip_invalid_accounts, which removes such user accounts. (Bug #32115948)

• Previously, MySQL Shell's dump loading utility util.loadDump() stopped with an error if the loadUsers option was set to true but the supplied dump files did not contain user accounts. The utility now displays a warning and continues in this situation. (Bug #32115861)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() falls back to using the LOCK TABLES privilege to lock dumped tables if the consistent option is set to true, which is the default, and the RELOAD privilege is not available. However, the locking operation could cause an implicit commit on active transactions, meaning that the data was not dumped consistently. The locking has now been corrected to ensure consistency in this situation. (Bug #32107327, Bug #101410)

• When MySQL Shell's dump loading utility util.loadDump() used indexes to identify row boundaries, an error occurred if an index pointed beyond the data in the read buffer. The utility now checks for this situation and ignores the index if so. (Bug #32072961)

• When MySQL Shell was attempting to reconnect to a server, Ctrl + C did not interrupt the operation. The interrupt now functions and sets the retry attempts counter to zero so that the sequence exits correctly. (Bug #32041342)

• MySQL Shell can now be built using Python 3.9. (Bug #32020230)

• The updateGtidSet option for MySQL Shell's dump loading utility util.loadDump() could not be used with MySQL DB System due to a permissions restriction. The utility now uses a stored procedure that is permitted, so the option can be used. (Bug #32009225)

• When MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), or table dump utility util.dumpTables() was exporting to an Oracle Cloud Infrastructure Object Storage bucket, if there was a loss of connectivity or routing to the Object Storage server, MySQL Shell stopped unexpectedly. The error is now handled correctly. (Bug #32005418)

• MySQL Shell's dump loading utility util.loadDump() returned an exception if a header value in a response was empty. (Bug #31979374)

• MySQL Shell did not initialize Python 3.8’s new cf_feature_version compiler flag field, which could cause an exception when format strings were used. (Bug #31926697)

• Where MySQL Shell is using a system installation of Python rather than the bundled version, the minimum version that MySQL Shell supports is now Python 3.6. Python 3.4.3 was the previous minimum for a system installation. The bundled version is Python 3.7.7. (Bug #31900744)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() use table statistics to identify a suitable default row size. If the statistics for a table are outdated or not present, this can cause issues for the chunking process. In this situation, MySQL Shell now issues a message to suggest using an ANALYZE TABLE statement to produce up to date statistics. (Bug #31766490)

• The skipBinlog option for MySQL Shell's dump loading utility util.loadDump() skips binary logging on the target MySQL instance for the import. The option is not suitable for MySQL DB System as the binary logging status cannot be changed, and the import now fails with an error message if the option is used in that situation. For other MySQL instances, the utility now checks whether the user has the required privileges to set the sql_log_bin system variable, and fails with an error message if they do not. (Bug #31748786)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() ordered the data fetched for export using the first column of a unique index for the table. The same method was used to query data for chunking purposes. The utilities now use all columns of the unique index for ordering. In addition, performance is improved by the addition of a cache to store frequently-used instance metadata. The cache is populated for all the schema objects at once, rather than by individual queries as needed. (Bug #31706755)

• MySQL Shell’s disconnect function was added to the shell global object. (Bug #31704380)