6.1.2 MySQL Server Compatibility

MySQL HeatWave on AWS has several security-related restrictions that are not present in an on-premise instance of MySQL. To make it easier to load existing databases into the Service, the dump commands in MySQL Shell can detect potential issues and, in some cases, automatically adjust your schema definition to be compliant.

The ocimds option, when set to true, performs compatibility checks on the schemas for these issues and aborts the dump if any are found, while producing a detailed list of those issues and suggests additional steps to correct those issues. loadDump command only allows import of dumps created with the ocimds option enabled.

Some issues found by the ocimds option may require you to manually edit your schema before it can be loaded into the MySQL HeatWave on AWS. The MySQL Shell compatibility option can be used to automatically modify the dumped schemas, resolving some of these compatibility issues. You can pass one or more modifiers to the MySQL Shell compatibility option in a comma-separated list.

The MySQL Shell compatibility option applies the specified requirements for compatibility with MySQL HeatWave on AWS for all tables in the dump output, altering the dump files as necessary. From MySQL Shell 8.0.23, this option is available for all MySQL Shell utilities, and before that release, it is only available for the instance dump utility and schema dump utility.

MySQL Shell compatibility option modifiers include:

force_innodb

MySQL HeatWave on AWS supports the InnoDB storage engine, only. This option modifies the ENGINE= clause of CREATE TABLE statements to specify INNODB for tables that do not already use the InnoDB storage engine.

skip_invalid_accounts

Skips user accounts created with external authentication plugins that are not supported in MySQL HeatWave on AWS. From MySQL Shell 8.0.26, this option also removes user accounts that do not have passwords set, except where an account with no password is identified as a role, in which case it is dumped using the CREATE ROLE statement.

strip_definers

Removes the DEFINER=account clause from views, routines, events, and triggers. MySQL HeatWave on AWS requires special privileges to create these objects with a definer other than the user loading the schema. By removing the DEFINER clause, these objects will be created with that default definer. Views and routines have their SQL SECURITY clause changed from DEFINER to INVOKER. This ensures that the access permissions of the account querying or calling these objects are applied, instead of the user that created them. If your database security model requires views and routines have more privileges than their invoker, you must manually modify the schema before loading it. For more information, see The DEFINER Attribute and The SQL SECURITY Characteristic.

strip_restricted_grants

Certain privileges are restricted in MySQL HeatWave on AWS, such as RELOAD , FILE , SUPER , BINLOG_ADMIN , and SET_USER_ID . It is not possible to create users granting these privileges. This option removes those privileges from dumped GRANT statements.

strip_tablespaces

MySQL HeatWave on AWS has some restrictions on tablespaces. This modifier removes the TABLESPACE clause from GRANT statements, so all tables are created in their default tablespaces.

Additionally, DATA DIRECTORY, INDEX DIRECTORY, and ENCRYPTION options in CREATE TABLE statements are always commented out in DDL scripts if the MySQL Shell ocimds option is enabled.

Note:

If you intend to export data from an older version of MySQL, you should run the MySQL Shell Upgrade Checker Utility to generate a report of all potential issues with your migration. For more information, see MySQL Shell User Guide - Upgrade Checker Utility .

Parent topic: Exporting Data