Author Topic: DB error in 2.1.4 to 2.2.3 upgrade (Read 1532 times)

Michael McAndrew · May 16, 2009, 04:12:13 pm

Hi,

I'm getting the following error when upgrading from 2.1.4 to 2.2.3

Error Details:

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

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_participant`
   DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id` [nativecode=1025 ** Error on rename of './vaw_live_civicrm/civicrm_participant' to './vaw_live_civicrm/#sql2-ebf-202f2' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_participant`
   DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id` [nativecode=1025 ** Error on rename of './vaw_live_civicrm/civicrm_participant' to './vaw_live_civicrm/#sql2-ebf-202f2' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_participant`
   DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id` [nativecode=1025 ** Error on rename of './vaw_live_civicrm/civicrm_participant' to './vaw_live_civicrm/#sql2-ebf-202f2' (errno: 152)]"]
)

Any ideas on what the problem is here and how to fix it?

Thanks,
Michael[/code]

Michael McAndrew · May 17, 2009, 01:47:11 pm

The problem here was that my foreign keys were missing - most likely because at some point i'd switched my database to myisam and then back to innodb thus removing the FKs.

Recreating the old database structure using the civicrm.mysql that came with the old package and then importing my data into that new structure solved the problem.

polestar · May 13, 2010, 04:41:10 pm

Hi Michael,

I got the same error while updating a site from 2.1 to 2.2

when I try to upload data to the fresh database with the structure from civicrm.mysql the following error comes:

Code: [Select]

#1452 - Cannot add or update a child row: a foreign key constraint fails (`rohitcivi/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)

I have cleaned up the civicrm_activity.source_contact.id to null as well as to a contact_id from civicrm_contact table but still of no use at all.

could you please suggest if and how did it work.

Thanks
Rohit

Michael McAndrew · May 14, 2010, 04:15:09 am

Hey,

The problem is that when you are importing a load of data into a database which has forgeign key constraints, there are most likely going to be foreign key constraint failures because it is looking for a key that isn't yet imported (but hopefully will be by the time the import finishes.

So the solution is to disable foreign key checks while you are importing.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE ... DISABLE KEYS

and then enable them when you are finished

ALTER TABLE ... ENABLE KEYS

If you are using phpMyAdmin one easy way to do that is to tick the 'Disable foreign key checks' checkbox on the export screen.

CiviCRM Community Forums (archive)

News:

Author Topic: DB error in 2.1.4 to 2.2.3 upgrade (Read 1532 times)

Michael McAndrew

DB error in 2.1.4 to 2.2.3 upgrade

Michael McAndrew

Re: DB error in 2.1.4 to 2.2.3 upgrade

polestar

Re: DB error in 2.1.4 to 2.2.3 upgrade

Michael McAndrew

Re: DB error in 2.1.4 to 2.2.3 upgrade