Answers.Body: <p>I do not know without checking on your site. I have recently tested 2.1 on an existing site and it worked there. You might want to check if the civirule_condition table is still populated? It might be that it got accidently cleaned out.</p>
  
  <p>If that is the case, you will have to take note of what your conditions/actions where, run the sql scripts <strong>insertCiviRulesConditions</strong> and <strong>insertCiviRuleActions</strong> from the sql folder of the extension to re-create them. And then you have to rebuild your rules in the UI.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-26T02:58:33.990Z
Answers.Text: Thanks for the response.   Civirule_condition is populated but I see that civirule_rule_action and civirule_rule_condition are empty. The site was recently moved from development domain to the live domain, and the database imported and domain names adjusted.
Answers.UserDisplayName: PhilF
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-26T03:09:07.763Z
Answers.Text: But the same problem is occurring on the older development site with same Civi version and older civirules.  I will try retesting the state transitions to make sure its all working.
Answers.UserDisplayName: PhilF
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-26T07:30:04.583Z
Answers.Text: As said, I have not experienced your problem nor do we have issues reported on the CiviRules repo that mention this problem. It might indeed be caused by civirule_rule_action and civirule_rule_condition being empty. No idea what caused that on the older development site, but that smells like database corruption.
Answers.UserDisplayName: ErikH - CiviCooP
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-29T01:47:36.077Z
Answers.Text: So somehow all the rule conditions and actions were deleted in the move or upgrade of the extension.  I recreated all of these and they look ok in the tables, but so far the database error is still present.  Is this a change that requires clearing the cache?
Answers.UserDisplayName: PhilF
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-29T06:34:49.130Z
Answers.Text: yes that might work. Give it a try?
Answers.UserDisplayName: ErikH - CiviCooP
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-25T16:53:54.770Z
Answers.LastActivityDate: 2018-06-25T16:53:54.770Z
Answers.UserDisplayName: ErikH - CiviCooP
Body: <p>I have 7 rules set up to help automate membership management.  All are tagged with the same tag and all but one are active.  WHen I search by tag or for all rules I get the correct result, but when I search by the tag AND all rules I get a DB query syntax fatal error. This is from the log:</p>
  
  <p>[db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" </p>
  
  <p>info="SELECT COUNT(DISTINCT(crr.id)) AS total 
        FROM civirule_rule crr
  LEFT JOIN civirule_trigger crtrigger ON crr.trigger_id = crtrigger.id
  LEFT JOIN civirule_rule_tag crtag ON crr.id = crtag.rule_id
  LEFT JOIN civicrm_contact contact ON crr.created_user_id = contact.id <strong>WHERE  AND</strong> (crtag.rule_tag_id = 1) </p>
  
  <p>[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 (crtag.rule_tag_id = 1)' at line 5]"]</p>
  
  <p>I suppose that some condition is expected between <strong>WHERE  AND</strong> that I emphasized in bold.   This same error happens on Civirules 1.23 and 2.1.  The upgrade also updated the database, but I tested on a separate, older system that had not been upgraded. </p>
  
  <p>Could this be due to some database corruption or is it a bug?</p>
  
Tags: <error><civirules><sql>
Title: Fatal DB query syntax error on rules search - Civi 5.0.0 and recent Civirules
ThreadText: # Fatal DB query syntax error on rules search - Civi 5.0.0 and recent Civirules
  
  (Asked by: username_0 on 2018-06-24)
  
  I have 7 rules set up to help automate membership management. All are tagged with the same tag and all but one are active. WHen I search by tag or for all rules I get the correct result, but when I search by the tag AND all rules I get a DB query syntax fatal error. This is from the log:
  
  [db\_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM\_Core\_Error::handle prefix=""
  
  info="SELECT COUNT(DISTINCT(crr.id)) AS total
  FROM civirule\_rule crr
  LEFT JOIN civirule\_trigger crtrigger ON crr.trigger\_id = crtrigger.id
  LEFT JOIN civirule\_rule\_tag crtag ON crr.id = crtag.rule\_id
  LEFT JOIN civicrm\_contact contact ON crr.created\_user\_id = contact.id **WHERE AND** (crtag.rule\_tag\_id = 1)
  
  [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 (crtag.rule\_tag\_id = 1)' at line 5]"]
  
  I suppose that some condition is expected between **WHERE AND** that I emphasized in bold. This same error happens on Civirules 1.23 and 2.1. The upgrade also updated the database, but I tested on a separate, older system that had not been upgraded.
  
  Could this be due to some database corruption or is it a bug?
  
  **Tags:** error, civirules, sql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-06-25. Score: 1 | Accepted answer
  
  I do not know without checking on your site. I have recently tested 2.1 on an existing site and it worked there. You might want to check if the civirule\_condition table is still populated? It might be that it got accidently cleaned out.
  
  If that is the case, you will have to take note of what your conditions/actions where, run the sql scripts **insertCiviRulesConditions** and **insertCiviRuleActions** from the sql folder of the extension to re-create them. And then you have to rebuild your rules in the UI.
  
  > **Comments:**
  > **username_0**: Thanks for the response.   Civirule_condition is populated but I see that civirule_rule_action and civirule_rule_condition are empty. The site was recently moved from development domain to the live domain, and the database imported and domain names adjusted.  
  > **username_0**: But the same problem is occurring on the older development site with same Civi version and older civirules.  I will try retesting the state transitions to make sure its all working.  
  > **username_1**: As said, I have not experienced your problem nor do we have issues reported on the CiviRules repo that mention this problem. It might indeed be caused by civirule_rule_action and civirule_rule_condition being empty. No idea what caused that on the older development site, but that smells like database corruption.  
  > **username_0**: So somehow all the rule conditions and actions were deleted in the move or upgrade of the extension.  I recreated all of these and they look ok in the tables, but so far the database error is still present.  Is this a change that requires clearing the cache?  
  > **username_1**: yes that might work. Give it a try?  
  
  ---
  
  