AWS DMS Duplicate entry error

0

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?

1 Answer
0

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/

Raghu_C
answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions