Is there a best practice or recipe for migrating a two database CiviCRM installation into one database?

Question

I am replicating a Drupal 7 CiviCRM installation into a Pantheon CiviCRM Kickstart site. I have gone through the tedious effort of moving the Drupal content and settings from a default Drupal profile into the CiviCRM Kickstart profile and I have a happy, empty installation of CiviCRM living beside my Drupal site but now I need to move my CiviCRM data into the consolidated database.

I have tried unsuccessfully to export the database with SET FOREIGN_KEY_CHECKS=0; and without the cache and domain tables but on my import I am receiving a "Cannot add foreign key constraint" error.

Has anyone come up with a cookbook for making this migration?

Thanks.

Answer 1

I managed to resolve this dilemma.

The goal was to move a 2 database Drupal/CiviCRM website into the CiviCRM Starterkit platform which requires that Drupal and CiviCRM use a single database. I was not able to import my database dump into the existing Drupal website because although I had set foreign_key check to zero, I was receiving a "Cannot add foreign key constraint" error.

My solution was as follows:

1. Download a dump of the CiviCRM database with ignore foreign keys, drop tables, triggers etc.

2. The dump does not include tables for: civicrm_acl_cache, civicrm_acl_contact_cache, civicrm_cache, civicrm_domain, civicrm_group_contact_cache

3. Remove the drop tables statements before importing. Leave in: IF NOT EXISTS create table.

4. It was important to Truncate tables before each table's data insert statement.

5. When I ran the import shell command the import would stop because the Log Tables didn't exit. They are in the Dump but they were not being created so I ran all of the IF NOT EXISTS create table commands before running the import command.

Once I had accomplished the above 5 items, I was able to run the import and populate the tables. All is well.

I hope this helps anyone else who runs into this challenge.

Answer 2

'Cannot add foreign key constraint' means that the data referencing a column is missing. You can either make sure that you delete orphan data or set null before exporting them or you can SET FOREIGN_KEY_CHECKS=0; before importing and then SET FOREIGN_KEY_CHECKS=1; once imported.

Cheers

Pradeep