Upgrade from CiviCRM 4.0.8 to 4.1 fails trying to add South Sudan

[majortom]

When doing an upgrade from CiviCRM 4.0.8 on Drupal 7.12 using MySQL 5.5, to CiviCRM 4.1.0 on Drupal 7.12 using MySQL 5.5 I get:

Code: [Select]

Sorry. A non-recoverable error has occurred.
DB Error: constraint violation
Error Details
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT IGNORE INTO civicrm_country (id, name,iso_code,region_id,is_province_abbreviated) VALUES("1247", "South Sudan", "SS", "5", "0") [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`Example`.`civicrm_country`, CONSTRAINT `FK_civicrm_country_region_id` FOREIGN KEY (`region_id`) REFERENCES `civicrm_worldregion` (`id`))]
    [type] => DB_Error
    [user_info] => INSERT IGNORE INTO civicrm_country (id, name,iso_code,region_id,is_province_abbreviated) VALUES("1247", "South Sudan", "SS", "5", "0") [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`Example`.`civicrm_country`, CONSTRAINT `FK_civicrm_country_region_id` FOREIGN KEY (`region_id`) REFERENCES `civicrm_worldregion` (`id`))]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO civicrm_country (id, name,iso_code,region_id,is_province_abbreviated) VALUES("1247", "South Sudan", "SS", "5", "0") [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`Example`.`civicrm_country`, CONSTRAINT `FK_civicrm_country_region_id` FOREIGN KEY (`region_id`) REFERENCES `civicrm_worldregion` (`id`))]"]
)
Return to home page.
Any suggestions?

[robinhood]

Me too ... exact same situation.

Edit: I salvaged the situation with this -
First, I restored from backup.  Then, add South Sudan to the civicrm_country table:

Code: [Select]

SET FOREIGN_KEY_CHECKS=0;
INSERT INTO civicrm_country (id, name,iso_code,region_id,is_province_abbreviated) VALUES("1247", "South Sudan", "SS", "5", "0");
SET FOREIGN_KEY_CHECKS=1;Then I re-ran the upgrade and it works.

[majortom]

Thanks to Fabinaux (on #CiviCRM), I found this post: http://www.trellon.com/content/blog/civicrm-upgrade-foreign-keys-contraint-violation-issue]CiviCRM Upgrade Foreign Key Constraint Violation Issue. While it talks about an older version, I realized that my data might be at fault. Looking at the failure, I saw that my problem was in the civicrm-worldregion table. Looking at that table, I discovered it was empty. I found the data in another, newer CiviCRM installation I have and imported it to the broken one. That fixed the problem for me and I was able to upgrade.

I am not sure if that table's data got deleted at some point or if it was never populated properly. This installation started life as a 1.3 or 1.4 site, so it has gone through many upgrades.