Your software My Mautic version is: 3.3.3 My PHP version is: 7.3.29 My Database type and version is: 10.3.30-MariaDB
Your problem
My problem is:
I have a campaign that’s been running fine for over a year to process leads imported from a csv file. I just noticed that for the last couple of months it’s been failing, and on further research, it’s failing with as SQL error.
I manually upload lead csv files and have the leads placed in an “import” segment.
The campaign is set to take this segment as the source.
If leads contain the letter “N” in the “interest” field, they are placed in a “news only” segment, and whatever is in the “Interest” field is replaced by “N” because sometimes the web form that feeds this changes the actual value returned.
If leads do not contain “N” in the interest field, they are places in the “Send all emails” segment.
Either way, leads are removed from the “import” segment.
I set this up a long, long time ago and tested that it was working. It’s worked ever since, categorizing my imported subscribers when the cron jobs run. Now, the import segment contains a few hundred leads, they are not correctly categorized, and nothing I can do changes that. There is no practical means to categorize and process them by hand, I need the campaign to do it’s thing or I’m out of luck.
These errors are showing in the log:
[2021-08-15 14:38:30] mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO maulg_campaign_lead_event_failed_log( log_id, date_added, reason) SELECT id, :dateAdded as date_added, :message as reason from maulg_campaign_lead_event_log WHERE is_scheduled = 1 AND lead_id = :contactId AND campaign_id = :campaignId AND rotation = :rotation’: 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 ‘:dateAdded as date_added, :message as reason from maulg_campaign_lead_event_l…’ at line 2
Steps I have tried to fix the problem:
I tried manually selecting the imported leads and adding them back to the campaign using “change campaigns.” No luck.
I tried cloning the campaign and running the jobs to rebuild, then trigger campaigns. Before i did that, I deleted all my logs and that’s how I found the error.
I created a brand new campaign from scratch to do the same steps–and got the same results.
I’ve cleared the cache, and just to be sure, ran php bin/console doctrine:migration:status just to make sure I have all the current database migrations. I do.
This must be a bug since I don’t write the SQL behind these campaigns.
Still an issue. I’ve tried all sorts of workarounds with no luck. I thought maybe it was a problem with removing contacts from the segment used to feed the current campaign, so did this:
Create new campaign fed by “import” segment which categorizes subscribers into the correct segment for mailing and does nothing else. This works.
Created a new “finisher” campaign that feeds off of both the subscriber segments assigned above and does nothing but remove the contacts from the import segment. This fails. Also, manually selecting all contacts in imports segment and choosing “Change segments” removes only the first contact, then fails with a “Uh-Oh” screen.
So my campaigns are dead in the water, and now I have 266 contacts in my “import” campaign and over 10,000 in my “finisher” campaign, and when the rebuild and trigger jobs run, nothing changes except the app log gets one more row reporting a sql error.
]
[2021-08-22 11:37:49] mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO maulg_campaign_lead_event_failed_log( log_id, date_added, reason) SELECT id, :dateAdded as date_added, :message as reason from maulg_campaign_lead_event_log WHERE is_scheduled = 1 AND lead_id = :contactId AND campaign_id = :campaignId AND rotation = :rotation’: 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 ‘:dateAdded as date_added, :message as reason from maulg_campaign_lead_event_l…’ at line 2
So either I have a DB problem (I tried updating schema and it says I’m current) or there is a bug in the SQL which I have no control over.
I ran the schema update and got errors, so I ran it with --dump-sql and went through line by line. There were a bunch of missing spaces (looks like someone ran a utility that got away from them?) mostly things like space missing after field names and around the word DEFAULT, all easier to correct.
So all the db updates that pulled have been run, and that resolve one of the two related issues. I can now open my “import” segment, select all, select “change segments” and tell it to remove from the current segment and that works. Previously that would fail.
My campaign still does not workhowever, and returns this:
[2021-08-23 23:24:15] mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO maulg_campaign_lead_event_failed_log( log_id, date_added, reason) SELECT id, :dateAdded as date_added, :message as reason from maulg_campaign_lead_event_log WHERE is_scheduled = 1 AND lead_id = :contactId AND campaign_id = :campaignId AND rotation = :rotation’: 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 ‘:dateAdded as date_added, :message as reason from maulg_campaign_lead_event_l…’ at line 2
I’ve gotten nowhere with this error. I even tried deleting all campaigns and starting over. I’ve verified my database schema and verified that when I deleted the campaigns, it correctly wiped the related DB tables.
As near as I can tell, the campaign is failing for a reason not logged anywhere, then failing on an update to the campaign lead event failed log table, then writing that failure (but not the root failure) to the log.
My campaign is trivial. I just want to take everyone in an input segment, check the value of their “interest” field, and based on that, stick them in one of two segments. It runs and puts them in the correct segments, but only removes the first from the input segment before failing.
Here is what it logs:
[2021-08-27 11:26:25] mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO maulg_campaign_lead_event_failed_log( log_id, date_added, reason) SELECT id, :dateAdded as date_added, :message as reason from maulg_campaign_lead_event_log WHERE is_scheduled = 1 AND lead_id = :contactId AND campaign_id = :campaignId AND rotation = :rotation’: 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 ‘:dateAdded as date_added, :message as reason from maulg_campaign_lead_event_l…’ at line 2
No part of this query exists anywhere that I can find, so I assume it’s being generated on the fly.
Do you have debugging enabled? Go to yourmautic/app/config/local.php and look at line 126 (or search the php file for debug) ’debug’ => true,
are you running your commands with -vvv flag to get verbose logging outputs? If not enable debugging and run the command with the -vvv flag and post the output
on line 3 of local.php is your db driver pdo_mysql or something else? If its not pdo_mysql change it.
Thank you so much! Turning on debug didn’t reveal anything, but changing the db driver from mysqli to pdo_mysql resolved it. Is that something that’s likely to be reverted with every upgrade, or more likely something leftover from the original install that should have been changed long ago?
@cStuartHardwick I believe the only time pdo or msqli can be selected is on the first or second initial install screen. After that it can only be edited as I described. I am glad it worked for you.