More than one primary address

[Eileen]

Hello,

After being asked to investigate a CiviReport problem (contribution records being counted twice in the Donor Report Detail I found that a number of contact records have more than one address marked as primary (ie. running this query returns 428 rows)

SELECT CONTACT_ID , count(`ID`) as count FROM `civicrm_address` WHERE is_primary=1
GROUP by CONTACT_ID
having count(`ID`) > 1

Does anyone have any ideas how this could have happened & whether it still might be happening - please consider running the same query on your own site to see if it affects you. It is not visible / editable in the front end.

I did the same test of two sites I am connected with. For one I found no duplicates. The other, which has been subject to many imports, had numerous duplicates so perhaps imports is the key?

[Donald Lobo]

i'm 99% sure its the import process

if u can set up a scenario to duplicate it on a consistent basis, we'll definitely investigate and fix. Might also have been am issue with the older version of import (which were a bit more buggier)

lobo

[Eileen]

Hmm, I'm leaning towards thinking it happens with the payment processor (possibly as well not instead).

On the main site I am looking at the contact records affected are not sequential

I have determined that all the duplicate primary address records are billing address records.  All billing address records are marked as primary address records

 he site uses paypal as a processor and primarily processes contribute (membership) transactions

My other site using payment express & event transactions is not affected.

[Eileen]

NB SQL to fix those where the billing address is affected:

UPDATE civicrm_address AS address RIGHT JOIN (
SELECT CONTACT_ID AS cid
FROM `civicrm_address`
WHERE is_primary =1
GROUP BY CONTACT_ID
HAVING count( `ID` ) >1
) AS dupes ON address.contact_id = cid
SET address.is_primary =0 WHERE address.is_billing =1

Or where a particular location type  is a problem:

 UPDATE civicrm_address AS address RIGHT JOIN (
SELECT CONTACT_ID AS cid
FROM `civicrm_address`
WHERE is_primary =1
GROUP BY CONTACT_ID
HAVING count( `ID` ) >1
) AS dupes ON address.contact_id = cid
SET address.is_primary =0 WHERE `location_type_id` =3

[Eileen]

I am definitely seeing newly created contacts & addresses with this problem. Last night several were created. They all signed up for memberships on-line paying by paypal (not all completed) using a profile where all the address fields (incl e-mail ) were of type 'primary'.

This site is on 2.2.5 & due to be upgraded so I will upgrade before investigating further but if anyone could check if this affects their databases using the SQL below & posts if affected it would be helpful


SELECT CONTACT_ID , count(`ID`) as count FROM `civicrm_address` WHERE is_primary=1
GROUP by CONTACT_ID
having count(`ID`) > 1

[Eileen]

After upgrading to 3.0.3 no more of these records were created & clearing out the existing ones solved several problems.

I have just dealt with someone on 2.2.7 who was experiencing errors because Drupal was trying to sync two users with one contact due to them having 2 primary addresses. This record was created recently under 2.2.7 so there may be a bug in some of the 2.2.x releases but not in 3.x

[Eileen]

I have hit this problem again on two more sites.

The symptom that caused them to pick it up was a bit disturbing:

When doing a search it advised that 127 records were found but instead of seeing 50 records per page (as I should have) I only saw about 30  & I was only able to see about 90 of the total.

I realise the reason is it finds the number of distinct contact records (127) and then runs a query which is left joined to the address table (returning about 231). On each page of 50 it gets the first 50 of the 231 and only displays the 30 which are distinct. The impact of this is that you can't actually see the last records.

[Eileen]

So, I have been back to this thread a few times to pick off the sql to fix various sites & now I'm doing it the other way around

Here's the SQL to find all the addresses where none of the addresses are marked as primary

Code: [Select]

SELECT a.id, contact_id, is_primary
FROM civicrm_address a
RIGHT JOIN (

SELECT c.id, CONTACT_ID AS cid
FROM `civicrm_address` c
GROUP BY CONTACT_ID
HAVING count( `ID` ) =1
) AS b ON a.id = b.id

WHERE is_primary =0



To update all these instances where a contact has only one address and it is not primary:

Code: [Select]

UPDATE civicrm_address as a
RIGHT JOIN (

SELECT c.id, CONTACT_ID AS cid
FROM `civicrm_address` c
GROUP BY CONTACT_ID
HAVING count( `ID` ) =1
) AS b ON a.id = b.id
SET a.is_primary =1
WHERE is_primary =0


AND on the e-mail table

Code: [Select]

SELECT a.id, contact_id, is_primary
FROM civicrm_email a
RIGHT JOIN (

SELECT c.id, CONTACT_ID AS cid
FROM `civicrm_email` c
GROUP BY CONTACT_ID
HAVING count( `ID` ) =1
) AS b ON a.id = b.id

WHERE is_primary =0

And to UPDATE

Code: [Select]

UPDATE civicrm_email as a
RIGHT JOIN (

SELECT c.id, CONTACT_ID AS cid
FROM `civicrm_email` c
GROUP BY CONTACT_ID
HAVING count( `ID` ) =1
) AS b ON a.id = b.id
SET a.is_primary =1
WHERE is_primary =0

Code: [Select]

UPDATE civicrm_phone as a
RIGHT JOIN (

SELECT c.id, CONTACT_ID AS cid
FROM `civicrm_phone` c
GROUP BY CONTACT_ID
HAVING count( `ID` ) =1
) AS b ON a.id = b.id
SET a.is_primary =1
WHERE is_primary =0