Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-18236

Scheduled reminder sends multiple reminders when reference date has changed


    • Type: Bug
    • Status: In Quality Assurance
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.6.20, 4.7.3
    • Fix Version/s: 4.6.22, 4.7.5
    • Component/s: None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
    • Funding Source:
      Core Team Funds


      Scheduled reminder code generates multiple reminders if the reference_date has changed.

      I have marked this as Major because it is a really bad look when members get unexpectedly spammed by CiviCRM


      Scheduled reminder set to run one week before membership end date
      Reminder runs once, then membership end date is changed
      When the next date comes around it correctly sends a reminder but it incorrectly sends a reminder every cron until the next date has ended.

      I have replicated this in a test which I am pushing.

      This is a cut down version of the offending query

      {{INSERT INTO civicrm_action_log (reference_date, contact_id, entity_id, entity_table, action_schedule_id)
      SELECT e.end_date, e.contact_id as contact_id, e.id as entity_id, 'civicrm_membership' as entity_table, 1 as
      FROM civicrm_membership e
      INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
      LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND
      reminder.entity_id = e.id AND
      reminder.entity_table = 'civicrm_membership' AND
      reminder.action_schedule_id = 1
      WHERE e.membership_type_id IN (1) AND ( e.is_override IS NULL OR e.is_override = 0 ) AND e.status_id IN (1,2
      ,3,4) AND reminder.id IS NOT NULL AND '20120601010001' >= DATE_SUB(e.end_date, INTERVAL 2 week) AND DATE_SUB(20120601010001, INTERVAL 1 DAY ) <= DATE_SUB(e.end_date, INTERVAL 2 week) AND
      reminder.action_date_time IS NOT NULL AND
      reminder.reference_date IS NOT NULL
      GROUP BY reminder.id, reminder.reference_date
      HAVING reminder.id = MAX(reminder.id) AND reminder.reference_date <> e.end_date}}

      The issue appears to be that
      reminder.id = MAX(reminder.id)
      does not work as the writer intended it to.

      If you load the attached sql & then run that query you can see the query failing & adding an extra row everytime it runs




            • Assignee:
              davej Dave Jenkins
              eileen Eileen McNaughton
            • Votes:
              1 Vote for this issue
              12 Start watching this issue


              • Created:

                Time Tracking

                Original Estimate - Not Specified
                Not Specified
                Remaining Estimate - 0 minutes
                Time Spent - 2 days, 30 minutes
                2d 30m