0

When we export our db and try restoring to another server we get the foreign key issue below so cant progress. Is it safe to Disable foreign key checks when importing into the new server?

(we tried to fix the indexes through the api even)

ERROR:

SQL query:

ALTER TABLE `civicrm_activity`
 ADD CONSTRAINT `FK_civicrm_activity_campaign_id` FOREIGN KEY 
 (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
 ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
 ADD CONSTRAINT `FK_civicrm_activity_parent_id` FOREIGN KEY (`parent_id`) 
 REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
 ADD CONSTRAINT `FK_civicrm_activity_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
 ADD CONSTRAINT `FK_civicrm_activity_relationship_id` FOREIGN KEY 
 (`relationship_id`) REFERENCES `civicrm_relationship` (`id`) ON DELETE SET NULL

MySQL said: Documentation

1452 - Cannot add or update a child row: a foreign key constraint fails (chercivi_life1.#sql-622_4c0a, CONSTRAINT FK_civicrm_activity_campaign_id FOREIGN KEY (campaign_id) REFERENCES civicrm_campaign (id) ON DELETE SET NULL)

  • Hi @JustinL, it would help, if you explain, how you do the export and the restore. In general, you can disable foreign keys checks, but if it is not possible to enable them again, you have trouble. – Kainuk - CiviCooP Jun 19 '18 at 8:21
  • I'm using phpadmy admin at both ends to export from server 1 to server 2. So once the data is imported with the foreign keys turned off is there a way to turn them back on? Or a way to troubleshoot? – JustinL Jun 19 '18 at 23:11
3

I think there might me some orphan data in your civicrm_activity table. Can you run the below query to check if you get any result?

SELECT * FROM `civicrm_activity` ca 
LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;

If you get result then probably you will need to delete this activity or make campaign_id to NULL for all this records.

Note: Foreign key is important in CiviCRM, Many foreign keys are set to ON CASCADE DELETE or SET NULL. So its possible you might end up more orphan data in your database if you disable them which may have problem while upgrading CiviCRM in future.

You can use below query to either update the civicrm_activity.campaign_id or delete campaign activities

UPDATE `civicrm_activity` ca 
LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
SET ca.campaign_id = NULL
WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;

OR

DELETE ca.* FROM `civicrm_activity` ca 
LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;

Note: Backup your database before you execute above sql.

HTH

Pradeep

  • That brought up 270 results. Do you know how to easily clear them out? This isn't our main db so the data isn't overly crucial if we lose some here. Any idea what might have caused it? Contacts deleted but still in the trash can maybe? We have about 25,000 contacts in the db. – JustinL Jun 19 '18 at 23:15
  • Can you run an query to set campaign_id to null? This might be because when you deleted campaign their data remained in db... – Pradeep Nayak Jun 19 '18 at 23:17
  • Do you have a suggested query to do that? :-) There is nothing in civicrm_campaign. Might I have imported contacts from another db who were linked to a campaign in that db that didn't have the corresponding campaign in this db? Is that possible? I'm thinking that might be the problem. – JustinL Jun 20 '18 at 0:41
  • Might be possible. I have updated my answer to include query to update or delete activity. Please let me know how it goes. – Pradeep Nayak Jun 20 '18 at 12:45
  • After running that UPDATE command and then exporting the db, the import worked. Thanks Pradeep. – JustinL Aug 17 '18 at 3:01
0

I agree. It's safe to disable them temporarily but if you get errors when trying to enable them there is data missing issue. You might want to run data check on your live data to be sure it's not getting DB error. And how are you exporting? You should be using mysqldump and taking out the definer clauses.

  • How do I run a data check? I did the export through phpmyadmin but get the error on mysql import on ssh. – JustinL Aug 17 '18 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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