Campaign Update Console Command Causes SQL Error

Product Support
1

Hi,



I setup a new 1.1.2 Mautic system from scratch. To test it, I imported leads and created a campaign which sends out an email an tracks a landing page.



The leads were successfully added to the campaign, but the emails were not sent out - so I manually fired to console commands via SSH:

Code:
php mautic/app/console mautic:leadlists:update --env=prod php mautic/app/console mautic:campaigns:update --env=prod php mautic/app/console mautic:campaigns:trigger --env=prod

The first and the last one finished properly, but the second one caused two error messages in the SSH terminal:
Code:
[DoctrineDBALDBALException] An exception occurred while executing 'SELECT max(ll.lead_id) as max_id, count(distinct(ll.lead_id)) as lead_count FROM lead_lists_leads ll WHERE (ll.leadlist_id IN ()) AND (ll.manually_removed = ?) AND (ll.date_added <= '2015-06-10 14:30:49') AND (ll.lead_id NOT IN (SELECT cl.lead_id FROM campaign_leads cl WHERE cl.campaign_id = 1)) ORDER BY ll.lead_id ASC' with params [0]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQLserver version for the right syntax to use near')) AND (ll.manually_removed = 0) AND (ll.date_added <= '2015-06-10 14:30:49') AN' at line 1
Code:
[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')) AND (ll.manually_removed = 0) AND (ll.date_added <= '2015-06-10 14:30:49') AN' at line 1

Any ideas?

Peter
2

Hi,

I setup a new 1.1.2 Mautic system from scratch. To test it, I imported leads and created a campaign which sends out an email an tracks a landing page.

The leads were successfully added to the campaign, but the emails were not sent out - so I manually fired to console commands via SSH:

php mautic/app/console mautic:leadlists:update --env=prod php mautic/app/console mautic:campaigns:update --env=prod php mautic/app/console mautic:campaigns:trigger --env=prod

The first and the last one finished properly, but the second one caused two error messages in the SSH terminal:

[DoctrineDBALDBALException] An exception occurred while executing 'SELECT max(ll.lead_id) as max_id, count(distinct(ll.lead_id)) as lead_count FROM lead_lists_leads ll WHERE (ll.leadlist_id IN ()) AND (ll.manually_removed = ?) AND (ll.date_added <= '2015-06-10 14:30:49') AND (ll.lead_id NOT IN (SELECT cl.lead_id FROM campaign_leads cl WHERE cl.campaign_id = 1)) ORDER BY ll.lead_id ASC' with params [0]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQLserver version for the right syntax to use near')) AND (ll.manually_removed = 0) AND (ll.date_added <= '2015-06-10 14:30:49') AN' 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')) AND (ll.manually_removed = 0) AND (ll.date_added <= '2015-06-10 14:30:49') AN' at line 1

Any ideas?

Peter

3

Hi Peter,

Do you have lead lists assigned to the campaign or just using a form as the lead source?

The error is coming from ll.leadlist_id IN () which makes me wonder if there are any lists assigned to the campaign. We need to add a check to prevent that SQL error.

I just submitted a patch to fix this at https://github.com/mautic/mautic/pull/533. Would you be willing to apply it and test then make a comment in the PR on the result. You can simply manually make the changes listed in https://github.com/mautic/mautic/pull/533/files or click the view button for each file then raw and copy the entire file’s contents to the associated file.

Thanks!
Alan

4

Hi Alan,

Thank you! Your patch resolved the error message. You were right, I created a campaign with a form assigned and no lead list.

Thanks again,

Peter