Author Topic: Can't delete contact (Read 1970 times)

Neil Adair · June 13, 2007, 08:34:49 am

I have 3 contacts which I can't delete because of a foreign key constraint. There is no data associated with them, they were duplicates.

Trying to delete gives the error below, the same for all 3 with the civicrm_contact.id changing.

Is there a simple way to clear these records via the database so they can be deleted?

Code: [Select]

     Sorry. A non-recoverable error has occurred.

    Database Error Code: Cannot delete or update a parent row: a foreign key constraint fails (`main_crm_db/civicrm_membership_log`, CONSTRAINT `civicrm_membership_log_ibfk_3` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`)), 1451

    Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 29140 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`main_crm_db/civicrm_membership_log`, CONSTRAINT `civicrm_membership_log_ibfk_3` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]
    [type] => DB_Error
    [user_info] => DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 29140 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`main_crm_db/civicrm_membership_log`, CONSTRAINT `civicrm_membership_log_ibfk_3` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 29140 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`main_crm_db/civicrm_membership_log`, CONSTRAINT `civicrm_membership_log_ibfk_3` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]"]
)

[/code]

Dave Greenberg · June 13, 2007, 10:47:26 am

Neil - I don't think there's any super easy way to do this. You might try the delete query from mysql console or phpMyAdmin and see if you get more info on the which table(s) have the foreign key entries. Else, I guess checking the ERD in the developer doc online and following the FK relationships to civicrm_contact.

Let us know what you find - as its sounds like we may be missing a child table delete action at least in your case. (Of course include the version / rev and relevant upgrade history for the DB :-) ).

Neil Adair · June 13, 2007, 04:21:41 pm

I'm on 1.6-8703 trying to clean up to upgrade to 1.7, the db hasn't been upgraded, built on 1.6.
I saw your reply to Bob Schmitt

* Trying to delete a contact associated w/ a user who has been updating other contacts (and hence they are referenced in civicrm_log).

This is likely the case with all of these 3 contacts. Any tips on clearing these? I can run queries directly on the db (first on a test of course).

Thanks

Donald Lobo · June 13, 2007, 05:50:13 pm

You should delete all the contacts in civicrm_log whose modified_id matches the contacts u want to delete

delete from civicrm_log where modified_id IN ( XX,YY,ZZ );

or u could also set it to null

lobo

Neil Adair · June 14, 2007, 01:00:38 pm

That didn't work but led me to something that did.

instead of
DELETE FROM civicrm_log WHERE modified_id IN ( 1,29140,33693 );
I ran
DELETE FROM civicrm_membership_log WHERE modified_id IN ( 1,29140,33693 );

The contacts could then be deleted normally. Is that query likely to cause any issues?

Thanks

Dave Greenberg · June 14, 2007, 01:57:12 pm

No it shouldn't. However, check if you have records from this query:

SELECT contact_id FROM civicrm_membership WHERE contact_id IN ( 1,29140,33693 );

... I want to make sure that associated membership records for these contacts have been removed.

BTW - did you happen to have CiviMember enabled at some point, and then disabled it prior to trying to delete this contacts? If so, this is a known bug that we've fixed in 1.8 (the delete logic didn't include civicrm_membership_log if CiviMember component wasn't enabled - hence causing problems for records which had membership_log records from a time when it WAS enabled).

Neil Adair · June 14, 2007, 07:12:40 pm

That gives an empty set. These contacts never had any membership records but they likely edited contacts and members.

No CiviMember was never disabled.

Thanks

CiviCRM Community Forums (archive)

News:

Author Topic: Can't delete contact (Read 1970 times)

Neil Adair

Can't delete contact

Dave Greenberg

Re: Can't delete contact

Neil Adair

Re: Can't delete contact

Donald Lobo

Re: Can't delete contact

Neil Adair

Re: Can't delete contact

Dave Greenberg

Re: Can't delete contact

Neil Adair

Re: Can't delete contact