Data Migration Pre-Check Failures and Solutions

Abstract

Alibaba Cloud Data Transmission Service supports data transmission between database-centered structured storage products. It integrates a wide variety of data transmission capabilities, including data migration, data subscription, and real-time data synchronization. With Data Transmission Service, you can easily build a secure, scalable, and highly available data architecture while achieving a one-click cloud migration. Data Transmission Service supports data migration between common database types including Oracle, MySQL, SQL Server, and Postgres Plus Advanced Server. Meanwhile, it also supports incremental data subscription for ApsaraDB for RDS MySQL and real-time synchronization between ApsaraDB for RDS instances.

Before ApsaraDB for RDS initiates data migration, Data Transmission Service will run a series of pre-check tests to ensure successful migration. This article briefly introduces the content found during the pre-check phase, as well as the causes of failures and solutions for each pre-check item.

Source Database Connectivity Check

This check aims to test whether the Data Transmission server can connect to the source database to be migrated. Data Transmission creates a connection to the source database through the JDBC protocol. When the creation fails, this check item fails.

The source database connection pre-check may fail for the following reasons:
1.Incorrect account or password provided when the migration task is created
Check method: Connect to the source database through client software on any network-ready server that can connect to the source database with the account/password used for creating the migration task, and confirm whether the connection works normally. If the connection reports errors, and the error prompt contains Access deny, it indicates the account or password is incorrect.
Solution: Modify the migration task in the Data Transmission Service console and enter the correct account and password, and then re-run the pre-check.

2.Source database migration account places restrictions on the source IP address
Check method:
(1) Connect to the source database through client software on any network-ready server that can normally connect to the source database with the account/password used to create the migration task. This indicates the source database limits the source IP address and only allowed servers can connect to it. The Data Transmission server IP address is not included in the authorized list, so it cannot be connected normally.
(2) If the source database is MySQL, you can connect to the source database with MySQL client, and execute select host from mysql.user where user='migration account',password='migration account password'. If the query result is not "%", it indicates that the Data Transmission server IP address is not included in the authorized IP address list, causing the connection failure.
(3) If the source database is SQL Server, you are advised to check whether a firewall has been set on the SQL Server installation server, or check whether there is an endpoint or trigger in the source database that places source IP address limitations.
(4) If the source database is Oracle, you are advised to check the sqlnet.ora configuration file of the source database and confirm whether the configuration item TCP.VALIDNODE_CHECKING is set to yes. If so, it indicates that the source database places source IP address limitations.
Solution:
(1) If the source database is MySQL, you can execute grant all on . to "migration account"@"%" identified by "migration password" in the source database to re-authorize the migration account. Replace the migration account in this command with the real migration account and migration password with the real migration password. Re-run the pre-check after authorization.
(2) If the source database is SQLServer, you need to first shut down the firewall or disable the trigger, and re-run the pre-check.
(3) If the source database is Oracle, you are advised to first modify TCP.VALIDNODE_CHECKING=no and restart the listener and then re-run the pre-check.

3.The source database installation server has set a firewall
Check method: If the source database installation server is Linux, run the iptables -L command in the shell to check whether the server has configured a firewall. If the installation server is Windows, you are advised to find Windows firewall in the Control Panel and check whether a firewall has been configured.
Solution: Disable the firewall, and re-run the pre-check.

Target Database Connection Check
This check aims to test whether the Data Transmission Service server can connect to the target database to be migrated. Data Transmission Service creates connection to the target database through the JDBC protocol. When the creation fails, this check item fails.

The target database connection pre-check failure may be caused be the following two reasons:
1.Incorrect account or password provided when the migration task is created
Check method: Connect to the target database through client software on any network-ready server that can connect to the source database with the account/password used for creating the migration task, and confirm whether the connection works normally. If the connection reports an error, and the error prompt contains Access deny, it indicates that the account or password is incorrect.
Solution: Modify the migration task in the Data Transmission Service console and enter the correct account and password, and then re-run the pre-check.
2.The Data Transmission server and target database are not connected over the network
If it is not because of incorrect migration account or password, it may be due to the connection failure between the Data Transmission Service server and target database. In such circumstances, contact the Data Transmission Service personnel on duty for help.

Schema Check

This check aims to check whether the database to be migrated exists in the target RDS instance. If no such database exists, Data Transmission Service will create one automatically. However, for the following circumstances, the automatic creation of databases will fail, triggering Data Transmission Service to report a pre-check failure directly.

(1) The database name contains lower-case letters, digits, underscores, strikethroughs, and other special characters.
In such circumstances, the cause of a pre-check failure is because the name of the database to be migrated does not comply with the RDS naming rules.
Solution: On the RDS console database management page, create a database complying with the RDS naming rules and grant the read/write permissions of the newly created database to the migration account. Then you can map the database to be migrated using the database name mapping feature provided by Data Transmission Service to the newly created database and re-run the pre-check.

(2) The character set of the database is not UTF8, GBK, Latin1 or UTF8MB4.
In such circumstances, the cause of pre-check failure is because the character set used in the name of the database to be migrated does not comply with the RDS naming rules.
Solution: On the RDS console database management page, create a database complying with the RDS naming rules, and grant read/write permissions for the newly created database to the migration account. If the newly created database and the database to be migrated have different names, you are advised to map the database to be migrated using the database name mapping feature provided by Data Transmission to the newly created database and re-run the pre-check.

(3) The migration account of the target database has no read/write permissions to the database to be migrated.
In such circumstances, the cause of pre-check failure is because the user has no permission to use the migration database name.
Solution: on the account management page of database management page in the RDS console, modify the permission to authorize read/write permissions for the database to be migrated to the migration account, and re-run the pre-check.

Checking for Objects with the Same Name

This check item mainly checks whether there are existing objects in the target database with the same name as the object to be migrated. When this check item fails, it indicates that an object with the same name of the object to be migrated already exists in the target RDS instance, causing a migration failure.

The solution for failure of this check is:
(1) Migrate the object to another object of a different name in the target database using the database and table name mapping feature provided by the Data Transmission Service.
(2) Delete or rename the object with the same name as the object to be migrated in the target database.
(3) Modify the migration task configuration, delete the object in question from the objects to be migrated, and pause its migration.

Checking the Integrity of Constraints

This check item mainly checks whether all the parent-child tables with foreign key dependency have been migrated in the migration object list to avoid damaging the integrity of foreign key constraints.
When this check fails, the cause of failure is because the “parent table name” parent table that the “child table name” table to be migrated is dependent on has not been migrated.
The solution for such errors is:
(1) Not migrating the child table involved in the failed constraints integrity check: modify the migration task configuration, delete the child tables involved in the failed constraints integrity check from the migration list, and re-run the pre-check.
(2) Migrating the parent table for the child table involved in the failed constraints integrity check: modify the migration task configuration, add the parent tables for these child tables involved in the failed constraints integrity check to the migration list, and re-run the pre-check.
(3) Deleting the foreign key dependency of the child table involved in the failed constraints integrity check: modify the source database and delete the foreign key dependency of these child tables, and re-run the pre-check.

Source Database Server-ID Check

This configuration item check is only required for MySQL -> MySQL incremental migration. This item mainly checks whether the source database server-id is set to an integer greater than 1.
Solution: You only need to execute set global server_id=a number other than 1 on the source database and re-run the pre-check.

Checking Whether Binlog Check Is Enabled for the Source Database

This configuration item check is only required for MySQL -> MySQL incremental migration. This check item mainly checks whether the source database has enabled binlog. If the check fails, it indicates that the source database has not enabled binlog.
Solution: You need to modify the log_bin in the source database configuration file. After the configuration is done, restart the source database and re-run the pre-check.

Checking the Binlog Mode of the Source Library

This configuration item check is only required for MySQL -> MySQL incremental migration. This check item mainly checks whether the binlog mode of the source database is ROW. If the check fails, it indicates that the binlog format of the source database is not ROW.
Solution: If the check fails, you need to execute “set global binlog_format=ROW” in the source database and re-run the pre-check. It is recommended that you restart MySQL after the modification. Otherwise, the connected session may continue to be written in the non-ROW mode, which will lead to data loss.

Checking Binlog of the Source Library

This configuration item check is only required for MySQL > MySQL incremental migration. This check item mainly checks whether the binlog file in the source database is deleted. If the check fails, it indicates that the binlog file of the source database is not complete.
Solution: You can execute “PURGE BINARY LOGS TO the first binlog file name not deleted” in the source database, and re-run the pre-check. For specific purge file names, see the pre-check solutions.

Checking Whether MySQL Source Database Binlog_Row_Image is FULL

This configuration item check is only required for MySQL -> MySQL incremental migration. This check item mainly checks whether the binlog_row_image of the source database is FULL, that is, recording the full image. If the check fails, it indicates that the binlog file of the source database does not record the full image.
Solution: You need to execute the set global binlog_row_image=FULL command in the source database and re-run the pre-check.

Federated-type Table Check

This configuration item check is only required for MySQL -> MySQL incremental migration. This check item mainly checks whether any storage engines not supported by the incremental migration exist in the database. At present, incremental migration does not support Federated and MRG_MyISAM storage engines.
If the check fails, and the cause of failure is because federated table exists in the source database, it indicates that the storage engine of some tables in the source database to be migrated is federated, causing the check failure.
If the check fails, and the cause of failure is because MRG_MYISAM table exists in the source database, it indicates that the storage engine of some tables in the source database to be migrated is MRG_MYISAM, causing the check failure.
Solution: You need to modify the migration tasks for the federated or MRG_MYISAM tables, and delete these tables from the migration list. Then, you can create a separate migration task for structure migration+full migration of these tables.