CiviCRM is creating a lot of orphaned records in civicrm_address with contact_id NULL and master_id NULL
CiviCRM is creating a lot of orphaned records in civicrm_address with contact_id NULL and master_id NULL. For CiviCRM sites which typically accept donations. There have been instances where thousands of orphaned records exist in the database.
Steps to reproduce
- Check the civicrm_address table that no orphaned records exist (contact_id NULL and master_id NULL)
- Visit a Donation form (Contribution page) as Anonymous user
- Complete the form and address fields
- Submit the Donation form to complete the Contribution
- Check the civicrm_address table for orphaned records
- Again, repeat the process
- Check the civicrm_address table for orphaned records
How to locate the orphaned records
You can easily locate them by querying:
SELECT COUNT(*) FROM civicrm_address WHERE contact_id IS NULL
;
And also you can find these orphaned records by excluding addresses created by Event locations.
SELECT * FROM civicrm_address a WHERE a.contact_id is null and a.is_billing=1 AND NOT EXISTS (SELECT id FROM civicrm_loc_block l WHERE l.address_id=a.id);
And also by checking contributions...
SELECT COUNT(*) FROM civicrm_address a LEFT JOIN civicrm_contribution c ON c.address_id = a.id WHERE a.contact_id IS NULL AND c.id IS NULL;
Agileware Ref: CIVICRM-1562