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 are loaded alphabetically by default, unless you configure the loading order for the task. For more information, see the Tables load order during full load section of AWS Database Migration Service improves migration speeds by adding support for parallel full load and new LOB migration mechanisms.

If the load order isn't configured to load parent tables first, then a child table might be loaded before its parent table. This causes the task to fail with a foreign key constraint violations error and log entries similar to the following:

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 enable foreign key constraints on the target. If you use Batch Apply mode for ongoing replication, then the foreign keys must be disabled, even during the change data capture (CDC) phase.

Resolution

To resolve this error, either:

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

Disable foreign key constraints

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

initstmt=SET FOREIGN_KEY_CHECKS=0

If the target is a PostgreSQL-compatible database, then you can see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica.

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

Use Drop tables on target mode

When using Drop tables on target mode (the DROP_AND_CREATE task setting), AWS DMS creates only objects that are necessary for a full load to succeed on the target. However, if you use Drop tables on target mode, then you must manually create other objects that aren't created by AWS DMS, such as secondary indexes, data defaults, and triggers.

Related Information

Using the Task Log to Troubleshoot Migration Issues