Changes in MySQL Shell 8.0.27 (2021-10-19, General Availability)

• Deprecation and Removal Notes

• AdminAPI Bugs Fixed

• Functionality Added or Changed

• Bugs Fixed

Deprecation and Removal Notes

• The options for AdminAPI’s dba.rebootClusterFromCompleteOutage() operation to specify a user and password to run the required tasks are now deprecated and should not be used. The user and password are taken from the active MySQL Shell session. (Bug #26586818)

AdminAPI Bugs Fixed

• MySQL Shell’s progress reporting for InnoDB Cluster operations could result in non-JSON output being produced when MySQL Shell was set to return JSON output. The issue has now been corrected. (Bug #33237648)

• The recovery user did not support SSL. As of this release, if the server is configured to use SSL, the recovery user is also configured to use it. (Bug #31227280)

• InnoDB Cluster error messages for an issue with a specific target instance now include the host name and port for the affected instance, so that it can be identified if the operation involved multiple instances. (Bug #28365584)

• AdminAPI’s dba.stopSandboxInstance() operation attempted to verify the existence of the PID file for logging purposes, but the operation stopped if the file was not found. The operation now proceeds in the absence of the file, and uses the sandbox port to provide the logging information. (Bug #25096293)

Functionality Added or Changed

• MySQL InnoDB ClusterSet is a new solution that provides disaster tolerance for MySQL InnoDB Cluster deployments by linking a primary InnoDB Cluster with one or more replicas of itself in alternate locations, such as different datacenters. InnoDB ClusterSet automatically manages replication from the primary cluster to the replica clusters using a dedicated ClusterSet replication channel. If the primary cluster becomes unavailable due to the loss of the data center or the loss of network connectivity to it, you can make a replica cluster active instead to restore the availability of the service.

  Emergency failover between the primary InnoDB Cluster and a replica cluster in an InnoDB ClusterSet deployment can be triggered by an administrator through MySQL Shell, using AdminAPI, which is included with MySQL Shell. You can also carry out an active controlled switchover while the primary cluster is still available, for example if a configuration change or maintenance is required on the primary cluster. MySQL Router automatically routes client applications to the right clusters in an InnoDB ClusterSet deployment. (WL #12804, WL #12805, WL #13815, WL #11894, WL #14538)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() now automatically detect partitioned tables in a dump, and parallelize chunking and dumping of each partition as an independent table. The data files in the dump are named in the format schema@table@partition. MySQL Shell's dump loading utility util.loadDump() then schedules loading for the chunks of each partition as if they belonged to different tables, improving parallelization and performance.

  Dumps created using the utilities from MySQL Shell 8.0.27 onwards cannot be loaded by versions of MySQL Shell before 8.0.27, due to incompatible changes in the metadata manage future additions of features that are not supported by older versions of the utilities, a capability list is also introduced in this release, which records the features that are actually used when creating a dump. From MySQL Shell 8.0.27 onwards, the dump loading utility checks the list and reports an error if it finds a feature there that it does not support. In future releases, the capability list will allow you to attempt a dump load with older versions of MySQL Shell and succeed wherever possible. (WL #14632)

• Oracle Cloud Infrastructure Object Storage now supports using a single pre-authenticated request (PAR) for accessing all objects in a bucket or objects in a bucket with a specific prefix. MySQL Shell's dump loading utility util.loadDump() has added support for this type of PAR. The previous method of using a PAR created for a MySQL Shell dump manifest file requires generating a PAR for each item in the dump, which is time consuming when exporting a large dataset. This method also requires recreating dumps when PARs expire. When using bucket and prefix PARs, only the PAR needs to be recreated, not the entire dump.

  With the introduction of support for bucket and prefix PARs, the MySQL Shell dump utility's ociParManifest option, which was previously set to true when the ocimds option was enabled and the osBucketName was specified (in order to generate the MySQL Shell dump manifest file with a PAR for each file in the dump), is now set to false by default and is only enabled if set to true explicitly. (WL #14645)

• The new maxBytesPerTransaction option, introduced for use with MySQL Shell dump loading utility util.loadDump(), defines the maximum number of bytes that can be loaded from a data file in a single LOAD DATA statement. If a data file exceeds the maxBytesPerTransaction size, multiple LOAD DATA statements load the data from the file in chunks less than or equal to the maxBytesPerTransaction value. An intended use for this option is to load data in smaller chunks when a data file is too large for the target server's limits, such as the limits defined by the server's group_replication_transaction_size_limit or max_binlog_cache_size settings. For more information, see Dump Loading Utility. (WL #14577)

• MySQL Shell now has full support for LDAP authentication methods and Kerberos authentication for classic MySQL protocol connections. Previously, some client-side plugins needed for these authentication methods were not available when using MySQL Shell, which used the MySQL client library for client-side authentication for classic MySQL protocol connections. Also, for Kerberos authentication, MySQL Shell could not take advantage of a cached Ticket Granting Ticket (TGT) that eliminates the need for a user name and password in the authentication process. This is because MySQL Shell automatically provides a system user name, prompts for a password, or supplies a password using the Secret Store Helper, if the user name or password are not present when accessing a system.

  The new persistent shell.options.mysqlPluginDir setting and non-persistent command-line option --mysql-plugin-dir can be used to specify paths to client-side authentication plugins that are not built into the libmysqlclient client library, including authentication_ldap_sasl_client and authentication_kerberos_client. These plugins are required to support SASL-based LDAP authentication and Kerberos authentication.

  When the --auth-method command option is used to specify the mysql_clear_password plugin, which is required for simple LDAP authentication, MySQL Shell now enables and uses the plugin. This client-side plugin is built in to the MySQL client library, but for security it is not enabled by default. This authentication type is only suitable for a secure connection that uses SSL or sockets.

  Cached TGTs for Kerberos authentication are supported when the --auth-method option is used to specify the authentication_ldap_sasl_client or authentication_kerberos_client plugin. When one of these options is set for a connection, MySQL Shell does not supply the system user name if the user name is missing, does not prompt for a password, and does not attempt to use the Secret Store helper to retrieve or store credentials. (WL #14553)

Bugs Fixed

• Incompatible Change: To manage additions of features that are not supported by older versions of the utilities, MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), table dump utility util.dumpTables(), and dump loading utility util.loadDump() now write a list of capabilities to the metadata file for a dump. If a feature is used while creating the dump, it is written to the capability list, otherwise it is omitted. The dump loading utility checks the capability list, and reports an error if it finds a feature. Partitioning awareness, introduced in this release, is the first feature compatible with this feature management capability.

  Due to this change, older releases of MySQL Shell cannot read the metadata produced by the dump utilities from MySQL Shell 8.0.27 and later, which means they cannot import dumps made with the later versions. (Bug #33063035)

• To improve MySQL Shell's integration capabilities when embedded in other applications, a new JSON shell mode has been added that can be activated by defining the MYSQLSH_JSON_SHELL environment variable. In this mode, MySQL Shell accepts the following commands formatted as JSON documents:

  • {"execute":json-string}

    Executes the given code in the active MySQL Shell mode (JavaScript, Python or SQL). The code is executed as a complete unit, and an error is returned if it is incomplete.

  • {"command":json-string}

    Executes the given MySQL Shell command.

  • {"complete":{"data":json-string[, "offset": uint}}

    Determines the options for auto-completion based on the given data and the current MySQL Shell context.

  Also, an issue was fixed where quotes were incorrectly added to string values when they were output in MySQL Shell’s JSON mode. (Bug #33310170)

• When MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), table dump utility util.dumpTables(), and dump loading utility util.loadDump() were interacting with an Oracle Cloud Infrastructure Object Storage bucket, minor clock variations could lead to an HTTP 401 error relating to the date of the Authorization header. This was caused by the utilities reusing headers when they retried a request. The utilities now refresh the headers for a request periodically, and also resend the request automatically in case of this specific error. (Bug #33271037)

• When MySQL Shell’s command-line option --file is used to specify a file to process, the arguments after the file name are now passed to the script in interactive mode as well as in batch mode. (Bug #33235685)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() returned an error when attempting to chunk a table where a column name contained a comma character. The issue has now been fixed. (Bug #33232480)

• A progress checker for MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), table dump utility util.dumpTables(), and dump loading utility util.loadDump() did not check what output format was set for MySQL Shell. As a result, some non-JSON output was produced when JSON mode output was selected and the option showProgress: true was set for the utility. (Bug #33223635)

• The value for the waitDumpTimeout option for MySQL Shell's dump loading utility util.loadDump() was not validated correctly and negative values could be used. (Bug #33212873)

• When the ociParManifest option is used, MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() now present progress information or a start and end message for the process of creating PARs and writing the manifest file. (Bug #33181308)

• When MySQL Shell was built with Python 3.8 or later, which set the locale to the user’s preference, Unicode characters in the MySQL Shell prompt were not displayed correctly on Windows. (Bug #33174999)

• Due to varying privilege requirements for the FLUSH TABLES WITH READ LOCK statement, MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() did not always correctly identify when the statement would succeed. If the statement failed, the dump was canceled rather than falling back to the use of a LOCK TABLES statement. Now, if the FLUSH TABLES WITH READ LOCK statement fails because access was denied, a LOCK TABLES statement is used instead. The FLUSH TABLES WITH READ LOCK statement is attempted first to keep the dry run consistent with the actual run, but it is set to a small timeout and releases the lock immediately if acquired. (Bug #33173739)

• When the MYSQLSH_USER_CONFIG_HOME environment variable was used to set a custom MySQL Shell configuration path, the path to the prompt.json file was built incorrectly if a path separator was not appended. The path build has been corrected to append the separator if needed. (Bug #33164726)

• When the ocimds option is enabled for MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables(), the utilities now flag explicit grants on objects in the mysql schema, which cannot be imported into a MySQL Database Service instance. The compatibility option’s strip_restricted_grants modification now removes such grants. (Bug #33162928)

• When the ocimds option is used with MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), or table dump utility util.dumpTables() to enable checks and modifications for compatibility with MySQL Database Service, the utilities now make an additional check for the number of columns in each table. If a table exceeds the InnoDB limit of 1017 columns, this is reported as an issue requiring a manual fix. (Bug #33159903)

• When MySQL Shell's dump loading utility util.loadDump() was used to load a dump from a pre-authenticated request (PAR) URL, if the URL was invalid, the utility did not recognize it as an attempted PAR, and the resulting error message was not informative. The utility now recognizes an invalid PAR and returns an error message including the expected URL format. The PAR URL itself is replaced with the word secret in error messages and the log. (Bug #33155373)

• MySQL Shell's dump loading utility util.loadDump() used a small input buffer when downloading data files written using zstd compression. This slowed performance for nonlocal files as each request was made separately. The buffer size has now been increased. (Bug #33150332)

• When a MySQL user account name included a netmask as part of the host value, MySQL Shell's dump loading utility util.loadDump() did not handle this correctly when initializing the load operation. As a result, user accounts with a netmask could not be used to run the utility. The issue has now been fixed. (Bug #33144419, Bug #104375)

• MySQL Shell's dump loading utility util.loadDump() now sets default roles for users after loading GRANT statements, to ensure that all the roles have been created and the appropriate grants have been granted. (Bug #33128803, Bug #104339)

• Some queries used by MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() had a dependency on the server version, including the use of EXPLAIN SELECT for chunking and identifying support for roles. The utilities now make the relevant checks without referencing the server version. (Bug #33103480)

• On Unix systems, the SIGINT signal did not interrupt a prompt or password prompt when sent from outside MySQL Shell. MySQL Shell now handles the signal consistently. (Bug #33096667)

• A data type mapping issue meant that Python plugins for MySQL Shell could not retrieve the column type for multiple columns using column.get_type(). (Bug #33084551)

• When the strip_definers compatibility requirement is applied to tables dumped by MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables(), the SQL SECURITY clause for views and routines is changed to specify INVOKER instead of DEFINER. If a stored routine contained only a simple statement, the clause was inserted at the wrong location, resulting in a dump that could not be loaded. The clause was also incorrectly omitted in some circumstances. The issues have now been corrected, and in addition the lack of the SQL SECURITY clause for a view or routine is now reported separately as a compatibility issue. (Bug #33079172, Bug #33087120)

• MySQL Shell's help search function produced duplicate topic results if the pattern matching used a wildcard prefix. (Bug #33060756)

• When a user-defined MySQL Shell Python plugin used the @plugin_function() decorator, the required attribute for a dictionary entry was overwritten to false. The issue has now been fixed. (Bug #33026024)

• Python 3.9.5 was bundled with MySQL Shell 8.0.26 was missing libraries required for Paramiko and for the Oracle Cloud Infrastructure SDK for Python. (Bug #32985104)

• The information gathering phase for MySQL Shell's instance dump utility util.dumpInstance() has been improved to reduce the time required to identify and fetch metadata from the source server. Filtering queries to retrieve metadata are now limited to the filtering specifically requested by the user, to improve the response time. Some unnecessary calls and requests have been removed, and threading and connection keep-alive functions have been added. As a result of the changes, the EXECUTE privilege is no longer required when a view in the dump calls a function to get its data. (Bug #32969290)

• Progress reporting and the final summary report for MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() have been improved. The final summary report now shows the duration of a dump with and without the preparation step, which was previously excluded from the time. The progress reporting includes additional steps, and status messages are now logged to the MySQL Shell application log file as informational messages, rather than being printed to the console. An error in calculating the throughput has been fixed. (Bug #32969287)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), table dump utility util.dumpTables(), and dump loading utility util.loadDump() now log any SQL statements that result in an error being returned, as well as logging the error. (Bug #32966510)

• An incorrect assumption in the chunking algorithm for MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() meant that if an individual table row exceeded the size specified by the bytesPerChunk option, and should therefore have been in its own chunk file, other rows might be written to the file as well. The algorithm has now been corrected. (Bug #32955616)

• When the consistent option is set to false for MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables(), the instance is not locked for backup while the dump is taken. The utilities now check the gtid_executed GTID set at the start and end of the dump process, and issue a warning if they are different. (Bug #32954757)

• MySQL Shell's dump loading utility util.loadDump() could stop with a deadlock error on a slow server when executing DDL for a table that is referenced by an existing view. Previously, the operation was retried up to 20 times with a 200ms delay between. Now, the delay is doubled after each attempt, and the utility retries for up to five minutes. (Bug #32928528)

• The help and documentation for the excludeUsers and includeUsers options of MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), table dump utility util.dumpTables(), and dump loading utility util.loadDump() incorrectly stated that a user name specified without a host was equivalent to 'user_name'@'%'. Actually all user accounts with that user name are excluded. MySQL Shell also now excludes certain accounts that should always be excluded, even if they are not specified using the excludeUsers option. (Bug #32799076)

• MySQL Shell's parallel table import utility util.importTable() was unable to import a compressed input file if the content exceeded the max_binlog_cache_size setting for the server. If you receive the error "MySQL Error 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage" on attempting to import a file, set the utility’s new option maxBytesPerTransaction to a value less than or equal to the server instance’s max_binlog_cache_size setting. The minimum value that the utility will use is 4096 bytes. When the option is set, the utility uses multiple LOAD DATA statements to request larger input files in chunks that fit into the specified cache size. When the option is not set, compressed input files are loaded in a single block. (Bug #32575351)

• MySQL Shell's dump loading utility util.loadDump() now includes the UUID of the target server instance in the progress file. If the load operation is stopped and resumed, the utility checks that the saved UUID matches the UUID of the current target server, and returns an error and halts the operation if it does not. This check ensures that a meaningful error is returned in case of accidental use of the wrong progress file due to copying the command to run the utility. (Bug #32561035)

• MySQL Shell’s plugin handling assumed that packages used the Egg packaging format, leading to errors in locating and loading modules. The Wheel packaging format is now taken into account when handling module paths. (Bug #32451772)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() failed while the user was connected to the server using a socket connection, unless the --host option was also specified. The utilities include a description of the host in a comment in the dump DDL files, and incorrectly assumed this would be present in the connection options data. The host is now set to localhost for the comment if the --host option is not specified. (Bug #32109967, Bug #101439)

• MySQL Shell's dump loading utility util.loadDump() did not provide progress updates while the metadata files were being scanned for a dump loaded from an Oracle Cloud Infrastructure Object Storage bucket. For a large dump, it could appear that progress had stalled. The utility now reports progress during this stage, and stores the timestamp of each operation in the progress log. A number of performance improvements have also been made, including multithreaded scanning and the use of a pool of background threads. The size of the thread pool can be set using the new backgroundThreads option, and defaults to the value of the threads option for a dump loaded from the local server, or four times the value of the threads option for a dump loaded from a non-local server. (Bug #31645798)

• A new command-line option --log-file has been added that lets you change the location of MySQL Shell’s application log file mysqlsh.log from the default. The default location is the user configuration path, %APPDATA%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix. It was previously possible to override the user configuration path by defining the environment variable MYSQLSH_USER_CONFIG_HOME, but now the --log-file option lets you do this from the command line when you start an individual MySQL Shell instance, so that different instances can write to different locations. (Bug #27253110)

• Comments entered interactively in MySQL Shell’s Python mode were not included in the MySQL Shell history, although JavaScript comments were. Both are now included. (Bug #26484975)

• When MySQL Shell’s command-line option --dba=enableXProtocol was used to install and enable X Plugin on connection with a MySQL 5.7 server, the process verified that the install command did not return any errors, but did not verify that the plugin had installed correctly and accepted the appropriate connection type. MySQL Shell now verifies the plugin state before returning a response to the user so they can see whether the plugin is now usable. (Bug #24554967, Bug #82759)

• Compiling without Python support resulted in a fatal error. This was caused by Python test files included in the compilation process. (Bug #101923, Bug #32256576)