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

Last updated: 2019-10-02

I have an AWS Database Migration Service (AWS DMS) task that is failing with a foreign key constraint violation. Why did I receive this error, and how do I resolve it?

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:

Messages
[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 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.


Did this article help you?

Anything we could improve?


Need more help?