1

After upgrading to v 4.7.16 in May, the scheduled reminders do not run. I get "DB Error: syntax error"

Here's the query from the log. I cut & pasted so I hope I got all the information someone needs.

I saw another similar problem with v4.7.16 but it didn't quite match what I'm experiencing.

    [debug_info] => 
SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 7 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
FROM civicrm_membership e
INNER JOIN civicrm_group_contact_cache grp ON e.contact_id = grp.contact_id
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 = 7
WHERE (e.membership_type_id IN ("1", "2", "3")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.contact_id NOT IN (41, 40, 42, 77, 92, 230, 231)) AND (e.status_id IN (8, 1, 2, 3, 4)) AND ( grp.group_id IN (2)) AND ("20170814100212" <= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170814100212 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL 1 YEAR)))
 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY rem' at line 9]
    [type] => DB_Error
    [user_info] => SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 7 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
FROM civicrm_membership e
INNER JOIN civicrm_group_contact_cache grp ON e.contact_id = grp.contact_id
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 = 7
WHERE (e.membership_type_id IN ("1", "2", "3")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.contact_id NOT IN (41, 40, 42, 77, 92, 230, 231)) AND (e.status_id IN (8, 1, 2, 3, 4)) AND ( grp.group_id IN (2)) AND ("20170814100212" <= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170814100212 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL 1 YEAR)))
 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY rem' at line 9]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 7 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
FROM civicrm_membership e
INNER JOIN civicrm_group_contact_cache grp ON e.contact_id = grp.contact_id
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 = 7
WHERE (e.membership_type_id IN ("1", "2", "3")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.contact_id NOT IN (41, 40, 42, 77, 92, 230, 231)) AND (e.status_id IN (8, 1, 2, 3, 4)) AND ( grp.group_id IN (2)) AND ("20170814100212" <= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170814100212 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL 1 YEAR)))
 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND ('20170814100212' >= DATE_SUB(e.end_date, INTERVAL 0 month))
GROUP BY rem' at line 9]"
0

This is a known bug, CRM-19017.

Every parenthesis must have a corresponding closing parenthesis and vice versa - but the line starting with "HAVING" has more closing parenthesis than opening parenthesis.

You can fix this by upgrading to CiviCRM 4.7.23 or later.

  • I've upgraded to 4.7.23 and am still getting the error. – Terri C Aug 29 '17 at 14:47
  • Try clearing caches? – Jon G - Megaphone Tech Aug 29 '17 at 17:27
  • Thanks, Jon, but I cleared caches as part of upgrading; but I just now cleared them again in case, as well as browser caches. Ran a system check - there was an indexing error on a table we never use (civicrm_case_contact) since we don't have cases. Fixed it. Ran scheduled jobs - same error. The bottom of the page does show 4.7.23. I'd copy/paste the SQL, but it takes more characters then I'm allowed here. – Terri C Aug 30 '17 at 2:21
  • Thanks, Jon G. for trying to help. After clearing caches, again, and looking at the error log, could the problem be that in one case double quotes are used, and in another single quotes? Here's where it seems to be choking: WHERE (e.membership_type_id IN ("1", "2", "3")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.contact_id NOT IN (41, 40, 42, 77, 230, 231, 94)) AND (e.status_id IN (8, 1, 2, 3, 4)) AND ( grp.group_id IN (2)) AND ("20170829220752" <= DATE_SUB(e.end_date, INTERVAL hour)) AND ('20170829220752' >= DATE_SUB(e.end_date, INTERVAL 0 month)) – Terri C Aug 30 '17 at 2:32
  • PROBLEM SOLVED! Needed a interval number for the reminder repetitions. Had # of years for 'every', but null in 'until' – Terri C Aug 30 '17 at 3:08

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.