How can I troubleshoot "ERROR: null value in column violates not-null constraint" for my AWS DMS full load and CDC task?
Last updated: 2019-10-02
I have an AWS Database Migration Service (AWS DMS) task that is both full load and change data capture (CDC). I received an error that says: "ERROR: null value in column violates not-null constraint." How do I resolve this issue?
If you have large object (LOB) columns in your migration task, then you see a log entry similar to the following:
|E: Command failed to load data with exit error code 1, Command output: ERROR: null value in column ’xyz’ violates not-null constraint|
When AWS DMS migrates LOB columns, first all the data except for the LOB column is migrated into your target table, and AWS DMS inserts a NULL record in the LOB column. Then, AWS DMS updates the rows in the target table with the LOB column data.
If the target isn't created by AWS DMS, check the target data description language (DDL) to see if a NOT NULL attribute is specified. If there is a NOT NULL attribute, alter the table to remove the NOT NULL constraint on LOB column datatypes. Then, resume your AWS DMS task.
See the following table for AWS DMS task behavior for LOB modes and task phases:
|LOB Mode||Full Load||Change Data Capture|
|Full LOB Mode||NOT NULL constraint isn't allowed||NOT NULL constraint isn't allowed|
|Limited LOB Mode||NULL constraint is allowed||NOT NULL constraint isn't allowed|