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?

Resolution

If you have large object (LOB) columns in your migration task, then you see a log entry similar to the following:

Messages
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

Did this article help you?

Anything we could improve?


Need more help?