DB Error: constraint violation civicrm_activity_assignment upgrading from 3.3.6

[mdlueck]

I am testing the upgrade on Drupal 6 from Civi 3.3.6 to 3.4.4 and ran into the following error:

Code: [Select]

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_activity_assignment ( activity_id, assignee_contact_id ) SELECT ca.id, ca.source_contact_id FROM civicrm_activity ca LEFT JOIN civicrm_activity_assignment cas ON ( cas.activity_id = ca.id ) WHERE ca.activity_type_id = @option_value_membership_reminder AND cas.id IS NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d1cir_civicrm/civicrm_activity_assignment`, CONSTRAINT `FK_civicrm_activity_assignment_assignee_contact_id` FOREIGN KEY (`assignee_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE C)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_activity_assignment ( activity_id, assignee_contact_id ) SELECT ca.id, ca.source_contact_id FROM civicrm_activity ca LEFT JOIN civicrm_activity_assignment cas ON ( cas.activity_id = ca.id ) WHERE ca.activity_type_id = @option_value_membership_reminder AND cas.id IS NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d1cir_civicrm/civicrm_activity_assignment`, CONSTRAINT `FK_civicrm_activity_assignment_assignee_contact_id` FOREIGN KEY (`assignee_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE C)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_activity_assignment ( activity_id, assignee_contact_id ) SELECT ca.id, ca.source_contact_id FROM civicrm_activity ca LEFT JOIN civicrm_activity_assignment cas ON ( cas.activity_id = ca.id ) WHERE ca.activity_type_id = @option_value_membership_reminder AND cas.id IS NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d1cir_civicrm/civicrm_activity_assignment`, CONSTRAINT `FK_civicrm_activity_assignment_assignee_contact_id` FOREIGN KEY (`assignee_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE C)]"]
)
Suggestions please?

Fortunately this is on a test copy of our 3.3.6 production site. "No pressure!" 

[Kurund Jalmi]

Just a wild guess, your civicrm_activity table has some activities for deleted contacts. So try deleting them first and retry upgrade.

SELECT source_contact_id FROM civicrm_activity WHERE source_contact_id NOT IN ( select id from civicrm_contact );

Above sql should give you orphan activities.

HTh
Kurund

[mdlueck]

Thanks for the suggestion. Here is the database's reply:

Code: [Select]

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0347 sec)

SQL query:
SELECT source_contact_id
FROM civicrm_activity
WHERE source_contact_id NOT
IN (
  SELECT id
  FROM civicrm_contact
)
LIMIT 0 , 30;


[Eileen]

Re-formatted the query & to make it easier to look at

Code: [Select]

INSERT INTO civicrm_activity_assignment ( activity_id, assignee_contact_id )

SELECT ca.id, ca.source_contact_id
FROM civicrm_activity ca
LEFT JOIN civicrm_activity_assignment cas ON ( cas.activity_id = ca.id ) 

WHERE ca.activity_type_id = @option_value_membership_reminder AND cas.id IS NULL

[mdlueck]

A big thanks to bgm and eileen via IRC, this problem is resolved.

Turns out that some really REALLY old data was stuck in that table. We went production with Civi earlier this year, so I purged from the table all records older than this year... AFTER dropping all tables and restoring from backup of course.

So, steps to success:
1) Drop all tables until all tables have been removed
2) Restore from backup
3) SELECT * FROM `civicrm_activity` WHERE `activity_date_time` < '2011-01-01 00:00:00'
4) DELETE FROM `civicrm_activity` WHERE `activity_date_time` < '2011-01-01 00:00:00'
5) Once more to make sure 0 records are returned: SELECT * FROM `civicrm_activity` WHERE `activity_date_time` < '2011-01-01 00:00:00'
6) On with the upgrade to a green light success. Woo hoo, on with 3.4.4 testing!

Thanks again bgm and eileen!!! 

[chriscant]

I found this problem as well.  I found the value of @option_value_membership_reminder to be 27 in my case:

SELECT @option_value_membership_reminder := value FROM civicrm_option_value v, civicrm_option_group g WHERE v.option_group_id = g.id AND g.name = 'activity_type' AND v.name = 'Membership Renewal Reminder';

The insert problem was due to ca.source_contact_id being NULL.  I found these cases as follows, using the value 27 found above:

SELECT ca.id, ca.source_contact_id
FROM civicrm_activity ca
   LEFT JOIN civicrm_activity_assignment cas
   ON ( cas.activity_id = ca.id )
WHERE ca.activity_type_id = 27 AND cas.id IS NULL AND ca.source_contact_id IS NULL

In my case there were three such records.  I deleted them and the script ran OK.

[Donald Lobo]

hey chris:

can u please file an issue with a link to this forum post. We should fix it to avoid problems for folks who will upgrade later

lobo

[chriscant]

Reported here: http://issues.civicrm.org/jira/browse/CRM-9245