Changes in MySQL Shell 8.0.22 (2020-10-19, General Availability)

• AdminAPI Bugs Fixed

• Functionality Added or Changed

• Bugs Fixed

AdminAPI Bugs Fixed

• Due to the changes introduced in bug#31467823, AdminAPI user messages have been updated to use the same terminology. (Bug #31798724)

  References: See also: Bug #31462783, Bug #31467823.

• In 8.0.22, Group Replication deprecates the group_replication_ip_whitelist system variable in favor of group_replication_ip_allowlist. Therefore AdminAPI now includes a new option named ipAllowlist and deprecates the ipWhitelist option used with dba.createCluster(), Cluster.addInstance() and Cluster.rejoinInstance().

  Regardless of the target instance version, if ipWhitelist is used MySQL Shell prints a deprecation warning. When the target instance is 8.0.22 or later Group Replication reports a deprecation warning, but AdminAPI avoids this by ensuring the appropriate variable is set according to the version of MySQL running on the instance. If the target instance is 8.0.22 or later and the old ipWhitelist option is used, AdminAPI updates the new group_replication_ip_allowlist system variable. On instances running MySQL 8.0.21 or earlier, AdminAPI updates the older group_replication_ip_whitelist system variable.

  This work also fixes a bug in Cluster.options(), which shows the list of all Group Replication options that are configurable by MySQL Shell. When an option is not set or does not exist (for example in a certain version) it is displayed as having a null value. However, when you passed in the all option to Cluster.options(), such variables were being excluded from the list. (Bug #31798495)

• The dba.configureLocalInstance() and dba.configureInstance() operations could not be used against instances that were part of unmanaged replication groups. This made it impossible to create a cluster administrator account, which is required when adopting a Group Replication group into an InnoDB Cluster. Creating cluster administrator accounts is very important for the remote management of InnoDB Clusters, and to avoid having to manually create the user and required privileges on each instance. The fix ensures that dba.configureLocalInstance() and dba.configureInstance() can be executed against instances belonging to unmanaged replication groups. Because such instances are ready for InnoDB Cluster usage, a message confirms this. (Bug #31691232)

• The dba.rebootClusterFromCompleteOutage() operation was only checking GTID_EXECUTED when validating for the instance that has the most transactions. Transactions that were received (and certified) but not executed yet were not included in that check. Also, transactions received through replication channels other than the Group Replication applier were also not being considered in that check, because the instance was probably the primary while the cluster was running. Ignoring these transactions led to data loss. Now, any known and managed replication channels are considered as part of the check. (Bug #31673163)

• The dba.removeInstance() operation failed if the instance being removed was not only unreachable but also unresolvable, which could be the case when the instance was running in a container that takes down its own DNS record when removed.

  Instances could not be removed even if force was enabled, because of a validation that ensured that the given instance is a valid address by checking if it is resolvable. If that validation failed, nothing else was attempted. The fix completely removes address resolution from the whole AdminAPI. That check was redundant, because invalid addresses would eventually lead to an error anyway when a connection was opened. IPv6 address syntax validations were left to remind you that ::1 should be specified as [::1]. (Bug #31632606)

• Calling Cluster.rejoinInstance("host:port") with no user name specified caused AdminAPI to try and connect using the operating system user name instead of the credentials used to connect the cluster object. Now, if credentials are not provided, they are taken from the target server's connection options. (Bug #31632554)

• When adding an instance to a cluster using MySQL Clone and monitoring the transfer of data, MySQL Shell could stop unexpectedly. This was due to the assumption that the Performance Schema would provide information on all four stages of cloning, which might not exist if the data set was very small. The fix ensures that updates are performed according to the information which is available at each time. (Bug #31545728)

• When an unreachable primary instance was forcibly removed from a cluster, the group_replication_group_seeds system variable was not updated because the group status was queried from the primary, which was missing. The cluster was left in an inconsistent state, and if any of the instances were restarted, they would not be able to automatically rejoin, because group_replication_group_seeds contained an invalid address, which caused Group Replication to abort trying to join the group. (Bug #31531704)

• If the validate_password plugin was enabled, the setupAdminAccount() and setupRouterAccount() operations would fail with an error indicating the password did not meet the policy requirements. This happened regardless of whether the password met the policy requirements or not. (Bug #31491092)

• In the event of dba.createCluster() failing, the metadata record was left behind and Group Replication was also started. The cluster was left in an inconsistent state that could not be recovered from when calling dba.createCluster() again. Now, metadata changes during a dba.createCluster() operation are enclosed in a transaction, so that both the cluster and instance records are only committed if the operation succeeds. Group Replication is also stopped if the create operation does not complete successfully.

  Similarly, Cluster.addInstance() has been changed to ensure that its metadata record is only inserted when everything else has completed successfully. On retry, it skips any steps that would cause conflicts because Group Replication is already running. This introduces a behavior change, where adding an instance that is part of the group but not in the metadata just adds it to the metadata, instead of aborting and requiring you to execute Cluster.rescan(). (Bug #31455419)

• If a connection timed out during a Cluster.status() operation, MySQL Shell could appear to hang for a long time. To improve the responsiveness, the default timeout of AdminAPI operations has been reduced from 10 seconds to 2 seconds. This ensures operations like Cluster.status() do not appear to freeze for a long time when there are unreachable instances. (Bug #30884174)

• Instances operating in an InnoDB Cluster or InnoDB ReplicaSet are all required to have the same password for the administrative account. In a situation where the password on an instance joining a InnoDB Cluster or InnoDB ReplicaSet did not match the other instances, the error message did not explain this and the instance failed to join. Now, in such a situation the error is detected and the resulting message mentions that the password is the cause of the failure. It is recommended that you set up administrator accounts using the setupAdminAccount() operation, see Creating User Accounts for AdminAPI. (Bug #30728744)

Functionality Added or Changed

• Two new utilities are available in MySQL Shell to export single tables from a MySQL server instance.

  • MySQL Shell's new table dump utility util.dumpTables() supports the export of a selection of tables or views from a schema, from an on-premise MySQL instance into an Oracle Cloud Infrastructure Object Storage bucket or a set of local files. It works in the same way as the instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas() introduced in 8.0.21, but with a different selection of suitable options. The exported items can then be imported into a MySQL Database Service DB System (a MySQL DB System, for short) or a MySQL Server instance using MySQL Shell’s dump loading utility util.loadDump().

  • MySQL Shell's new table export utility util.exportTable() exports a MySQL relational table into a data file in a variety of formats, either on the local server or in an Oracle Cloud Infrastructure Object Storage bucket. The data can then be uploaded into a table on a target MySQL server using MySQL Shell's parallel table import utility util.importTable(), which uses parallel connections to provide rapid data import for large data files. The data file can also be used to import data to a different application, or as a lightweight logical backup for a single data table.

  (WL #13804)

• From MySQL Shell 8.0.22, when you export schemas to an Oracle Cloud Infrastructure Object Storage bucket using MySQL Shell's instance dump utility and schema dump utility, util.dumpInstance() and util.dumpSchemas(), during the dump you can generate a pre-authenticated request URL for every item. The utilities do this by default when the ocimds option is set to true, and you can control the feature using the ociParManifest and ociParExpireTime options. The user account that runs MySQL Shell's dump loading utility util.loadDump() then uses the pre-authenticated request URLs to load the dump files without additional access permissions. (WL #14154)

Bugs Fixed

• MySQL Shell's dump loading utility util.loadDump() would stop with an error if a data file’s size was larger than an applicable server limit relating to the maximum transaction size, such as the max_binlog_cache_size limit. Now, the utility stops the data load in mid-file if the number of bytes uploaded is about to exceed 1.5 times the bytesPerChunk setting of the utility that created the data files, which is stored in the dump metadata. The data load is then restarted to upload the remainder of the file. Due to this new safeguard, the default bytesPerChunk setting of MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() has been increased from 32 MB to 64 MB. (Bug #31945539)

• MySQL Shell's instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas() did not take the row size into account when deciding whether to chunk table data, only the number of rows. A table with a smaller number of rows containing large amounts of data might therefore bypass chunking even if the row size exceeded the specified chunk size for the dump. The utilities now carry out chunking regardless of the number of rows, unless the data is estimated to fit in a single chunk of the specified size. (Bug #31938831)

• MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() use a chunking algorithm to split the data for a table into multiple files. It was possible for the algorithm to create a large number of very small files, causing issues with the dump. A minimum valid value is now specified, and the algorithm has been enhanced to ensure that the range being checked is always large enough to include sufficient rows. (Bug #31909408)

• When MySQL Shell's instance dump utility util.dumpInstance() or schema dump utility util.dumpSchemas() was splitting a table into chunks, an integer overflow, loop, and consequent out of memory error could occur if the maximum value in the index column was close to the maximum value of an integer. Extra checks have now been added to avoid this situation. (Bug #31896448)

• Corrupted SQL files could occur in MySQL Shell’s dumps if the dumped string was exactly 2048 bytes, due to an internal module error which is now accounted for. (Bug #31843832)

• The administrative user account on an Oracle Cloud Infrastructure Compute instance, which was used when importing data with MySQL Shell’s dump loading utility util.loadDump(), was unable to revoke privileges on MySQL system schemas (mysql and sys) that it did not have itself. Affected REVOKE statements are now stripped from an instance or schema dump created by MySQL Shell's instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas() when the strip_restricted_grants compatibility option is used. (Bug #31842532)

• MySQL Shell's instance dump utility and schema dump utility, dumpInstance() and dumpSchemas(), previously fetched the value of the gtid_executed system variable before the read lock was established, which could potentially lead to an inconsistency with the dumped data. The GTID set is now retrieved while the read lock is active. (Bug #31706940)

• From MySQL Shell 8.0.22, you can use the –pym command-line option to execute a specified Python module as a script in MySQL Shell’s Python mode. --pym works in the same way as Python's -m command line option. (Bug #31694202)

• In MySQL Shell in Python mode, functions registered in JavaScript could not be called from global extension objects if they had optional arguments that were not provided. (Bug #31693096)

• MySQL Shell treated the character sequence */ as the end of a comment even when it was part of a quoted string. (Bug #31689135)

• MySQL Shell's instance dump utility and schema dump utility, dumpInstance() and dumpSchemas(), automatically select an index column to order and chunk the data. When an index with a functional key part was present for a table, the query for index columns returned NULL for the column name, causing an exception in the utility. These column names are now filtered out in the query. (Bug #31687059)

• MySQL Shell's parallel table import utility util.importTable() has a new option decodeColumns, and an enhancement to the columns option, to enable you to capture columns from the import file for input preprocessing (or to discard them) in the same way as with a LOAD DATA statement. The decodeColumns option specifies preprocessing transformations for the captured data in the same way as the SET clause of a LOAD DATA statement, and assigns them to columns in the target table. (Bug #31683641)

• MySQL Shell's dump loading utility, loadDump(), now verifies that it can open and write to the progress state file before it starts to retrieve the dump files, so that the utility does not spend time fetching the dump files if the import is subsequently going to fail for that reason. (Bug #31667539)

• Before MySQL 8.0, user accounts that have all privileges used the statement GRANT ALL PRIVILEGES, rather than the full list of privileges. When MySQL Shell's instance dump utility dumpInstance() was used to dump an instance, ALL PRIVILEGES was stripped from the statement, leaving the accounts with no privileges. The utility now replaces this grant with the administrator role. (Bug #31661180)

• MySQL Shell's instance dump utility and schema dump utility, dumpInstance() and dumpSchemas(), and dump loading utility, loadDump(), previously timed out after the main thread had been idle for 8 hours. The timeout has now been extended indefinitely (to 1 year) so that long data load times do not cause the dump or import to fail. (Bug #31652265)

• MySQL Shell's dump loading utility, loadDump(), now includes a comment in its LOAD DATA statements to identify the data chunk that is currently being loaded. If you need to cancel the import, you can use this information to decide whether to let the import of this chunk complete or stop it immediately. (Bug #31646650)

• Previously, MySQL Shell's dump loading utility loadDump() closed DDL files after all tables from the schema had finished loading. For a dump with a very large number of DDL files, this could lead to unexplained runtime errors being returned. The utility now closes DDL files immediately after reading them. (Bug #31645896)

• MySQL Shell's dump loading utility, loadDump(), previously used only its main thread to execute the DDL scripts for tables. For a dump containing a large number of tables, fetching the DDL scripts could have a significant impact on the time taken. The utility now fetches and executes DDL scripts for tables using all its threads, with the exception of DDL scripts for views, which are fetched in parallel but executed only in the main thread to avoid race conditions. (Bug #31645806)

• MySQL Shell's dump loading utility, loadDump(), now loads views in sequence and only after all tables and placeholders from all schemas have been loaded, ensuring that views do not reference items that do not yet exist. (Bug #31645792)

• MySQL Shell now ignores any other available Python installations when a bundled compatible version of the Python interpreter has been installed. (Bug #31642521)

• When MySQL Shell's dump loading utility, loadDump(), is importing users and their roles and grants, an error is now returned if the user already exists in the target instance, and the user's grants from the dump files are not applied. Previously, the grants were applied to the existing user. (Bug #31627432)

• MySQL Shell's dump loading utility, loadDump(), now has an option updateGtidSet to apply the gtid_executed GTID set from the source MySQL instance  to the gtid_purged GTID set on the target MySQL instance. You can append or replace the GTID set depending on the release of the target MySQL instance. (Bug #31627419)

• MySQL Shell's instance dump utility, dumpInstance(), and dump loading utility, loadDump(), now have options to include (includeUsers) or exclude (excludeUsers ) named user accounts from the dump files or from the import. You can use these options to exclude user accounts that are not accepted for import to a MySQL DB System, or that already exist or are not wanted on the target MySQL instance. (Bug #31627292)

• With the deferTableIndexes option set to all, MySQL Shell's dump loading utility loadDump(), defers creation of all secondary indexes until after the table is loaded. Previously, a table with a unique key column containing an auto-increment value failed to load in this situation. The utility now also creates indexes defined on columns with auto-increment values when the deferTableIndexes option is set to all. (Bug #31602690)

• The upload method used by MySQL Shell's instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas() to transfer files to an Oracle Cloud Infrastructure Object Storage bucket has a file size limit of 1.2 TiB. In MySQL Shell 8.0.21, the multipart size setting means that the numeric limit on multiple file parts applies first, creating a limit of approximately 640 GB. From MySQL Shell 8.0.22, the multipart size setting has been changed to allow the full file size limit. (Bug #31589858)

• MySQL Shell's upgrade checker utility checkForServerUpgrade() now checks for the obsolete NO_AUTO_CREATE_USER SQL mode. (Bug #31501981, Bug #99903)

• The parallelization of table loading by MySQL Shell's dump loading utility, loadDump(), has been improved. (Bug #31441903)

• If the settings for the server’s global character set variables differed from the settings for the current session, MySQL Shell's parallel table import utility, importTable(), could not import data into a table created in the session whose name contained non-ASCII characters. Now, when the utility’s characterSet option is specified, MySQL Shell executes a SET NAMES statement with the given value. (Bug #31412330)

• MySQL Shell's parallel table import utility, importTable(), could not import data if the global SQL mode NO_BACKSLASH_ESCAPES was set. The utility now clears the global SQL mode in sessions created to run the import. (Bug #31407133)

• When a function that was defined as a member of a MySQL Shell extension object had a number of optional parameters but no required parameters, in some situations calling the function with zero parameters or one parameter returned an error. (Bug #30744994)

• MySQL Shell's db global object did not return the current schema when its properties were queried. (Bug #30296825, Bug #96839)

• MySQL Shell has a new command \disconnect. The command disconnects MySQL Shell's global session (the session represented by the session global object) from the currently connected MySQL server instance, so that you can close the connection but still continue to use MySQL Shell. (Bug #28240416)