How do I resolve AWS DMS task issues during data migration from the source to the target?

Resolution

To resolve issues when you're migrating data, first run diagnostic scripts that are specific to your database. Then, use the following resolution based on the issue that you identified.

Task failures during migration

An AWS DMS task might fail during migration for the following reasons:

• Missing prerequisites
• Insufficient database permissions for your source or target
• Network connectivity
• Data issues

To resolve these issues, take the following actions:

• Confirm that AWS DMS supports your source and target database versions and configurations.
• Review the last failure message on the AWS DMS console to identify why the migration task stopped.
  Example of a last failure message:
  "[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Lost connection to MySQL server during query [122502] ODBC general error.'"
• Activate detailed debug logging. Search for "E:" to find error lines and search for "W: " to find warning lines.
• To identify the log component, run the following commands.
  Source extraction:

  SOURCE_UNLOAD

  - or-

  SOURCE_CAPTURE errors

  Target loading:

  TARGET_LOAD

  - or-

  TARGET_APPLY errors

Change data capture latency issues

If you have high latency, then the AWS DMS migration might take longer to appear on the target endpoint.

To resolve this issue, take the following actions:

• Review the CDCLatencySource or CDCLatencyTarget Amazon CloudWatch metrics to determine whether latency is at the source or target.
• Check the CPU, memory, and input/output per second (IOPS) usage on the source, target, and replication instance. If your resources are constrained, then resize the replication instances.
• Review the network bandwidth between components.

Full load performance issues

If migrated data doesn't appear on the target after you start a full load task, then the initial data load might run slowly or get stuck.

To resolve this issue, take the following actions:

• Check table statistics and CloudWatch metrics to determine whether the task is stuck.
• Make sure that the source, target, and AWS DMS instance have enough CPU, memory, and IOPS to run your tasks.
• Use limited large binary object (LOB) mode, and set the maximum LOB size.
• Use parallel load for large tables, configure ParallelLoadThreads for supported target endpoints, and create multiple tasks.
• Test network performance between components. Check network bandwidth, especially for on-premises sources. Minimize network hops between components.
• Turn off detailed debug logging after you troubleshoot.
• Turn off Amazon Relational Database Service (Amazon RDS) Multi-AZ deployments and backups on your target Amazon RDS instance during migration. 
  Note: Amazon RDS Multi-AZ deployments require synchronous replication to a standby instance. During high-volume INSERT operations, synchronous replication might increase the write latency. Automated backups use input/output resources and increase transaction log growth. For production-level protection, turn on Multi-AZ deployments after you complete the full load.
• Remove foreign keys and indexes during full load.

Data validation failures

If you activate data validation and run an AWS DMS task, then the validation state shows "Error" or "Mismatched records".

To resolve this issue, take the following actions:

• To identify the failure, run the following query on the target:

  awsdms_validation_failures_v1

• Make sure that you mapped the source datatype to a compatible target.

• Connect to the databases to confirm that the source and target character sets and collations match.

• Review the data validation limitations for your database engines.

• Use Time Travel to debug data discrepancies. Time Travel doesn't require you to rerun tasks.

Endpoint connection failures

If you create a new AWS DMS endpoint and your TestConnection API operation fails for your source or target endpoints, then you might experience connectivity issues.

To resolve this issue, see How do I troubleshoot AWS DMS endpoint connectivity failures?

Related information

Best practices for AWS Database Migration Service

Troubleshooting migration tasks in AWS Database Migration Service

Troubleshooting latency issues in AWS Database Migration Service