Replies: 11 comments 47 replies
-
My first guess would be to use For the errors occured, it's tricky. |
Beta Was this translation helpful? Give feedback.
-
Yes disabling the foreign keys will work, but the tables are locked during sync and data inconsistencies can happen. In most scenarios sync is a background process so now any application using the database will have queries hanging because sync has locks on the tables within the scope. I made a sample a few months ago. https://github.com/slagtejn/Dotmim.Sync.FKError This issue is not limited to this specific scenario. It's a general issue when using filters on a relational database. The data has to be perfect for sync to finish. If filtered data is crossing tables then sync could continue to fail instead of continuing to sync the rest of the data and only fail specific records. It will fail at the same point every sync cycle and now it's in a broken state with no way to fix it. Yes it's tricky to find a solution for this. |
Beta Was this translation helpful? Give feedback.
-
Yes I think we only use the Bulk stored procs and the setting 'UseBulkOperations' is never used. Maybe if these records fail we fallback to use the _update stored proc and try to insert them one at a time so then we know which records failed. |
Beta Was this translation helpful? Give feedback.
-
We have this issue a lot. We cannot disable foreign keys as sync errors can result in inconsistent data. So we often find a single sync error can cause all subsequent syncs to fail. Our customers are beyond our control so we have customers who ignore sync errors for months at a time. |
Beta Was this translation helpful? Give feedback.
-
Perhaps I have is understood. We don't want to bypass foreign keys. We would prefer if a record that failed to sync did not cause all subsequent data to fail to sync. I think where it gets tricky is in determining which row has the error that causes a batch to fail. Is there any guidance on how to do that? If we had that information we could remediate. |
Beta Was this translation helpful? Give feedback.
-
How about an option to choose 1 record at a time? We service lots of companies but they typically sync 1 order at a time. Bulk operations aren't that important for us. At the moment even if we choose a very small batch size, we still see hundreds of orders in a batch. |
Beta Was this translation helpful? Give feedback.
-
I've just create a POC that re-introduce the possibility to choose between bulk or not bulk operations for SQL Server (Sqlite & MySql are not concerned here, since bulk is not supported) using the It's a major performance downgrade, especially for first sync (when initializing a new client database) The I've created a new interceptor called (I think I will rename it ... if you have any idea ;) ) var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var employee1Provider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName1))
{
// .... And .... We're back again
UseBulkOperations = false
};
try
{
var emp1Agent = new SyncAgent(employee1Provider, serverProvider);
var emp1Params = new SyncParameters(("EmployeeId", 1));
emp1Agent.LocalOrchestrator.OnApplyChangesErrorOccured(args =>
{
Console.WriteLine(args.ErrorRow);
// We can do something here the failed row
// ....
// Then pass the resolution to Continue to prevent a fail
args.Resolution = ErrorResolution.Continue;
});
var emp1result = await emp1Agent.SynchronizeAsync(setup, emp1Params, progress);
Console.WriteLine(emp1result);
} As you can see we downloaded 6 rows, but only 5 have been applied locally. Your thoughts ? |
Beta Was this translation helpful? Give feedback.
-
Will this solution work when UsingBulkOperations? If an error occurs on the bulk records it falls back to using the single stored proc to figure out the failed records. We would be able to UseBulkOperations and still figure out which records failed by the fallback |
Beta Was this translation helpful? Give feedback.
-
I think it is ok for bulk operations to be the default. Then on error switch to row-at-a-time-sync and then sync again to find the faulting row. After a faulting row has been remediated, switch back to bulk operations. This might be done in a loop ... switch back and forth between bulk operations until the last faulting row is remediated. |
Beta Was this translation helpful? Give feedback.
-
Will this be finished in 0.9.6? I noticed it was in the draft, but does it work when using bulk operations? |
Beta Was this translation helpful? Give feedback.
-
Can a similar scenario arise without a schema change? I see that quite a bit in our logs. Maybe I'm barking up the wrong branch again. EDIT: It's such a big issue that I might have to introduce soft deletes in the next release of this product ... with all the querying overhead that requires |
Beta Was this translation helpful? Give feedback.
-
I know I have asked something similar before, but probably did not explain it well. In our syncing environment we have a scope called user data. This scope contains all the tables needed for the users data with all the correct foreign key relations. This scope is filtered, but how we handle filtered scopes is to create an instance of the base scope with a single filter parameter. The user logging into the application can be linked to multiple users and thus will generate multiple user scopes. For example the scopes would be in the format
UserData_1
UserData_2
UserData_3
We do this because it's a dynamically changing parameter and we would need to get the historical data of a user if they are linked to a new user. A manager getting a new employee for example. The scope_info table will contain the timestamp for each filter. With this implementation we don't have to re-initialize the scope every time the parameter changes to get the historical data and it's transferring a lot less data if new users are added instead of getting all data for all linked users. Over time some of the scopes will become obsolete and the data in the database becomes old, so we have a mechanism to clean it up.
Ok so now the problem. There are cases were foreign key data is crossing scopes. For example some data from UserData_1 crosses into UserData_2. When this happens there are foreign key constraint errors and sync just stops with no other data syncing on the scope. Now UserData_2 syncs and the data is inserted so when UserData_1 syncs again it might finish. If there are a lot of FK errors this process would take a long time and could end in an endless loop. An endless loop would be a data issue that needs to be fixed on server, but at least sync would be able to detect it instead of failing.
Sync framework was able to handle this with the knowledge blob which contained the failed records and this error went through OnApplyChangedFailed interceptor with the type 'ErrorsOccurred'. Sync was able to continue syncing the rest of the data for the scope, but ignore those failed records somehow (still a mystery on how it was able to do that).
Do you see a possible solution to this? I know disabling foreign keys will work, but that locks the table and is not the best solution since it could ruin the integrity of the data. I can think of adding a table that contains the json of the failed records per scope and appends them during the next sync of a specific scope so data is not lost. If sync continues and updates the timestamp on the scope then the failed records would not be retrieved again so there needs to be some sort of tracker.
Beta Was this translation helpful? Give feedback.
All reactions