Some problems about using AWS DMS

AWS DMS is a new type of service used to migrate data from different types of database and data-warehouse. I met some problems when trying to use it in production environment.

Problem 1. When using a MySQL server of AWS RDS as the source of a replication task. It reported errors after started the task:

The failure message looks terrible. But at least I can find this doc to follow. After changed the configurations as below:

binlog_format ROW
binlog_checksum NONE
binlog_row_image FULL

the error still existed.
The real answer is in here since I used RDS instead of self-managed MySQL. After I add one line Terraform code to enable “automatic backups”:

the replication task began to work without the error.

Problem 2. Running replication task for a while to export data from MySQL to AWS Redshift. A new error log appeared in Redshift load logs:

Why masteruser is not authorized? The answer is here. Below is the Terraform code:

Then I had giiven “dms_assume_role” two Trusty Entities

Problem 3. There was still a error in Redshift load log (so many errors in AWS DMS…):

Error Type Raw Field Value
Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS] timestamp 0000-00-00 00:00:00

Seems the answer is here. Therefore I added “acceptanydate=true;timeformat=auto” into the “extra connection settings” in Redshift endpoint. But the error just changed to:

Error Type Raw Field Value
Invalid data timestamp 0000-00-00 00:00:00

After searching for almost two days, I found that the reason is in the schema of Redshift, which is automatically created by AWS DMS replication task.

Since the schema doesn’t allow “mydate” column to be null but the “acceptanydate=true” is trying to transfer “0000-00-00 00:00:00 to null”, the final error is “Invalid data” for Redshift.
The solution for this problem is: create table of Redshift manually to let “mydate” column to be “nullable”, and change the working mode of replication task to “TRUNCATE_BEFORE_LOAD”.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.