0

The user wants to send a recurring email - twice a week on successive days. So I set up a recurring event and then added a CiviRule with the trigger 'Event date reached'. The event didn't trigger because of the following crash - it doesn't have any participants, could this be the problem?

SELECT `p`.*
        FROM `civicrm_participant` `p`
        INNER JOIN `civicrm_event` `e` ON `e`.`id` = `p`.`event_id`
        LEFT JOIN `civirule_rule_log` `rule_log` ON `rule_log`.entity_table = 'civicrm_participant' AND `rule_log`.entity_id = p.id AND `rule_log`.`contact_id` = `p`.`contact_id` AND DATE(`rule_log`.`log_date`) = DATE(NOW())  AND `rule_log`.`rule_id` = %3
        WHERE DATE(`e`.`start_date`) = CURDATE()
        AND `rule_log`.`id` IS NULL
        AND `e`.`event_type_id` = 12
        AND `p`.`contact_id` NOT IN (
          SELECT `rule_log2`.`contact_id`
          FROM `civirule_rule_log` `rule_log2`
          WHERE `rule_log2`.`rule_id` = %3 AND DATE(`rule_log2`.`log_date`) = DATE(NOW()) and `rule_log2`.`entity_table` IS NULL AND `rule_log2`.`entity_id` IS NULL
        ) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%3
        WHERE DATE(`e`.`start_date`) = CURDATE()
        AND `rule...' at line 4]

I'd like to understand how to get this working, and also if anyone has a suggestion of an alternative way of achieving this?

0

I think the issue would indeed be that there are no participants, so therefore no events joined and an empty start_date. Can you try with a participant?

If the issue persists can you please report it as a bug on the Civirules extension page (https://lab.civicrm.org/extensions?utf8=%E2%9C%93&filter=civirules)? Would fixing it be possible for you?

2
  • I tried with participants, but got a different crash. Issue added as you suggested. I don't have the PHP skills to fix, sorry.
    – Andy Clark
    Sep 28 '20 at 19:36
  • No worries...might well be that others run into the same problems so it gets fixed. Or you'd have to wait until the next CiviRules sprint that CiviCooP does (not scheduled yet) Sep 29 '20 at 6:46

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.