Multiple Civi contacts sharing the same e-mail address, name and interests causes DB error

[mjcarter]

Sync fails for me (using current master) with a repeatable error on the first step:

DB Error: already exists

Looking in the logs, the full error is this (involving the same e-mail address every time):

Duplicate entry 'test@example.com-30e3cfb4d4a2f4a728a3145a628fcf79' for key 'PRIMARY']#012 * ERROR DEBUGINFO: INSERT INTO tmp_mailchimp_push_c VALUES('29868', 'test@example.com', '', '', '30e3cfb4d4a2f4a728a3145a628fcf79', 'a:2:{s:10:\"6c88d33c11\";b:0;s:10:\"e1e1a247c2\";b:0;}') From searching contacts in the smartgroup in Civi which it is synced from, there are two contacts using this e-mail address, the same person in two different roles at different institutions.

From looking at the code this situation is not a problem as long as the two contacts sharing an e-mail address don't share the same interests and first and last name, but when this is the case the same hash is generated and so a duplicate entry is attempted for the primary key resulting in the error.

I tried the same fix as @bhahumanists from this earlier closed issue and sync completes without the error, but not sure what side effects doing so might cause.

[Kajakaran]

@artfulrobot

in the previous version, we had included contact email as one of the inputs in generating hash key. It is removed in the new API. Do you know why?

in the old api - $hash = md5($contact['email'] . $contact['first_name'] . $contact['last_name'] . $info);

current master - $hash = md5($contact['first_name'] . $contact['last_name'] . $info);

Also do you have any input on why we have not included contact id in generating hash? In this issue, two contacts have same email, no first name, no last name that is why two contacts hash are same.

@mjcarter

[artfulrobot]

At the root of this issue is the fact that MC won't allow (understandably) two subscribers with the same email on the same list, while it's quite possible to create this situation in CiviCRM.

The question is how to square the circle - where's the fudge? At what point do you drop a contact? And how do you choose which contact to drop? What if the two contacts have different interests because of their different roles at different organisations - then it would matter which one you chose. Mailchimp can't handle that.

I understand that falling over is not very graceful way of alerting a user to this issue :wink:

You could edit the table def in CRM_Mailchimp_Sync::createTemporaryTableForCiviCRM, e.g. PRIMARY KEY (email, contact_id, hash) which would then allow you to create the table with multiple contacts in it. But then we'd need some SQL to delete, any duplicate emails - I think this would be indiscriminate and could lead to a lot of [further] confusion. Or likewise, catch the key error and ignore it, but that's doing the same thing.

I can't think of a good fix for this because at the centre of it is the issue that Mailchimp doesn't allow multiple people with the same email per list. "Wilma" who is Director of Good Co. and also Fancy Plc. cannot get two copies of your mailchimp mailout one with "Director of Good Co" and one with "Fancy Plc".

Ideas?

[mjcarter]

@Kajakaran @artfulrobot - had experienced no issue with exactly the same dataset with an earlier version of the extension, this error only began after upgrading to 1.8.7

I wonder if it's related to this change:

• Optimizing sync process - filling up tmp_mailchimp_push_c and using CiviCRM API (than too many objects) on contact fetch

[artfulrobot]

@mjcarter I can't speak for versions <2.0 (current master is v 2.0). v2.0 from me uses the current Mailchimp API and included a big rewrite of the sync routines.

I think the logic would need agreeing before anyone could do any useful work on this. i.e. given the possibility of N contacts with the same email address and potentially different group memberships in CiviCRM like:

Contact	Interests	In mailing group in Civi
A	dancing, skiiing	yes
B	hula hooping	yes
C	dancing	no

What would you expect to happen in Mailchimp after an update Mailchimp from CiviCRM?

[deepak-srivastava]

Update: v2.x release is out.