Mautic Community Forums

mautic:campaigns:trigger DBALException

php /var/www/mautic/app/console -f mautic:campaigns:trigger --env=prod



[DoctrineDBALDBALException]

An exception occurred while executing ‘SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 4) AND (e.lead_id IN (32)) HAVING ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN ())) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN (13))) = 0)’:SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1



[PDOException]

SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1



This is how you can reproduce this error:



Create a campaign

Source: campaign form -> Decision: submit form -> (decision: yes) action: adjust lead points (decision: no) action: send email

Then in Leads add a lead to the campaign



During mautic:campaigns:trigger execution, triggerNegativeEvents() call $repo->getEventLog($campaignId, $campaignLeads, array($grandParentId), array_keys($events)) with $grandParentId = NULL.

In getEventLog $grandParentId is used inside the SQL query in “eh.event_id IN ()” condition.





To fix this error and to skip null array $havingEvents I changed this line in EventRepository.php:



if (!empty($havingEvents)) {



In:



if (!empty($havingEvents) && !is_null(current($havingEvents))) {

https://github.com/mautic/mautic/pull/906 is not part of staging branch yet. Try this:

git checkout -b escopecz-form_field_value_decision staging git pull https://github.com/escopecz/mautic.git form_field_value_decision

then clear cache and test again please.

It works!

Triggering events for campaign 1
Triggering first level events
0 event(s) executed

Triggering scheduled events
0 total events(s) to be processed in batches of 100
0 event(s) executed

Triggering ‘non-action’ events
1 total lead(s) to be analyzed in batches of 100
0/1 [>---------------------------] 0%
1/1 [============================] 100%
1 event(s) executed

Fresh install from staging branch, same error.

php app/console -f mautic:campaigns:trigger

Triggering events for campaign 1
Triggering first level events
0 event(s) executed

Triggering scheduled events
0 total events(s) to be processed in batches of 100
0 event(s) executed

Triggering ‘non-action’ events
1 total lead(s) to be analyzed in batches of 100
0/1 [>---------------------------] 0%

[DoctrineDBALDBALException]
An exception occurred while executing ‘SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 1) AND (e.lead_id IN (1)) HAVING ((SELECT count(eh.event_id) FR
OM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN ())) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN (3))) = 0)’:
SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(
eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(
eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1

mautic:campaigns:trigger [-i|–campaign-id[="…"]] [–scheduled-only] [–negative-only] [-l|–batch-limit[="…"]] [-m|–max-events[="…"]] [-f|–force]

php /var/www/mautic/app/console -f mautic:campaigns:trigger --env=prod

[DoctrineDBALDBALException]
An exception occurred while executing ‘SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 4) AND (e.lead_id IN (32)) HAVING ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN ())) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (eh.lead_id = e.lead_id) AND (eh.event_id IN (13))) = 0)’:SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 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 ‘))) > 0) AND ((SELECT count(eh.event_id) FROM campaign_lead_event_log eh WHERE (’ at line 1

This is how you can reproduce this error:

Create a campaign
Source: campaign form -> Decision: submit form -> (decision: yes) action: adjust lead points (decision: no) action: send email
Then in Leads add a lead to the campaign

During mautic:campaigns:trigger execution, triggerNegativeEvents() call $repo->getEventLog($campaignId, $campaignLeads, array($grandParentId), array_keys($events)) with $grandParentId = NULL.
In getEventLog $grandParentId is used inside the SQL query in “eh.event_id IN ()” condition.

To fix this error and to skip null array $havingEvents I changed this line in EventRepository.php:

    if (!empty($havingEvents)) {

In:

    if (!empty($havingEvents) && !is_null(current($havingEvents))) {

This issue might be solved by https://github.com/mautic/mautic/pull/906. If you use the GitHub version for development, please, test that pull request and write a comment.