3

We have cloned a live DB and run the following SQL to anonymise the contact data:-

delete from civicrm_email; 
delete from civicrm_address; 
update civicrm_contact set birth_date = NULL; 
update civicrm_contact set first_name = 'Staging';
update civicrm_contact set middle_name = '';   
update civicrm_contact set last_name = concat('Test',id); 
update civicrm_contact set legal_name = ''; 
update civicrm_contact set display_name = concat('Staging Test',id); 
update civicrm_contact set addressee_display = '';

However, when we search for contacts (Search/Find Contacts) it still displays the original contact name under the "Name" column.

How can we improve this SQL script to remove all traces of contact names?

4

As people have pointed out there is a very comprehensive answer to the question of anonymising data, however this questions was about simply clearing data.

The correct answer from Pete and Demerit was that I had missed the sort_name column from the civicrm_contact table. So the full SQL is now:-

delete from civicrm_email; 
delete from civicrm_address; 
delete from civicrm_phone;
update civicrm_contact set birth_date = NULL; 
update civicrm_contact set first_name = 'Staging';
update civicrm_contact set middle_name = '';   
update civicrm_contact set last_name = concat('Test',id);
update civicrm_contact set legal_name = ''; 
update civicrm_contact set display_name = concat('Staging Test',id); 
update civicrm_contact set sort_name = concat('Staging, Test',id); 
update civicrm_contact set addressee_display = 'Dear Staging';
update civicrm_contact set email_greeting_display = 'Dear Staging';
update civicrm_contact set postal_greeting_display = 'Dear Staging';

And as pointed out by @Chris you will also need to clear the cache tables, but please use the GUI for this as direct SQL on these tables can cripple the CiviCRM caching logic.

Feel free to edit and expand this answer if you know of any other fields that can be cleared by SQL.

| improve this answer | |
1

While there's a good answer as indicated by the comments above, in this specific issue might be seeing data from the cache in those autocomplete results?

CiviCRM has a UI to clear the cache at something like Administer > System Settings > Cleanup Caches & Update Paths

Using CiviCRM's public interfaces / ux / tools like civix are recommended over direct SQL. But you asked about a specific case (contact table wiped, autocomplete shows retained results), so there you go :)

Your SQL may still not fully anonymize the DB (see other answer), and there's no simple answer which can ensure you do this short of wiping and installing a fresh DB.

| improve this answer | |
  • It's probably also sort_name. – Demerit Mar 18 at 19:06
  • Dear Chris, I voted down as although this is sage advice (to use the GUI) it's not an answer. It was the sort_name that Pete and Demerit have mentioned. – Darren Mar 19 at 9:38
  • No worries, thanks for saying why :) – Chris Burgess Mar 22 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.