Answers.Body: <p>This is a known bug, <a href="https://issues.civicrm.org/jira/browse/CRM-19017" rel="nofollow noreferrer">CRM-19017</a>.</p>
  
  <p>Every parenthesis must have a corresponding closing parenthesis and vice versa - but the line starting with "HAVING" has more closing parenthesis than opening parenthesis.</p>
  
  <p>You can fix this by upgrading to CiviCRM 4.7.23 or later.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-29T14:47:17.010Z
Answers.Text: I've upgraded to 4.7.23 and am still getting the error.
Answers.UserDisplayName: Terri C
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-29T17:27:39.767Z
Answers.Text: Try clearing caches?
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-30T02:21:31.923Z
Answers.Text: 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.
Answers.UserDisplayName: Terri C
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-30T02:32:07.443Z
Answers.Text: 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))
Answers.UserDisplayName: Terri C
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-30T03:08:21.423Z
Answers.Text: PROBLEM SOLVED! Needed a interval number for the reminder  repetitions. Had # of years for 'every', but null in 'until'
Answers.UserDisplayName: Terri C
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-15T02:20:37.477Z
Answers.LastActivityDate: 2017-08-15T02:20:37.477Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>After upgrading to v 4.7.16 in May, the scheduled reminders do not run. I get
  "DB Error: syntax error"</p>
  
  <p>Here's the query from the log. I cut &amp; pasted so I hope I got all the information someone needs.</p>
  
  <p>I saw another similar problem with v4.7.16 but it didn't quite match what I'm experiencing.</p>
  
  <pre><code>    [debug_info] =&gt; 
  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" &lt;= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' &gt;= 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)) &gt;= 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' &gt;= DATE_SUB(e.end_date, INTERVAL 0 month))
  GROUP BY rem' at line 9]
      [type] =&gt; DB_Error
      [user_info] =&gt; 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" &lt;= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' &gt;= 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)) &gt;= 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' &gt;= DATE_SUB(e.end_date, INTERVAL 0 month))
  GROUP BY rem' at line 9]
      [to_string] =&gt; [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" &lt;= DATE_SUB(e.end_date, INTERVAL  hour)) AND ('20170814100212' &gt;= 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)) &gt;= 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' &gt;= DATE_SUB(e.end_date, INTERVAL 0 month))
  GROUP BY rem' at line 9]"
  </code></pre>
  
Tags: <mysql><scheduled-reminders>
Title: Scheduled Reminders DB Error v 4.7 under Drupal
ThreadText: # Scheduled Reminders DB Error v 4.7 under Drupal
  
  (Asked by: username_0 on 2017-08-14)
  
  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]"
  
  **Tags:** mysql, scheduled-reminders
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-08-15. Score: 0 | Accepted answer
  
  This is a known bug, [CRM-19017](https://issues.civicrm.org/jira/browse/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.
  
  > **Comments:**
  > **username_0**: I've upgraded to 4.7.23 and am still getting the error.  
  > **username_1**: Try clearing caches?  
  > **username_0**: 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.  
  > **username_0**: 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))  
  > **username_0**: PROBLEM SOLVED! Needed a interval number for the reminder  repetitions. Had # of years for 'every', but null in 'until'  
  
  ---
  
  