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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
5
  • 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, 2018 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, 2018 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, 2018 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, 2018 at 1:47
  • yes that might work. Give it a try?
    – ErikH - CiviCooP
    Jun 29, 2018 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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