5

So we lost 2/3 of our email and addresses on 6/15 around 1pm. Everything else seemed fine. I was able to get the civi log that pointed to a deletion command (DELETE FROM civicrm_location_type) around the same time. We have no idea how this happened. Thankfully we were able to make a new database from a backup file and not much damage was done.

Does anyone know what could have caused this and how we could prevent it in the future? Thanks!

Here's the log:

Jun 15 13:11:01  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`gbapgh_civicrm`.`civicrm_mapping_field`, CONSTRAINT `FK_civicrm_mapping_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`))]
    [type] => DB_Error
    [user_info] => DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`gbapgh_civicrm`.`civicrm_mapping_field`, CONSTRAINT `FK_civicrm_mapping_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`))]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`gbapgh_civicrm`.`civicrm_mapping_field`, CONSTRAINT `FK_civicrm_mapping_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`))]"]
)


Jun 15 13:11:01  [info] $backTrace = #0 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(239): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB.php(975): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [...")
#4 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [...")
#5 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -3, NULL, NULL, "DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 )  [...", "DB_Error", TRUE)
#6 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(899): DB_common->raiseError(-3, NULL, NULL, NULL, "1451 ** Cannot delete or update a parent row: a foreign key constraint fails ...")
#7 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 ) ")
#9 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2442): DB_common->query("DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 ) ")
#10 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1456): DB_DataObject->_query("DELETE FROM civicrm_location_type  WHERE (  civicrm_location_type.id = 1 ) ")
#11 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/BAO/LocationType.php(179): DB_DataObject->delete()
#12 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/LocationType.php(96): CRM_Core_BAO_LocationType::del("1")
#13 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(328): CRM_Admin_Form_LocationType->postProcess()
#14 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/StateMachine.php(162): CRM_Core_Form->mainProcess()
#15 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(60): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_LocationType), "next", "Next")
#16 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_LocationType), "next")
#17 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_LocationType), "next")
#18 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(356): HTML_QuickForm_Page->handle("next")
#19 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(395): CRM_Core_Controller->run()
#20 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(178): CRM_Core_Page_Basic->edit(8, "1")
#21 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(331): CRM_Core_Page_Basic->run((Array:3), NULL)
#22 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(75): CRM_Core_Invoke::runItem((Array:13))
#23 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#24 /usr/home/gbapgh/public_html/go-gba/wp-content/plugins/civicrm/civicrm.php(506): CRM_Core_Invoke::invoke((Array:3))
#25 [internal function](): CiviCRM_For_WordPress->invoke("")
#26 /usr/home/gbapgh/public_html/go-gba/wp-includes/plugin.php(505): call_user_func_array((Array:2), (Array:1))
#27 /usr/home/gbapgh/public_html/go-gba/wp-admin/admin.php(212): do_action("toplevel_page_CiviCRM")
#28 {main}


Jun 15 13:41:30  [info] r_authresponse X07

Jun 15 13:41:30  [info] r_code 03924D1557233524:YYYM:

Jun 15 13:41:30  [info] r_tdate 1434390089

Jun 15 13:41:30  [info] r_avs YYYM

Jun 15 13:41:30  [info] r_ordernum A7DEDC06-557F0E49-608-E0474

Jun 15 13:41:30  [info] r_error 

Jun 15 13:41:30  [info] csp FDMS

Jun 15 13:41:30  [info] r_message APPROVED

Jun 15 13:41:30  [info] r_ref 1557233524

Jun 15 13:41:30  [info] r_time Mon Jun 15 13:41:30 2015

Jun 15 13:44:08  [info] $Fatal Error Details = Array
| improve this question | |
  • In the future, please edit your post rather than creating an entirely new question. Previous question: Email and Addresses Missing – choster Jun 17 '15 at 16:45
  • We are also seeing email addresses disappearing. From the log it is my id which did a bulk delete but I don't remember doing it. Also my Home record is in place and never deleted or changed, – user469 Oct 8 '15 at 11:10
  • I had this same issue in March 2015 and i restored data from backup, see my post here forum.civicrm.org/index.php?topic=35945.msg152720#msg152720 – Kboy Oct 8 '15 at 11:30
5

This is a result of someone going in CiviCRM and deleting the Home location type, either through Administer > Customize Data and Screens > Dropdown Options > Location Types or clicking the wrench next to the dropdown of location types on some other form.

It's obviously not a good idea to do that if you have a lot of email addresses, phone numbers, and postal addresses with that location type; doing so will actually give you the message:

WARNING: Deleting this option will result in the loss of all location type records which use the option. This may mean the loss of a substantial amount of data, and the action cannot be undone. Do you want to continue?

I hope/assume that nobody did that on purpose, but you might double-check with everyone with that permission.

If you can't track down who did it, you should assume someone has compromised your system and has admin permissions (or potentially full shell and/or MySQL access such that they could create a user). At the very least, each user should change passwords, and you should review all user accounts in WordPress. To be truly safe, you might reset/reinstall everything in your hosting environement, replace all your WordPress, CiviCRM, and other plugins' code with freshly-downloaded copies, manually review any other files one-by-one, and check your database for anything fishy.

| improve this answer | |
  • Thanks Andrew! We're tracking down anyone internally that might have done this and changing all admin passwords. I'll work with our developer on the next level of security should no one fess up:) Have a great day! – BraddC Jun 17 '15 at 17:33
  • 1
    If they have logging enabled, it would be easy to find who to blame^H^H^H^H^Hpolitely educate on the virtues of looking before you leap. SELECT * FROM log_civicrm_location_type WHERE log_action = 'Delete' – DaveFF Jun 18 '15 at 9:58
  • @DaveFF if you don't have logging enabled, how to you find out how the data has been deleted? – Kboy Oct 8 '15 at 17:11
  • @Kennedy don't know I'm afraid! There is also a table called civicrm_log but I've never had cause to look in there. Or the debug log files, but they don't cover every change in detail the way having full logging enabled does. – DaveFF Oct 8 '15 at 17:29
  • For an advanced forensic investigation, you could figure out the url they had to POST to to do this, and then look for the corresponding entry in your web server log file, and then try and figure out who it was from the IP. – Alan Dixon Oct 8 '15 at 18:22
2

As well as Andrew's answer, which involves directly deleting the Location Type, I believe there is a route that this can be done inadvertently when editing a single contact record in order to delete a single address record. This came up on this JIRA ticket.

In the scenario we investigated, it seems that the user was editing a single record, and in order to edit a single address entry they accidentally / inadvertently clicked on the 'spanner' icon to the right which opens up the Location Type page, and this gives access to the Delete button which it is concievable the person might click on thinking they are deleting the single Address, rather than the Location Type.

They do then get the big warning noted in Andrew's answer which we are suggesting could be improved in the JIRA ticket.

Note that the user requires 'administer CiviCRM' permission to get that link to edit location types.

It was possible to restore the data from the log_tables in this case.

| improve this answer | |
2

I've seen this happen three times now - someone intends to delete an email/address/phone number, ends up attempting to delete the entire location type. I decided this was too dangerous, so as of CiviCRM 4.6.12 and 4.7, you can no longer edit location type when inline editing phone/email/address/IM. See https://issues.civicrm.org/jira/browse/CRM-17273 for details.

| improve this answer | |
0

I'm curious about your claim that you lost all that data. Is it possible it's there but just not showing up where you expect? The location type is just a way of classifying the data that lives in it's own tables.

Unless there's another trigger that clears all the data, it's possible that the data is still there and could be recovered by adding a new location and then using sql to update the id of that new location so it's connected to all the existing actual data. Your error suggests that because of the foreign key dependency the type wasn't even deleted, but maybe just the label.

| improve this answer | |
  • I'm pretty sure the data is gone, both from the debug message, but also because when you try to delete a location type, this warning pops up: "WARNING: Deleting this option will result in the loss of all location type records which use the option. This may mean the loss of a substantial amount of data, and the action cannot be undone. Do you want to continue?" Of course - this message may have been added in response to this person's problem, which happened back in June. – Jon G - Megaphone Tech Oct 7 '15 at 16:39

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.