MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
MySQL Shell 8.0.23 for MySQL Server 8.0 and 5.7 has been released

Dear MySQL users,

MySQL Shell 8.0.23 is a maintenance release of MySQL Shell 8.0 Series (a
component of the MySQL Server). The MySQL Shell is provided under
Oracle’s dual-license.

MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and
5.7. Please upgrade to MySQL Shell 8.0.23.

MySQL Shell is an interactive JavaScript, Python and SQL console
interface, supporting development and administration for the MySQL
Server. It provides APIs implemented in JavaScript and Python that
enable you to work with MySQL InnoDB cluster and use MySQL as a document
store.

The AdminAPI enables you to work with MySQL InnoDB cluster and InnoDB
ReplicaSet, providing integrated solutions for high availability and scalability
using InnoDB based MySQL databases, without requiring advanced MySQL
expertise.  For more information about how to configure and work with
MySQL InnoDB cluster and MySQL InnoDB ReplicaSet see

https://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-userguide.html

The X DevAPI enables you to create “schema-less” JSON document
collections and perform Create, Update, Read, Delete (CRUD) operations
on those collections from your favorite scripting language.  For more
information about how to use MySQL Shell and the MySQL Document Store
support see

https://dev.mysql.com/doc/refman/en/document-store.html

For more information about the X DevAPI see

https://dev.mysql.com/doc/x-devapi-userguide/en/

If you want to write applications that use the the CRUD based X DevAPI
you can also use the latest MySQL Connectors for your language of
choice. For more information about Connectors see

https://dev.mysql.com/doc/index-connectors.html

For more information on the APIs provided with MySQL Shell see

https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/

and

https://dev.mysql.com/doc/dev/mysqlsh-api-python/8.0/

Using MySQL Shell’s SQL mode you can communicate with servers using the
legacy MySQL protocol. Additionally, MySQL Shell provides partial
compatibility with the mysql client by supporting many of the same
command line options.

For full documentation on MySQL Server, MySQL Shell and related topics,
see

https://dev.mysql.com/doc/mysql-shell/8.0/en/

For more information about how to download MySQL Shell 8.0.23, see the
“General Availability (GA) Releases” tab at

http://dev.mysql.com/downloads/shell/

We welcome and appreciate your feedback and bug reports, see

http://bugs.mysql.com/

Enjoy and thanks for the support!

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()
       (https://dev.mysql.com/doc/mysql-shell/8.0/en/monitoring-
       innodb-cluster.html#check-innodb-cluster-status).

     * 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:
               o binlog_transaction_dependency_tracking=WRITESET
               o slave_preserve_commit_order=ON
               o slave_parallel_type=LOGICAL_CLOCK
               o 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
            (https://dev.mysql.com/doc/mysql-shell/8.0/en/config
            uring-innodb-cluster.html#configuring-parallel-appli
            er).

          + 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.

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
       (https://dev.mysql.com/worklog/task/?id=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.

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)

On Behalf of Oracle/MySQL Release Engineering
Prashant Tekriwal