Synchronization Cannot Pass Through Module Repeatedly (Database Contains Duplicate Relations)

Description

Synchronization of eWay-CRM Mobile stops at some module and repeats itself again and again.

Error log in eWay-CRM Mobile for iPhones contains this error:

[WARN] [BACK] [2018-03-28 12:57:32] Exception caught while loading all items into module 11.
[WARN] [BACK] [2018-03-28 12:57:32] Exception caught while loading all items into module 11.
[WARN] [BACK] [2018-03-28 12:57:33] Exception caught while loading all items into module 11.
[ERROR] [BACK] [2018-03-28 12:57:33] Sync error: The loader has loaded more items than the wanted list size. Check for duplicit relations.

Number 11 is specific module, in this case Deals. eWay-CRM Mobile has different numbers for all modules.

Error log in eWay-CRM Mobile for Android phones logs also duplicated item identifier:

2018-03-29 16:18:17 com.eway_crm.mobile.androidapp.sync.exceptions.SynchronizationException: The item 3FA25D51-EBA7-11E5-A70D-549F350D3CCE is here twice.
    at com.eway_crm.mobile.androidapp.sync.SynchronizationThread$BlockLoader.loadAndSave(SynchronizationThread.java:1129)
    at com.eway_crm.mobile.androidapp.sync.SynchronizationThread.syncItems(SynchronizationThread.java:867)
    at com.eway_crm.mobile.androidapp.sync.SynchronizationThread.syncAllModuleItems(SynchronizationThread.java:732)
    at com.eway_crm.mobile.androidapp.sync.SynchronizationThread.runFullSync(SynchronizationThread.java:565)
    at com.eway_crm.mobile.androidapp.sync.SynchronizationThread.run(SynchronizationThread.java:229)
    at com.eway_crm.mobile.androidapp.sync.WcfSyncAdapter.onPerformSync(WcfSyncAdapter.java:180)
    at android.content.AbstractThreadedSyncAdapter$SyncThread.run(AbstractThreadedSyncAdapter.java:259)

Version

All versions of eWay-CRM

Difficulty

Medium

Resolution

The cause is duplicity of 1:N relation that duplicates items in the list. The second possible reason can be a duplicate relation between Flows and Actions.

Option 1: Automatic Delete of Duplicate Relations

Availability: The procedure is available since eWay-CRM 5.2.2..

Important: Before you run procedure described below, create a backup of your database first!

If you want to delete all duplicate relations at once, you can use eWaySP_DeleteDuplicatedRelations procedure - run it over server database. You can run it by this command:

EXEC [dbo].[eWaySP_DeleteDuplicatedRelations] 

Option 2: Manual Delete of Duplicate Relations

Important: Before you follow instructions below, create a backup of your database!

All problematic relations can be found by this command:

SELECT ItemGUID1, ObjectTypeID2, RelationType, COUNT(*)
FROM EWR_ObjectRelations WHERE RelationType NOT IN ('GENERAL', 'GROUP') AND Direction = 1
GROUP BY ItemGUID1, ObjectTypeID2, RelationType HAVING COUNT(*) > 1

Command will return ItemGUID1 that can be used to search all duplicated relations. You need to decide what relation will be left and what will be removed (you can decide by the date of created or by the relation owner). These should be old relations only because duplicates are checked by the database trigger.

Deletion can be done by this command:

EXEC eWaySP_DeleteRelations '8E131BDF-B376-11E3-9426-0010188DFF72'

 where you need to set only RelationGUID parameter.

Repeat the procedure so you have no results returned by the search command above. Consistent state of the database is only in the case when command returns no results. Then synchronization on eWay-CRM Mobile should work after that.

Option 3. Duplicate actions for one flow record

The duplicity can be also caused by FlowGUID foreign key in EWD_Actions table. Every Flow record can be referenced at most by one record in table Actions. So run this command to seek the failing Flows:

 SELECT FlowGUID FROM EWD_Actions GROUP BY FlowGUID HAVING count(*) > 1

In older eWay-CRM, there is no column FlowGUID. The following command finds the very same problem:

SELECT f.ItemGUID FROM EWF_Flows f INNER JOIN EWR_ObjectRelations r ON f.ItemGUID = r.ItemGUID1  AND r.ObjectTypeID2 = dbo.GetObjectTypeID('Actions') GROUP BY f.ItemGUID HAVING count(*) > 1

Each FlowGUID in the results has to be solved - pick only one Actions record for it and delete the rest. At the end, the command above must return no results.