SyncException: Column Name Conflict with Short Column Names "D" and "M" using SqlSyncChangeTrackingProvider

[almuatacim]

I am developing an ASP.NET Web API to synchronize SQL Server databases using Dotmim.Sync and the SqlSyncChangeTrackingProvider. While syncing a table named opr, I encountered a Dotmim.Sync.SyncException related to two specific columns named D and M. The exception is as follows:

Dotmim.Sync.SyncException
  HResult=0x80131500
  Message=[ProvisionAsync].Provision:NotSet.Overwrite:False..[InternalEnsureScopeInfoAsync].Overwrite:False..[InternalGetSchemaAsync]..[InternalGetTableSchemaAsync].Table:opr..[FillSyncTableWithColumns].Table:opr..Column does not exist in the table opr.
  Source=Dotmim.Sync.Core
  StackTrace:
     at Dotmim.Sync.RemoteOrchestrator.<ProvisionAsync>d__6.MoveNext()
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     ...
Inner Exception 1:
MissingColumnException: Column  does not exists in the table opr.

Steps to Reproduce:

1. Create a SQL Server table with the following schema:

   
   CREATE TABLE opr (
   D Money NULL,
   M Money NULL,
   ID_PK uniqueidentifier NOT NULL PRIMARY KEY,);

2. Set up Dotmim.Sync using SqlSyncChangeTrackingProvider to sync the opr table between SQL Server databases.

3. Attempt to provision and sync this table using Dotmim.Sync in an ASP.NET Core Web API.

4. The sync operation fails with the above exception, specifically when it encounters the columns named D and M.

Workaround:

When I rename the columns D and M to more descriptive names like D_Col and M_Col, the sync operation works successfully without any issues. Another workaround is to exclude these columns from the sync setup:

setup.Tables["opr"].Columns.AddRange(new string[]
{ "ID_PK"
// Excluded "D" and "M"
 });

Additional Information:

• Dotmim.Sync version: 1.2.0
• SQL Server version: 16.0.4135.4

I suspect that this issue might be related to the short column names D and M, potentially conflicting with internal Dotmim.Sync handling or reserved keywords.

Request:

Could you please look into whether column name length or specific characters in column names might be causing this issue, or if there are certain limitations with Dotmim.Sync and SqlSyncChangeTrackingProvider that need to be addressed? I'd appreciate guidance on how to best handle this scenario while keeping the original column names.

Thank you for your help!

[Mimetis]

Thanks for the detailed feedback ! It helped a lot to debug this issue.

Indeed, there is a bug here in the TableParser.

I've just made a fix that should resolve your issue. Can you grab the last code source from the main branch, and make a new test ?

[almuatacim]

Hi,

Thanks for the quick fix! I’ve just grabbed the latest code from the main branch, and I can confirm that the issue is now resolved. The sync works perfectly with the original column names D and M in the opr table.

Just a quick question: do you have an estimated release date for when this update will be available on NuGet? It would help me and others plan our updates accordingly.

Thanks again for your great support and for addressing this so quickly!

Best regards,
Almuatacim Hamouda

[Mimetis]

I will make a pre release soon, like next week :)