Migration between DBs of different versions

Available in VPC

You might run into the following problems when using Database Migration Service. Find out causes and possible solutions.

Compatibility issue

When migrating from the lower version of MySQL/MariaDB to the higher version, a compatibility issue occurs.

Cause

It is recommended to migrate between the same Major versions but you can use Database Migration Service even when the versions of Source DB and Target DB are different. For example, you can migrate from the MySQL 5.7 version to the MySQL 8.0 version. However, the MySQL/MariaDB version that reached EOL can cause compatibility-related error messages during migration.

Solution

Check the version of Source DB MySQL/MariaDB to migrate is not retired. If the MySQL/MariaDB version is retired, it can cause compatibility-related error messages during migration.

Error when migrating from Source DB MySQL version 5.7 and MariaDB version 10.1 or later to Target DB MySQL version 8.0

Error occurs when migrating from Source DB MySQL version 5.7 and MariaDB version 10.1 or later to Target DB MySQL version 8.0.

Cause

The sql_mode='NO_AUTO_CREATE_USER' setting, which is supported by MySQL 5.7 and MariaDB 10.1.7 and later versions as the Default value, is not supported starting with MySQL 8.0.

Solution

• If sql_mode='NO_AUTO_CREATE_USER' setting is applied to Source DB, delete and migrate. If the error found during executing Import, delete migration and retry.
• Backup method: When selecting mydumper, error related to sql_mode does not occur.
• Confirm whether there is any compatibility issue between the existing application and MySQL 8.0 before switching services.

Replication consistency issues with MySQL 5.7.40

Replication consistency issues occur in MySQL version 5.7.40.

Cause

MySQL 5.7.40 version can have mysqldump bug due to its own MySQL specification, which can cause replication consistency issues.

Solution

It is recommended that you choose the mydumper method or set Target DB to a version other than MySQL 5.7.40.

"Collations unsupported in Target DB are included." Troubleshoot error messages

"Collations unsupported in Target DB are included." The error message appears.

Cause

It is an error message that occurs if the Source DB uses the MySQL 8.0 version and the Target DB uses 5.7 or an earlier version. "utf8mb4_0900_ai_ci," the default collation supported by the MySQL 8.0 version, is not supported in the 5.7 version.

Solution

Change Collation options in the Source DB, such as Database, Table, View, Procedure, and Function, before migration.

Migration error for tables without a primary key.

If a table without a primary key is included, an error occurs during the Importing stage when migrating to a target DB running MySQL 8.4 or later.

Cause

Starting with Cloud DB for MySQL 8.4, the default value of sql_require_primary_key is set to ON, preventing tables without a primary key from being created in the target DB.

Solution

• Run the following query to check whether the source DB contains any tables without a primary key.

  SELECT t.TABLE_SCHEMA, t.TABLE_NAME
  FROM information_schema.TABLES t
  LEFT JOIN information_schema.TABLE_CONSTRAINTS c
    ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
   AND c.TABLE_NAME = t.TABLE_NAME
   AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
  WHERE t.TABLE_TYPE = 'BASE TABLE'
    AND t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND c.CONSTRAINT_TYPE IS NULL;
  

• If the query returns any results, change the sql_require_primary_key setting to OFF in the target DB's DB Config before starting the migration.
• If this error has already occurred during the Importing stage, delete the migration, change the setting, and then perform the migration again.
• Tables without a primary key can cause degraded row-based replication performance. Therefore, it is recommended to add a primary key after the migration is completed.