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.