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개 답변
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

AWS
Eli DOE
답변함 2년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인