CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • 2.0 installation appears to be losing contacts
Pages: [1]

Author Topic: 2.0 installation appears to be losing contacts  (Read 1993 times)

tvaughan

  • Guest
2.0 installation appears to be losing contacts
June 16, 2008, 04:49:43 am
Hi, I have a bit of a worrying situation here!

Users of a database with around 1700 contacts are reporting that they are losing contacts without deleting them.  I have a copy of the entire database from about a month ago and the contacts are there but not in the current database.
I've been able to log database access in Apache since May 2 and have been grepping through the logs seeing whether the contacts have been deleted (using this command:  grep delete /var/log/apache2/site.log | grep POST) to check for HTTP POSTs with delete in the URL.  I then compare this list (looking for &cid=xxx to find the contact ID that was deleted) to the list of known missing contact IDs.
So far I've found that one contact was deleted manually but, worryingly, I've found that there are missing contacts with no obvious deletion request in the logs.
Is there a way of deleting contacts that won't show up in the logs as a POST?

If I go to the current version of the database and manually enter the cid into the url (e.g. http://database.tld/civicrm/contact/view?reset=1&cid=414) I get a "You do not have the necessary permission to view this contact" error which I guess is what happens when a contact record isn't found.

My initial reaction was that my users were actually deleting records and forgetting but now I'm not so sure!  Any advice would be very welcome.

tvaughan

  • Guest
Re: 2.0 installation appears to be losing contacts
June 16, 2008, 05:22:18 am
I'm now grepping through the MySQL binlogs and have found the following lines (as an example):

# at 29641445
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 414/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 414 )/*!*/;

Contact ID 327 is a Drupal user and a user of the database.  I'm not sure if it's relevant but it seems interesting to me!

tvaughan

  • Guest
Re: 2.0 installation appears to be losing contacts
June 16, 2008, 05:29:51 am
Sorry to keep spamming this thread, but I've now done a more extensive search which has shown the following.  Each of the contact_ids at the end of the line (1073, 1447 etc.) is missing from the database.

Code: [Select]
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1073/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1447/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 134/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1040/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 156/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 463/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 765/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1058/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 475/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 925/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1082/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1018/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1077/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 693/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 19/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 215/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1001/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1009/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1049/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 865/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1063/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1071/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 425/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1024/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1272/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1152/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 52/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1020/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1436/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 89/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1033/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1031/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 671/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1081/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 918/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1022/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 339/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1079/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 136/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 701/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 963/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1440/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 654/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1007/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 143/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 78/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 77/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 157/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 76/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 606/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1111/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 20/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 138/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1014/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1030/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1175/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1186/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1195/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1196/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1197/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1194/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 414/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 147/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1113/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 785/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1135/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1143/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1132/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 730/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 412/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1134/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 695/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1582/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1532/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1579/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 791/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1181/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 747/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1581/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1207/*!*/;

tvaughan

  • Guest
Re: 2.0 installation appears to be losing contacts
June 16, 2008, 07:08:52 am
Last one!  Realised that the command output wasn't very helpful without also listing the actual command.  This (I think) proves a 1:1 relationship between adding a note and deleting a contact under certain conditions:

Code: [Select]
# mysqlbinlog /var/log/mysql/mysql-bin.000063  --database=db | grep DELETE | grep civicrm_contact | wc -l
86
# mysqlbinlog /var/log/mysql/mysql-bin.000063  --database=db | grep UPDATE | grep civicrm_note | wc -l
86

And here's more output around the entries showing timestamps:

Firstly looking for UPDATE on civicrm_note specific to user 327 (the problem one):
Code: [Select]
# mysqlbinlog /var/log/mysql/mysql-bin.000063  --database=db | grep 327 -B 5 -A 5 | grep UPDATE -B 5 -A 5 | grep civicrm_note -B 1 | tail -n 20
SET TIMESTAMP=1213111677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1532/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1579/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 791/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1181/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 747/*!*/;
--
SET TIMESTAMP=1213111705/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1581/*!*/;
--
SET TIMESTAMP=1213111745/*!*/;
UPDATE civicrm_note SET contact_id = 327 WHERE contact_id = 1207/*!*/;
Secondly looking for DELETE on civicrm_contact
Code: [Select]
# mysqlbinlog /var/log/mysql/mysql-bin.000063  --database=db | grep DELETE -B 5 -A 5 | grep civicrm_contact -B 1 | tail -n 20
SET TIMESTAMP=1213111677/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 791 )/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 1181 )/*!*/;
--
SET TIMESTAMP=1213111677/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 747 )/*!*/;
--
SET TIMESTAMP=1213111705/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 1581 )/*!*/;
--
SET TIMESTAMP=1213111745/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 1207 )/*!*/;
--
SET TIMESTAMP=1213366404/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 1586 )/*!*/;
--
SET TIMESTAMP=1213377680/*!*/;
DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 1594 )/*!*/;

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: 2.0 installation appears to be losing contacts
June 16, 2008, 01:12:40 pm

This basically means contact id 327 initiated the deletion. Considering how close the timestamps are, most likely he/she did a batch deletion from the search page. We dont delete the notes which the contact created, we reassign the creator id to the current contact id (in this case 327)

This should probably give you enough clues on how the deletion came about, and the user you need to talk to :)

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

tvaughan

  • Guest
Re: 2.0 installation appears to be losing contacts
June 17, 2008, 11:33:09 am
Got it, thanks a lot - that put my mind at rest.  Now all  I have to do is convince our client that there's no way contacts can be deleted spontaneously!
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • 2.0 installation appears to be losing contacts

This forum was archived on 2017-11-26.