I have lots of tables that are stuck in the "Pending Records" Validation state and I can't get them to progress

Question

I have a migration from MySQL ---> MariaDB that is pretty straightforward and is generally working well.  However, I have several tables that have a Validation State of "Pending records".  This task has been running for several days and the validation state never changes.

The problem doesn't seem to be caused by the number of records in the table.  Some of the tables have a small total number of records (~900) and a small number of records in the "pending" state (<10). Some of the tables have a large total number of records (~1.5MM) and several thousand in the "pending" state.  And other tables have 0 validated records and various numbers of total records.  I've have noticed that if I "Reload table data" for some of the tables it works and they become 100% validated.  However, it doesn't work every time and reloading data seems like a bad way to resolve the problem.

I've read through the support documents and have an open case with AWS support for this issue, but I thought I would reach out to see if anyone has had this problem and how they solved it.

Tips:
* You're going to need a *much* larger migration instance than you think; these migration tasks are **very** RAM intensive!
* Increase the `ThreadCount` property of the Validation portion of the migration task; it *seems* to help somewhat

Answer

Hello,

Thank you for reaching us. I understand that you have noticed that a small number of records to remain in "pending" state after full load completion and sometimes reloading the table will resolve the issue. While the specific issue needs to be investigated upon by the Premium Support Engineer, a probable reason can be due to the presence of rows which are being continuously modified at the source during validation; then AWS DMS can't validate those rows.

Besides, we also have an option to manually validate the tables again by clicking on the 'Validate again' button on the task console. This will work for a full load and cdc dms task and a cdc only dms task but not for a full load only dms task.

I will also recommend you to let the task run with these pending records for a while to see if they are marked as 'Mismatched records' and then you can make use of the 'awsdms_validation_failures_v1' control table at the target to look them up:

[+] AWS DMS data validation - Troubleshooting - 
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html#CHAP_Validating.Troubleshooting

I have lots of tables that are stuck in the "Pending Records" Validation state and I can't get them to progress

Relevant content