Author Topic: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what (Read 5885 times)

ijames · November 22, 2009, 07:04:04 pm

I'm starting to think that I've got bad data. Is that possible? When I do the upgrade, and the constraints are in place, I get:

Code: [Select]

Cannot add or update a child row: a foreign key constraint fails (`yfe_civicrm_data/#sql-59aa_df16e`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL), 1452

I'm trying to sort that out now.

James

emilyf · December 01, 2009, 05:56:12 pm

I sent my db over to lobo and had a brief irc chat with him, and he was able to determine i needed to clean up a few tables to ensure database integrity for the upgrade. these are the queries he had me run:

delete from civicrm_activity_assignment where id IN ( 299, 300 );
delete from civicrm_activity where id IN ( 409, 337, 450, 557, 692, 939 );
delete from civicrm_activity_target where id IN ( 349, 277, 455, 490 );

Then he wanted to also ensure the civicrm_uf_match records which dont match so I did the select query below and deleted anything that came up:

select * from civicrm_uf_match a LEFT JOIN civicrm_contact c ON a.contact_id = c.id WHERE c.id IS NULL;

And it all worked great!! Finally!

Thanks, Lobo!

ijames · December 01, 2009, 07:45:47 pm

Yep, it turned out to be the same thing for me. I created a clean 3.0.2 database and then started loading up the data.sql. It would bail on a particular insert, and then I would investigate that set of inserts and found some dangling records.

After I got through to the bottom, the upgrade made it through!

jakedeo · February 10, 2010, 10:25:30 pm

James, Emily, Lobo, etc...

I have been going through the painful upgrading process for a Drupal / Civi site and am getting some of the exact same errors listed in this thread (Going from 2.2.9 to 3.0.4) when upgrading.

I have ensured db integrity by reloading clean, tried a million different things and am finally stuck. I am particularly interested in hearing what James did to find out where the issues were. You loaded civicrm.mysql from a 3.0.x install and then did what?

I've come a long ways from my errors down at 2.0.x and I really want to get all the way to 3.1 - Thanks for any insights into why I am having these issues. I would really appreciate it! I have posted the error below:

Code: [Select]

Upgrade CiviCRM to Version 3.0.4

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 (`menaspeacemakerstest2/#sql-53cb_fbaf58`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL), 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_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 (`menaspeacemakerstest2/#sql-53cb_fbaf58`, 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 (`menaspeacemakerstest2/#sql-53cb_fbaf58`, 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 (`menaspeacemakerstest2/#sql-53cb_fbaf58`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]"]
)

[/code]

ijames · February 10, 2010, 11:13:58 pm

Hi Jake,

What I ended up doing was somehow realizing that there was some reason why the constraints were failing and looking particularly at the table where the error occurred and the FK associated. So in your case the um civicrm_contact table and the civicrm_activity table? If you were to look at civicrm_activity you might possibly find that there are records in that table in which the source_contact_id doesn't match a civicrm_contact's id field. At least if that's what I'm reading.

Let's see if I can recollect what I did... I remember taking my entire civicrm 2.2.5 SQL dump and pretty much using phpmyadmin to paste in a bunch of it to insert into the new structure or something like that. Then if it survived, I'd continue. If not, I'd go to the table it failed on and figure out what constraint it messed up on, and sure enough in each case there was a dangler... Getting rid of those solved the problem for me.

I've got no idea where this corruption came from but the nice thing is it shouldn't happen now because we've got these constraints. Just be careful to make sure you're always copying the constraints when you're copying the database from now on, otherwise they just get lost (in phpMyAdmin).

So I hope that makes some sense and even helps! It took me quite a number of trials. I was very glad to be on a host where I could instantly copy databases as many times as I wanted and also that I was dealing with a small database that was pretty recently created. Pasting the SQL into phpMyAdmin was painful enough.

So good luck!!! Maybe it won't take you 10 trys from this point! I'm about to go to 3.1 hoping my experience will not be needed....

James

jakedeo · March 04, 2010, 10:46:37 pm

James,

Thanks so much for responding. It took me a while to get around to actually finishing this upgrade...

You were right on about the source_contact_id not matching. There was just one user who had been deleted from the system and the software apparently didn't delete all of the associated data from that table. Strange that all of my earlier upgrades worked without issues, but, hey! I'm just happy to finally be done with this and up to 3.1.1.

To anyone else running into this same error on upgrades, check through your civicrm_activity table and check each source_contact_id against the id column in the civicrm_contact table. You'll find that some of the source_contact_ids don't actually exist. Tedious but after I got this worked out the upgrade ran like a champ.

I appreciate all of the work other people have posted that has helped me figure out these database integrity issues along the upgrade path.

CiviCRM Community Forums (archive)

News:

Author Topic: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what (Read 5885 times)

ijames

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what

emilyf

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what

ijames

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what

jakedeo

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what

ijames

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what

jakedeo

Re: Upgrade 2.2.5 > 3.0.2: FK Issues no matter what