AWS DMS Duplicate entry error

Question

I'm running AWS DMS task in full load + ongoing replication mode, both source and target are aws aurora mysql. Target database was created by cloning source and truncating all tables to preserve the schema. For one of the tables replication always fails with following error:

```
2022-06-23T17:12:05 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 23000 NativeError: 1062 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.12-log]Duplicate entry '1213210-50' for key 'PRIMARY' [1022510] (ar_odbc_stmt.c:4828)
```
I have verified that there is no duplicates in source database table, the tables have identical schema in both source and target, and both source and target table has primary key with auto increment.
The only workaround I found was to drop the table on target db and let DMS create it, but DMS creates it without indexes and foreign keys that we need. Is there any fix for this error that would allow me to preserve the table schema?

Answer

Hello,
From the provided description you are running in to a Duplicate key error. Below are some suggestions that can be followed to mitigate the issue.
When the task is in Full Load please disable foreign keys and triggers on the target table.
For Full Load remove the Auto Increment property on the table and add it after the full load is done with a maximum value for the column by resetting it.

[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html#CHAP_Source.MySQL.Limitations
[+] https://aws.amazon.com/blogs/database/debugging-your-aws-dms-migrations-what-to-do-when-things-go-wrong-part-1/
[+] https://aws.amazon.com/blogs/database/debugging-your-aws-dms-migrations-what-to-do-when-things-go-wrong-part-2/
[+] https://aws.amazon.com/blogs/database/debugging-your-aws-dms-migrations-what-to-do-when-things-go-wrong-part-3/

AWS DMS Duplicate entry error

Relevanter Inhalt