Duplicated scheduled reminders are sent after unifying contacts

[capo]

I've detected something that maybe a bug but I'm not sure, so I prefer to discuss it here before creating an issue. I'll describe the steps, so you can reproduce it:

First part

• Create a two contacts: contact A and contact B.
• Create a schedule reminder for a certain activity type and mark it for being sent only once.
• Create an activity of the type defined before having contact A as the target.

Once we've done it, we'll receive an email (the scheduled reminder) addressed to the email of contact A. Everything is ok, until here.

Sencond part

• Merge contacts A and B.

The same schedule reminder is being sent (once again) to contact B.

Diagnosis
If I understood the code, for determining when a scheduled reminder has been sent, CiviCRM looks at the table civicrm_action_log. In this table, we should find a record with entity_table = civicrm_activity, entity_id the id of the corresponding activity and contact_id the id of our contact.

But the records of contact A weren't changed to contact B when merging!

Possible solution

When merging contacts, we should fire something like:

Code: [Select]

//Pseudo-SQL
UPDATE civicrm_action_log SET contact_id = $new_contact_id WHERE contact_id = $old_contact_id;

Or is it, maybe, the desired behaviour?

[Yashodha Chaku]

capo :

Can you please file an issue for 4.5 in JIRA, preferably with patch

Thanks for catching this!
Yashodha

[capo]

Ok, but I'll need a bit of help before doing it.

I've been taking a look at the code and it looks like the trick is at CRM_Dedupe_Merger:

Code: [Select]

/**
   * Return tables and their fields referencing civicrm_contact.contact_id explicitely
   */
  static function cidRefs() {
    static $cidRefs;
    if (!$cidRefs) {
      // FIXME: this should be generated dynamically from the schema's
      // foreign keys referencing civicrm_contact(id)
      $cidRefs = array(
        'civicrm_acl_cache' => array('contact_id'),
        'civicrm_activity_contact' => array('contact_id'),
        'civicrm_case_contact' => array('contact_id'),
        'civicrm_contact' => array('primary_contact_id'),
        'civicrm_contribution' => array('contact_id', 'honor_contact_id'),
        'civicrm_contribution_page' => array('created_id'),
        'civicrm_contribution_recur' => array('contact_id'),
        'civicrm_contribution_soft' => array('contact_id'),
        'civicrm_custom_group' => array('created_id'),
        'civicrm_entity_tag' => array('entity_id'),
        'civicrm_event' => array('created_id'),
        'civicrm_grant' => array('contact_id'),
        'civicrm_group_contact' => array('contact_id'),
        'civicrm_group_organization' => array('organization_id'),
        'civicrm_log' => array('modified_id'),
        'civicrm_mailing' => array('created_id', 'scheduled_id'),
        'civicrm_mailing_event_queue' => array('contact_id'),
        'civicrm_mailing_event_subscribe' => array('contact_id'),
        'civicrm_membership' => array('contact_id'),
        'civicrm_membership_log' => array('modified_id'),
        'civicrm_membership_type' => array('member_of_contact_id'),
        'civicrm_note' => array('contact_id'),
        'civicrm_participant' => array('contact_id'),
        'civicrm_pcp' => array('contact_id'),
        'civicrm_relationship' => array('contact_id_a', 'contact_id_b'),
        'civicrm_uf_match' => array('contact_id'),
        'civicrm_uf_group' => array('created_id'),
        'civicrm_pledge' => array('contact_id'),
      );
I can manually add civicrm_action_log to the list but that won't completely fix the issue. As the TODO states, this list should be obtained automatically from the foreign keys referencing civicrm_contact(id).

I can obtain this dynamic list using something like:

Code: [Select]

/* {civicrm_database} should be replated with database name */
SELECT
    table_name,
    column_name
FROM information_schema.key_column_usage
WHERE
    referenced_table_schema = '{civicrm_database}' AND
    referenced_table_name = 'civicrm_contact' AND
    referenced_column_name = 'id';
But my question is, can I assume that we'll have the right to perform this query? I've doubts because I'm reading a different database (information_schema).

According to MySQL 5.5 documentation (https://dev.mysql.com/doc/refman/5.5/en/information-schema.html), we won't have problems to access this records:

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.

What do you think? Is it a good idea to fill the $cidRefs array by using this query?

[capo]

Issue created (https://issues.civicrm.org/jira/browse/CRM-14500). We continue the discussion there. Thanks!