Data Migration Service (DMS): How do I Continue after Error ( Failed to execute delete statement)?

Question

AWS/DMS

Source Aurora MySQL -> Target AuroraMySQL

My goal it to replicate all changes except deletes.

Replicating a single table from source to target. Target has a BEFORE DELETE trigger on the table which stops deletes from happening (I want full history in the target table).

Replication works fine until I delete a record on the source.

From CloudWatch the errors are:

Failed (retcode -1) to execute statement [1022502] (ar_odbc_stmt.c:2721)
RetCode: SQL_ERROR  SqlState: HY000 NativeError: 1644 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.12]table MyBigTable does not support deletion
Failed to execute delete statement, stream position is mysql-bin-changelog.000011:24635029:0:24637196:47269275079:mysql-bin-changelog.000011:24634899

My task settings include the following "ApplyErrorDeletePolicy": "IGNORE_RECORD",

However, DMS does not "ignore the record" and instead fails.

Is there a way I can instruct DMS to ignore the error and continue rolling forward?

score 0 · Answer 1 · answered Aug 10 '21 at 23:00

Turns out there's a difference between performing a delete that deletes no rows (no error) and stopping a delete with a trigger that performs a signal.

CREATE DEFINER=`admin`@`%` TRIGGER NO_DELETES_ON_MYBIGTABLE
BEFORE DELETE
ON MyBigTable FOR EACH ROW
  if (user() like 'repluser%') then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table MyBigTable does not support deletion';
  end if

A delete that deletes no rows (i.e. the row doesn't exist in the table) is handled by DMS.

Anyone know how to stop a delete in MySQL without throwing a signal? A delete that returns an error (or a signal), is NOT handled by DMS.

Data Migration Service (DMS): How do I Continue after Error ( Failed to execute delete statement)?

1 Answers1