CiviCRM Community Forums (archive)

In an attempt to move from 1.9 to 2.0.2, I followed the instructions here:
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0

Initially, I skipped step 4, because this database is not upgraded from a prior version.  However, when I hit step 7, I got the lovely yellow error:

Sorry. A non-recoverable error has occurred.

Database consistency check failed for step 1. FK constraint names not in the required format. Please rebuild your 1.9 database to ensure schema integrity.

Return to home page.

OK, so I stepped back to step 4 after all.  I created a new database, imported the civicrm_41.mysql file, then exported my 1.9 data and tried to import it into the clean 1.9 structure.  No luck.

The only tables that seem to import are:  civicrm_activity_history and empty tables.  I'm not sure exactly how one goes about isolating such issues, because once a bad request has been hit the whole thing just stops.  So I tried moving the offending table imports to the bottom of the query, first civicrm_address, then civicrm_contact, then civicrm_contribution, and so forth as each table (except activity_history) caused FK failures.  However, I stopped here:

INSERT INTO `civicrm_contribution_type`
VALUES ( 1, 1, 'Donation', NULL , NULL , 1, 0, 1 ) ;

MySQL said: Documentation
#1216 - Cannot add or update a child row: a foreign key constraint fails

That table has, from what I can see, exactly one FK and the entry above complies (domain = 1).  So I am at a loss as to how to even attack these darn foreign keys.

Foreign key errors are what caused me to abandon my 1.6 completely and rebuild in 1.9.  We have too much data to rebuild from scratch again.  This is not an prior-version-upgraded DB so I'm not sure how the database scheme integrity could have failed from 1.9 to, ah, 1.9.

Thoughts?  Condolences?  Something?  Blarg.
Brian

Right-oh,

Brian

what version of mysql are you using? Might be an issue with your mysql version

lobo

This time, received an error at last step:
nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_cms/#sql-21eb_4d`,
CONSTRAINT `FK_civicrm_event_page_event_id` FOREIGN KEY (`event_id`) REFERENCES `civicrm_event` (`id`) ON DELETE CASCADE

Which basically says that there are orphaned entries in civicrm_event_page pointing to non-existing event record.

After removing the orphaned data with the following query, the db got upgraded.
DELETE cep.*  FROM civicrm_event_page cep LEFT JOIN civicrm_event ce ON cep.event_id = ce.id WHERE ce.id IS NULL 

The query has has been made part of cleanup process (rev:13895).

(upgraded db mailed.)

Thanks

v2 is compatible and tested with the latest version of mysql5 (5.0.4x/5.0.51). We also do state that mysql5 is the supported version. That might be some of your issues (u never know with different versions of mysql)

secondly, i'm pretty sure deepak was using 2.0.2, not sure what version you are using

thirdly, deepak did find a bug / data inconsistency which he fixed for 2.0.3 which might have tripped you

lobo

thirdly, deepak did find a bug / data inconsistency which he fixed for 2.0.3 which might have tripped you

Hello Lobo, I think we hit the same bug while upgrading. Do you have any idea when 2.0.3 will be released to fix this?
Thanks.

Luc

Our error:
(`devcivicrm/civicrm_option_value`, CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`)), 1452


Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`) VALUES
(@og_id_cs, 'Ongoing', '1', 'Ongoing', NULL, 0, 1, 1, NULL, 0, 1, 1),
(@og_id_cs, 'Resolved', '2', 'Resolved', NULL, 0, NULL, 2, NULL, 0, 1, 1),
(@og_id_ct, 'Civil & Political', '1', 'Civil & Political', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ct, 'Economic, Social & Cultural', '2', 'Economic, Social & Cultural', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_ct, 'Gender Issues', '3', 'Gender Issues', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Sample', '1', 'CRM/Contact/Form/Search/Custom/Sample.php', NULL, 0, NULL, 1, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', '2', 'CRM/Contact/Form/Search/Custom/ContributionAggregate.php', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Basic', '3', 'CRM/Contact/Form/Search/Custom/Basic.php', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Group', '4', 'CRM/Contact/Form/Search/Custom/Group.php', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_PostalMailing', '5', 'CRM/Contact/Form/Search/Custom/PostalMailing.php', NULL, 0, NULL, 5, NULL, 0, 0, 1),
(@og_id_pl, 'Name Only', '1', 'Name Only', NULL, 0, 0, 1, NULL, 0, 1, 1),
(@og_id_pl, 'Name and Email', '2', 'Name and Email', NULL, 0, 0, 2, NULL, 0, 1, 1),
(@og_id_ps, 'Pending', (SELECT @max_val_aso := @max_val_ps+1), 'Pending', NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_ps+1), NULL, 0, 1, 1),
(@og_id_cvo, 'Cases', (SELECT @max_val_aso := @max_val_cvo+1), NULL, NULL, 0, NULL, (SELECT @max_wt_cvo := @max_wt_cvo+1), NULL, 0, 0, 1),
(@og_id_aso, 'Cases', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Grants', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Activities', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_gs, 'Pending', 1, 'Pending', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gs, 'Granted', 2, 'Granted', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gs, 'Rejected', 3, 'Rejected', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Emergency', 1, 'Emergency', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gt, 'Family Support', 2, 'Family Support', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gt, 'General Protection', 3, 'General Protection', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Impunity', 4, 'Impunity', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_ht, 'In Honor of', 1, 'In Honor of', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ht, 'In Memory of', 2, 'In Memory of', NULL, 0, NULL, 2, NULL, 0, 0, 1) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`devcivicrm/civicrm_option_value`, CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`))]
    [type] => DB_Error
    [user_info] => INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`) VALUES
(@og_id_cs, 'Ongoing', '1', 'Ongoing', NULL, 0, 1, 1, NULL, 0, 1, 1),
(@og_id_cs, 'Resolved', '2', 'Resolved', NULL, 0, NULL, 2, NULL, 0, 1, 1),
(@og_id_ct, 'Civil & Political', '1', 'Civil & Political', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ct, 'Economic, Social & Cultural', '2', 'Economic, Social & Cultural', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_ct, 'Gender Issues', '3', 'Gender Issues', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Sample', '1', 'CRM/Contact/Form/Search/Custom/Sample.php', NULL, 0, NULL, 1, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', '2', 'CRM/Contact/Form/Search/Custom/ContributionAggregate.php', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Basic', '3', 'CRM/Contact/Form/Search/Custom/Basic.php', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Group', '4', 'CRM/Contact/Form/Search/Custom/Group.php', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_PostalMailing', '5', 'CRM/Contact/Form/Search/Custom/PostalMailing.php', NULL, 0, NULL, 5, NULL, 0, 0, 1),
(@og_id_pl, 'Name Only', '1', 'Name Only', NULL, 0, 0, 1, NULL, 0, 1, 1),
(@og_id_pl, 'Name and Email', '2', 'Name and Email', NULL, 0, 0, 2, NULL, 0, 1, 1),
(@og_id_ps, 'Pending', (SELECT @max_val_aso := @max_val_ps+1), 'Pending', NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_ps+1), NULL, 0, 1, 1),
(@og_id_cvo, 'Cases', (SELECT @max_val_aso := @max_val_cvo+1), NULL, NULL, 0, NULL, (SELECT @max_wt_cvo := @max_wt_cvo+1), NULL, 0, 0, 1),
(@og_id_aso, 'Cases', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Grants', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Activities', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_gs, 'Pending', 1, 'Pending', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gs, 'Granted', 2, 'Granted', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gs, 'Rejected', 3, 'Rejected', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Emergency', 1, 'Emergency', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gt, 'Family Support', 2, 'Family Support', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gt, 'General Protection', 3, 'General Protection', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Impunity', 4, 'Impunity', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_ht, 'In Honor of', 1, 'In Honor of', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ht, 'In Memory of', 2, 'In Memory of', NULL, 0, NULL, 2, NULL, 0, 0, 1) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`devcivicrm/civicrm_option_value`, CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`))]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`) VALUES
(@og_id_cs, 'Ongoing', '1', 'Ongoing', NULL, 0, 1, 1, NULL, 0, 1, 1),
(@og_id_cs, 'Resolved', '2', 'Resolved', NULL, 0, NULL, 2, NULL, 0, 1, 1),
(@og_id_ct, 'Civil & Political', '1', 'Civil & Political', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ct, 'Economic, Social & Cultural', '2', 'Economic, Social & Cultural', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_ct, 'Gender Issues', '3', 'Gender Issues', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Sample', '1', 'CRM/Contact/Form/Search/Custom/Sample.php', NULL, 0, NULL, 1, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', '2', 'CRM/Contact/Form/Search/Custom/ContributionAggregate.php', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Basic', '3', 'CRM/Contact/Form/Search/Custom/Basic.php', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_Group', '4', 'CRM/Contact/Form/Search/Custom/Group.php', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_custom, 'CRM_Contact_Form_Search_Custom_PostalMailing', '5', 'CRM/Contact/Form/Search/Custom/PostalMailing.php', NULL, 0, NULL, 5, NULL, 0, 0, 1),
(@og_id_pl, 'Name Only', '1', 'Name Only', NULL, 0, 0, 1, NULL, 0, 1, 1),
(@og_id_pl, 'Name and Email', '2', 'Name and Email', NULL, 0, 0, 2, NULL, 0, 1, 1),
(@og_id_ps, 'Pending', (SELECT @max_val_aso := @max_val_ps+1), 'Pending', NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_ps+1), NULL, 0, 1, 1),
(@og_id_cvo, 'Cases', (SELECT @max_val_aso := @max_val_cvo+1), NULL, NULL, 0, NULL, (SELECT @max_wt_cvo := @max_wt_cvo+1), NULL, 0, 0, 1),
(@og_id_aso, 'Cases', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Grants', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_aso, 'Activities', (SELECT @max_val_aso := @max_val_aso+1), NULL, NULL, 0, NULL, (SELECT @max_wt_aso := @max_wt_aso+1), NULL, 0, 0, 1),
(@og_id_gs, 'Pending', 1, 'Pending', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gs, 'Granted', 2, 'Granted', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gs, 'Rejected', 3, 'Rejected', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Emergency', 1, 'Emergency', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_gt, 'Family Support', 2, 'Family Support', NULL, 0, NULL, 2, NULL, 0, 0, 1),
(@og_id_gt, 'General Protection', 3, 'General Protection', NULL, 0, NULL, 3, NULL, 0, 0, 1),
(@og_id_gt, 'Impunity', 4, 'Impunity', NULL, 0, NULL, 4, NULL, 0, 0, 1),
(@og_id_ht, 'In Honor of', 1, 'In Honor of', NULL, 0, 1, 1, NULL, 0, 0, 1),
(@og_id_ht, 'In Memory of', 2, 'In Memory of', NULL, 0, NULL, 2, NULL, 0, 0, 1) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`devcivicrm/civicrm_option_value`, CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`))]"]
)
[/code]