4

We have attempted to upgrade the CiviCRM instance multiple times, but all attempts for that failed with the following error:

ALTER TABLE civicrm_mailing_event_queue DROP FOREIGN KEY FK_civicrm_mailing_event_queue_email_id [nativecode=1091 ** Can't DROP 'FK_civicrm_mailing_event_queue_email_id'; check that column/key exists]

I have followed multiple suggestions on the Internet of people experiencing similar (if not the same) issues, but this did not help either. For example, I have tried the following recommendation:

Unable to upgrade to 4.7.6

but it did not resolve the issue. I have also followed the instructions here:

https://forum.civicrm.org/index.php?topic=29889.0

as well as the official documentation of CiviCRM here:

https://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

Here's my site profile link. https://mycivi.org/profile/snapshot/8yejn-Wwh0X-pHIht-GVWI3

Any suggestions on how to proceed?

  • Is this question incomplete? Anybody have any suggestions or insight on this issue? Am I supposed to provide any additional info? – V3rmie May 4 '16 at 19:06
5

UPDATE: CRM-18526 fixes this issue - it's just been checked in. To bypass this problem, upgrade to CiviCRM 4.7.8 or higher.

John is correct about this being related to CRM-18345. There are two problems with the upgrade code - and the SET FOREIGN_KEY_CHECKS=0 others posted only solves one of them.

I've posted my analysis here, and I expect that someone will fix this issue. However, if you're experiencing this now and want to upgrade, here's what to do. I'm assuming that folks reading this will feel comfortable editing a file on their server, but aren't generally comfortable playing around with MySQL constraints:

First, restore your database to a state before the backup!

When you upgrade, look at the error.

Problem 1 Does the SQL statement that failed have the words DROP FOREIGN KEY in it? If so, the problem is that you don't have the foreign keys. In this case, it's safe to delete the code that tries to remove the foreign key. Open up the file <civicrmroot>/CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl. Find the command ALTER TABLE civicrm_mailing_event_queue DROP FOREIGN KEY FK_civicrm_mailing_event_queue_email_id (or whichever command corresponds to the error you're getting) and delete it. Reload the page and the upgrade will continue. It's very likely that if you don't have one foreign key, you don't have all four - so expect to repeat this three times.

Problem 2 Does your SQL statement that failed have the words ADD CONSTRAINT in it? If so, you've already encountered the problem this code is trying to fix - but the code doesn't work if you've already encountered the issue. To resolve this, add the line SET FOREIGN KEY CHECKS=0 on the line above the command that's giving you an error, and SET FOREIGN KEY CHECKS=1 on the line directly below it.

Don't forget - these commands are very similar, so if you think that this solution hasn't solved your problem, make sure you're looking at an identical error and not a very similar one!

2

This issue is related to this improvement that was made in a recent version: https://issues.civicrm.org/jira/browse/CRM-18345

The update function is deleting foreign key constraints - however it sounds like they were never set up in your database? You can inspect your database and check the status of the foreign keys as a test.

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.