Author Topic: 1.9.13 to 2.0.3 fails at step three, four, six (Read 1653 times)

dharmatech · May 14, 2008, 11:12:34 am

We're testing the upgrade from 1.9 to 2.0.3 and ran into some troubles. We were able to get around it by modifying the sql but we thought we should post in case it's a bug. BTW, their production site is 1.9 so since 2.0 was first announced we've been testing the upgrade process. 2.0.1 upgrade from 1.9 was fine so it seems that something has come up since then.

We followed the steps outlined in the upgrade process, including the "database rebuild to ensure schema integrity."

At step three...

ALTER TABLE `civicrm_address`
ADD CONSTRAINT `FK_civicrm_address_county_id`
   FOREIGN KEY (`county_id`) REFERENCES `civicrm_county` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `FK_civicrm_address_state_province_id`
   FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `FK_civicrm_address_country_id`
   FOREIGN KEY (`country_id`) REFERENCES `civicrm_country` (`id`) ON DELETE SET NULL
[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19fix/#sql-170d_a0907`,
CONSTRAINT `FK_civicrm_address_county_id` FOREIGN KEY (`county_id`) REFERENCES `civicrm_county` (`id`) ON DELETE SET NULL)]

At step four...

INSERT INTO civicrm_activity_target (activity_id, target_contact_id)
SELECT ca.id, ca.target_entity_id
FROM civicrm_activity ca
WHERE ca.target_entity_table = 'civicrm_contact'
ON DUPLICATE KEY UPDATE activity_id=ca.id

[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19fix/civicrm_activity_target`,
CONSTRAINT `FK_civicrm_activity_target_target_contact_id` FOREIGN KEY (`target_contact_id`)
REFERENCES `civicrm_contact` (`id`) ON DELETE CASCA)]

At step six...

ALTER TABLE `civicrm_relationship`
ADD CONSTRAINT `FK_civicrm_relationship_contact_id_a`
   FOREIGN KEY (`contact_id_a`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_civicrm_relationship_contact_id_b`
   FOREIGN KEY (`contact_id_b`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_civicrm_relationship_relationship_type_id`
   FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type` (`id`) ON DELETE CASCADE

[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19fix/#sql-170d_a406b`,
CONSTRAINT `FK_civicrm_relationship_contact_id_a` FOREIGN KEY (`contact_id_a`)
REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]

Deepak Srivastava · May 14, 2008, 11:59:54 pm

FK CONSTRAINT Errors are due to data-inconsistency and should be resolved at data-level which in upgrade process is handled by cleanup sql script (Step1). But it's just not possible do the cleanup for all kinds of CONSTRAINTS (especially FK) for the entire db (unless you using some additional tool, i guess) and so is the reason that you noticed those errors.
This is how i would go about solving those problems / errors you pasted -

Quote

[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19fix/#sql-170d_a0907`,
CONSTRAINT `FK_civicrm_address_county_id` FOREIGN KEY (`county_id`) REFERENCES `civicrm_county` (`id`) ON DELETE SET NULL)]

This implies there are some invalid entries in civicrm_address table related to county_id column. The query for identifying such entries could be :

Code: [Select]

SELECT ca.* FROM civicrm_address ca
LEFT JOIN civicrm_county cc ON ca.county_id=cc.id
WHERE cc.id IS NULL;

Now since it's safe to update those entries to NULL rather than deleting the record, query would be:

Code: [Select]

UPDATE civicrm_address ca
LEFT JOIN civicrm_county cc ON ca.county_id=cc.id
SET ca.county_id = NULL
WHERE cc.id IS NULL AND ca.county_id IS NOT NULL;

We 'll make the cleanup process handle the step-4 error you specified. The cleanup query would be -

Code: [Select]

DELETE ca.* FROM civicrm_activity ca 
LEFT JOIN civicrm_contact cc ON ca.target_entity_id=cc.id 
WHERE ca.target_entity_table = 'civicrm_contact' AND cc.id is NULL;

And similarly for step-6 error.

dharmatech · May 15, 2008, 02:15:42 pm

Thanks Deepak, I'll update the database correcting the county_id errors and try the upgrade again.

CiviCRM Community Forums (archive)

News:

Author Topic: 1.9.13 to 2.0.3 fails at step three, four, six (Read 1653 times)

dharmatech

1.9.13 to 2.0.3 fails at step three, four, six

Deepak Srivastava

Re: 1.9.13 to 2.0.3 fails at step three, four, six

dharmatech

Re: 1.9.13 to 2.0.3 fails at step three, four, six