CiviCRM Community Forums (archive)

I am currently upgrading a civicrm 1.9 application to 2.2.6 & debugging the upgrade scripts.  I have some concerns with how the upgrade process handles the activities.  During the upgrade, I've gotten numerous errors mostly relating to foreign key constraint failing.

Here is one:

[debug_info] =>
INSERT INTO custom_value_1_Patient_Services (domain_id,entity_id,Medical_Treatment_Question)
SELECT 1, cv.entity_id, cv.memo_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=162
ON DUPLICATE KEY UPDATE Medical_Treatment_Question=cv.memo_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`idfcrm_upgradetest`.`custom_value_1_Patient_Services`, CONSTRAINT `FK_custom_value_1_Patient_Services_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCA)]

So, this is taking all custom field values that have a custom field id of 162, and inserting the entity_id and the memo data into the custom_value_1_Patient_Services table.  The foreign key constraint is failing because the entity_id does not match up to the civicrm_activities table.  And in alot of cases that is correct, because the entity_table in alot of these are not for civicrm_activity.  Some of them are for civicrm_phonecall, civicrm_meeting, etc.  So why would the entity_id match to the civicrm_activity table?

Does this make sense?

Say I have this data in the civicrm_custom_value table:

+---------+-----------------+-------------------+-----------+----------+------------+--------------+-----------+-----------+-----------+---------+
| id      | custom_field_id | entity_table      | entity_id | int_data | float_data | decimal_data | char_data | date_data | memo_data | file_id |
+---------+-----------------+-------------------+-----------+----------+------------+--------------+-----------+-----------+-----------+---------+
| 1232217 |             162 | civicrm_phonecall |      1803 |     NULL |       NULL |         NULL | NULL      | NULL      |           |    NULL |
| 1241462 |             162 | civicrm_phonecall |      1825 |     NULL |       NULL |         NULL | NULL      | NULL      |           |    NULL |
| 1583678 |             162 | civicrm_phonecall |      2378 |     NULL |       NULL |         NULL | NULL      | NULL      |           |    NULL |
| 1645329 |             162 | civicrm_meeting   |        31 |     NULL |       NULL |         NULL | NULL      | NULL      |           |    NULL |
+---------+-----------------+-------------------+-----------+----------+------------+--------------+-----------+-----------+-----------+---------+

This query: SELECT 1, cv.entity_id, cv.memo_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=162

Pulls all the data from the custom value table, and tries to insert into the custom_value_1_Patient_Services (entity id & memo).  The constraint is trying to match the entity id up to the civicrm_activities table, but it should not because the entity_table for some of these is not the activities table, it is the phonecall table or the meeting table, etc.

When I initially went through this upgrade process (and eventually to 4.1.2), I had ran a bunch of queries to delete the custom value data because I thought they were orphaned rows, they did not join to the activities table.  Then after using the 4.1.2 application for a while, I saw that a whole lot of custom values were missing and I initially thought it was me removing the data during the initial upgrade.  But now it seems like the upgrade process is not handling converting this data correctly. 

Can anyone shed light onto this?

Thanks.

Ian H.

Yeah I know it was a while ago, it just seems to be a fundamental problem with the upgrade process though.

I am not exactly sure what you mean by that.  Are you saying to do this after the upgrade? Because that doesn't seem like it will resolve the FK constraints.  Or are you saying to map the phonecall, meeting, etc ids in the custom value table to a valid activity id, so the fk constraint is satisfied?