Symptoms:

In the target node's log file, there will be an error that looks like the following:

Failed to process insert event in batch [batch ID] on channel [channel ID].

The log file will include the row data followed by the below message and stack trace:

org.jumpmind.symmetric.io.data.writer.ConflictException: Detected conflict while executing INSERT on [table name].  The primary key data was: [PK data].
Failed to fallback.  The original error message was: Violation of PRIMARY KEY constraint [PK constraint name].
Cannot insert duplicate key in object [table name].  The duplicate key value is [duplicate PK value].
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.performFallbackToUpdate(AbstractDatabaseWriterConflictResolver.java:366)
    at org.jumpmind.symmetric.io.data.writer.DefaultTransformWriterConflictResolver.performFallbackToUpdate(DefaultTransformWriterConflictResolver.java:108)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.performChainedFallbackForInsert(AbstractDatabaseWriterConflictResolver.java:207)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.needsResolved(AbstractDatabaseWriterConflictResolver.java:63)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:204)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:186)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1068)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1044)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Explanation:

In SQL Server 2016, there was a change in how datetime values are converted and compared. Converting from a datetime to a different data type that has higher precision behaves differently than it did in previous versions of SQL Server. In addition, predicates involving datetime values also exhibit different behavior by considering the full precision of the value instead of rounding it. This change is discussed in detail in the below article written by Dan Guzman:

https://www.dbdelta.com/sql-server-2016-and-azure-sql-database-v12-breaking-change/

This breaking change can cause synchronization issues when a datetime column is part of a primary key or a table with a datetime column does not have a primary key. In either case, SymmetricDS is able to correctly detect insert conflicts, but it is not able to convert the insert statements into successful update statements. This occurs because the datetime comparison in a select statement's where clause behaves differently to the datetime comparison in an update statement's where clause.

Resolution:

To resolve this issue, first make sure your SQL Server driver is version 12.2.0 or newer. You can find the driver in the lib directory. Its name should begin with mssql-jdbc- followed by the version number. You can find the latest version of the SQL Server JDBC driver on Microsoft's website below:

https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server

Once your SQL Server driver is up to date, edit your engine file and add the following property to your db.url (or target.db.url if applicable):

datetimeParameterType=datetime

Once you have edited this parameter, save your engine file and restart SymmetricDS.