Mautic Community Forums

Campaign Update Console Command Causes SQL Error

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

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

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

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