CiviCRM Upgrade - Foreign keys / Constraint violation issue

Error message

The spam filter installed on this site is currently unavailable. Per site policy, we are unable to accept new submissions until that problem is resolved. Please try resubmitting the form in a couple of minutes.

Well, it is just about time for CiviCon. Even though I am not going, I want to relay a good CiviCRM war story.

Recently, we went through the process of upgrading a CiviCRM site (Drupal 6.x - CiviCRM 3.4). This can turn into a painful process if you aren't careful, and sometimes even when you are. Among the possible issues you may run into, there are some nasty errors that break the upgrade process when attempting the database upgrade script. I'm going to tell you how to get past them.

First attempt

The whole upgrade process is complicated and requires a lot of attention. First of all backup your databases. Do not skip over this step. If there is a bug such as the one that I have encountered, there will be no easy way to restore the database structure and/or data without a backup to revert to.

It's essential to follow the upgrade guide. However, on step 8, you might stumble on the foreign keys bug. First, I thought it was somehow a corruption in the database structure, so I tried to repair it. Still, the error caused the upgrade process to quit. After a lot of trial and error, going version by version (we were upgrading from CiviCRM 2.2), I realized it was actually the data that was corrupt.

If at first you don't succeed, try and try again!

Well, if you run into a foreign key error (actually it will be issued as a constraint violation), it is likely that the data in the tables is corrupt. I don't know exactly how this could happen, but the fix depends on the situation. You will have to check the tables and look for the foreign key from the error message. This should be pointing to another table (hence, a foreign key), and some of it's values may be pointing to missing entries on the target table. Once you figure out the tables and fields properly, it's time to restore your backup and follow these steps:

  1. Ensure the integrity of the database
  2. Run a manual fix on mysql to change all foreign keys that are pointing to missing entries. As an example, here is the query I had to run:
    UPDATE `civicrm_pcp_block` SET `supporter_profile_id` = NULL WHERE `supporter_profile_id` NOT IN (SELECT id FROM civicrm_uf_group);
    Adapt this query to your own needs. The 'supporter_profile_id' is the foreign key pointing to the 'id' field of table 'civicrm_uf_group'. You should test it on the broken database before.
  3. Perform system checks. I had some settings related file directories reverted. If anything looks weird, time to debug and maybe revert with the DB backup.
  4. Replace the codebase with the target version
  5. Run civicrm/upgrade

After that, you should be good to go with the upgrade. Carefully check your data, read the documentation thoroughly and enjoy the fruits of an upgraded CiviCRM installation.