By using AWS re:Post, you agree to the Terms of Use
/Phantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Source/

Phantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Source

0

We have an EC2 instance with SQL Server installed and AWS DMS Replication set up to sync changes from Database A - Table A to an Aurora Postgres Database A Table A. This has been working fine for the last year for inserts/updates/deletes. We recently added a new field (LastUpdateDate) into Table A in SQL Server of type datetimeoffset, this was automatically added to the corresponding Table A in Aurora Postgres of type varchar(34). This works fine for insert and updates. However if the column LastUpdateDate is NULL and we delete this row on the source (SQL Server), an error is throw in DMS where it is attempting to do an update of the corresponding row in Aurora Postgres but trying to set the LastUpdateDate to an invalid date (10000-01-01 04:17:59.0000000 +04:18), this is 35 characters long and so fails to insert into the LastUpdateField which is on 34 characters. My question is we are only doing a delete, where is this update coming from? Where is this strange date coming from? It is not occurring on the SQL Server side as we have no triggers on the table, have cdc enabled and can see that only a Delete operation was done. The delete operation does succeed but we still have these update errors showing up in our 'public.awsdms_apply_exceptions table.'

Any help would be appreciated. Thanks

1 Answers
3

By default DMS maps DATETIMEOFFSET to STRING (a), so if DMS replicates the ALTER TABLE .. ADD LastUpdateDate DATETIMEOFFSET it will create it on PostgreSQL as LastUpdateDate VARCHAR(##).

I understand that, after the DDL ALTER TABLE .. ADD LastUpdateDate DATETIMEOFFSET operation, when you DELETE a record from the source table DMS is logging an UPDATE exception in the awsdms_apply_exceptions table with an error message like "..source column(s) contain data that exceed(s) the size limit of the corresponding target column ... source column is 35 characters while target column is 34 characters" even though the DELETE is successfully replicated.

The behavior seems like a bug and I have raised a bug report with the Service team. For now, you can safely ignore this specific exception in the awsdms_apply_exceptions table.

(a) https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.DataTypes

answered 3 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions