Import from one db to another fails

Question

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

Answer 1

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).

Answer 2

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