CiviCRM Community Forums (archive)

Trying to upgrade from 2.2.5 > 3.0

The first time I got FK issues, so I followed procedure here: http://forum.civicrm.org/index.php/topic,4259.msg21599.html#msg21599

When I tried to source my data dump into the 2.2 clean DB I was getting FK issues so I set foreign constraints to 0 when sourcing the data dump and then it all went in fine. I switched over to this new clean 2.2 DB.

Then I tried to upgrade this DB to 3.0 and I still get FK issues:


unrecoverable error
    Sorry. A non-recoverable error has occurred.

    DB Error: constraint violation

    Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/#sql-557d_195ffe4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452

    Return to home page.

Error Details:

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/#sql-557d_195ffe4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/#sql-557d_195ffe4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/#sql-557d_195ffe4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)


what should i do now?[/code]

so i get three results on this. i just want to make sure i understand what i'm doing if i am going to delete these because one of the id's that comes up is '1' and has a fk to contact id '1'....should i be concerned about this?

Help me Lobo (or anybody else!!!), you're my only hope!

I've tried everything going from our 2.2.5 to 3.0.2 system!!  Jump to the code below if you want.

We've got Drupal: 6.12
MySQL: 5.0.84
PHP: 5.2.10-pl1-gentoo
phpMyAdmin: 3.2.3

I've made copies of the whole Drupal/CiviCRM installation and using phpMyAdmin, copies of the database because I've tried to upgrade so many times I've made a process out of it.

I've assumed that the problem was getting the database in the pristine pre-upgrade state it needs to be in.  So I've used the "Ensuring" post to export data, create clean tables, add the additional table structures (do I need the "import_" tables?), and import the data into the new database.

Following that system I get the error below.  I know it looks familiar but I don't know what else to show.  So I'll try to describe more.

When I'm wiping out a database phpMyAdmin gives me FK errors and I have to iterate to get all the tables to disappear. 

When I'm copying tables, do I use "Add Constraints"?  I've discovered that sometimes the constraints are included in the mysqldump and sometimes they're not?!?  Are FKs just indexes with "FK_" in front of them??? 

I was about to say when I did the command manually below, it errors out, but no!  It just worked! 

Also, I'm assuming once I get an unrecoverable error, I have to start the whole process over again right?

I have created a version of the pre-upgrade database and then I copy it to try the upgrade using phpMyAdmin.  I do the copy with all these settings checked:
CREATE DATABASE before copying
Add DROP TABLE / DROP VIEW
Add AUTO_INCREMENT value
Add constraints
Switch to copied database

When I go to the home page before doing the upgrade (with the 3.0.2 php code in place) I get the following problem missing data field which I assume is correct:

WHERE (  civicrm_preferences.domain_id = 1 )  AND (  civicrm_preferences.is_domain = 1 )
Then I go to the db update page...  I click "Upgrade Now..." and...

Poof:   ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './yfe_civicrm_data/civicrm_group_organization' to './yfe_civicrm_data/#sql2-59aa-c2f61' (errno: 152)]
   
Same as below, yes?

I tried to do some manual stuff and found that this SQL code was totally necessary because even using phpMyAdmin and trying to copy a database, the FKs were getting in the way and that seems like a problem. 

SET foreign_key_checks = 0; 

That helped a LOT in messing with the data, but the manual attempt failed miserably.

So that's where I'm at.  I seem to be doing everything correctly, but the upgrade seems to be consistently choking on the below code. 

I just tried:
ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id`Again but this time it failed...

Also, I just created another clean copy of the pre-upgrade database.  I tried to use phpMyAdmin to copy it to a backup and I get this syntax error???

SQL query:

ALTER TABLE `yfe_civicrm_data_preupgrade`.`civicrm_acl_cache` ADD CONSTRAINT FOREIGN KEY ( `contact_id` ) REFERENCES `civicrm_contact` ( `id` ) ON DELETE CASCADE , ADD CONSTRAINT FOREIGN KEY ( `acl_id` ) REFERENCES `civicrm_acl` ( `id` ) ON DELETE CASCADE ; ALTER TABLE `yfe_civicrm_data_preupgrade`.`civicrm_activity` ADD CONSTRAINT FOREIGN KEY ( `source_contact_id` ) REFERENCES `civicrm_contact` ( `id` ) ON DELETE CASCADE , ADD CONSTRAINT FOREIGN KEY ( `phone_id` ) REFERENCES `civicrm_phone` ( `id` ) ON DELETE SET NULL , ADD CONSTRAINT FOREIGN KEY ( `parent_id` ) REFERENCES `civicrm_activity` ( `id` ) ON DELETE CASCADE , ADD CONSTRAINT FOREIGN KEY ( `relationship_id` ) REFERENCES `civicrm_relationship` ( `id` ) ON DELETE SET NULL , ADD CONSTRAINT FOREIGN KEY ( `original_id` ) REFERENCES `civicrm_activity` ( `id` ) ON DELETE CASCADE ; ALTER TABLE `yfe_civicrm_data_preupgrad[...]

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER  TABLE  `yfe_civicrm_data_preupgrade`.`civicrm_activity`  ADD  CONSTRAINT ' at line 4
So I hope this all points to something obvious that you can say AH HA, that at the same time doesn't make me look stupid!  Thanks for the assistance, it is desperately needed!

James


WHEN I RUN THE UPGRADE, HERE ARE TWO DIFFERENT ERRORS I HAVE HIT...

Upgrade CiviCRM to Version 3.0.2

Sorry. A non-recoverable error has occurred.
DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Database Error Code: Error on rename of './yfe_civicrm_302/civicrm_group_organization' to './yfe_civicrm_302/#sql2-59aa-c10c4' (errno: 152), 1025

Return to home page.

Error Details:

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

    [c0de] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './yfe_civicrm_302/civicrm_group_organization' to './yfe_civicrm_302/#sql2-59aa-c10c4' (errno: 152)]
    [type] => DB_Error
    [user_info] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './yfe_civicrm_302/civicrm_group_organization' to './yfe_civicrm_302/#sql2-59aa-c10c4' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="-- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './yfe_civicrm_302/civicrm_group_organization' to './yfe_civicrm_302/#sql2-59aa-c10c4' (errno: 152)]"]
)
Tried it again....  And now it's activity that is giving me the error:

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

    [coode] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_activity`
    ADD CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`yfe_civicrm_data/#sql-59aa_c3052`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_activity`
    ADD CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`yfe_civicrm_data/#sql-59aa_c3052`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_activity`
    ADD CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`yfe_civicrm_data/#sql-59aa_c3052`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]"]
)

I'm making progress on my end, but it's still not working.

So here's where I'm at:

- I've got a newly structured 2.2.5 DB
- I have removed null entires from the civicrm_uf_match table

I try the upgrade again, and now I get this fatal error:

     Sorry. A non-recoverable error has occurred.

    DB Error: constraint violation

    Return to home page.

Error Details:

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] =>
                UPDATE civicrm_activity_target cat, civicrm_activity ca
                    SET cat.activity_id = 412 
                WHERE ca.source_record_id IS NOT NULL   AND
                      ca.activity_type_id = 34          AND
                      ca.id <> 412              AND
                      ca.source_record_id = 1 AND
                      ca.id = cat.activity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/civicrm_activity_target`, CONSTRAINT `FK_civicrm_activity_target_target_contact_id` FOREIGN KEY (`target_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] =>
                UPDATE civicrm_activity_target cat, civicrm_activity ca
                    SET cat.activity_id = 412 
                WHERE ca.source_record_id IS NOT NULL   AND
                      ca.activity_type_id = 34          AND
                      ca.id <> 412              AND
                      ca.source_record_id = 1 AND
                      ca.id = cat.activity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/civicrm_activity_target`, CONSTRAINT `FK_civicrm_activity_target_target_contact_id` FOREIGN KEY (`target_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="
                UPDATE civicrm_activity_target cat, civicrm_activity ca
                    SET cat.activity_id = 412 
                WHERE ca.source_record_id IS NOT NULL   AND
                      ca.activity_type_id = 34          AND
                      ca.id <> 412              AND
                      ca.source_record_id = 1 AND
                      ca.id = cat.activity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/civicrm_activity_target`, CONSTRAINT `FK_civicrm_activity_target_target_contact_id` FOREIGN KEY (`target_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)

Suggestions?[/code]

Whoa, that's scary.  One record lacking data integrity in the data will cause the upgrade to fail? 

Uh oh...  Now I'm worried.  That could be my problem too. 

Seeing how a database without constraints can get pretty whacked up, moving to one with constraints could cause LOTS of problems.

It sounds like it would be good to have an integrity check in the upgrade which makes sure that all necessary records are there and lets the user decide what to do with danglers, no?

Is there a way to check this across a database for a given set of new constraints?

>>>	Side programming style question: Implementing CiviCRM, does this mean that now there is less code because CiviCRM is letting MySQL do the work, or that it just ensures that when programming CiviCRM, that all records are appropriately removed or built in the correct order?

James

OK I am still out of luck on making any progress here.

I talked with lobo on irc and I ran a query looking for bogus entries in the civicrm_activity_target table, but none showed up. I just tried upgrading again, got the same error and then ran this query on my partially upgraded db:

 UPDATE civicrm_activity_target cat,
civicrm_activity ca SET cat.activity_id =412 WHERE ca.source_record_id IS NOT NULL AND ca.activity_type_id =34 AND ca.id <>412 AND ca.source_record_id =1 AND ca.id = cat.activity_id

I don't get anything more helpful, just the same error I was seeing when trying to upgrade:

Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`cctvcivicrm3/civicrm_activity_target`, CONSTRAINT `FK_civicrm_activity_target_target_contact_id` FOREIGN KEY (`target_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)

I'm at a loss for what to do here to get this database upgraded successfully. Please help!

can u email me your db

i'll take a look at it sometime in the next few days

thanx

lobo

i would use the first document:

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

after u've ensure schema integrity, check and see if the FK's exist? if they still dont, there is something messed up with the InnoDB part of your mysql install. Constraints appear on InnoDB databases (and not MyISAM)

lobo