CiviCRM 4.7.27 Usually, our CiviCRM guru handles these, but I figured I would ask a wider audience.

When choosing date instead of an interval, the scheduled reminder fails with a DB syntax error. The error is in the " DATE_SUB(, INTERVAL hour))" part of the SQL.

The first argument to the function is missing and the time component for the interval is missing: (see below in bold). I would imagine this is a bug.. The SQL from the logfile:

Here's the SQL that is created by CiviCRM and fails:

SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 3 as action_schedule_id, MAX as latest_log_time 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 INNER 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 = 3 WHERE (e.membership_type_id IN ("21")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.status_id IN (5, 2, 9, 10, 4, 8)) AND ("20171108110009" <= DATE_SUB(, INTERVAL hour)) GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table HAVING (TIMESTAMPDIFF) >= TIMESTAMPDIFF))

NOTE: There are missing parameters in the DATE_SUB function, the SQL fails and the scheduled reminder does not go out.


My friendly neighborhood Civi expert solved this problem. The issue is with the "repeat" option in the scheduled reminder form.

If you specify, when to repeat without the "Until" piece, it generates faulty SQL. Our expert modified the form so you can't leave that value blank if you click the repeat radio box.

enter image description here

| improve this answer | |
  • 2
    A patch was submitted upstream for this problem yesterday - presumably Brian S. is your CiviCRM person! For anyone else with this problem, that patch is at CRM-21523. – Jon G - Megaphone Tech Dec 6 '17 at 17:03
  • Small comment on this: We just had the problem that neither a reminder date not hours before event was filled out, thus creating a faulty SQL statement matching your problem as well. While I think that this is not smart execution on the user site, but the UI shouldn't allow to create a faulty reminder either. – Phil_B Mar 16 '18 at 9:19
  • I am still getting same error in civicrm 5.6.1 and Drupal 7.63. Repeat option is selected and 'Until' field is populated – Hassan Farooq Feb 13 '19 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.