DMS replication error: malformed array literal

Question

I have a DMS task set up for full replication and CDC for a single table from a RDS Postgres source to another RDS Postgres destination. Full replication is successful but during CDC I immediately get an error caused from replicating a uuid[] column which is sometimes null in the source database.

It seems to me that the null value is being converted into an empty string `""` and then when the value is inserted into the target table, an empty string is not a valid value for a `uuid[]` column.

I've pasted my task settings JSON below but here are some key details:
- Full LOB mode is enabled
- Lob chunk size: 128kb

Here are some selected long entries highlighting the issue:
```
2025-03-26T16:39:17 [TARGET_APPLY    ]E:  RetCode: SQL_ERROR  SqlState: 22P02 NativeError: 1 Message: ERROR: malformed array literal: ""; Error while executing the query [1022502]  (ar_odbc_stmt.c:3216)
2025-03-26T16:39:17 [TARGET_APPLY    ]W:  Failed to apply record 17 to target  {operation:UPDATE (3), tableName:session, schemaName:public, txnId:8288481, connectionId:30241, statement:UPDATE "src_app"."session_v2"                                 SET "planned_intervention_ids"=?                                  WHERE "id"=? , streamPosition:00002A7B/88030ED0.2.00002A7B/88030F08}  (endpointshell.c:5398)
```

These are my task settings.
```
{
    "Logging": {
        "EnableLogging": true,
        "EnableLogContext": true,
        "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_DEFAULT",
                "Id": "COMMON"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "ADDONS"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "DATA_STRUCTURE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "COMMUNICATION"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "FILE_TRANSFER"
            }
        ],
        "CloudWatchLogGroup": "",
        "CloudWatchLogStream": ""
    },
    "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,
        "DataMaskingErrorPolicy": "STOP_TASK",
        "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"
    },
    "TTSettings": {
        "TTS3Settings": null,
        "TTRecordSettings": null,
        "EnableTT": false
    },
    "FullLoadSettings": {
        "CommitRate": 10000,
        "StopTaskCachedChangesApplied": false,
        "StopTaskCachedChangesNotApplied": false,
        "MaxFullLoadSubTasks": 8,
        "TransactionConsistencyTimeout": 600,
        "CreatePkAfterFullLoad": false,
        "TargetTablePrepMode": "TRUNCATE_BEFORE_LOAD"
    },
    "TargetMetadata": {
        "ParallelApplyBufferSize": 0,
        "ParallelApplyQueuesPerThread": 0,
        "ParallelApplyThreads": 0,
        "TargetSchema": "",
        "InlineLobMaxSize": 0,
        "ParallelLoadQueuesPerThread": 0,
        "SupportLobs": true,
        "LobChunkSize": 128,
        "TaskRecoveryTableEnabled": false,
        "ParallelLoadThreads": 0,
        "LobMaxSize": 0,
        "BatchApplyEnabled": false,
        "FullLobMode": true,
        "LimitedSizeLobMode": false,
        "LoadMaxFileSize": 0,
        "ParallelLoadBufferSize": 0
    },
    "BeforeImageSettings": null,
    "ControlTablesSettings": {
        "historyTimeslotInMinutes": 5,
        "HistoryTimeslotInMinutes": 5,
        "StatusTableEnabled": true,
        "SuspendedTablesTableEnabled": true,
        "HistoryTableEnabled": true,
        "ControlSchema": "",
        "FullLoadExceptionTableEnabled": false
    },
    "LoopbackPreventionSettings": null,
    "CharacterSetSettings": null,
    "FailTaskWhenCleanTaskResourceFailed": false,
    "ChangeProcessingTuning": {
        "StatementCacheSize": 50,
        "CommitTimeout": 1,
        "RecoveryTimeout": 0,
        "BatchApplyPreserveTransaction": true,
        "BatchApplyTimeoutMin": 1,
        "BatchSplitSize": 0,
        "BatchApplyTimeoutMax": 30,
        "MinTransactionSize": 1000,
        "MemoryKeepTime": 60,
        "BatchApplyMemoryLimit": 500,
        "MemoryLimitTotal": 1024
    },
    "ChangeProcessingDdlHandlingPolicy": {
        "HandleSourceTableDropped": true,
        "HandleSourceTableTruncated": true,
        "HandleSourceTableAltered": true
    },
    "PostProcessingRules": null
}
```

re:Post Agent · Answer

The error you're encountering is related to how AWS DMS is handling null values in the uuid[] column during the Change Data Capture (CDC) phase. It appears that DMS is converting null values to empty strings, which are not valid for uuid[] columns in PostgreSQL.

To resolve this issue, you can try the following approaches:

1. Use a transformation rule: You can create a transformation rule in your DMS task to handle null values correctly. Add a rule that converts empty strings back to null for the specific column. This can be done using the 'CASE' expression in a transformation rule.

2. Adjust target endpoint settings: In your target endpoint settings, you can add an extra connection attribute to handle null values differently. Try adding the following attribute:
   `emptyStringIsNull=true`
   This tells DMS to treat empty strings as null values when inserting into the target database.

3. Modify source endpoint settings: If the issue persists, you might need to adjust how the source database handles the uuid[] column. In your source endpoint settings, add the following extra connection attribute:
   `captureDdls=N`
   This disables DDL capture, which might help with the uuid[] column handling.

4. Update DMS version: Ensure you're using the latest version of AWS DMS, as newer versions often include bug fixes and improvements in handling various data types.

5. Consider using LOB settings: Although you have Full LOB mode enabled, you might want to experiment with different LOB settings. Try adjusting the 'LobChunkSize' or switching to 'LimitedSizeLobMode' to see if it affects how the uuid[] column is handled.

If these solutions don't resolve the issue, you may need to consider creating a custom pre-processing script on the source database or a post-processing script on the target database to handle the uuid[] column data appropriately.

Remember to test any changes in a non-production environment first to ensure they don't cause any unintended consequences.  
**Sources**  
[DMS between two RDS instances, grown OldestReplicationSlotLag | AWS re:Post](https://repost.aws/questions/QUZMwLfiuFSZ-NCpaAI4TlNQ/dms-between-two-rds-instances-grown-oldestreplicationslotlag)  
[AWS DMS  table load error at target: invalid byte sequence for encoding UTF8 | AWS re:Post](https://repost.aws/questions/QULjpoQGxcTtCLbUdY8nXB3A/aws-dms-table-load-error-at-target-invalid-byte-sequence-for-encoding-utf8)

Olwethu_N · Answer

I can emphasize as per previous recommendation that switching to LimitedSizeLobMode is the recommended solution for this specific issue with uuid[] columns and changing to Limited LOB Mode should resolve the "malformed array literal" error you're experiencing during CDC replication.

To implement this, focus specifically on modifying these two settings in your TargetMetadata configuration:

"FullLobMode": false, "LimitedSizeLobMode": true

To implement this, focus specifically on modifying these two settings in your TargetMetadata configuration:

"FullLobMode": false,
"LimitedSizeLobMode": true

DMS replication error: malformed array literal

Relevant content