Your software
My Mautic version is: 4.0.1
My PHP version is: 7.4.25
My Database type and version is: 10.6.4-MariaDB
Our broadcast emails suddenly stopped getting sent. Everything had been working fine and we hadn’t made any configuration changes or updates.
I did find a few entries like so in the logs:
[2022-05-24 19:27:06] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘SELECT ch.lead_id, fr.frequency_number, fr.frequency_time FROM email_stats ch INNER JOIN lead_frequencyrules fr ON ch.lead_id = fr.lead_id WHERE (fr.channel = :channel) AND (fr.frequency_time IS NOT NULL AND fr.frequency_number IS NOT NULL) AND ((ch.date_sent >= case fr.frequency_time when ‘MONTH’ then DATE_SUB(NOW(),INTERVAL 1 MONTH) when ‘DAY’ then DATE_SUB(NOW(),INTERVAL 1 DAY) when ‘WEEK’ then DATE_SUB(NOW(),INTERVAL 1 WEEK) end)) AND (ch.lead_id IN (85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, etc… etc…)) GROUP BY ch.lead_id, fr.frequency_time, fr.frequency_number HAVING count(ch.lead_id) >= fr.frequency_number’: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘:channel) AND (fr.frequency_time IS NOT NULL AND fr.frequency_number IS NOT N…’ at line 1 (uncaught exception) at /opt/bitnami/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 98 while running console command mautic:broadcasts:send
[2022-05-24 19:27:06] mautic.WARNING: Command mautic:broadcasts:send exited with status code 1
If you notice in the above db query I wrote “etc… etc…”. In that space were somewhere in the neighborhood of 173k ids that I removed for brevity. Although I didn’t really believe it was the issue I increased the max_allowed_packet db system variable from 16M to 64M to make sure that it would more than accommodate it. (MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables).
The funny thing is that, even before I made that change, the error messages stopped and running mautic:broadcasts:send from the command line created no error message but did nothing.
“| Channel | # sent | # failed |”
Weirder still is that it seems to send fine with a small broadcast email if I just hit send from the browser. And test emails work too.
No related errors at all in the db or system logs either. The above “Syntax error or access violation” is super confusing too because how in the world would there all of the sudden be a syntax error in code that was just working and never changed? Some bit of really bad data somewhere? And access violation? I don’t think so given what I’m seeing.
Kinda stumped, please help. Thanks in advance.