2

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?

1

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.

  • 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. – PhilF Jun 26 '18 at 2:58
  • 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. – PhilF Jun 26 '18 at 3:09
  • 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. – ErikH - CiviCooP Jun 26 '18 at 7:30
  • 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? – PhilF Jun 29 '18 at 1:47
  • yes that might work. Give it a try? – ErikH - CiviCooP Jun 29 '18 at 6:34

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.