How can I troubleshoot an AWS DMS task that failed with a foreign key constraint violation error?

Short Description

By default, AWS DMS tasks load eight tables at a time during the full load phase. These tables load alphabetically by default, unless you configure the loading order for the task. For more information, see Tables load order during full load in AWS Database Migration Service improves migration speeds by adding support for parallel full load and new LOB migration mechanisms.

If you don't configure the load order to load parent tables first, then a child table might load before its parent table. This causes the task to fail with a foreign key constraint violations error. In this case, you see log entries that are similar to the following examples:

[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 0A000 NativeError: 1 Message: ERROR: cannot truncate a table referenced in a foreign key constraint; Error while executing the query [1022502] (ar_odbc_stmt.c:4622)

[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1217 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.23-log]Cannot delete or update a parent row: a foreign key constraint fails [1022502] (ar_odbc_stmt.c:4615)

Ongoing replication uses the Transactional Apply mode that applies the transactions in the same commit order as the source. When the task is in the ongoing replication phase, you can activate foreign key constraints on the target. If you use Batch Apply mode for ongoing replication, then you must deactivate the foreign keys, even during the change data capture (CDC) phase.

Resolution

To resolve this error, complete either of the following steps:

• Deactivate foreign key constraints
• Use Drop tables on target mode

Deactivate foreign key constraints

If the target is a MySQL-compatible database, then you can use extra connection attributes to deactivate foreign key constraints:

initstmt=SET FOREIGN_KEY_CHECKS=0

If the target is a PostgreSQL-compatible database, then you see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica. To do this, add the extra connection attribute afterConnectScript=SET session_replication_role='replica' to the endpoint. Or, use the AWS Command Line Interface to add endpoint settings to the target endpoint.

For other databases engines, manually deactivate or drop foreign key constraints.

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

Use Drop tables on target mode

When you use Drop tables on target mode, AWS DMS creates only objects that are necessary for a full load to succeed on the target. AWS DMS also refers to this as the DROP_AND_CREATE task setting. However, if you use Drop tables on target mode, then you must manually create other objects outside of AWS DMS. This includes objects such as secondary indexes, data defaults, and triggers.

Related Information

Using the task log to troubleshoot migration issues