CiviCRM Community Forums (archive)

I am working on upgrading a site from Drupal 5 and CiviCRM 1.9 to Drupal 7 and the latest CiviCRM. The upgrade to 2.0 works, but to get to 2.1, I am getting the message:

Database consistency check failed. FK constraint names not in the required format.
Please rebuild your 2.0 database to ensure schema integrity.
I tried the suggestion from the post here from emilyf but I still get the same error.

I realize that this version is a few years old and this database has indeed been upgraded a few times, as the original version was 1.5 I think. It was built in 2006 anyhow. It is still in use today, and now (finally) they want to upgrade.

So then I tried, just for fun, a fresh install of 2.0 and then (without even using it) I tried to upgrade it to 2.1 and got the same error. Hmmmm.

I was trying this with "2.0.6 Drupal PHP5" and "2.1.6 Drupal PHP5" so I switched to 2.1.4. Same result. So I switched to 2.0.4 and reinstalled fresh and then ran 2.1.4 upgrader and got the same result. Hmmmm.

Then I tried to add a contact to my 2.0.4 site and I got

[debug_info] => INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 , 'civicrm_contact,1' ,  101 ,  20121015093850 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`beitnatan_TMP`.`civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 , 'civicrm_contact,1' ,  101 ,  20121015093850 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`beitnatan_TMP`.`civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  1 , 'civicrm_contact,1' ,  101 ,  20121015093850 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`beitnatan_TMP`.`civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
Hmmm. So then I emptied the DB and loaded just

/sites/all/modules/civicrm/sql/civicrm.mysql
from 2.0.4 and ran the upgrader and I got

Database check failed - the current database is not v2.0.
So then I emptied the DB and loaded both

/sites/all/modules/civicrm/sql/civicrm.mysql
 /sites/all/modules/civicrm/sql/civicrm_data.mysql
from 2.0.4 and ran the upgrader and I got

Database consistency check failed. FK constraint names not in the required format.
Please rebuild your 2.0 database to ensure schema integrity.
I am clearly missing something here. Can anyone tell me what?

Hi Kurund. I tried what you wrote yesterday (as that is what emilyf also wrote), but I gave it a shot again today. However I still get "Database consistency check failed" every time.

Quite perplexing...

So I wonder if there is an issue with my PHP 5.2 / MySQL 5.5 environment because the code in question was written for older versions of both. So I tried the test I ran yesterday and I installed a fresh version of 2.0.4. Install runs OK and returns me to the home page. However as you noted:

Looks like your civicrm contact was not created for logged in user. Did you try logout / login to drupal ?

no Contact was made. I also noticed that yesterday and I even tried to manually add a record to the civicrm_contact table but it didn't help. Anyhow, now with this fresh 2.0.4 install I go to CiviCRM and run a search and no contacts exist. The civicrm_contact table is indeed empty. So I tried your suggestion logout / login to Drupal and then the contact was made, and he appears in a search.

I can also view and edit him normally with no problems.

So made sure my D6 CiviCRM 2.1.4 is pointing to TMP and then I visited civicrm/upgrade?reset=1 and I still get the dreaded "Database consistency check failed" error.

Something is not working as expected here.

So I stepped through the code and I found that it's choking on

  KEY `FK_civicrm_contribution_recur_domain_id` (`domain_id`),
because it expects it to have the word CONSTRAINT instead of the word KEY.

So I disabled all checks in function checkFKConstraintInFormat to see what would happen and then the upgrader chokes on

ALTER TABLE `civicrm_contribution_recur`
    DROP FOREIGN KEY `FK_civicrm_contribution_recur_domain_id`,
    DROP INDEX UI_contrib_trxn_id_domain_id,
    DROP INDEX UI_contrib_invoice_id_domain_id
because it must be DROP INDEX as opposed to DROP FOREIGN KEY. I now noticed that I actually changed this same code in the upgrader code for 1.9 -> 2.0 to get that upgrade to work.

So I changed that in the code for 2.1.4 also (in /CRM/Upgrade/TwoOne/sql/domain_ids.mysql) and now the upgrader works for my real database.

Not 100% sure why this issue crept in, but anyhow for now it seems to be working. I will continue upgrading and report back if I run into any other snags.

Thank you.