CiviCRM Community Forums (archive)

Hi, I've tried this a few times now, brand new database, the structure file goes in just fine, but I get the following foreign key error when I try to import the sample data. The usual fix is to set the domain ID to 1. It already is here, so that isn't the problem.

Error

SQL query:

INSERT INTO `civicrm_acl` ( `id` , `domain_id` , `name` , `deny` , `entity_table` , `entity_id` , `operation` , `object_table` , `object_id` , `acl_table` , `acl_id` , `is_active` )
VALUES ( 1, 1, 'Edit All Contacts', 0, 'civicrm_acl_role', 1, 'Edit', 'civicrm_saved_search', 0, NULL , NULL , 1 ) ;

MySQL said: Documentation
#1216 - Cannot add or update a child row: a foreign key constraint fails
Any ideas?

Thanks,
Troy

Hi, the mysql version is 4.1.21 and I'm using a provider so I can't really do anything about the version. If the DB ignores the FK command, what do i do about getting the data in. I have also tried using the civicrm_data.mysql file instead, but then I get FK errors when I try to import the drupal users.

Thanks,
Troy

Code: [Select]

INSERT INTO `civicrm_acl` ( `id` , `domain_id` , `name` , `deny` , `entity_table` , `entity_id` , `operation` , `object_table` , `object_id` , `acl_table` , `acl_id` , `is_active` )
VALUES ( 1, 1, 'Edit All Contacts', 0, 'civicrm_acl_role', 1, 'Edit', 'civicrm_saved_search', 0, NULL , NULL , 1 ) ;

MySQL said: Documentation #1216 - Cannot add or update a child row: a foreign key constraint fails


The only foreign key in this table is the domain_id column (to civicrm_domain.id), so if it throws the above error then you most probably don’t have an entry in civicrm_domain with id of 1. Can you double-check whether you do have such an entry?

If the DB ignores the FK command, what do i do about getting the data in.

Uncomment the lines that change the FOREIGN_KEY_CHECKS, so they look like this:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
…
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;


I have also tried using the civicrm_data.mysql file instead, but then I get FK errors when I try to import the drupal users.

What errors? What were the exact steps you took?

When I do:
mysql -u civicrm_user -h civicrm_db_server -pcivicrm_password civicrm_db < civicrm_41.mysql
mysql -u civicrm_user -h civicrm_db_server -pcivicrm_password civicrm_db < civicrm_data.mysql

and then try to synchronize the drupal users I get:

Synchronize Users-to-Contacts

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    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] => INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 ,  102 ,  20070705221305 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 ,  102 ,  20070705221305 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` 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="INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 ,  102 ,  20070705221305 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]"]
)


Then I try to create an individual and get:

New Individual

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`)), 1452

    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] => INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  2 ,  102 ,  20070705221623 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  2 ,  102 ,  20070705221623 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` 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="INSERT INTO civicrm_log (entity_table , entity_id , modified_id , modified_date ) VALUES ('civicrm_contact' ,  2 ,  102 ,  20070705221623 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`))]"]
)

When I do:
mysql -u civicrm_user -h civicrm_db_server -pcivicrm_password civicrm_db < civicrm_41.mysql
mysql -u civicrm_user -h civicrm_db_server -pcivicrm_password civicrm_db < civicrm_generated.mysql

And try to synchronize the drupal users, everything works fine.

But when I try to delete all (dummy) users, it asks me if I really want to delete the 105 contacts (102 from generated + the ones I have setup in drupal). I say yes and get:

Find Contacts

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    Database Error Code: Cannot delete or update a parent row: a foreign key constraint fails (`civicrm/civicrm_participant`, CONSTRAINT `FK_civicrm_participant_contact_id` FOREIGN KEY (`contact_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 = 76 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`civicrm/civicrm_participant`, CONSTRAINT `FK_civicrm_participant_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`))]
    [type] => DB_Error
    [user_info] => DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 76 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`civicrm/civicrm_participant`, CONSTRAINT `FK_civicrm_participant_contact_id` FOREIGN KEY (`contact_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 = 76 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`civicrm/civicrm_participant`, CONSTRAINT `FK_civicrm_participant_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`))]"]
)


Your help is well appreciated.[/code][/code][/code]

Thanks for your suggestions, however, it didn't work.

Resetting the drupal session table didn't have any effect. And the second, both modules were actually enabled.

I gave 1.8 alpha a try and found the 'generated' working. Loading 'data' only didn't allow me to import the drupal users. With 'generated' I was able to delete the dummy users and import the drupalers.

I think I can go from here, thanks again.


Schnuckovic