1

I exported all the civi tables from one database and saved as a sql file, including a droptable statement. I then tried to import the data into a fresh site with brand new civi installation. The import fails with a foreign key constraint failure. When that failed I tried to import a sql file with data only, no structure, no drop table. Also failed with

1st SQL query (with structure & data):

-- --------------------------------------------------------
--
-- Table structure for table `civicrm_action_mapping`
--
DROP TABLE IF EXISTS `civicrm_action_mapping` ;

MySQL said: Documentation
#1217 - Cannot delete or update a parent row: a foreign key constraint fails 

2nd SQL Query (data only)

-- Dumping data for table `civicrm_acl_entity_role`
--
INSERT INTO `civicrm_acl_entity_role` ( `id` , `acl_role_id` , `entity_table` , `entity_id` , `is_active` )
VALUES ( 1, 3, 'civicrm_group', 1, 1 ) , ( 2, 1, 'civicrm_group', 15, 1 ) , ( 3, 4, 'civicrm_group', 2, 1 ) , ( 4, 2, 'civicrm_group', 2, 1 ) ;

MySQL said: Documentation
#1062 - Duplicate entry '1' for key 'PRIMARY' 

Any help is appreciated. Kate

2 Answers 2

3

You can add this to the top of your .sql file, then try loading it again.

SET FOREIGN_KEY_CHECKS = 0;

This will permit MySQL to load the data while ignoring the foreign key checks.

Since CiviCRM uses foreign key checks, this is a good idea only if the checks throw errors when loading the DB but not when using CiviCRM afterwards.

Alternatively, to ensure foreign key integrity and handle existing tables not wanting to be deleted, prefix your .sql with

DROP DATABASE nameofdb;
CREATE DATABASE nameofdb;

(where nameofdb is your actual DB name of course).

2
  • Thanks. Since I had no errors and the existing db was fresh, I disabled the foreign key check and imported just fine. No problems seen since.
    – GrannyKat
    May 15, 2015 at 14:17
  • @GrannyKat - if this answer worked for you, please mark it as accepted! May 20, 2015 at 14:37
0
  1. You might want to ensure that the DB you are importing into is a brand new db (rather than a brand new civi install which will install data into the db)

  2. If you cannot do the above, there is a script in sql/civicrm_drop.mysql which you can run on mysql shell to drop all civicrm standard tables. This script will not drop any custom tables created

1
  • Thanks for the heads-up on the script -- could come in handy.
    – GrannyKat
    May 15, 2015 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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