Merging contacts with the same dedupe exceptions causes DB error
In certain circumstances, merging two contacts which both have the same dedupe exception pointing to the same third contact will cause a DB error (duplicate entry). This is a typical example:
UPDATE civicrm_dedupe_exception SET contact_id2 = <cid1> WHERE contact_id2 = <cid2> [nativecode=1062 ** Duplicate entry '<cid3>-<cid1>' for key 'UI_contact_id1_contact_id2']
To create the circumstances required to generate this issue, the following really needs to happen:
- A new person is added to the database
- Their partner is added to the database
- A dedupe exception is created between the above contacts to prevent merging partners
- The partner created at step 2 changes their email or something, thus causing a new duplicate contact to be created
- A dedupe exception is created between the contact at step 4 and the contact at step 1.
- The duplicate contacts created at steps 2 and 4 are discovered and a merge is attempted.
- This fails with the above error.
Note that the order that the above occurs is important. The above order will definitely reproduce the problem, but a different order may not. The real trick is to produce two contact1-contact2 pairs in the civicrm_dedupe_exception table.
Merge fails with unhelpful error message to the user. In our case, it says "DB Error: Already exists".
The duplicated dedupe exception should simply be ignored. Making the above SQL query "UPDATE IGNORE" instead of simply "UPDATE" would do the trick, although that may not necessarily be the solution, perhaps some test should be made.
Reproduced on CiviCRM 5.28.1 Demo.