AWS DMS - [DATA_STRUCTURE]W: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detai

We are running a AWS DMS task to replicate data from one MySQL RDS to another MySQL RDS.

But we run into this problems where the dms task stops and gives us the following error:

Last Error Task '4LT64IOJB46SPDLFOWT73ZLWHDP5K6GQWK5OTCY' was suspended due to 6 successive unexpected failures Stop Reason FATAL_ERROR Error Level FATAL

Looking at the logs we find:

2023-02-03T23:39:09 [DATA_STRUCTURE ]W: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>. (at_sqlite.c:536)

No matter how many times we attempt to resume we get the same error. Why is this happening?

Logs

2023-02-03T23:39:09.000+00:00

Copy
2023-02-03T23:39:09 [TASK_MANAGER    ]I:  Task '4LT64IOJB46SPDLFOWT73ZLWHDP5K6GQWK5OTCY' starting full load and CDC in resume mode after recoverable error, retry #1  (replicationtask.c:1325)
2023-02-03T23:39:09 [TASK_MANAGER ]I: Task '4LT64IOJB46SPDLFOWT73ZLWHDP5K6GQWK5OTCY' starting full load and CDC in resume mode after recoverable error, retry #1 (replicationtask.c:1325)

2023-02-03T23:39:09.000+00:00	2023-02-03T23:39:09 [DATA_STRUCTURE ]D: sqlStatement=ALTER TABLE validation_tables ADD COLUMN error_message TEXT; (at_sqlite.c:325)

2023-02-03T23:39:09.000+00:00	2023-02-03T23:39:09 [DATA_STRUCTURE ]D: sqlStatement=ALTER TABLE tables_status ADD COLUMN data_error_counter INTEGER; (at_sqlite.c:325)

2023-02-03T23:39:09.000+00:00

Copy
2023-02-03T23:39:09 [DATA_STRUCTURE  ]W:  SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>.  (at_sqlite.c:536)
2023-02-03T23:39:09 [DATA_STRUCTURE ]W: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>. (at_sqlite.c:536)

2023-02-03T23:39:28.000+00:00	2023-02-03T23:39:28 [TASK_MANAGER ]I: Task '4LT64IOJB46SPDLFOWT73ZLWHDP5K6GQWK5OTCY' starting full load and CDC in resume mode after recoverable error, retry #2 (replicationtask.c:1325)

2023-02-03T23:39:28.000+00:00	2023-02-03T23:39:28 [DATA_STRUCTURE ]D: sqlStatement=ALTER TABLE validation_tables ADD COLUMN error_message TEXT; (at_sqlite.c:325)

2023-02-03T23:39:28.000+00:00	2023-02-03T23:39:28 [DATA_STRUCTURE ]D: sqlStatement=ALTER TABLE tables_status ADD COLUMN data_error_counter INTEGER; (at_sqlite.c:325)

task settings:

{
    "Logging": {
        "EnableLogging": true,
        "EnableLogContext": false,
        "LogComponents": [
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TRANSFORMATION"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SOURCE_UNLOAD"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "IO"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TARGET_LOAD"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "PERFORMANCE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SOURCE_CAPTURE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SORTER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "REST_SERVER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "VALIDATOR_EXT"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TARGET_APPLY"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TASK_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TABLES_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "METADATA_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "FILE_FACTORY"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEBUG",
                "Id": "COMMON"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "ADDONS"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEBUG",
                "Id": "DATA_STRUCTURE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "COMMUNICATION"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "FILE_TRANSFER"
            }
        ],
        "CloudWatchLogGroup": "dms-tasks-geeiq-prod-master-replication-instance-large",
        "CloudWatchLogStream": "dms-task-4LT64IOJB46SPDLFOWT73ZLWHDP5K6GQWK5OTCY"
    },
    "StreamBufferSettings": {
        "StreamBufferCount": 3,
        "CtrlStreamBufferSizeInMB": 5,
        "StreamBufferSizeInMB": 8
    },
    "ErrorBehavior": {
        "FailOnNoTablesCaptured": true,
        "ApplyErrorUpdatePolicy": "LOG_ERROR",
        "FailOnTransactionConsistencyBreached": false,
        "RecoverableErrorThrottlingMax": 1800,
        "DataErrorEscalationPolicy": "SUSPEND_TABLE",
        "ApplyErrorEscalationCount": 0,
        "RecoverableErrorStopRetryAfterThrottlingMax": true,
        "RecoverableErrorThrottling": true,
        "ApplyErrorFailOnTruncationDdl": false,
        "DataTruncationErrorPolicy": "LOG_ERROR",
        "ApplyErrorInsertPolicy": "LOG_ERROR",
        "EventErrorPolicy": "IGNORE",
        "ApplyErrorEscalationPolicy": "LOG_ERROR",
        "RecoverableErrorCount": -1,
        "DataErrorEscalationCount": 0,
        "TableErrorEscalationPolicy": "STOP_TASK",
        "RecoverableErrorInterval": 5,
        "ApplyErrorDeletePolicy": "IGNORE_RECORD",
        "TableErrorEscalationCount": 0,
        "FullLoadIgnoreConflicts": true,
        "DataErrorPolicy": "LOG_ERROR",
        "TableErrorPolicy": "SUSPEND_TABLE"
    },
    "ValidationSettings": {
        "ValidationPartialLobSize": 0,
        "PartitionSize": 10000,
        "RecordFailureDelayLimitInMinutes": 0,
        "SkipLobColumns": false,
        "FailureMaxCount": 10000,
        "HandleCollationDiff": false,
        "ValidationQueryCdcDelaySeconds": 0,
        "ValidationMode": "ROW_LEVEL",
        "TableFailureMaxCount": 1000,
        "RecordFailureDelayInMinutes": 5,
        "MaxKeyColumnSize": 8096,
        "EnableValidation": true,
        "ThreadCount": 5,
        "RecordSuspendDelayInMinutes": 30,
        "ValidationOnly": false
    },
    "TTSettings": {
        "TTS3Settings": null,
        "TTRecordSettings": null,
        "EnableTT": false
    },
    "FullLoadSettings": {
        "CommitRate": 10000,
        "StopTaskCachedChangesApplied": false,
        "StopTaskCachedChangesNotApplied": false,
        "MaxFullLoadSubTasks": 10,
        "TransactionConsistencyTimeout": 600,
        "CreatePkAfterFullLoad": false,
        "TargetTablePrepMode": "TRUNCATE_BEFORE_LOAD"
    },
    "TargetMetadata": {
        "ParallelApplyBufferSize": 0,
        "ParallelApplyQueuesPerThread": 0,
        "ParallelApplyThreads": 0,
        "TargetSchema": "",
        "InlineLobMaxSize": 0,
        "ParallelLoadQueuesPerThread": 0,
        "SupportLobs": true,
        "LobChunkSize": 0,
        "TaskRecoveryTableEnabled": false,
        "ParallelLoadThreads": 0,
        "LobMaxSize": 1024,
        "BatchApplyEnabled": true,
        "FullLobMode": false,
        "LimitedSizeLobMode": true,
        "LoadMaxFileSize": 0,
        "ParallelLoadBufferSize": 0
    },
    "BeforeImageSettings": null,
    "ControlTablesSettings": {
        "historyTimeslotInMinutes": 5,
        "HistoryTimeslotInMinutes": 5,
        "StatusTableEnabled": false,
        "SuspendedTablesTableEnabled": false,
        "HistoryTableEnabled": false,
        "ControlSchema": "",
        "FullLoadExceptionTableEnabled": false
    },
    "LoopbackPreventionSettings": null,
    "CharacterSetSettings": null,
    "FailTaskWhenCleanTaskResourceFailed": false,
    "ChangeProcessingTuning": {
        "StatementCacheSize": 50,
        "CommitTimeout": 1,
        "BatchApplyPreserveTransaction": true,
        "BatchApplyTimeoutMin": 1,
        "BatchSplitSize": 0,
        "BatchApplyTimeoutMax": 30,
        "MinTransactionSize": 1000,
        "MemoryKeepTime": 240,
        "BatchApplyMemoryLimit": 500,
        "MemoryLimitTotal": 5000
    },
    "ChangeProcessingDdlHandlingPolicy": {
        "HandleSourceTableDropped": true,
        "HandleSourceTableTruncated": true,
        "HandleSourceTableAltered": true
    },
    "PostProcessingRules": null
}