CiviCRM Community Forums (archive)

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

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!" 

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

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;


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!!! 

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