CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)
Pages: [1]

Author Topic: 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)  (Read 764 times)

iandev

  • I’m new here
  • *
  • Posts: 16
  • Karma: 0
  • CiviCRM version: 4.1
  • CMS version: Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3
1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)
October 05, 2012, 01:15:08 pm
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:
Quote
[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:
Quote
+---------+-----------------+-------------------+-----------+----------+------------+--------------+-----------+-----------+-----------+---------+
| 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.
« Last Edit: October 05, 2012, 01:24:43 pm by iandev »

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)
October 05, 2012, 02:18:48 pm

that was a long long time ago, so not sure how many people even remember that code

from my faint recollection, we collapsed a bunch of tables (meeting, phonecall) etc into one unified activity table. so u'll need to do the mapping of the old meeting table id to the new activity table id

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

iandev

  • I’m new here
  • *
  • Posts: 16
  • Karma: 0
  • CiviCRM version: 4.1
  • CMS version: Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3
Re: 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)
October 05, 2012, 03:33:50 pm
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?
« Last Edit: October 05, 2012, 03:35:36 pm by iandev »

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)
October 05, 2012, 04:56:12 pm

yeah, a bit surprised that no one tripped on it so far and i suspect they've been quite a few upgrades.

seems like u'll need to debug and fix the upgrade process / tables for your site so the id constraints are satisfied (i.e. the latter part of your post)

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • 1.9 to 2.2.6 upgrade process. missing activity data (faulty upgrade process?)

This forum was archived on 2017-11-26.